Home » SQL & PL/SQL » SQL & PL/SQL » Temporary Table - Insertion dynamically
Temporary Table - Insertion dynamically [message #216738] Tue, 30 January 2007 03:59 Go to next message
sreebhas
Messages: 8
Registered: January 2007
Location: Birmingham
Junior Member
Dear All,

I am testing one procedure, could able to create temp table sucessfully, But I could not able to insert data into that. Neither errors nor insertion. Can some body tell what is the problem? Please find the code for reference.

CREATE OR REPLACE procedure strtokenizer (
sStr in VARCHAR2
)
AS
sResult1 Varchar2(256);
CUR_IMPLICIT INTEGER;
SQL_STATEMENT VARCHAR2(256);
Result_Aft NUMBER;
THIS_SESSION NUMBER;
CID INTEGER;

BEGIN
sSubStr := sStr;
iStrLength := Length(sSubStr);
-- THIS_SESSION := USERENV('SESSIONID');
THIS_SESSION := 4260208;
sResult1 := sStr;

CUR_IMPLICIT := DBMS_SQL.OPEN_CURSOR;
SQL_STATEMENT := 'CREATE GLOBAL TEMPORARY TABLE PRTOWNER.TEMP1_'||THIS_SESSION||' (FIELD1 Varchar(256)) ';
DBMS_OUTPUT.PUT_LINE('Sql_Statement '||SQL_STATEMENT);
dbms_sql.parse(CUR_IMPLICIT, SQL_STATEMENT, DBMS_SQL.NATIVE);
Result_Aft := DBMS_SQL.EXECUTE(CUR_IMPLICIT);
dbms_sql.close_cursor(CUR_IMPLICIT);

CID := DBMS_SQL.OPEN_CURSOR;
Sql_Statement := 'INSERT INTO kill_temp values ('''|| sResult1 ||''')';
DBMS_OUTPUT.PUT_LINE('Sql_Statement '||Sql_Statement);
DBMS_SQL.PARSE(CID, Sql_Statement, dbms_sql.v7);
Result_Aft := DBMS_SQL.EXECUTE(CID);


END strtokenizer ;
/
Re: Temporary Table - Insertion dynamically [message #216776 is a reply to message #216738] Tue, 30 January 2007 05:57 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi.

1. You don't have to CREATE global temporary table inside the procedure.
Create it OUTSIDE of procedure and just use it inside the proc.

2. Why are you using DBMS_SQL : NDS ( Native Dynamic SQL is simplier and usually better).
However if you move CREATE TABLE statement out of proc - you won't have to use dynamic SQL at all.

3. Only session inserting the data may see/reference the data.

HTH.
Re: Temporary Table - Insertion dynamically [message #216796 is a reply to message #216776] Tue, 30 January 2007 07:41 Go to previous messageGo to next message
sreebhas
Messages: 8
Registered: January 2007
Location: Birmingham
Junior Member
Thanks a lot.

I do not have option to create out side the procedure. Is there any work around or alternative to store the data as if in temp table.

Regs,
Re: Temporary Table - Insertion dynamically [message #216798 is a reply to message #216796] Tue, 30 January 2007 07:44 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What do you mean by "not having the option"? If you can create a (GT) table from PL/SQL, you can do it from SQL too.
Re: Temporary Table - Insertion dynamically [message #216804 is a reply to message #216798] Tue, 30 January 2007 08:08 Go to previous messageGo to next message
sreebhas
Messages: 8
Registered: January 2007
Location: Birmingham
Junior Member
As per my requirement I cannot create temp table/object from outside.
Re: Temporary Table - Insertion dynamically [message #216815 is a reply to message #216798] Tue, 30 January 2007 09:59 Go to previous messageGo to next message
sreebhas
Messages: 8
Registered: January 2007
Location: Birmingham
Junior Member
Hi all,

A small correction in my code, i am using same kill_temp for creation and insertion.

Please read this statement as follows

SQL_STATEMENT := 'CREATE GLOBAL TEMPORARY TABLE KILL_TEMP1 (FIELD1 Varchar(256)) ';
Re: Temporary Table - Insertion dynamically [message #216855 is a reply to message #216738] Tue, 30 January 2007 13:04 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Change the reqs.

Otherwise you are going to create hundreds of GT tables (possibly once for each user logon), so somebody have to clear that stuff afterward.

Re: Temporary Table - Insertion dynamically [message #216940 is a reply to message #216855] Wed, 31 January 2007 00:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Apart from the (very valid) reasons Michael_balik mentioned I have a question: are you sure you actually need a GTT?
Could it be that you come from the SQLServer/Sybase world where it is very common to create tables on the fly?
Very very often it is NOT necessary in Oracle to use an intermediate table to get the same result. In Oracle you should perform all steps in a single query.
Re: Temporary Table - Insertion dynamically [message #216978 is a reply to message #216940] Wed, 31 January 2007 02:41 Go to previous messageGo to next message
sreebhas
Messages: 8
Registered: January 2007
Location: Birmingham
Junior Member
Then my actual requirement is like this.

‘,’ row terminator and ‘|’ is column terminator. Number of rows and columns will vary.

String: aaa|bbb|ccc,ddd|eee,fff|ggg|hhh|iiii

Output:
Col1 col2 col3 col4
=== ==== === ====
Aaa bbb ccc NULL
Ddd eee NULL NULL
Fff ggg hhh iii

If string: aaa|bbb,ccc|ddd|eee

Output:

Col1 Col2 Col3
==== ==== =====
Aaa bbb NULL
Ccc ddd eee

Any body have idea how to solve this?

Regards,
Re: Temporary Table - Insertion dynamically [message #216986 is a reply to message #216978] Wed, 31 January 2007 03:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you use pl/sql to create the GTT, where would you need the results? In a sql session or in a pl/sql process (e.g. a batch?)

You could create a piped function in pl/sql that returns the rows.
Re: Temporary Table - Insertion dynamically [message #216987 is a reply to message #216986] Wed, 31 January 2007 03:06 Go to previous message
sreebhas
Messages: 8
Registered: January 2007
Location: Birmingham
Junior Member
I should return this output as sql session if i run function/procedure.

Thanks
Previous Topic: truncate
Next Topic: alter sequence
Goto Forum:
  


Current Time: Tue Dec 06 06:35:19 CST 2016

Total time taken to generate the page: 0.14106 seconds