Oracle| stored procedure ASP invokes an Oracle stored procedure and returns the result set
Look over the Internet many ASP call Oracle method, but there is no special very direct things, so summed up the things of the last one to come up with a way of their own, feel more popular so hereby released
###################################################
Oracle's stored procedures
###################################################
--Create Baotou
Create or Replace package pck_as
As
Type t_product_id is table of number
Index by Binary_integer;
Type t_serial_no is table of varchar (300)
Index by Binary_integer;
Type t_buy_date is table of date
Index by Binary_integer;
Procedure ALLSFC
(
product_id out t_product_id,
Serial_no out T_serial_no,
Buy_date out T_buy_date
);
--procedure ALLSFC1;
End Pck_as;
--Create the package body
Create or replace package body Pck_as
As
Procedure ALLSFC
(
product_id out t_product_id,
Serial_no out T_serial_no,
Buy_date out T_buy_date
)
Is
Cursor Cur_sfc_tbl_product_reg is
Select Product_id,serial_no,buy_date from Sfc_tbl_product_reg;
I number default 1;
Begin
For Onrecord in Cur_sfc_tbl_product_reg
Loop
PRODUCT_ID (i): = onrecord.product_id;
Serial_no (i): = Onrecord.serial_no;
Buy_date (i): = Onrecord.buy_date;
I: = i + 1;
End Loop;
End
End
###################################################
ASP's Call
###################################################
<meta http-equiv= "Content-type" content= "text/html; charset=gb2312 ">
<title> Untitled Document </title>
<body>
<%
Dim Str_ora
Dim Objconn_ora
Str_ora = "Driver={microsoft ODBC for Oracle}; Server=ora192;uid=dwuser;pwd=family.samsung.com.cn "
Set Objconn_ora = Server. CreateObject ("Adodb.connection")
objconn_ora.connectionstring = Str_ora
Objconn_ora. CursorLocation = 3
Objconn_ora.open
Dim strSQL
Set rs1 = Server. CreateObject ("ADODB.") RecordSet ")
strSQL = "{call PCK_AS.ALLSFC ({resultset 90000, product_id, Serial_no, buy_date})}"
Set objcomm1 = Server. CreateObject ("Adodb.command")
Objcomm1.activeconnection = Objconn_ora
Objcomm1.commandtype = 1
Objcomm1.commandtext = strSQL
Set rs1 = Objcomm1.execute
While not rs1.eof
Response. Write (rs1 (0) & "|" & Rs1 (1) & "|" & Rs1 (2) & "|<br>")
Rs1.movenext
Wend
%>
</body>