Oracle Stored Procedure that returns a recordset and ASP code to retrieve this recordset
Scroll to the bottom to copy the source code from a textarea.
Source Code:
1. create package header
CREATE OR REPLACE PACKAGE MY_PACKAGE
AS
-- ref cursor to return procedure results with an unlimited number of records
TYPE CUR_DATA IS REF CURSOR;
PROCEDURE MY_PROCEDURE(OUT_DATA OUT CUR_DATA);
END MY_PACKAGE;
2. create package body:
CREATE OR REPLACE PACKAGE BODY MY_PACKAGE
AS
PROCEDURE MY_PROCEDURE(OUT_DATA OUT CUR_DATA)
IS
/* this procedure returns a Ref Cursor with all the requested parameters
calling the stored procedure from an asp page (and anywhere else)
does not require posting a predefined number of records */
BEGIN
OPEN OUT_DATA FOR
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEE
ORDER BY LAST_NAME;
END MY_PROCEDURE;
END MY_PACKAGE;
3. call from asp:
strSQL = "{call MY_PACKAGE.MY_PROCEDURE}"
Set rsData = objConn.Execute (strSQL)
|
To copy the source code, click inside the textbox => Ctrl-A => Ctrl-C
|