Want to Run Multiple SQL Script File in One Go With in Sqlplus

Oracle 11g - run windows batch file to run multiple sql files in sqlplus

make a 'driver' sql script.

inside it would look similar to this:

@Table1_.sql
@Table2_.sql
@Table3_.sql
@Table4_.sql

then just call this one once from the OS

How to run multiple SQL script files in a Shell script

Don't override the system PATH (or now SQLPATH) and don't put commands in single quotes. Use lowercase for your private variables, and you can't have spaces around the equals sign in an assignment; but a variable you only use once is useless anyway, so I took it out, and hardcoded the cd argument.

I'm guessing you want something like

#!/usr/bin/ksh

# No spaces around equals sign, and don't use uppercase for private variables
# But why use a variable at all anyway
#sqlpath=/usr/sql
cd /usr/sql  # is this really necessary and useful??

sqlplus usr/password@sid <<____EOF &&
 spool <db_file>.log
 @<db_name>.sql
 set echo off
 set heading off
 spool off
____EOF

sqlplus usr/password@sid <<____EOF &&
 spool <db_file2>.log
 @<db_name2>.sql
 set echo off
 set heading off
 spool off
____EOF

sqlplus usr/password@sid <<____EOF
 spool <db_file3>.log
 @<db_name3>.sql
 set echo off
 set heading off
 spool off
____EOF

# exit 0  # Not necessary or useful

If the multiple sqlplus commands can be executed in a single session, that would be an obvious improvement; but I'm guessing sqlplus has no way of expressing what you appear to mean with && (this syntax seems to have a quite distinct meaning in sqlplus).

Run different pl/sql scripts from one file

@ is an SQLPlus command

But toad partially supports it too: there are 2 common options:

1. Use SQL Editor - it should execute scripts in case of @ command as in SQL*Plus
2. Use "Execute as script" --> "Execute via SQL plus": it should start SQL*plus connected to your database, there you can run your scripts.

SQL*Plus how to execute multiple queries in single line?

SQL*Plus expects either:

• A single SQL command, terminated by either a ";" character or a "/" on a line by itself.
• A PL/SQL block
• A SQL*Plus command

What you have entered is 2 queries on a single line, which SQL*Plus will send to the RDBMS - Oracle will then try and parse the string sent as a single query and fail because it is not valid SQL.

A quick workaround would be to have all your commands in a sql file and run them using @file.sql

How to run multiple statements in one sqlscript for Oracle

As long as it's on its own line and left aligned, it should be ok in sql*plus:

SQL> create sequence MLTS_SEQUENCE start with 1 cache 20;

Sequence created.

SQL> select MLTS_SEQUENCE.nextval from dual;

   NEXTVAL
----------
         1

SQL> declare
  2    new_sequence INTEGER;
  3  begin
  4    select LAST_NUMBER + 1
  5      into new_sequence
  6      from user_sequences
  7     where SEQUENCE_NAME = 'MLTS_SEQUENCE';
  8
  9    execute immediate 'Create sequence Table_SEQ start with '
 10        || new_sequence ||' increment by 1';
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select Table_SEQ.nextval from dual;

   NEXTVAL
----------
        22

---

Related Topics