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.