Home » SQL & PL/SQL » SQL & PL/SQL » Creation of multiple temp tables
Creation of multiple temp tables [message #211843] Tue, 02 January 2007 11:33 Go to next message
jaydba
Messages: 86
Registered: September 2006
Location: UK
Member

I wish to know that how multiple temp tables can be created using all the existing tables of current schema. But, I need to filter out the data also using my where clause as mentione below. I tried to create the same but remained fail to execute successfully. Can you please suggest me how to achive it?

Your help would be highly apprreciated.

DECLARE
sqlStmt VARCHAR2(1000);
BEGIN

FOR x IN (SELECT * FROM user_tables where table_name like 'T%')LOOP
sqlStmt := 'CREATE TABLE ' || x.table_name || '_TEMP ';-- Note the extra concatenation operator

sqlStmt := sqlStmt || ' AS ' ;

sqlStmt := sqlStmt || ' SELECT * FROM ' || x.table_name||' WHERE integerdate( c3 ) < add_months( sysdate, -36 )';

dbms_output.put_line( sqlStmt );-- Better to log to a table or log to a file perhaps
EXECUTE IMMEDIATE sqlStmt;
END LOOP;
END;

integerdate is the name of customised function and c3 is the name of column. It working fine with single SQL statement.

But, it returns an error message at the time of above creation script.

Alternatively, you can suugest me the alternate script to achive it.

Thanks in advance.

Re: Creation of multiple temp tables [message #211853 is a reply to message #211843] Tue, 02 January 2007 12:29 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
well, the only error I get when I run it is :

ORA-00904: "INTEGERDATE": invalid identifier
ORA-06512: at line 13


if I remove the "where" clause the script runs on my DB, and the tables are created
Re: Creation of multiple temp tables [message #211857 is a reply to message #211843] Tue, 02 January 2007 12:56 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
But, it returns an error message at the time of above creation script.

Nice. Would you mind to publish this error message too (at least the ORA numeric code)? Plus the sql statement it caused (as you write it). It should help to derive the cause.
Without it I can only guess:
- your temp table name is longer than 30 characters
- the temp table already exists (was created in the previously ran single SQL statement)
- some of the source tables does not contain column c3
- some of the source tables have the column c3 with wrong type
- ...
Previous Topic: sql*plus commands
Next Topic: Trigger Compile Error
Goto Forum:
  


Current Time: Sat Dec 10 02:44:31 CST 2016

Total time taken to generate the page: 0.13079 seconds