OraFAQ Forum: Client Tools » sqlplus : multiple scripts in argument (batch execution)

Home » Other » Client Tools » sqlplus : multiple scripts in argument (batch execution) (sql*plus)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sqlplus : multiple scripts in argument (batch execution) [message #340497]

Wed, 13 August 2008 02:13

123soleil
Messages: 35
Registered: July 2006

Member

Hi,

I have two scripts (test1.sql and test2.sql) that need to be executed one after the other. I would like to call them both using a single command line:

sqlplus testuser/testpwd@testdb @c:\test1.sql @c:\test2.sql

This does not work Sad

Any ideas? creating a test3.sql script that calls test1.sql and test2.sql is not an option

thanks

[Updated on: Wed, 13 August 2008 02:26]

Report message to a moderator

Re: sqlplus : multiple scripts in argument (batch execution) [message #340508 is a reply to message #340497]

Wed, 13 August 2008 02:40

Littlefoot
Messages: 21824
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

creating a test3.sql script that calls test1.sql and test2.sql is not an option

It isn't? Why not? It is the most straightforward solution.

Or, you might copy the whole code from "test2.sql" and put it at the end of "test1.sql".

According to SQL*Plus User's Guide and Reference

Code: [Select all] [Show/ hide]

SQLPLUS un/pw @file_name

@file_name

Documentation

Specifies the name of a script

(not scripts).

Report message to a moderator

Re: sqlplus : multiple scripts in argument (batch execution) [message #340510 is a reply to message #340497]

Wed, 13 August 2008 02:40

Frank
Messages: 7901
Registered: March 2000

Senior Member

Call test2.sql from test1.sql
If you rule out all the ordinary options, you will be left with none.

Report message to a moderator

Re: sqlplus : multiple scripts in argument (batch execution) [message #340511 is a reply to message #340497]

Wed, 13 August 2008 02:41

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Code: [Select all] [Show/ hide]

sqlplus testuser/testpwd@testdb <<EOF
@c:\test1.sql 
@c:\test2.sql
EOF

Regards
Michel

Report message to a moderator

Re: sqlplus : multiple scripts in argument (batch execution) [message #340515 is a reply to message #340497]

Wed, 13 August 2008 02:58

123soleil
Messages: 35
Registered: July 2006

Member

thank you for your answers

@Michel, this seems to be exactly what i need.
However i get the following error : "<< was unexpected at this time"
Is this a UNIX command? i'm using windows, is there something similar?

Report message to a moderator

Re: sqlplus : multiple scripts in argument (batch execution) [message #340523 is a reply to message #340515]

Wed, 13 August 2008 03:14

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

The somewhat similar Windows way would be :

Code: [Select all] [Show/ hide]

(
echo @c:\test1.sql 
echo @c:\test2.sql 
) | sqlplus testuser/testpwd@testdb

Report message to a moderator

Re: sqlplus : multiple scripts in argument (batch execution) [message #340533 is a reply to message #340515]

Wed, 13 August 2008 03:47

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes and this is the reason you MUST post your OS.
See Thomas' answer for Windows.

Regards
Michel

Report message to a moderator

Re: sqlplus : multiple scripts in argument (batch execution) [message #340543 is a reply to message #340497]

Wed, 13 August 2008 04:35

123soleil
Messages: 35
Registered: July 2006

Member

Thanks TomasG but your code doesn't seem to work. I get "sp2-0024 Nothing to change" error. Is the code suppose to work exactly as is or should i adapt anything?

Report message to a moderator

Re: sqlplus : multiple scripts in argument (batch execution) [message #340550 is a reply to message #340543]

Wed, 13 August 2008 04:47

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

That must seems to be an error inside your scripts.

Code: [Select all] [Show/ hide]

Oracle Error : SP2-0024 Nothing to change

Cause        : There was nothing in the SQL buffer when 
               using the CHANGE command.

Action       : Make sure the SQL buffer is not empty before 
               using the CHANGE command.

Since we don't know your scripts, and you didn't even post the exact session that threw the error, you have to debug it yourself.

Report message to a moderator

Re: sqlplus : multiple scripts in argument (batch execution) [message #340571 is a reply to message #340550]

Wed, 13 August 2008 05:42

123soleil
Messages: 35
Registered: July 2006

Member

my test script is as follow:

Code: [Select all] [Show/ hide]

declare
test varchar(50);
begin
select 'test' into test from dual;
dbms_output.put_line(test);
end;
/

test1.sql = test2.sql

launched individually the scripts work fine... how do I find the session that throws the error? I'm in the command prompt, I execute your code, sqlplus starts, then trows the error then disconnects and i return to the command prompt.

Thanks again for your help

Report message to a moderator

Re: sqlplus : multiple scripts in argument (batch execution) [message #340574 is a reply to message #340571]

Wed, 13 August 2008 05:54

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

The session is what you do in the command prompt.

For example I have run your test.sql with my test.cmd, and it works : (with an additional "set serverout on" so that we actually SEE the dbms_output :

Code: [Select all] [Show/ hide]

U:\>test.cmd

U:\>(
echo set serverout on
 echo @u:\test.sql
 echo @u:\test.sql
)  | sqlplus user/pass@testdb

SQL*Plus: Release 9.2.0.1.0 - Production on Wed Aug 13 12:50:05 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> SQL> test

PL/SQL procedure successfully completed.

SQL> test

PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Pro
duction
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

U:\>

Report message to a moderator

Re: sqlplus : multiple scripts in argument (batch execution) [message #340586 is a reply to message #340574]

Wed, 13 August 2008 06:16

123soleil
Messages: 35
Registered: July 2006

Member

many thanks! Smile

Report message to a moderator

Re: sqlplus : multiple scripts in argument (batch execution) [message #552800 is a reply to message #340586]

Sat, 28 April 2012 13:40

skm_inn
Messages: 14
Registered: January 2009
Location: New Delhi

Junior Member

Hi,
Please tell me in the below mentioned script provided by ThomasG
(
echo @c:\test1.sql
echo @c:\test2.sql
) | sqlplus testuser/testpwd@testdb

How do i display message before every compilation. I tried as:
(
PROMPT Compiling test1.sql
echo @c:\test1.sql
PROMPT Compiling test2.sql
echo @c:\test2.sql
) | sqlplus testuser/testpwd@testdb

But It doesn't displays PROMPT message instead just compiles and exits.

Thanks
SK

Report message to a moderator

Re: sqlplus : multiple scripts in argument (batch execution) [message #552802 is a reply to message #552800]

Sat, 28 April 2012 13:47

Littlefoot
Messages: 21824
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Open command prompt and type:

Code: [Select all] [Show/ hide]

c:\> help prompt
c:\> help echo

Hopefully, you'll notice a difference.

Report message to a moderator

Re: sqlplus : multiple scripts in argument (batch execution) [message #552804 is a reply to message #552802]

Sat, 28 April 2012 14:13

skm_inn
Messages: 14
Registered: January 2009
Location: New Delhi

Junior Member

Hi,
@Littlefoot
I put PROMPT in the script as i think it will run when it connects to sqlplus.
I tried by putting
(
echo Compiling test1.sql
echo @c:\test1.sql
echo Compiling test2.sql
echo @c:\test2.sql
) | sqlplus testuser/testpwd@testdb
but i am getting the below mentioned error:
SP2-0734: unknown command beginning "Compiling ..." - rest of line ignored.

NOTE: OS : WINDOWS

Report message to a moderator

Re: sqlplus : multiple scripts in argument (batch execution) [message #552805 is a reply to message #552804]

Sat, 28 April 2012 14:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: sqlplus : multiple scripts in argument (batch execution) [message #552806 is a reply to message #552805]

Sat, 28 April 2012 14:27

Littlefoot
Messages: 21824
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Well, you need to provide SQL statement to ECHO if you want it to be executed properly. Here's an example (A.BAT); TEST1.SQL selects number of records from the EMP table, while TEST2.SQL returns SYSDATE.

Code: [Select all] [Show/ hide]

(
 echo select 'compiling test1.sql' from dual;
   echo @test1.sql
 echo select 'compiling test2.sql' from dual;
   echo @test2.sql
) | sqlplus scott/tiger

Its execution:

Code: [Select all] [Show/ hide]

C:\>a

C:\>(
echo select 'compiling test1.sql' from dual;
 echo @test1.sql
 echo select 'compiling test2.sql' from dual;
 echo @test2.sql
)  | sqlplus scott/tiger

SQL*Plus: Release 11.2.0.2.0 Production on Sub Tra 28 21:25:58 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production


Session altered.

SQL>
'COMPILINGTEST1.SQL
-------------------
compiling test1.sql

SQL>
  COUNT(*)
----------
        14

SQL>
'COMPILINGTEST2.SQL
-------------------
compiling test2.sql

SQL>
SYSDATE
----------
28.04.2012

SQL> Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

C:\>

Report message to a moderator

Re: sqlplus : multiple scripts in argument (batch execution) [message #552807 is a reply to message #552804]

Sat, 28 April 2012 14:37

skm_inn
Messages: 14
Registered: January 2009
Location: New Delhi

Junior Member

Please find the details below:
Oracle DB: Oracle 11g 11.1.0.6.0
OS: Windows XP SP2

Hi,
@Littlefoot
I have put "PROMPT" in the script as i think it will run when it connects to sqlplus.
I tried by putting
(
echo Compiling test1.sql
echo @c:\test1.sql
echo Compiling test2.sql
echo @c:\test2.sql
) | sqlplus testuser/testpwd@testdb
but i am getting the below mentioned error:

Code: [Select all] [Show/ hide]

D:\>temp

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Apr 29 00:58:41 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SP2-0734: unknown command beginning "Compiling ..." - rest of line ignored.
SQL>
Procedure altered.

SQL> SP2-0734: unknown command beginning "Compiling ..." - rest of line ignored.
SQL>
Procedure altered.

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

D:\>

Report message to a moderator

Re: sqlplus : multiple scripts in argument (batch execution) [message #552808 is a reply to message #552807]

Sat, 28 April 2012 14:40

Littlefoot
Messages: 21824
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It appears that you didn't review my previous message carefully enough.

Report message to a moderator

Re: sqlplus : multiple scripts in argument (batch execution) [message #552809 is a reply to message #552808]

Sat, 28 April 2012 14:45

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You can view SQL*Plus resources at
http://www.oracle.com/technology/tech/sql_plus/

Report message to a moderator

Re: sqlplus : multiple scripts in argument (batch execution) [message #552810 is a reply to message #552808]

Sat, 28 April 2012 14:46

skm_inn
Messages: 14
Registered: January 2009
Location: New Delhi

Junior Member

Hi,
@Littlefoot

Actually, I received a message from BlackSwan regarding forum guidelines so was formatting my message.

I read your reply.

Thanks a lot it solved my purpose.

Regards
SK

Report message to a moderator