How To Execute a Stored Procedure on an Oracle Server

• Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0

SUMMARY

This article describes how to use SQL pass-through technology to execute a stored procedure on an Oracle server. Oracle does not use the standard SQL EXECUTE command to execute stored procedures.

MORE INFORMATION

This example executes a stored procedure called SP_MYPROC. It is assumed that you have a valid ODBC datasource configured for the Oracle server. This example assumes that you have created a valid datasource called "ORADSN." This article does not cover the steps necessary to create a valid datasource. This article shows the proper syntax necessary to call a stored procedure called "SP_MYPROC," which has no parameters, using Visual FoxPro. For information on creating stored procedures in Oracle see your Oracle documentation:

************************************* ** Description of variables used ** * h = handle * ORADSN = datasource name

* USERID   = user id
* PWD        = password

   h=SQLConnect("ORADSN","USERID","PWD")   && Establish a connection

   IF h > 0
      WAIT WINDOW "Connection Successful" NOWAIT
      =SQLExec(h,"{Call SP_MYPROC}")       && Execute the stored procedure
      =SQLDisconnect(h)                    && Terminate the connection
   ELSE
      WAIT WINDOW "Connection Failed"
   ENDIF