Home » SQL & PL/SQL » Client Tools » sqlplus : multiple scripts in argument (batch execution) (sql*plus)
sqlplus : multiple scripts in argument (batch execution) [message #340497] Wed, 13 August 2008 02:13 Go to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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
SQLPLUS un/pw @file_name
@file_name
Documentation
Specifies the name of a script
(not scripts).
Re: sqlplus : multiple scripts in argument (batch execution) [message #340510 is a reply to message #340497] Wed, 13 August 2008 02:40 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: sqlplus : multiple scripts in argument (batch execution) [message #340511 is a reply to message #340497] Wed, 13 August 2008 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sqlplus testuser/testpwd@testdb <<EOF
@c:\test1.sql 
@c:\test2.sql
EOF

Regards
Michel
Re: sqlplus : multiple scripts in argument (batch execution) [message #340515 is a reply to message #340497] Wed, 13 August 2008 02:58 Go to previous messageGo to next message
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?
Re: sqlplus : multiple scripts in argument (batch execution) [message #340523 is a reply to message #340515] Wed, 13 August 2008 03:14 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The somewhat similar Windows way would be :

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


Re: sqlplus : multiple scripts in argument (batch execution) [message #340533 is a reply to message #340515] Wed, 13 August 2008 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes and this is the reason you MUST post your OS.
See Thomas' answer for Windows.

Regards
Michel
Re: sqlplus : multiple scripts in argument (batch execution) [message #340543 is a reply to message #340497] Wed, 13 August 2008 04:35 Go to previous messageGo to next message
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?
Re: sqlplus : multiple scripts in argument (batch execution) [message #340550 is a reply to message #340543] Wed, 13 August 2008 04:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That must seems to be an error inside your scripts.

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.
Re: sqlplus : multiple scripts in argument (batch execution) [message #340571 is a reply to message #340550] Wed, 13 August 2008 05:42 Go to previous messageGo to next message
123soleil
Messages: 35
Registered: July 2006
Member
my test script is as follow:

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
Re: sqlplus : multiple scripts in argument (batch execution) [message #340574 is a reply to message #340571] Wed, 13 August 2008 05:54 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 :

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:\>
Re: sqlplus : multiple scripts in argument (batch execution) [message #340586 is a reply to message #340574] Wed, 13 August 2008 06:16 Go to previous messageGo to next message
123soleil
Messages: 35
Registered: July 2006
Member
many thanks! Smile
Re: sqlplus : multiple scripts in argument (batch execution) [message #552800 is a reply to message #340586] Sat, 28 April 2012 13:40 Go to previous messageGo to next message
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
Re: sqlplus : multiple scripts in argument (batch execution) [message #552802 is a reply to message #552800] Sat, 28 April 2012 13:47 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Open command prompt and type:
c:\> help prompt
c:\> help echo
Hopefully, you'll notice a difference.
Re: sqlplus : multiple scripts in argument (batch execution) [message #552804 is a reply to message #552802] Sat, 28 April 2012 14:13 Go to previous messageGo to next message
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
Re: sqlplus : multiple scripts in argument (batch execution) [message #552805 is a reply to message #552804] Sat, 28 April 2012 14:20 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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/
Re: sqlplus : multiple scripts in argument (batch execution) [message #552806 is a reply to message #552805] Sat, 28 April 2012 14:27 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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.
(
 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:
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:\>
Re: sqlplus : multiple scripts in argument (batch execution) [message #552807 is a reply to message #552804] Sat, 28 April 2012 14:37 Go to previous messageGo to next message
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:

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:\>



Re: sqlplus : multiple scripts in argument (batch execution) [message #552808 is a reply to message #552807] Sat, 28 April 2012 14:40 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It appears that you didn't review my previous message carefully enough.
Re: sqlplus : multiple scripts in argument (batch execution) [message #552809 is a reply to message #552808] Sat, 28 April 2012 14:45 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You can view SQL*Plus resources at
http://www.oracle.com/technology/tech/sql_plus/
Re: sqlplus : multiple scripts in argument (batch execution) [message #552810 is a reply to message #552808] Sat, 28 April 2012 14:46 Go to previous messageGo to next message
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
Re: sqlplus : multiple scripts in argument (batch execution) [message #552811 is a reply to message #552810] Sat, 28 April 2012 14:50 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Great! Now, you can play with SQL*Plus' SET command to make it prettier (for example, remove column heading and such).
Re: sqlplus : multiple scripts in argument (batch execution) [message #552812 is a reply to message #552811] Sat, 28 April 2012 14:53 Go to previous message
skm_inn
Messages: 14
Registered: January 2009
Location: New Delhi
Junior Member
@Littlefoot
Yes.
Thanks once again.
Previous Topic: Can't read SCN
Next Topic: Debug
Goto Forum:
  


Current Time: Sat Dec 10 13:04:02 CST 2016

Total time taken to generate the page: 0.09425 seconds