Tuesday, April 17, 2007
Returning oracle table object
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
Returning table object from oracle procedure or function
Creating Package and Package body
CREATE OR REPLACE PACKAGE PKG_SAMPLE AS
TYPE TY_TAB_OBJECT IS TABLE OF VARCHAR2(100);
PROCEDURE PR_SAMPLE(TY_TAB_OBJECT_INST OUT TY_TAB_OBJECT);
END PKG_SAMPLE;
/
CREATE OR REPLACE PACKAGE BODY PKG_SAMPLE AS
PROCEDURE PR_SAMPLE(TY_TAB_OBJECT_INST OUT TY_TAB_OBJECT) IS
EMP_OBJ VARCHAR2(100);
BEGIN
TY_TAB_OBJECT_INST := TY_TAB_OBJECT();
FOR SAMPLE_CURSOR IN (SELECT EMPID, EMPNAME, SALARY FROM EMPLOYEE
WHERE DEPTID IN ('0004','0005','0007')
ORDER BY EMPID)
LOOP
TY_TAB_OBJECT_INST.EXTEND;
EMP_OBJ := SAMPLE_CURSOR.EMPID ' -> ' SAMPLE_CURSOR.EMPNAME ' -> ' SAMPLE_CURSOR.SALARY;
TY_TAB_OBJECT_INST(TY_TAB_OBJECT_INST.LAST) := EMP_OBJ;
END LOOP;
END PR_SAMPLE;
END PKG_SAMPLE;
/
Ananymous block which is used to extract the data from the table object returned by the oracle procedure,
DECLARE
TY_TAB_OBJECT_INST PKG_SAMPLE.TY_TAB_OBJECT;
BEGIN
PKG_SAMPLE.PR_SAMPLE(TY_TAB_OBJECT_INST);
IF TY_TAB_OBJECT_INST.COUNT > 0 THEN
FOR I IN TY_TAB_OBJECT_INST.FIRST..TY_TAB_OBJECT_INST.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(TY_TAB_OBJECT_INST(I));
END LOOP;
END IF;
END;
/
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment