PL/SQL Table as a Recordset in ASP
384424May 13 2005 — edited May 14 2005Dear 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