| creat table by Dynamic SQL [message #374922] |
Wed, 11 July 2001 16:49  |
George
Messages: 68
Registered: April 2001
|
Member |
|
|
I like to create a table from this SP using 'CREATE TABLE ABC AS SELECT * FROM EMP' in Dynamic SQL, but failed. The create statements worked in SQL*PLUS:
procedure createtemp (
table_name VARCHAR2,
view_name VARCHAR2,
orderbystring IN VARCHAR2) AS
sql_statement VARCHAR2(1000);
BEGIN
sql_statement :='CREATE '| |table_name | |' AS SELECT a.*, rownum rn FROM '| |view_name | |' a ORDER BY :str' ;
EXECUTE IMMEDIATE sql_statement USING orderbystring;
END createtemp;
*** RUN THIS:
SQL> EXECUTE createtemp ('emptemp', 'emp', 'empno');
BEGIN empora.createtemp ('emptemp', 'emp', 'empno'); END;
*
ERROR at line 1:
ORA-00901: invalid CREATE command
ORA-06512: at "SCOTT.EMPORA", line 11
ORA-06512: at line 1
Can anybody help me to correct this?
Thanks
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
