Forum Stats

  • 3,876,265 Users
  • 2,267,083 Discussions
  • 7,912,487 Comments

Discussions

How to insert output of a table function into a table using a procedure (read qn for detailed exp)

karthick_senthilnathan
karthick_senthilnathan Member Posts: 4 Red Ribbon
edited Jul 26, 2022 11:26AM in SQL & PL/SQL

Hi,

I would like to insert the return type of a function into a table using a procedure. Function is returning a collection as its output. We are calling that function from a select statement inside the procedure to validate and store it's results.


Let me give u the output result.


Create table customer

(Cust_id number,

Address varchar2(10),

Pincode number);


Create type t1 is object

(Cust_id number,

Address varchar2 (10),

Pincode number);


Create type t is table of t1;


Create function fn1

Return t

Is

i t := t();

Cursor C1 is

Select t1(cust_id, address,pincode)

From customer;

Begin

Open C1;

I.extend();

Fetch C1 bulk collect into i;

Close c1;

Return i;

End ;


Now I'm calling this function inside a procedure using select statement and doing some validation.



Create procedure PS1

Is

Begin

For i in (select * from table(fn1))

Loop

If i.pincode is not null

Then insert into tb1 values (i.cust_id,i.pincode);

End if;

Commit;

End loop;

End;



If i tried running this procedure as an anonymous block, it is working. however I need to run this only as a proc/function.


Kindly help me in this.

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 42,591 Red Diamond

    You would likely be better to have your function as a pipelined table function that returns the data as "rows" so that it can be queried as if it's a table, and then inserting the data to another table is trivial.

    e.g. of pipelined table function with multiple columns...

    SQL> CREATE OR REPLACE TYPE myemp AS OBJECT
      2  ( empno    number,
      3    ename    varchar2(10),
      4    job      varchar2(10),
      5    mgr      number,
      6    hiredate date,
      7    sal      number,
      8    comm     number,
      9    deptno   number
     10  )
     11  /
    
    Type created.
    
    SQL> CREATE OR REPLACE TYPE myrectable AS TABLE OF myemp
      2  /
    
    Type created.
    
    SQL> CREATE OR REPLACE FUNCTION pipedata(p_min_row number, p_max_row number) RETURN myrectable PIPELINED IS
      2    v_obj myemp := myemp(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
      3  BEGIN
      4    FOR e IN (select *
      5              from (
      6                    select e.*
      7                          ,rownum rn
      8                    from (select * from emp order by empno) e
      9                   )
     10              where rn between p_min_row and p_max_row)
     11    LOOP
     12      v_obj.empno    := e.empno;
     13      v_obj.ename    := e.ename;
     14      v_obj.job      := e.job;
     15      v_obj.mgr      := e.mgr;
     16      v_obj.hiredate := e.hiredate;
     17      v_obj.sal      := e.sal;
     18      v_obj.comm     := e.comm;
     19      v_obj.deptno   := e.deptno;
     20      PIPE ROW (v_obj);
     21    END LOOP;
     22    RETURN;
     23  END;
     24  /
    
    Function created.
    
    SQL> select * from table(pipedata(1,5));
    
         EMPNO ENAME      JOB               MGR HIREDATE                    SAL       COMM     DEPTNO
    ---------- ---------- ---------- ---------- -------------------- ---------- ---------- ----------
          7369 SMITH      CLERK            7902 17-DEC-1980 00:00:00        800                    20
          7499 ALLEN      SALESMAN         7698 20-FEB-1981 00:00:00       1600        300         30
          7521 WARD       SALESMAN         7698 22-FEB-1981 00:00:00       1250        500         30
          7566 JONES      MANAGER          7839 02-APR-1981 00:00:00       2975                    20
          7654 MARTIN     SALESMAN         7698 28-SEP-1981 00:00:00       1250       1400         30
    
    SQL> select * from table(pipedata(6,10));
    
         EMPNO ENAME      JOB               MGR HIREDATE                    SAL       COMM     DEPTNO
    ---------- ---------- ---------- ---------- -------------------- ---------- ---------- ----------
          7698 BLAKE      MANAGER          7839 01-MAY-1981 00:00:00       2850                    30
          7782 CLARK      MANAGER          7839 09-JUN-1981 00:00:00       2450                    10
          7788 SCOTT      ANALYST          7566 19-APR-1987 00:00:00       3000                    20
          7839 KING       PRESIDENT             17-NOV-1981 00:00:00       5000                    10
          7844 TURNER     SALESMAN         7698 08-SEP-1981 00:00:00       1500          0         30
    
    


    Although, it appears that your "collection" is just getting the data from querying existing data on the database in the first place, so why not just insert - select from the source data and avoid using a collection altogether? Using the collection is just going to slow things down and use up more resources (PGA etc.)

    Mohammad Omar Flaifel
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,794 Red Diamond

    Hi, @karthick_senthilnathan

    Whenever you have a question, please post a complete test script, including CREATE TABLE and INSERT statements for all tables involved (relevant columns only) so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

    If i tried running this procedure as an anonymous block, it is working. however I need to run this only as a proc/function.

    What exactly is the problem? Show how you tried to use the procedure. If you get an error, post the complete error message, including line numbers.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,591 Red Diamond

    And p.s.

    Create procedure PS1
    Is
    Begin
    For i in (select * from table(fn1))
    Loop
    If i.pincode is not null
    Then insert into tb1 values (i.cust_id,i.pincode);
    End if;
    Commit;
    End loop;
    End;
    

    Don't commit inside cursor loops. That's a really silly thing to do and can lead to exceptions being raised.

    Only commit once a logical business transaction has completed i.e. after you've looped through all the data and finished processing (if you really must use a cursor loop).