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

karthick_senthilnathan

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.

BluShadow

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.)

Frank Kulash

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

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).