OraFAQ Forum: SQL & PL/SQL » Create Global Temporary Table

Home » SQL & PL/SQL » SQL & PL/SQL » Create Global Temporary Table - Error

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Create Global Temporary Table - Error [message #19835]

Fri, 12 April 2002 03:15

Amrendra
Messages: 2
Registered: April 2002

Junior Member

I am getting error (ORA-00901 - Invalid Create Command) while executing the following
CREATE GLOBAL TEMPORARY TABLE XYZ (
..
..
) ON COMMIT DELETE ROWS;

Report message to a moderator

Re: Create Global Temporary Table - Error [message #19841 is a reply to message #19835]

Fri, 12 April 2002 09:31

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Are you running 8i (8.1.5) or later? You need to be in order to use GTTs. If you are, post your entire statement so we can see the details.

Report message to a moderator

Re: Create Global Temporary Table - Error [message #19868 is a reply to message #19835]

Mon, 15 April 2002 08:06

uma
Messages: 67
Registered: May 2001

Member

I use this query and works for me. Try like this

CREATE GLOBAL TEMPORARY TABLE UMA.TABLE1 ON COMMIT DELETE ROWS AS SELECT * FROM
UMA.ACTUALTABLE

Tks,

Uma

Report message to a moderator

Re: Create Global Temporary Table - Error [message #19888 is a reply to message #19835]

Tue, 16 April 2002 09:41

André ALIMBA
Messages: 16
Registered: April 2002

Junior Member

1) Before creating Temporary Table, verify allways that if this table does not exist in YOUR schema .
Example :
SELECT * FROM ALL_OBJECTS
WHERE OBJECT_NAME = 'table_tempo'
and object_type = 'TABLE';
If the table exist, drop the table or continue...
...
2) Creation of the temporary table "table_tempo" by using of DYNAMIC SQL :

...
DECLARE
var_sqlcode number;
BEGIN
EXECUTE IMMEDIATE
'BEGIN
CREATE GLOBAL TEMPORARY TABLE table_tempo
(
tab_tempo_id INTEGER NOT NULL ,
col1 DATE NOT NULL ,
col2 varchar2(5) NOT NULL ,
col3 NUMBER(12,3),
...
col_end number(12,3)
)
ON COMMIT PRESERVE ROWS'
USING IN OUT var_sqlcode;
...

Report message to a moderator

Previous Topic:	increasing the screen width........
Next Topic:	Help me.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 20:59:39 CDT 2024