3 Replies Latest reply: Sep 4, 2014 11:18 AM by rp0428 RSS

    Pipe row function to return multiple column, all thing should be handle in single package or PL/SQL unit

    Prashant Dabral

      I have a SQL statement which have to be executed from schema "A" only because of security issues. Now i want to query to be executed from any schema other than "A".

      For this i've wrote a package where a fucntion will get executed within schema "A" from any schema.

       

      but other Schema want it to achieve through SQL only.

      To do so i've create below mentioned package, but it showed me following error.

      I need guidance to take this approach forward and eliminate error and bug from code.

       

      CREATE OR REPLACE PACKAGE PKG_ODI_APEX
      AS
      TYPE AnEntry IS RECORD (
            term    VARCHAR2(20),
            meaning VARCHAR2(200)
      );
      TYPE T IS TABLE OF AnEntry;
            FUNCTION prodFunc RETURN T  PIPELINED;
      END;
      
      CREATE OR REPLACE PACKAGE BODY PKG_ODI_APEX
      AS
            FUNCTION prodFunc RETURN  --test_tab
              T  PIPELINED
             IS
             BEGIN
      
              FOR i in 1 .. 5
                  LOOP
                          PKG_ODI_APEX.t.term    :=i;
                          PKG_ODI_APEX.t.meaning:='data pushed on row '||i ;
                    PIPE ROW (T);
                  END LOOP;
                  RETURN;
             END;
      END;
      
      
      
      
      

       

      Any help / guidance would be much appreciated.

        • 1. Re: Pipe row function to return multiple column, all thing should be handle in single package or PL/SQL unit
          Prashant Dabral

          Hi All,

           

           

          I've done rectification in code and fix the error and bug.

          Hope this piece of code can help you in any ways.

           

           

          CREATE OR REPLACE PACKAGE BODY pkg_odi_apex
          AS
             l_row   anentry;
          
          
             FUNCTION prodfunc
                RETURN                                                       --test_tab
                      t PIPELINED
             IS
             BEGIN
                FOR i IN 1 .. 5
                LOOP
                   l_row.term := i;
                   l_row.meaning := 'data pushed on row ' || i;
                   PIPE ROW (l_row);
                END LOOP;
          
          
                RETURN;
             END;
          END;
          

           

          Regads

          Prashant

          • 2. Re: Pipe row function to return multiple column, all thing should be handle in single package or PL/SQL unit
            2681844

            Prashant, I guess the prodfunc function you cant use outside of this package. Because of the ANENTRY scope available in this procedure. If you need to access the this function outside of this package you need to create the ANENTRY object. And use the same in this pkg. Thanks VK

            • 3. Re: Pipe row function to return multiple column, all thing should be handle in single package or PL/SQL unit
              rp0428

              I have a SQL statement which have to be executed from schema "A" only because of security issues. Now i want to query to be executed from any schema other than "A".

              So create a VIEW in schema A that uses that SQL statement.

               

              Then grant SELECT privileges on the view to other users.

               

              Your example doesn't have a SQL statement in it and you do NOT need PL/SQL, collections or PIPELINED functions to do what you need to do.

               

              Even if you DID need to use PL/SQL you would just open a REF CURSOR for the sql statement and return the cursor to the caller.

              -- proc returning ref cursor

              CREATE OR REPLACE
              PROCEDURE pr_print_result(
                  p_deptno dept.deptno%type,
                  p_result OUT sys_refcursor)
              IS
              BEGIN
                OPEN p_result FOR SELECT * FROM emp WHERE deptno=p_deptno;
              END ;

              -- printing ref cursor in sql*plus

              SQL> var emp_dtls refcursor

              SQL> EXECUTE pr_print_result(p_deptno=>10,p_result=>:emp_dtls);

              PL/SQL procedure successfully completed.

              SQL> PRINT emp_dtls;

                   EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
              ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                    7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
                    7839 KING       PRESIDENT            17-NOV-81       5000                    10
                    7934 MILLER     CLERK           7782 23-JAN-82       1300                    10