Hi Guys,

Anyone have experience of calling an oracle procedure using SQL via ODBC?

I am trying to accomplish this but get the folling ODBC error:

"ORACLEODBC Syntax error or access violation"

Anyone any ideas as to why this might be?

Cheers, Rich

Richard,

What syntax are you using to call the SP? Furthermore, does the user ID you are using to connect to the Oracle database have execute permissions for the SP, and appropriate privileges for any internal SQL commands the SP might be calling?

Are you passing all of the required parameters to the SP, and are they the correct data type? Have you tried calling the SP from another remote environment (Visual Basic, Access, etc.) to verify that the SP is working correctly when remotely called?

msorrel@greyhound.com

Greyhound Lines, Inc.

Hi Matt

I think our main questions are much simpler "can we call a stored procedure from OI?" and "has anyone else done ever done this sort of thing already?". If the answers are both yes we will investigate the privilege issues (and the syntax of the SP) further otherwise we will abandon this route and either build some sort of join table to do the job or bury the SQL from the stored procedure in OI.

Yes, it is possible to call stored procedures.

look at the QryMethod function

Bob

Thanks, we have been using the correct query method but with little joy. The customer has now admitted that the Oracle database we are trying to link to is 7.0 so we're pretty sure that there is some intermediary software component (the stored proc component) that interacts with the standard communication object and does most of the donkey work, unfortunately, we're also pretty sure no one there really understands what this component does and how it does it, which means we could be opening a can of worms.

Not specifically for Oracle 7 but,

We had similar problems with crystal reports that were developed to connect to oracle 8.1.5. @ 8.1.5 we used the oracle provided odbc drivers. After upgrading to oracle 8.1.7 none of the crystal reports would work.

Finally after much hair loss we switched to Microsoft's oracle odbc driver, we could never get the oracle 8.1.7 drivers to function as advertised. After the switch we had to change some connection parameters due to permissions/etc (I think this was back in 2002)

Rich

Thanks for that, when puch came to shove the customer couldn't even demonstrate the SP working correctly via TOAD so we've had to throw the towel in on the SP route and gone for a much simpler approach, the customer has build a new table for us to update and they will do more complex updating via triggers in that table.

Michael,

Out of curiosity – did you try the Openinsight Native Oracle driver, rather than ODBC? I suspect it is vintage 6.x or so.

Bob

View this thread on the forum...