I don't understand why you want to use SVRMGRL instead of SQL*Plus to create a table. Also, the example you have provided won't give you what you want since you are asking to generate a dynamic CREATE TABLE statement and also because &new_table will not be assigned by the command line parameter passed to the script.
For Oracle 8.1.5 and later the following will work:
sqlplus / @new_table %1
sqlstmt:='create table '||new_table||' as select * from emp';
execute immediate sqlstmt;
If the Oracle version is older than 8.1.5 then it will be necessary to use the DBMS_SQL package. The batch file will not change, but new_table.sql would then be:
set serveroutput on size 1000000 verify off feedback off
source_cursor := dbms_sql.open_cursor;
dbms_sql.parse(source_cursor, 'create table &&1 as select * from emp', 2);
if created <> 0 then
dbms_output.put_line('Table Create failed for &&1');
dbms_output.put_line('Table &&1 created');
Thanks Oratune, this helps. I knew I was barking up the wrong tree. I just didn't know which tree to go to.
One other quick question. Is there a way to display what the sqlstmt looks like will be executed? Basically, I want to see what the statement looks like to make sure I built it correctly before executing it.
In the first example, yes. Add the following line after sqlstmt has been 'built':
To pause the execution you can use the SLEEP function from DBMS_LOCK:
The parameter passed to SLEEP is in seconds.
For the second example you would need to change the code slightly to build the command as in the first example. You would then pass that variable to dbms_sql.parse. After that change has been made you could use the steps above to view the statement and pause the execution long enough to cancel the remaining instructions should the statement be incorrectly constructed.