| insert into temporary table [message #122293] |
Sun, 05 June 2005 03:08  |
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  |
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 #123220 is a reply to message #122293] |
Fri, 10 June 2005 10:33 |
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 |
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 |
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 #448199 is a reply to message #122293] |
Sat, 20 March 2010 09:09 |
 |
Kevin Meade
Messages: 2103
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 |
 |
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
