How To Call a Parameterized Stored Procedure in Oracle from a Recordset DTC

Article translations Article translations
Article ID: 240337 - View products that this article applies to.
This article was previously published under Q240337
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

Summary

This article shows how to use a Recordset Design-Time Control (DTC) to call an Oracle stored procedure, pass parameters, and retrieve recordsets.

This article assumes that you are familiar with the procedure to use ActiveX Data Objects (ADO) in an Active Server Page (ASP) page to call an Oracle package.

For more information on calling an Oracle package in ASP, see the "References" section of this article.

This article also assumes that Internet Information Server (IIS) and the development workstation are configured properly to connect to an Oracle database server. See "References" for more information on configuring IIS to connect to Oracle.

The procedure described in this article uses the PERSON table and PACKPERSON package. See "References" for instructions on their creation. You must create the PERSON table and PACKPERSON package before you use the procedure.

More information

To Add a Data Connection

  1. From the Project menu, choose Add Data Connection.
  2. In the Data Link Properties dialog box, select Microsoft OLE DB Provider for ODBC drivers, and then click Next >>.
  3. Choose Use data source name and provide the system DSN that was created to connect to the Oracle database. (For information on how to create ODBC DSNs to connect to Oracle databases, see the "References" section).
  4. Type a User name and Password. Click the Test Connection button to make sure that you can connect to your Oracle database.
Once you have established a successful data connection in the Visual InterDev Web project, you can start to create ASP pages to call nonparameterized and parameterized stored procedures from your Oracle database server.

To Call a Nonparameterized Stored Procedure in Oracle

  1. Add an ASP page to the Web project: right-click the project in the Project Explorer, click Add, and then select Active Server Page.
  2. Add a Recordset DTC to the ASP page. To do so:
    1. From the View menu, select Toolbox, and then select Design-Time Controls.
    2. Drag a recordset DTC to a position following the <BODY> tag on your ASP page. A dialog box appears and asks if the Visual InterDev Scripting Object Model should be implemented.
    3. Click yes.
  3. Right-click the recordset DTC, and then select Properties. Select SQL Statement.
  4. In the SQL Statement text box, type the following:
    {call packperson.allperson({resultset 9, ssn, lname, fname})}
    						
  5. Add a Grid DTC to the ASP page. To do so:
    1. From the Design-Time Controls Toolbox, select Grid DTC.
    2. Drag the GRID DTC onto the ASP page to a position following the recordset DTC.
    3. Bind it to the recordset DTC. To do so, right-click the Grid DTC, and then select Properties.
    4. Click the Data tab, and then select your recordset DTC (for example, Recordset1) as the Recordset.
  6. Save the ASP page and view it in the browser.
This brings up all records from the Oracle stored procedure and only the specific columns ssn, lname, and fname.

Call a Parameterized Stored Procedure in Oracle

NOTE: See Microsoft Knowledge Base article Q240338 in the "References" section of this article if you want to call a parameterized stored procedure in Oracle.
  1. Add an ASP page to the Web project.
  2. Add a recordset DTC to the ASP page. A dialog box appears and asks if you want the Visual InterDev Scripting Object Model to be implemented. Click yes.
  3. Right-click the recordset DTC, and then select Properties.
  4. On the General tab, select SQL Statement.
  5. In the SQL Statement text box, type the following:
    {call packperson.oneperson(?, {resultset 2, ssn, lname, fname})}
    						
  6. Select the Parameters tab of the Recordset DTC dialog box. In the Value column of the Values for parameters frame, type the following (include the quotation marks):
    '555662222'
    						
  7. Add a Grid DTC to the ASP page and bind it to the recordset DTC.
  8. Save the ASP page and view it in your browser.
Only the one record with the "SSN = '555662222'" is returned from the Oracle stored procedure (and only the columns ssn, lname, and fname appear).

References

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
229919 How To Retrieve a Recordset from an Oracle Stored Procedure using ADO on ASP
240338 PRB: Error Calling an Oracle Parameterized Query or Stored Procedure Using the Visual Interdev 6.0 Recordset DTC
For additional information on why you must use an Oracle Package instead of an Oracle stored procedure, click the article number below to view the article in the Microsoft Knowledge Base:
167225 How To Access an Oracle Database Using RDO

Please see the Microsoft Universal Data Access Web site for additional information on ADO and to download the latest version of the Microsoft Data Access Components (MDAC):
http://msdn.microsoft.com/en-us/data/aa937729.aspx

Properties

Article ID: 240337 - Last Review: November 2, 2013 - Revision: 4.0
Applies to
  • Microsoft Visual InterDev 6.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbctrl kbhowto kboracle kbstoredproc KB240337

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com