Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
How to insert output of a table function into a table using a procedure (read qn for detailed exp)

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