Showing posts with label Oracle Table Object. Show all posts
Showing posts with label Oracle Table Object. Show all posts

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