Home » SQL & PL/SQL » SQL & PL/SQL » How to save resultset of procedure into a table ?
How to save resultset of procedure into a table ? [message #242914] Tue, 05 June 2007 09:02 Go to next message
Shvarts
Messages: 2
Registered: June 2007
Junior Member
Hi,
How can I insert records of a result-set of a stored procedure into a table other than placing INSERT inside of stored procedure? Basically, I need something like this:

insert into my_table
execute my_stored_proc (parameters);

Thanks
Re: How to save resultset of procedure into a table ? [message #242917 is a reply to message #242914] Tue, 05 June 2007 09:04 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You cannot. Not with a procedure. A function could work.

MHE
Re: How to save resultset of procedure into a table ? [message #242926 is a reply to message #242917] Tue, 05 June 2007 09:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, with a function you can do something like:
insert into mytable select * from table(my_function(parameters));

Regards
Michel
Re: How to save resultset of procedure into a table ? [message #242931 is a reply to message #242926] Tue, 05 June 2007 09:38 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Here's a small example. It runs on the default HR schema:

CREATE TABLE mhe_emps(employee_id NUMBER, salary NUMBER)
/

CREATE TYPE emp_rec_type IS OBJECT ( employee_id NUMBER, salary NUMBER);
/

CREATE TYPE emp_table_type IS TABLE of emp_rec_type;
/

CREATE OR REPLACE FUNCTION my_emp_function( pin_deptno IN NUMBER)
RETURN emp_table_type
IS
  v_return emp_table_type;
BEGIN
  SELECT emp_rec_type(employee_id, salary)
  BULK COLLECT INTO v_return
  FROM   employees
  WHERE department_id = pin_deptno;
  
  RETURN v_return;
END my_emp_function;
/
sho err

INSERT INTO mhe_emps
SELECT *
FROM   TABLE(my_emp_function(30))
/

SELECT * 
FROM   mhe_emps
/
DROP FUNCTION my_emp_function
/

DROP TABLE mhe_emps
/
DROP TYPE emp_table_type
/
DROP TYPE emp_rec_type
/


When I run it, I get this:
SQL> @orafaq

Table created.


Type created.


Type created.


Function created.

No errors.

6 rows created.


EMPLOYEE_ID     SALARY
----------- ----------
        114      11000
        115       3100
        116       2900
        117       2800
        118       2600
        119       2500

6 rows selected.


Function dropped.


Table dropped.


Type dropped.


Type dropped.

SQL>

MHE
Re: How to save resultset of procedure into a table ? [message #242936 is a reply to message #242931] Tue, 05 June 2007 09:52 Go to previous messageGo to next message
Shvarts
Messages: 2
Registered: June 2007
Junior Member
Thanks a lot.
Now I will convert my procedure into a functoin. The only difference is that this is packaged procedure, but I think it will work as well.
Thanks again.
Re: How to save resultset of procedure into a table ? [message #243039 is a reply to message #242936] Tue, 05 June 2007 15:17 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
wait a moment before hacking your way through well-organised code Wink

1) What exactly is the 'resultset' of your procedure? Is it an out-parameter of some collection-datatype? Is it an out parameter of ref cursor type
Or do you mistake stored procedures for what they are like in SQL-Servers (i.e. stored queries)
2) Why do you want to store these (intermittent, I assume) results in a table? Why not store them in a pl/sql variable and continue processing them?
Previous Topic: nested subqueries
Next Topic: Complex Sql
Goto Forum:
  


Current Time: Sun Dec 04 10:55:38 CST 2016

Total time taken to generate the page: 0.07709 seconds