Oracle: Recordsets from Stored Procedures using RE

Special : Web Developer's Collection CD-ROM
50,000+ Web Templates, Logos, Cliparts, Scripts. Order Now!

Add to Favorites Make Home Page

20230 Online

Language Categories

Source Codes Home
Project Ideas New!
Interview Questions
FAQs Home
ASP Home: ASP Source Codes; ASP Script Directory New!; ASP .Net Script Directory New!; ASP Interview Questions; ASP FAQs; ASP How Tos
C Home: C Source Codes; C Script Directory New!; C Interview Questions; C FAQs; C How Tos
C++ Home: C++ Source Codes; C++ Script Directory New!; C++ Interview Questions; C++ FAQs; C++ How Tos
Java Home: Java Source Codes; Java Directory New!; Java Interview Questions; Java FAQs; Java How Tos
JavaScript Home: JavaScript Directory New!; JavaScript Source Codes; JavaScript FAQs; JavaScript How Tos
COBOL Home: COBOL Source Codes; COBOL FAQs; COBOL How Tos
Pascal Home: Pascal Source Codes; Pascal FAQs; Pascal How Tos
PHP Script Directory New!
Python Script Directory New!
Perl & CGI Script Directory New!
Flash Script Directory New!
CFML Script Directory New!
Remotely Hosted Scripts New!
Tools & Utilities Directory New!
XML Script Directory New!
Best Programmers: Amit Mathur; Vishal Bhardwaj; Deepesh Jain
Vyom NetWork

Our Services

Get 9,000 Interview Questions & Answers in an eBook.

9500+ Pages
9000 Question & Answers
All Tech. Categories

14 MB Content

Get it now !!

Home » ASP Home » ADO Home » Oracle: Recordsets from Stored Procedures using RE

A D V E R T I S E M E N T

Title	Oracle: Recordsets from Stored Procedures using RE
Description	For people who might be interested in the new feature of the latest OLE DB providers that allows one to return recordsets from stored procedures via ADO, here are the detailed test steps I used:
Category	ASP » ADO
Hits	366494
Code	Select and Copy the Code
1. For testing the Microsoft OLE DB Provider For Oracle version 2.5: Download the latest version of MDAC 2.5 (mdac_typ.exe) from http://www.microsoft.com/data/download_250rtm.htm Install the New MDAC by following all prompts. Restart your computer. Do the necessary stuff To create a SQLNET connect String On your NT/IIS server. I was using a Local database so I skipped this step. Create a PL/SQL package called employees_msft under the scott schema using the code below (my intention was To return all employees In a particular department, using the famous emp table): CREATE Or Replace PACKAGE employees_msft As Type empcur Is REF CURSOR; PROCEDURE GetEmpRecords(indeptno In NUMBER, p_errorcode OUT NUMBER, p_cursor OUT empcur); End employees_msft; / CREATE Or Replace PACKAGE BODY employees_msft As PROCEDURE GetEmpRecords(indeptno In NUMBER, p_errorcode OUT NUMBER, p_cursor OUT empcur) Is BEGIN p_errorcode := 0; Open p_cursor For Select FROM emp WHERE deptno = indeptno ORDER BY ename; EXCEPTION WHEN OTHERS Then p_errorcode := SQLCODE; End GetEmpRecords; End employees_msft; / Then create the following ASP file: <%@LANGUAGE="VBSCript"%> <% Option Explicit Response.Buffer = True %> <!-- #include file="/adovbs.inc" --> <% Dim objConn, objCmd, objRs Set objConn = Server.CreateObject("ADODB.Connection") Set objCmd = Server.CreateObject("ADODB.Command") Set objRs = Server.CreateObject("ADODB.RecordSet") Dim strConnection ' Use the correct SQLNET connect string!!!! strConnection = "PROVIDER=MSDAORA;DATA SOURCE=beq-local;USER ID=scott;PASSWORD=tiger;" objConn.Open strConnection objCmd.ActiveConnection = objConn objCmd.CommandType = adCmdStoredProc objCmd.CommandText = "employees_msft.getemprecords" ' I used dept 30 as an example objCmd.Parameters.Append objCmd.CreateParameter("param1", adVarChar, adParamInput, 10, "30") objCmd.Parameters.Append objCmd.CreateParameter("param2", adVarChar, adParamOutput, 10) Set objRs = objCmd.Execute %> <HTML> <HEAD> <META NAME="GENERATOR" Content="Microsoft FrontPage 4.0"> <TITLE></TITLE> </HEAD> <BODY> <B>ADO REF CURSOR DEMO Using the Microsoft OLE DB Provider For Oracle2.5</B> <P> <TABLE BORDER="1"> <TR> <TH>Emp No.</TH> <TH>Emp Name</TH> </TR> <% Do While Not objRs.EOF %> <TR> <TD><%=objRs.Fields("empno").Value%></TD> <TD><%=objRs.Fields("ename").Value%></TD> </TR> <% objRs.MoveNext Loop objRs.Close Set objRs = Nothing Set objCmd = Nothing objConn.Close Set objConn = Nothing %> </TABLE> </BODY> </HTML> See the result In your browser! 2. For testing the Oracle Provider For OLE DB version 8.1.5.2.0 Beta: Download the provider at http://technet.oracle.com/tech/nt/ole_db/ Install Oracle 8.1.5 Client software from the Oracle 8i CD On your IIS server. This Is absolutely necessary For the provider To work, even though you may be connecting To earlier versions of Oracle (I used Oracle 8.0.5.2.1). Install the provider. Again create a SQLNET connect String For your database. Create the following PL/SQL package under the scott schema: CREATE Or Replace PACKAGE employees_orcl As Type empcur Is REF CURSOR; PROCEDURE GetEmpRecords(indeptno In NUMBER, p_cursor OUT empcur, -- Notice the REF CURSOR -- parameter Is In the middle! p_errorcode OUT NUMBER); End employees_orcl; / CREATE Or Replace PACKAGE BODY employees_orcl As PROCEDURE GetEmpRecords(indeptno In NUMBER, p_cursor OUT empcur, p_errorcode OUT NUMBER) Is BEGIN p_errorcode := 0; Open p_cursor For Select FROM emp WHERE deptno = indeptno ORDER BY empno; EXCEPTION WHEN OTHERS Then p_errorcode := SQLCODE; End GetEmpRecords; End employees_orcl; / * Create the following ASP page: <%@LANGUAGE="VBSCript"%> <% Option Explicit Response.Buffer = True %> <!-- #include file="/adovbs.inc" --> <HTML> <HEAD> <META NAME="GENERATOR" Content="Microsoft FrontPage 4.0"> <TITLE></TITLE> </HEAD> <BODY> <% Dim objConn, objCmd, objRs Set objConn = Server.CreateObject("ADODB.Connection") Set objCmd = Server.CreateObject("ADODB.Command") Set objRs = Server.CreateObject("ADODB.RecordSet") Dim strConnection 'Use the correct SQLNET connect string! strConnection="PROVIDER=OraOLEDB.Oracle;DATA SOURCE=beq-local;USER ID=scott;PASSWORD=tiger;PLSQLRSet=1" objConn.Open strConnection objCmd.ActiveConnection = objConn objCmd.CommandType = adCmdStoredProc objCmd.CommandText = "employees_orcl.getemprecords" objCmd.Parameters.Append objCmd.CreateParameter("param1", adVarChar, adParamInput, 10, "30") objCmd.Parameters.Append objCmd.CreateParameter("param3", adVarChar, adParamOutput, 10) Set objRs = objCmd.Execute %> <p> <B>ADO REF CURSOR DEMO Using the Oracle Provider For OLE DB 8.1.5.2.0 Beta</B> <P> <TABLE BORDER="1"> <TR> <TH>Emp No.</TH> <TH>Emp Name</TH> </TR> <% Do While Not objRs.EOF %> <TR> <TD><%=objRs.Fields("empno").Value%></TD> <TD><%=objRs.Fields("ename").Value%></TD> </TR> <% objRs.MoveNext Loop objRs.Close Set objRs = Nothing Set objCmd = Nothing objConn.Close Set objConn = Nothing %> </TABLE> </BODY> </HTML> Again, see the result In the browser! Folks, I hope you are As excited As I am about the New feature offered by the New OLE DB providers. I know a lot of us had asked For it For so long. They (MSFT And ORCL) finally delivered. Start To use these New providers!

Related Source Codes

Script Name Author

�� VyomWorld

Resistor color code reader A.Chermarajan.

Telephone Directory dhivya

card swapping game (Mini Project) nityanand

simple hangman-pascalsource Seabert

college dirtectory (Mini Project) msridhar

Poll Application John van Meter

ASP Daily Hit Counter. Tejaskumar Gandhi

To avoid null in asp environment using sql Sami

Maklumbalas webmaster

poll John van Meter

EasyASP Template Engine. TjoekBezoer

Basic Calculator using HTML & Javascript. Patrick M. D Souza

What servers support ASP ? VyomWorld

What is ASP? VyomWorld

A D V E R T I S E M E N T

Google Groups Subscribe to SourceCodesWorld - Techies Talk

Email:

Free eBook - Interview Questions: Get over 1,000 Interview Questions in an eBook for free when you join JobsAssist. Just click on the button below to join JobsAssist and you will immediately receive the Free eBook with thousands of Interview Questions in an ebook when you join.

Free Tutorials - HTML, Java, SQL, PHP, ASP, Perl, SQL Tutorials
Discuss Programming Problems and Freshers Jobs related issues here.
Discuss Final Year Project Ideas here.