Home » SQL & PL/SQL » SQL & PL/SQL » Temporary table
Temporary table [message #228180] Mon, 02 April 2007 00:37 Go to next message
ataufique
Messages: 79
Registered: November 2006
Member
Hi!

I have a procedure in which i create temporary table for my
processing purpose.
Now when i use
create global temporary table...(col name......) on commit....
it does not give any error.
but when i use create global temporary table as select....
it gives error.
how can i use the create table base on select statement for
temporary table..

Thanks in advance

Regards,
Re: Temporary table [message #228188 is a reply to message #228180] Mon, 02 April 2007 00:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
I have a procedure in which i create temporary table

Bad idea.
Create your GTT once, (re)use it in your procedure. Don't do ddl in procedures!

Since you did not even hint WHAT error you got, I can only show you that a ctas does work on GTTs as well:
SQL> create global temporary table test on commit preserve rows as select * from all_tables where 1 = 2;

Table created.
Re: Temporary table [message #228217 is a reply to message #228188] Mon, 02 April 2007 02:18 Go to previous messageGo to next message
ataufique
Messages: 79
Registered: November 2006
Member
Thanks Frank,
It works with the Syntax given by you.

Another error - Insufficient previlages(IP).

When i am executing the package with user as sys, it gets executed.
But when i execute using any normal user it gives error...whereas if i use the same create code and
use in SQL, the GTT gets created..

What grants are required for creating it through procedure..

Thanks in advance.

Regards,
Re: Temporary table [message #228236 is a reply to message #228217] Mon, 02 April 2007 03:27 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

privilages


regards
shanth
Re: Temporary table [message #228248 is a reply to message #228217] Mon, 02 April 2007 04:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Reread my reply:
DO NOT DO DDL FROM A PROCEDURE.
Re: Temporary table [message #228399 is a reply to message #228248] Mon, 02 April 2007 22:46 Go to previous messageGo to next message
ataufique
Messages: 79
Registered: November 2006
Member
shanthkumaar - I have given the execute privilage but still not working..as i said below it works well outside the proc.

Frank - My Procedure requires DDL from inside the procedure,
and if DDL not to be used in procedure then why oracle would
have provided "EXECUTE IMMEDIATE".
Now do you have any solution ?

Now code is given below.

create or replace
PROCEDURE TAUF1_PROC
AS

BEGIN
dbms_output.put_line('Machine Name ='||sys_context('USERENV','TERMINAL'));
dbms_output.put_line('User Name ='||USER);
dbms_output.put_line('Start Time of FRAUD Procedure= '||systimestamp);

--EXECUTE IMMEDIATE 'TRUNCATE TABLE TAP3_SRVC_USED_FRAUD_SUM1';
--EXECUTE IMMEDIATE 'DROP TABLE TAP3_SRVC_USED_FRAUD_SUM1';
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE TAP3_SRVC_USED_FRAUD_SUM1
ON COMMIT PRESERVE ROWS
AS
select srce_plmn_code, dest_plmn_code,file_seq_no
from EDCH.tap3_srvc_used_fraud where file_seq_no = 9979 and rec_no = 62
group by srce_plmn_code, dest_plmn_code,file_seq_no'
;
dbms_output.put_line('TEMP TABLE TAP3_SRVC_USED_FRAUD_SUM CREATED');
dbms_output.put_line('End Time of FRAUD Procedure= '||systimestamp);
END TAUF1_PROC;
/

Thanks.

Re: Temporary table [message #228400 is a reply to message #228180] Mon, 02 April 2007 22:55 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
It appears you are clue resistant.
The solution was given by Frank in Re: Temporary table [message #228188
In the future do NOT describe what you think you did.
In the future use CUT & PASTE so we can see what you actually did and how Oracle responded. Be sure to include who/how you connect to the database.
Re: Temporary table [message #228425 is a reply to message #228399] Tue, 03 April 2007 00:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Can you please explain why it is necessary to create the table from within the procedure? Why can't you create the table once and only INSERT using the procedure?
Look, I am not trying to make life harder for you, I want to make it (lots!) easier. DDL in procedures are a major pain.
For example: I don't see you dropping the table prior to (re)creating it, so your procedure will fail if the table already exists. This is only one of the minor issues.

As to your vague 'still not working' statement: define 'still not working'. Do you get an error message? Does the table get created but with no rows?
Re: Temporary table [message #228460 is a reply to message #228425] Tue, 03 April 2007 02:25 Go to previous messageGo to next message
ataufique
Messages: 79
Registered: November 2006
Member
Dear anacedent,
If you do not have solution pls dont get involve in others
conversation, Frank/others are helping me in getting the reqd solution..
Clue resistant ?? ..

Frank,
Can you explain in detail why not to use DDL in proc ?
This i require to justify my decision.
Reason(in brief) for using GTT in Proc,
I will be using 07 GTT.
First 3 will be created and values inserted.
Then based on the values 3 more GTT created using select from the previous GTT..
Then based on the values 2 more GTT created and values inserted
by looping using the previous GTT..
Based on this 2 GTT i will populate my Database table from which
i will be generating the report.
The procedure contains lots of Manupulation
Now if i create this outside Proc, then i might have to move in/out of the proc.
This proc is running in existing ingres system and is taking around 1 hour, so i also need to optimised this .

Still not working means -
error - ORA-01031: insufficient privileges.
Neither table nor rows get created.

Pls.

Regards,
Re: Temporary table [message #228462 is a reply to message #228180] Tue, 03 April 2007 02:35 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Why on earth do you even consider doing this ?
Quote:
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE TAP3_SRVC_USED_FRAUD_SUM1
ON COMMIT PRESERVE ROWS
AS
select srce_plmn_code, dest_plmn_code,file_seq_no
from EDCH.tap3_srvc_used_fraud where file_seq_no = 9979 and rec_no = 62
group by srce_plmn_code, dest_plmn_code,file_seq_no';


This is not DB2 , Informix etc
You totally missed the point on using GTT

I suggest you "google" and read the manuals.
Re: Temporary table [message #228464 is a reply to message #228180] Tue, 03 April 2007 02:41 Go to previous messageGo to next message
freethinking
Messages: 10
Registered: November 2006
Junior Member
If I guess right , you are come from SQL SERVER, you want use temp table just like SQL SERVER, this problem also confused me for a long time Sad , for my opinion you can just use Oracle collection ,and it is quite different way ,good luck Smile
Re: Temporary table [message #228467 is a reply to message #228464] Tue, 03 April 2007 02:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
In 99% of the cases you don't need any intermediate table. Since SQLServer/Sybase (especially the older versions) suck at joining, they use temporary tables to store intermediate results.
Oracle is very powerful in joining, which makes it better to do everything in a single sql statement.

Quote:
Dear anacedent,
If you do not have solution pls dont get involve in others
conversation, Frank/others are helping me in getting the reqd solution..
Clue resistant ?? ..

There is no such thing as 'others conversation' in a public forum, so anyone can respond to his/her liking, as long as normal courtesy is used. Anacedent was right. I asked you to publish your errors in a prior reply and still you answer with a vague 'it does not work'.
Re: Temporary table [message #228473 is a reply to message #228464] Tue, 03 April 2007 03:02 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

freethinking wrote on Tue, 03 April 2007 09:41
If I guess right , you are come from SQL SERVER, you want use temp table just like SQL SERVER, this problem also confused me for a long time Sad , for my opinion you can just use Oracle collection ,and it is quite different way ,good luck Smile

Uhmm well you have global temporary tables.........

But as Frank says
Quote:
In 99% of the cases you don't need any intermediate table
Re: Temporary table [message #228476 is a reply to message #228473] Tue, 03 April 2007 03:21 Go to previous messageGo to next message
ataufique
Messages: 79
Registered: November 2006
Member
Dear senior members,
I missed out that this is a public forum..sorry..I appologise.
I dont know SQL server / Sybase / Ingres.

I am sitting at this side and i know that the kind of processing
my procedure is going to do, so it requires a GTT.

What i asked was a solution to below error, when GTT is created
through procedure(using execute immediate).
error - ORA-01031: insufficient privileges.
There is no error with same user when GTT is created from SQLPlus.

Thanks all for the kind support..

Bye.
Re: Temporary table [message #228483 is a reply to message #228476] Tue, 03 April 2007 03:39 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

I still think this is a very bad idea....

Anyway this might help you
Re: Temporary table [message #228487 is a reply to message #228476] Tue, 03 April 2007 03:45 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
ataufique wrote on Tue, 03 April 2007 10:21

I am sitting at this side and i know that the kind of processing
my procedure is going to do, so it requires a GTT.
Fair enough. But does it require the GTT to be created and to be dropped on the fly? The content will be erased once either your session or your transaction has ended, depending on how you created the GTT.

The "Temporary" in the global temporary table stands means that its data is temporary, not the table itself. You might want to reconsider your approach. I hope you will.

MHE
Previous Topic: how to call an sql script with in the PL/SQL script
Next Topic: How to load data from an excel sheet to a global temporary table using SQLLDR
Goto Forum:
  


Current Time: Fri Dec 09 04:09:13 CST 2016

Total time taken to generate the page: 0.08506 seconds