DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: SQL Script file

  1. #1
    Join Date
    Sep 2001
    Posts
    163
    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

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    could you give a sample of your operation in steps including the format of batch file.


    -Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Sep 2001
    Posts
    163
    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.

  4. #4
    Join Date
    Nov 2001
    Location
    Central U.S.
    Posts
    35
    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.

    David D. Fitzjarrell
    Oracle Certified DBA

  5. #5
    Join Date
    Sep 2001
    Posts
    163
    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.


  6. #6
    Join Date
    Nov 2001
    Location
    Central U.S.
    Posts
    35
    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.
    David D. Fitzjarrell
    Oracle Certified DBA

  7. #7
    Join Date
    Nov 2001
    Location
    Central U.S.
    Posts
    35
    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.
    David D. Fitzjarrell
    Oracle Certified DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width