I need to get a recordset from an Oracle Package by giving it some
parameters.
right now I can get a recordset but I can not pass input parameters
Here is the code:
<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<H1>Llamando a un Paquete</H1>
<%
dim objConn
set objConn=CreateObject("ADODB.Connection")
dim objComm
set objComm=CreateObject("ADODB.Command")
dim objRS
set objRS=CreateObject("ADODB.Recordset")
dim strConnect
dim strSQL
strConnect="driver={Microsoft ODBC for
Oracle};SERVER=MiServer;UID=scott;pwd=tiger"
strSQL="{call OraResultset.empResultSet
({resultset80,o_empno,o_ename,o_job,o_mgr,o_hiredate,o_sal,o_comm,o_deptno}
)}"
objConn.ConnectionString=strConnect
objConn.CursorLocation=3
objConn.Open
set objComm.ActiveConnection=objConn
objComm.CommandText=strSQL
objComm.CommandType =1
objRS.CursorType=3
objRS.LockType =1
set objRS.Source =objComm
objRS.Open
dim objField
Response.Write"<TABLE BORDER=1>"
Response.Write"<THEAD>"
Response.Write"<TR>"
for each objField in objRS.Fields
Response.Write"<TH>" & objField.name & "</TH>"
next
Response.Write"</TR>"
Response.Write"<TBODY>"
Do while Not objRS.EOF
Response.Write"<TR>"
for each objField in objRS.Fields
Response.Write"<TD>" & objField.value & "</TD>"
next
Response.Write"</TR>"
objRS.MoveNext
loop
Response.Write"</Table>"
objRS.MoveFirst
%>
</BODY>
</HTML>
CREATE OR REPLACE PACKAGE OraResultSet as
type tbl_empno is table of emp.EMPNO%type
index by binary_integer;
type tbl_ename is table of emp.ename%type
index by binary_integer;
type tbl_job is table of emp.job%type
index by binary_integer;
type tbl_mgr is table of emp.mgr%type
index by binary_integer;
type tbl_hiredate is table of emp.hiredate%type
index by binary_integer;
type tbl_sal is table of emp.sal%type
index by binary_integer;
type tbl_comm is table of emp.comm%type
index by binary_integer;
type tbl_deptno is table of emp.deptno%type
index by binary_integer;
procedure empResultset(o_empno out tbl_empno,
o_ename out tbl_ename,
o_job out tbl_job,
o_mgr out tbl_mgr,
o_hiredate out tbl_hiredate,
o_sal out tbl_sal,
o_comm out tbl_comm,
o_deptno out tbl_deptno);
procedure showResult;
end OraResultSet;
/
CREATE OR REPLACE package body OraResultSet as
procedure empResultSet(o_empno out tbl_empno,
o_ename out tbl_ename,
o_job out tbl_job,
o_mgr out tbl_mgr,
o_hiredate out tbl_hiredate,
o_sal out tbl_sal,
o_comm out tbl_comm,
o_deptno out tbl_deptno)
is
cursor emp_cur is select * from emp;
cnt number default 1;
begin
for emp_rec in emp_cur
loop
o_empno(cnt):=emp_rec.empno;
o_ename(cnt):=emp_rec.ename;
o_job(cnt):=emp_rec.job;
o_mgr(cnt):=emp_rec.mgr;
o_hiredate(cnt):=emp_rec.hiredate;
o_sal(cnt):=emp_rec.sal;
o_comm(cnt):=emp_rec.comm;
o_deptno(cnt):=emp_rec.deptno;
cnt:= cnt + 1 ;
end loop;
end;
procedure showresult is
fldempno tbl_empno;
fldename tbl_ename;
fldjob tbl_job;
fldmgr tbl_mgr;
fldhiredate tbl_hiredate;
fldsal tbl_sal;
fldcomm tbl_comm;
flddeptno tbl_deptno;
numofrows number;
cnt number;
cursor getCount is select count(*) from emp;
begin
open getCount;
fetch getcount into numofrows;
close getCount;
dbms_output.PUT_LINE('Num: '||numofrows);
oraresultset.empResultSet(fldempno,fldename,fldjob,
fldmgr,fldhiredate,fldsal,fldcomm,flddeptno);
for loop_index in 1.. numofrows
loop
dbms_output.PUT_LINE('Record '||loop_index);
dbms_output.PUT_LINE('EmpNo: '||fldempno(loop_index));
dbms_output.PUT_LINE('Name: '||fldename(loop_index));
dbms_output.PUT_LINE('Job:'||fldjob(loop_index));
dbms_output.PUT_LINE('Mgr: '||fldmgr(loop_index));
dbms_output.PUT_LINE('HierDate: '||fldhiredate(loop_index));
dbms_output.PUT_LINE('Sal:'||fldsal(loop_index));
dbms_output.PUT_LINE('Comm: '||fldcomm(loop_index));
dbms_output.PUT_LINE('DeptNo: '||flddeptno(loop_index));
dbms_output.PUT_LINE(' ');
end loop;
end showresult;
end ;
/
View Cart
