Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

PL/SQL Table as a Recordset in ASP

384424May 13 2005 — edited May 14 2005
Dear Friends,
I have created Stored Procedure in Oracle which takes two input parameter and one output parameter,
now the Output parameter in procedure is an PL/SQL Table type which stores multiple records now i want to get this output parameter result into Recordset of ADO. can any body help me in solving my problem.

Package Define:
-----------------------------------------
create or replace package Employees as
TYPE tblEmp IS TABLE OF VARCHAR2(5); PROCEDURE GetEmployee(strEmpid in varchar2,strParentid in varchar2,orEmp out tblEmp);
PROCEDURE GetEmployeeSub2(strEmpid varchar2, strParentid varchar2);
PROCEDURE GetEmployeeSub1(strEmpid varchar2, strParentid varchar2);
PROCEDURE GetEmployeeEmp(strEmpid varchar2,strParentid varchar2);
end Employees;

-----------------------------------------------------
Below is the code I have written in ASP.

dim rs1
dim strPara
dim mCommand
set mCommand = Server.CreateObject("ADODB.Command")
dim objcnx set objcnx = server.CreateObject("ADODB.CONNECTION")
OBJCNX.ConnectionString = "driver={Microsoft ODBC for Oracle};" & _
"server=local;UID=test;PWD=test;"
OBJCNX.CursorLocation = 3
OBJCNX.OPEN

IF OBJCNX.STATE = 1 THEN RESPONSE.Write("CONNECTED") END IF

with mCommand
set .ActiveConnection = objCnX
.CommandText = "{call Employees.GetEmployee(?,?, {resultset 10000, orEmp})}" .CommandType = 4
.Parameters.Append .CreateParameter("strEmpid", 200, 1, 5, Session("PEmpID"))
.Parameters.Append .CreateParameter("strParentid", 200, 1, 5, "0")

response.Write(.Parameters.count)
Set rs1 = .Execute()
End With

strPara = ""
if not rs1.eof and not rs1.bof then
while not rs1.eof
strPara = strPara & "'" & rs1.fields(0) & "',"
rs1.movenext
wend end
if strPara = left(strPara,len(strPara)-1)
end if

Regards Bhavesh Solanki
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 11 2005
Added on May 13 2005
6 comments
652 views