Microsoft KB Archive/240337

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

Article ID: 240337

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Visual InterDev 6.0 Standard Edition

-



This article was previously published under Q240337



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.



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
 Add an ASP page to the Web project: right-click the project in the Project Explorer, click Add, and then select Active Server Page. Add a Recordset DTC to the ASP page. To do so:  From the View menu, select Toolbox, and then select Design-Time Controls. Drag a recordset DTC to a position following the  tag on your ASP page. A dialog box appears and asks if the Visual InterDev Scripting Object Model should be implemented. Click yes. </li> Right-click the recordset DTC, and then select Properties. Select SQL Statement.</li> In the SQL Statement text box, type the following:

<pre class="fixed_text">{call packperson.allperson({resultset 9, ssn, lname, fname})}

</li> Add a Grid DTC to the ASP page. To do so:  From the Design-Time Controls Toolbox, select Grid DTC.</li> Drag the GRID DTC onto the ASP page to a position following the recordset DTC.</li> Bind it to the recordset DTC. To do so, right-click the Grid DTC, and then select Properties.</li> Click the Data tab, and then select your recordset DTC (for example, Recordset1) as the Recordset.</li></ol> </li> Save the ASP page and view it in the browser.</li></ol>

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.  Add an ASP page to the Web project.</li> 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.</li> Right-click the recordset DTC, and then select Properties.</li> On the General tab, select SQL Statement.</li> In the SQL Statement text box, type the following:

<pre class="fixed_text">{call packperson.oneperson(?, {resultset 2, ssn, lname, fname})}

</li> 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):

<pre class="fixed_text">'555662222'

</li> <li>Add a Grid DTC to the ASP page and bind it to the recordset DTC.</li> <li>Save the ASP page and view it in your browser.</li></ol>

Only the one record with the "SSN = '555662222'" is returned from the Oracle stored procedure (and only the columns ssn, lname, and fname appear).

<div class="references_section">