Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

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

karthick_senthilnathanJul 26 2022 — edited Jul 26 2022

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.

Comments
Post Details
Added on Jul 26 2022
3 comments
991 views