syntax to run PL/SQL stored proc via ms access pass through query

[Edwinah63]

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_ALLx, '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_ALLx, '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

 

[Albert D. Kallal]

Hum, a pass-through should work here.

Perhaps you could try the same syntax that works in ado.

call ttadm.AST005_CLIENTRPT_ALLx,
'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..

 

[Van T. Dinh]

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_ALLx, '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_ALLx, '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',