Oracle stored procedure

[jon]

Hi.

Can't get stored procedure to execute from withing MS Access. I have a
pass-through query defined with the following settings:

connection string: ODBC;DSN=PPRD;SERVER=PPRD; -- prompt for User/Pwd

Returns Records: No

The query reads:
execute jshaw.jsmailroomlist.refreshaddresses('200510');

This is a stored procedure that builds a table on the server.

I get the message:

ORA-00900: invalid SQL statement (#900).

Any help?

Thanks in advance!

 

[Brian]

Hi.

Can't get stored procedure to execute from withing MS Access. I have a
pass-through query defined with the following settings:

connection string: ODBC;DSN=PPRD;SERVER=PPRD; -- prompt for User/Pwd

Returns Records: No

The query reads:
execute jshaw.jsmailroomlist.refreshaddresses('200510');

This is a stored procedure that builds a table on the server.

I get the message:

ORA-00900: invalid SQL statement (#900).

Any help?

Thanks in advance!

Clearly the returned error is an Oracle error, so an Oracle newsgroup is
more likely to be productive.

 

[jon]

Actually, this error occurs before the query gets passed to Oracle. The full
text of the error message reads:
[Microsoft][ODBC driver for Oracle][Oracle]ORA-00900:invalid SQL statement
(#900). Access doesn't like something about this format or the characters in
the line???

 

[jon]

For any who followed this thread,

I found that by changing 'execute' to 'call' the p-t query executed even
without the trailing ';' and the schema name:

call jsmailroomlist.refreshaddresses('200510')

So you can execute an Oracle stored procedure from within Access!!

Actually, this error occurs before the query gets passed to Oracle. The full
text of the error message reads:
[Microsoft][ODBC driver for Oracle][Oracle]ORA-00900:invalid SQL statement
(#900). Access doesn't like something about this format or the characters in
the line???

Clearly the returned error is an Oracle error, so an Oracle newsgroup is
more likely to be productive.

 

[Brian]

Actually, this error occurs before the query gets passed to Oracle. The full
text of the error message reads:
[Microsoft][ODBC driver for Oracle][Oracle]ORA-00900:invalid SQL statement
(#900). Access doesn't like something about this format or the characters in
the line???

Access doesn't generate errors beginning "ORA", and nor does Access parse,
validate or in any way attempt to interpret a passthrough query. You could
put your grocery shopping list in the query if you wanted, Access wouldn't
know or care.

Maybe the Oracle ODBC driver is generating the error rather than the Oracle
server, but either way, it's not an Access problem.

 

[Brian]

For any who followed this thread,

I found that by changing 'execute' to 'call' the p-t query executed even
without the trailing ';' and the schema name:

call jsmailroomlist.refreshaddresses('200510')

So you can execute an Oracle stored procedure from within Access!!

Of course, but you have to use a syntax that is supported by Oracle and the
ODBC driver. That's what makes it a passthrough query: Access simply passes
it through. If this were not the case, you wouldn't be able to execute a
stored procedure at all, because Access doesn't have stored procedures and
neither "EXECUTE" nor "CALL" has any meaning in Access SQL.

To put it another way, once you start talking about the difference between
"execute" and "call", and schema names and whatever, you are talking pure
Oracle, not Access.