How to trigger an ORACLE stored procedure from VFP? 1

[petervfp]

I've already posted in the VFP forum and was lead here. Thank you in advance for any help.

I have an ORACLE database that I'm hooked to via VFP 6.0
I can create remote views or use SQLExec() to fire off pass-through SQL statements against the ORACLE tables. I need to trigger/execute some ORACLE stored procedures from a VFP program.

I can hook to and send SQL queries to my ORACLE database no problem. Code below works fine

nHandle = SQLConnect('db','userid','password')
IF nHandle > -1       * connection established
 SQLFlag = SQLExec(nHandle,'SELECT * FROM tblname','MyCur')
ENDIF

The book says that the code below should trigger a stored proc:

nHandle = SQLConnect('db','userid','password')
IF nHandle > -1       * connection established
 SQLFlag = SQLExec(nHandle,'StProcNme')
ENDIF

But SQLFlag always comes back as -1 indicating failure. I've been told that for SQL Server the code below works:

SQLFlag = SQLExec(nHandle,'Exec StProcNme')


Does ORACLE need a command to preceed the stored procedure name (I tried 'Exec' and it crapped out)? How would you fire off a stored proc from SQL*Plus?

I've tried quite a few tests and this is definitely not related to access rights etc. I'm using a GOD level administration account on the ORACLE db.

I've already told everyone that this can be done. I have to make this work. Any help would be appreciated. Can someone save me the cost of a 50$ ORACLE book?

Peter in Ottawa

 

[rcurva]

Peter,

I am not an expert in VFP but I would like you to test your stored procs first. Logon to an sql*plus and try running your stored procs from there.

Your friends are correct you can run stored procs by placing an EXEC in front of the proc name but that is done in sqlplus, I dont know with VFP.

For ex:

SQL> exec StProcNme
SQL>

It could be that the stored procedure is INVALID, meaning, it needs to be debugged and recompiled.

Do this and see what happens.


Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[sem]

Try to use
SQLFlag = SQLExec(nHandle,'begin StProcNme; end;')

Of course if you procedure needs no parameters.

 

[petervfp]

Thank You SEM!!! Success at last. This has been a tough one and I've been using many resources attempting to find something that works. This did the trick. I owe you a beer!

Peter