Home » SQL & PL/SQL » SQL & PL/SQL » insert into temporary table
insert into temporary table [message #122293] Sun, 05 June 2005 03:08 Go to next message
patrykp
Messages: 31
Registered: April 2005
Member
I migrate procedures MS SQL Server to Oracle.
In MS SQL SSERVER the use of instructions INSERT with procedure results which are in storage or dynamic instructions EXECUTE in place of VALUES clause is permissible. This construction is similar to INSERT/SELECT but we have to do with EXEC instead of SELECT. The part of EXEC should include exactly one resulted collection about the equivalent types to the types of table columns. In case of the stored procedure, we can pass on proper parameters, use the form of EXEC('string') and even call up wideranging procedures or remote control procedures from different servers. Calling up remote control procedures from different server, which place data in temporary table, and later realizing join with obtainable data, we can construct diffuse joins.
For example. I want insert results stored procedures sp_configure, proc_obj in temporary table.
1)INSERT #konfig
exec sp_configure.

2)
CREATE PROCEDURE proc_test
@Object_ID int,
AS
SET XACT_ABORT ON
BEGIN TRAN
CREATE TABLE #testObjects ( Object_ID int NOT NULL )
INSERT
#testObjects
EXEC
proc_obj @Object_ID,3,1
COMMIT TRAN
RETURN(0)
go

I don't know how migrate for example code to Oracle? Please examples in pl/sql.
Best regards.
Patryk

[Updated on: Sun, 05 June 2005 03:09]

Report message to a moderator

