If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
syntax to run PL/SQL stored proc via ms access pass through query
Hi everyone,
very quick question: am trying to create a pass through query (not in a code module, just the "point and click" new query/query/sql specific/pass through thang) to run an oracle stored proc. am using msaccess 2000/ microsoft odbc for oracle driver/oracle 9i. keeps coming back to me saying that the syntax is incorrect. here's the call syntax in sql plus: exec TTADM.AST005_CLIENTRPT_ALL(:x, 'AA','ZZ','AA','ZZ', '11/06/2004','08:00', '11/06/2004', '09:00'); where :x is the variable for the cursor to return to. and here's the call syntax in vb6/ADO: {call ttadm.AST005_CLIENTRPT_ALL ( {resultset 0, cur}, 'AA','ZZ','AA','ZZ', '11/06/2004','08:00', '11/06/2004', '09:00' ) } have tried: exec TTADM.AST005_CLIENTRPT_ALL(:x, 'AA','ZZ','AA','ZZ', '11/06/2004','08:00', '11/06/2004', '09:00') exec TTADM.AST005_CLIENTRPT_ALL('AA','ZZ','AA','ZZ', '11/06/2004','08:00', '11/06/2004', '09:00') TTADM.AST005_CLIENTRPT_ALL('AA','ZZ','AA','ZZ', '11/06/2004','08:00', '11/06/2004', '09:00') all no good is it possible to use a non-code pass through query from access to an oracle stored proc? if so what's the syntax in ms access?? regards Edwinah63 |
#2
|
|||
|
|||
syntax to run PL/SQL stored proc via ms access pass through query
Hum, a pass-through should work here.
Perhaps you could try the same syntax that works in ado. call ttadm.AST005_CLIENTRPT_ALL(:x, 'AA','ZZ','AA','ZZ','11/06/2004','08:00', '11/06/2004', '09:00'); I would also try it with out the ";". But, you are on the right track..and simply using execute MyStoreProc generally works (assuming that the server based produced has a "execute" command! Do you know what the command looks like to run from the "command" prompt on the oracle console? (get that working, and then use the SAME syntax in the pass-through).... It might even be somting like single vs double quotes. However, whatever you can type at the console and works, should work with a pass through.. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.attcanada.net/~kallal.msn |
#3
|
|||
|
|||
syntax to run PL/SQL stored proc via ms access pass through query
I am more familiar with SQL Server and the Pass-
ThroughQuery to run the SP is EXEC [SpName] {Arg1}, {Arg2}, ... HTH Van T. Dinh MVP (Access) -----Original Message----- Hi everyone, very quick question: am trying to create a pass through query (not in a code module, just the "point and click" new query/query/sql specific/pass through thang) to run an oracle stored proc. am using msaccess 2000/ microsoft odbc for oracle driver/oracle 9i. keeps coming back to me saying that the syntax is incorrect. here's the call syntax in sql plus: exec TTADM.AST005_CLIENTRPT_ALL(:x, 'AA','ZZ','AA','ZZ', '11/06/2004','08:00', '11/06/2004', '09:00'); where :x is the variable for the cursor to return to. and here's the call syntax in vb6/ADO: {call ttadm.AST005_CLIENTRPT_ALL ( {resultset 0, cur}, 'AA','ZZ','AA','ZZ', '11/06/2004','08:00', '11/06/2004', ' 09:00' ) } have tried: exec TTADM.AST005_CLIENTRPT_ALL(:x, 'AA','ZZ','AA','ZZ', '11/06/2004','08:00', '11/06/2004', '09:00') exec TTADM.AST005_CLIENTRPT_ALL('AA','ZZ','AA','ZZ', '11/06/2004','08:00', '11/06/2004', '09:00') TTADM.AST005_CLIENTRPT_ALL ('AA','ZZ','AA','ZZ', '11/06/2004','08:00', '11/06/2004', '09:00') all no good is it possible to use a non-code pass through query from access to an oracle stored proc? if so what's the syntax in ms access?? regards Edwinah63 . |
Thread Tools | |
Display Modes | |
|
|