Tuesday, April 17, 2007

Returning oracle table object



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;
/