Add to Favorites    Make Home Page 4870 Online  
 Language Categories  
 Our Services  

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

Search Projects & Source Codes:

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 336512
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 SQL*NET 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 SQL*NET 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 SQL*NET 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 SQL*NET 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.

New! Click here to Add your Code!


ASP Home | C Home | C++ Home | COBOL Home | Java Home | Pascal Home
Source Codes Home Page

 Advertisements  

Google Search

Google

Source Codes World.com is a part of Vyom Network.

Vyom Network : Web Hosting | Dedicated Server | Free SMS, GRE, GMAT, MBA | Online Exams | Freshers Jobs | Software Downloads | Interview Questions | Jobs, Discussions | Placement Papers | Free eBooks | Free eBooks | Free Business Info | Interview Questions | Free Tutorials | Arabic, French, German | IAS Preparation | Jokes, Songs, Fun | Free Classifieds | Free Recipes | Free Downloads | Bangalore Info | Tech Solutions | Project Outsourcing, Web Hosting | GATE Preparation | MBA Preparation | SAP Info | Software Testing | Google Logo Maker | Freshers Jobs

Sitemap | Privacy Policy | Terms and Conditions | Important Websites
Copyright ©2003-2023 SourceCodesWorld.com, All Rights Reserved.
Page URL: http://www.sourcecodesworld.com/source/show.asp?ScriptID=16


Download Yahoo Messenger | Placement Papers | Free SMS | C Interview Questions | C++ Interview Questions | Quick2Host Review