Re: insert into temporary table [message #122296 is a reply to message #122293] Sun, 05 June 2005 05:29 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
You have several options here.

One is to use global temporary table and ref cursor.
The second is to use collections and table functions.
Temporary table keeps session-specific data for you,
so you don't need to create individual temporary storage.
Ref cursor allows to to return cursors from one
procedure to another.

SQL> create global temporary table obj$num (object_id number);

Table created.

SQL> create package your_pkg
  2  is
  3   type refcur is ref cursor;
  4  end;
  5  /

Package created.

SQL> create function get_cur return your_pkg.refcur
  2  is
  3   ret your_pkg.refcur;
  4  begin
  5   open ret for select empno from emp;
  6   return ret;
  7  end;
  8  /

Function created.

SQL> create or replace procedure your_insert
  2  is
  3   rc your_pkg.refcur;
  4   type objtab is table of number index by binary_integer;
  5   objt objtab;
  6  begin
  7   rc := get_cur;
  8   fetch rc bulk collect into objt;
  9   close rc;
 10   forall j in 1..objt.count
 11     insert into obj$num values(objt(j));
 12  end;
 13  /

Procedure created.

SQL> exec your_insert;

PL/SQL procedure successfully completed.

SQL> select * from obj$num;

 OBJECT_ID
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876

 OBJECT_ID
----------
      7900
      7902
      7934

14 rows selected.


Consider, the standard option of temporary table
forces the clearance of it's context after commit:

SQL> commit;

Commit complete.

SQL> select * from obj$num;

no rows selected


You can create temporary table wich keeps data while
session activity:

SQL> drop table obj$num;

Table dropped.

SQL> create global temporary table obj$num (id number) on commit preserve rows;

Table created.

SQL> exec your_insert;

PL/SQL procedure successfully completed.

SQL> select * from obj$num;

        ID
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876

        ID
----------
      7900
      7902
      7934

14 rows selected.

SQL> commit;

Commit complete.

SQL> select * from obj$num;

        ID
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876

        ID
----------
      7900
      7902
      7934

14 rows selected.


but in this case you will be responsible for
deletion old data fron temporary table (
until your session is logged off).

Also, you can use collections to keep session-specific
data and table function to return them in select:

SQL> create type num_table is table of number;
  2  /

Type created.

SQL> create or replace package your_pkg
  2  is
  3   type refcur is ref cursor;
  4   loaded_objs num_table := num_table();
  5   function get_objs return num_table;
  6  end;
  7  /

Package created.

SQL> create or replace package body your_pkg
  2  is
  3   function get_objs return num_table
  4   is
  5   begin
  6    return loaded_objs;
  7   end;
  8  end;
  9  /

Package body created.

SQL> create or replace procedure your_insert
  2  is
  3   rc your_pkg.refcur;
  4   type objtab is table of number index by binary_integer;
  5   objt objtab;
  6  begin
  7   your_pkg.loaded_objs.delete;
  8   rc := get_cur;
  9   fetch rc bulk collect into your_pkg.loaded_objs;
 10   close rc;
 11  end;
 12  /

Procedure created.

SQL> exec your_insert;

PL/SQL procedure successfully completed.

SQL> select column_value from table(your_pkg.get_objs);

COLUMN_VALUE
------------
        7369
        7499
        7521
        7566
        7654
        7698
        7782
        7788
        7839
        7844
        7876

COLUMN_VALUE
------------
        7900
        7902
        7934

14 rows selected.


Rgds.

[Updated on: Sun, 05 June 2005 09:23]

Report message to a moderator

Re: insert into temporary table [message #122435 is a reply to message #122293] Mon, 06 June 2005 09:15 Go to previous messageGo to next message
MikeL
Messages: 15
Registered: January 2005
Junior Member
To mimic the dataset returned by a t-sql stored procedure in Oracle I would use a pipelined function. Using a pipelined function with bulk binds will return a dataset to the insert. Look here for some helpful hints on how to do this:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:30404463030437
http://www.databasejournal.com/features/oracle/article.php/3383061
http://databasejournal.com/features/oracle/article.php/3352091
http://www.oreillynet.com/pub/a/network/2003/01/22/feuerstein.html?page=3

Enjoy!
P.S. Check out asktom.oracle.com, if Tom can't answer your question about Oracle, no one can!
Re: insert into temporary table [message #123220 is a reply to message #122293] Fri, 10 June 2005 10:33 Go to previous messageGo to next message
patrykp
Messages: 31
Registered: April 2005
Member
I have function
FUNCTION spcxdb_expandtree(
Object_Id IN NUMBER DEFAULT NULL,
Flags IN NUMBER DEFAULT NULL,
Recursive IN NUMBER DEFAULT NULL,
RC1 IN OUT Omwb_emulation.globalPkg.RCT1)
return integer
as
...
and I can't insert results functions spcxdb_expandtree in temporary table.

var ec refcursor
var a number
exec :a := spcxdb_expandtree(38,2,3,:ec);
print ec;

OBJECT_ID
----------
44
46
62
PL/SQL procedure successfully completed.

I traing

SQL> create global temporary table test_temp (object_id number);

Table created.

SQL> create package test_pkg
2 is
3 type refcur is ref cursor;
4 end;
5 /

Package created.

SQL> create function get_cur return test_pkg.refcur
2 is
3 ret test_pkg.refcur;
4 begin
5 open ret for select spcxdb_expandtree(38,2,3,ec);
6 return ret;
7 end;
8 /

But get error:
PL/SQL: ORA-06572:Function TEST has out arguments
I have it that is bad conception.
"Function which you call from SQL statement can't have
OUT or IN OUT parameters but just IN."
How can I modyfi function get_cur that put into practice procedure test_insert.
SQL> create or replace procedure test_insert
2 is
3 rc test_pkg.refcur;
4 type objtab is table of number index by binary_integer;
5 objt objtab;
6 begin
7 rc := get_cur;
8 fetch rc bulk collect into objt;
9 close rc;
10 forall j in 1..objt.count
11 insert into test_temp values(objt(j));
12 end;
13 /

How insert results functions spcxdb_expandtree in temporary table.
Pls. help

Re: insert into temporary table [message #123232 is a reply to message #123220] Fri, 10 June 2005 11:49 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
Review Oracle doc references I have posted to you.
Right code is:

create function get_cur return test_pkg.refcur
is
ret test_pkg.refcur;
rnum number;
begin
rnum := spcxdb_expandtree(38,2,3,ret);
return ret;
end;
/

Rgds.
Re: insert into temporary table [message #123233 is a reply to message #123232] Fri, 10 June 2005 11:53 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
...and it would be more accurate to do the following:

create function get_cur (
Object_Id IN NUMBER DEFAULT NULL,
Flags IN NUMBER DEFAULT NULL,
Recursive IN NUMBER DEFAULT NULL
)
return test_pkg.refcur
is
ret test_pkg.refcur;
rnum number;
begin
rnum := spcxdb_expandtree(Object_Id,Flags,Recursive,ret);
return ret;
end;
/

and call this function with input parameters:

rc := get_cur(38,2,3);

Rgds.

[Updated on: Fri, 10 June 2005 11:53]

Report message to a moderator

Re: insert into temporary table [message #437417 is a reply to message #123233] Mon, 04 January 2010 06:10 Go to previous messageGo to next message
rozerdemit
Messages: 1
Registered: January 2010
Location: london
Junior Member
Hi this is enable to understand, can anybody suggest me regarding the above post?

Re: insert into temporary table [message #437429 is a reply to message #122293] Mon, 04 January 2010 06:47 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Which post exactly and what do you not understand?
Re: insert into temporary table [message #448094 is a reply to message #122293] Fri, 19 March 2010 07:55 Go to previous messageGo to next message
rozordermit
Messages: 1
Registered: March 2010
Location: london
Junior Member
I think he is asking about dmitry.nikiforov post
Re: insert into temporary table [message #448095 is a reply to message #448094] Fri, 19 March 2010 07:59 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You just register to post this? I don't think that 2 months later no one is interesting on who understand what.
After registering another user to comment a post 4 years and half old!

Regards
Michel

[Updated on: Fri, 19 March 2010 08:00]

Report message to a moderator

Re: insert into temporary table [message #448199 is a reply to message #122293] Sat, 20 March 2010 09:09 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
the guy has a question.

he searches our forum and finds a thread four years old that covers his question.

so he add his question to the thread.

What is wrong with that? Nothing in my mind. It is what people should do. Why does it matter how old the post is?


rozerdemit, can you be more specific about what it is you want to know please.

Kevin

[Updated on: Sat, 20 March 2010 09:10]

Report message to a moderator

Re: insert into temporary table [message #448208 is a reply to message #448199] Sat, 20 March 2010 10:23 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The point is that "rozordermit" is posting that "I think he is asking about dmitry.nikiforov post" talking about "rozerdemit" question.

"rozerdemit"'s question is perfectly legitimate (althought totally obscure) but "rozordermit"'s post is quite weird if you assume (which is not proved) they are both the same one and nevertheless it is not common to register just to post this.

Regards
Michel

[Updated on: Sat, 20 March 2010 10:24]

Report message to a moderator

Previous Topic: How to get the distinct count of records from a csv file?
Next Topic: Query for non nulls in a row
Goto Forum:
  


Current Time: Thu Sep 29 10:50:25 CDT 2016

Total time taken to generate the page: 0.08167 seconds