How to Interact with Oracle SQLPLUS Using Windows Batch Script

smallwei 2:41 pm on February 10, 2010 | Answers: 1    
Tags: batch (12), oracle (136), program (10), script (26), sqlplus (7), windows (150)

Rate Knowledge:

 Loading ...

• smallwei 2:41 pm on February 10, 2010
  

  Use the following format in your Windows batch program to interact with sqlplus:

  @echo off
  (
  echo USERNAME/PASSWORD@DB_SID
  echo set cmdsep on
  echo set cmdsep '"'; --"
  echo "YOUR NORMAL SQLPLUS COMMAND OR SQL STATEMENTS"
  ...
  ...
  ...
  ) | sqlplus -s

  Here is an example for you:
  

  @echo off
  (
  echo scott/tiger@devdb
  echo set cmdsep on
  echo set cmdsep '"'; --"
  echo "set head off"
  echo "set feedback off"
  echo "spool truncate.sql"
  echo "select 'TRUNCATE TABLE ' || table_name || ' REUSE STORAGE;' from user_tables;"
  echo "prompt EXIT;"
  echo "spool off"
  ) | sqlplus -s

  In the above example, you can expect the batch script to send the following commands to sqlplus:
  

  
  set head off
  set feedback off
  spool truncate.sql
  select 'TRUNCATE TABLE ' || table_name || ' REUSE STORAGE;' from user_tables;
  prompt EXIT;
  spool off