SQL Script file

[ocp2b]

Has anyone ever created a script file in sql that is called by server manager from a command prompt and is passed a parameter?

I need to create an sql script that will be fed a parameter from a batch file and I am not sure how to do it.

Example:
Batch file accepts parameter then executes svrmgrl which in turn runs a sql script and is fed the parameter accepted by the batch file.

Oracle 8.1.6.0.0
Windows NT 4.0

[sambavan]

could you give a sample of your operation in steps including the format of batch file.


-Sam

[ocp2b]

I'm not sure how it will work, but I think it should look something like this:

C> create_new_table.bat NEWTBL (NEWTBL being the parameter that is passed into create_new_table.bat

Inside of Create_new_table.bat:

call SVRMGRL command=@new_table.sql%1
(%1 has the value of NEWTBL)

Inside of new_table.sql is:

CREATE TABLE &new_table AS SELECT * FROM someothertable;

(&new_table should be the value of NEWTBL


I'm open to any and all suggestions as to how to do this.

[oratune]

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:

create_new_table.bat:

sqlplus / @new_table %1

new_table.sql

declare
new_table varchar2(40):='&1';
sqlstmt varchar2(255);
begin
sqlstmt:='create table '||new_table||' as select * from emp';
execute immediate sqlstmt;
end;
/
exit

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
declare
source_cursor integer;
created integer;
begin
source_cursor := dbms_sql.open_cursor;
dbms_sql.parse(source_cursor, 'create table &&1 as select * from emp', 2);
created:=dbms_sql.execute(source_cursor);
if created <> 0 then
dbms_output.put_line('Table Create failed for &&1');
else
dbms_output.put_line('Table &&1 created');
end if;
dbms_sql.close_cursor(source_cursor);
end;
/
exit

I hope this helps.

[ocp2b]

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.

[oratune]

In the first example, yes. Add the following line after sqlstmt has been 'built':

dbms_output.put_line(sqlstmt);

To pause the execution you can use the SLEEP function from DBMS_LOCK:

dbms_lock.sleep(60);

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.

[oratune]

In my haste to get the response to you I forgot to mention that you'll need to also do this:

set serveroutput on size 1000000

This will allow you to actually see the statements output by DBMS_OUTPUT.