| Trouble with sqlplus command in batch file [message #13490] |
Wed, 14 July 2004 12:12  |
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  |
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?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
