| Creation of multiple temp tables [message #211843] |
Tue, 02 January 2007 11:33  |
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  |
ThomasG
Messages: 3212
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 |
flyboy
Messages: 1903
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
- ...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
