Home » SQL & PL/SQL » Client Tools » Trouble with sqlplus command in batch file
Trouble with sqlplus command in batch file [message #13490] Wed, 14 July 2004 12:12 Go to next message
L Boren
Messages: 16
Registered: July 2004
Junior Member
I am trying to write a batch file that will call a PL/SQL procedure.  I created a test procedure that accepts two input parameters so that I could test my batch file. The PL/SQL procedure is in my Oracle database (called PROC_TEST) and I also have it in a file on my c: drive (called proc_test.sql).  I have executed the procedure through the SQL*Plus interface and it works fine.

However, I have then tried to run it from a DOS cmd prompt and can't get it to work.  I have tried many different forms of the command line based on different examples I have found online, but none work.  Here are some samples of what I have tried:

sqlplus user1/user1@testdb @proc_test.sql 'M' 'x'

sqlplus -s user1/user1@testdb @proc_test.sql "M" "x";

sqlplus user1/user1@testdb @C:proc_test.sql ('M','x')

sqlplus user1/user1@testdb @C:proc_test.sql ('M','x')

sqlplus.exe user1/user1@testdb @proc_test.sql ('M', 'x')

I can't get any of these commands to work.  It seems to be connecting fine to sqlplus, but then the command window seems to be waiting for something else to be entered.  In all of these cases, the results that come through in the command window look like this:

C:>sqlplus.exe user1/user1@testdb @proc_test.sql ('M', 'x')

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Jul 14 <st1:time Hour="14" Minute="35">14:35:44</st1:time> 2004

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

Connected to:
Oracle Database 10g
<st1:City><st1:place>Enterprise</st1:place></st1:City> Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options


 20

I just get a blinking cursor after the '20' like it is waiting for me to enter more commands.

I can get my test stored procedure to work through a command prompt if I enter the commands like this:

C:Documents and SettingsAdministrator>cd

C:>sqlplus

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Jul 14 <st1:time Hour="14" Minute="36">14:36:59</st1:time> 2004

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

Enter user-name: user1
Enter password:


 



Connected to:
Oracle Database 10g
<st1:City><st1:place>Enterprise</st1:place></st1:City> Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL> exec test_schema.proc_test ('M','kitty');

PL/SQL procedure successfully completed.

SQL>

Where proc_test is the name of the stored procedure in the database instance testdb in the schema called test_schema.

Although this works in the command prompt, I'm unsure how to code this into a batch file so I get the same results.  Shouldn't I be able to accomplish the same thing with in a one line sqlplus command?  I tried combining what works into a one line command as follows:

sqlplus user1/user1@testdb @test_schema.proc_test ('M','x')

It seems to connect fine, but then I get the following error message:

SP2-0310: unable to open file "bp_ferc.proc_test"

Can anyone provide assistance?  I apologize for the length of this post, but wanted to be clear on everything I had tried.
Re: Trouble with sqlplus command in batch file [message #13494 is a reply to message #13490] Wed, 14 July 2004 14:18 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Let's look at the various pieces here:

1) A stored procedure in the database. Let's say it is:

create or replace procedure proc_test
  (p_1 in varchar2, p_2 in varchar2)
is
begin
  dbms_output.put_line( 'P1 value: ' || p_1 );
  dbms_output.put_line( 'P2 value: ' || p_2 );
end;
/


2) A local .SQL file that contains a call to the procedure, not the procedure itself. Let's say I have a local file called CALLPROC.SQL and it contains:

set serveroutput on
exec proc_test('&1', '&2')
exit

3) A command-line call to SQL*Plus with a reference to this local script file and the parameters:

C:>sqlplus barryt/xxxx@yyyy @callproc Shaq Miami
 
SQL*Plus: Release 9.2.0.5.0 - Production on Wed Jul 14 15:21:31 2004
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
 
P1 value: Shaq
P2 value: Miami
 
PL/SQL procedure successfully completed.
 
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
 
C:>


Does that help?
Re: Trouble with sqlplus command in batch file [message #13515 is a reply to message #13494] Thu, 15 July 2004 07:01 Go to previous message
L Boren
Messages: 16
Registered: July 2004
Junior Member
Todd,

Yes, that works beautifully. Thank you so much for your help!

Lanette
Previous Topic: passing variables into sqlplus
Next Topic: How to start Debugger in SQL*Navigator
Goto Forum:
  


Current Time: Wed Aug 05 20:57:11 CDT 2020