KB149882 - How To Execute a Stored Procedure on an Oracle Server

Article ID: 149882

Article Last Modified on 3/14/2005

Microsoft Visual FoxPro 3.0 Standard Edition
Microsoft Visual FoxPro 3.0b Standard Edition
Microsoft Visual FoxPro 5.0 Standard Edition
Microsoft Visual FoxPro 6.0 Professional Edition
Microsoft Data Access Components 2.5

This article was previously published under Q149882

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

NOTE: The 32-bit Visigenic Oracle ODBC driver that ships with Visual FoxPro 3.0b and many other 32-bit Microsoft products does not support the execution of stored procedures.

NOTE: As of Dec-09-1998, Visigenic was acquired by INPRISE, Inc.

For information about how to contact INPRISE, click the appropriate article number in the following list to view the article in the Microsoft Knowledge Base:

65416 Hardware and Software Third-Party Vendor Contact List, A-K

60781 Hardware and Software Third-Party Vendor Contact List, L-P

60782 Hardware and Software Third-Party Vendor Contact List, Q-Z

Additional query words: ODBC

Keywords: kbhowto kbinterop kbcode KB149882