Home » Infrastructure » Windows » How to call stored procedures by batch file (merged)  () 1 Vote
How to call stored procedures by batch file (merged) [message #145498] Thu, 03 November 2005 09:15 Go to next message
lucas4394
Messages: 24
Registered: October 2005
Junior Member
Does anyone have ideas how to call stored
procedures from batch file? And how to
notify the stored procedures completed
to the batch file, so it can pull out
so messages from the database?

thanks
lucas
Re: How to call stored procedures by batch file [message #145500 is a reply to message #145498] Thu, 03 November 2005 09:22 Go to previous messageGo to next message
kiran
Messages: 503
Registered: July 2000
Senior Member
Are you speaking about DOS Batch file (or) an Sql Script.

--Kiran.
Re: How to call stored procedures by batch file [message #145504 is a reply to message #145500] Thu, 03 November 2005 09:29 Go to previous messageGo to next message
lucas4394
Messages: 24
Registered: October 2005
Junior Member
kiran wrote on Thu, 03 November 2005 09:22

Are you speaking about DOS Batch file (or) an Sql Script.

--Kiran.


Like the stored procedures are already created in my
Oracle database, and I want to create a batch file
to call these stored procedures. And I am not
familiar in batch file creation.

Thanks,
lucas
Re: How to call stored procedures by batch file [message #145509 is a reply to message #145498] Thu, 03 November 2005 09:44 Go to previous messageGo to next message
kiran
Messages: 503
Registered: July 2000
Senior Member
If you need to execute the procedures that you have defined you can write a Pl/Sql Script (which contains .sql extension) and can execute that script from SQL Prompt.

If you need to execute those procedures repeatedly in some intervals , you could use DBMS_JOB package.

For more info on this package,have a look at here.

--Kiran.
Re: How to call stored procedures by batch file [message #145512 is a reply to message #145509] Thu, 03 November 2005 09:53 Go to previous messageGo to next message
lucas4394
Messages: 24
Registered: October 2005
Junior Member
For my case, I can only use Perl or batch file to launch
the stored procedures. Basically, I just need to create
an engine to launch the stored procedures not in the
oracle envirtment such as SQL prompt.

Any clues?
lucas
Re: How to call stored procedures by batch file [message #145535 is a reply to message #145512] Thu, 03 November 2005 13:21 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Try something like this:

sqlplus -s scott/tiger@dev1 @t.sql

t.sql
=====
exec my_proc(123);
exit



or this in a shell script:
==========================
#!/bin/ksh
hostvar=5
sqlplus -s scott/tiger@dev <<EOF
exec my_proc($hostvar);
exit;
EOF
Re: How to call stored procedures by batch file [message #145558 is a reply to message #145535] Thu, 03 November 2005 16:02 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you want to use Perl, download the DBI and DBD::Oracle packages from http://www.cpan.org

Using DBI, you can make connections to a database, execute SQLs and Stored Procs, load results into hashes and lists. Basically, everything you ever dreamed of. Error handling is roughly 50,000 times easier than executing SQL*Plus from a Unix .sh / DOS .bat file.

_____________
Ross Leishman
How to call Stroed Procedure in Batch file [message #300686 is a reply to message #145498] Sun, 17 February 2008 18:21 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Hello,

I need to call stored procedure in batch file, so it executes automatically. I dont want to put in sql file and to call it.

For eg.
sqlplus uid/pwd@connect;
exec proc_met;

Is there any other way to call or execute stored procedure from batch file. Thanks in advance.

bye
Karthik
Re: How to call Stroed Procedure in Batch file [message #300689 is a reply to message #300686] Sun, 17 February 2008 18:45 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Quick search would reveal this.
Re: How to call Stroed Procedure in Batch file [message #300693 is a reply to message #300689] Sun, 17 February 2008 20:10 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
I saw the link, provided by you

sqlplus -s scott/tiger@dev1 @t.sql

t.sql
=====
exec my_proc(123);
exit


but I dont want two files, I want to do it in single file (in batch file).

bye
Karthik
Re: How to call Stroed Procedure in Batch file [message #300694 is a reply to message #300693] Sun, 17 February 2008 20:53 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
And what was wrong with the other example in that post ??

#!/bin/ksh
hostvar=5
sqlplus -s scott/tiger@dev <<EOF
exec my_proc($hostvar);
exit;
EOF
Re: How to call Stroed Procedure in Batch file [message #300695 is a reply to message #300694] Sun, 17 February 2008 21:16 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
I saw it, I want to schedule it in Windows OS, I think it is for Unix OS.

Doubts also...
1. #!bin/ksh -> ksh means?
2. hostvar = 5 -> whats the need to assign the value?
3. Why <<EOF is required ?

Thanks
Karthik

Re: How to call stored procedures by batch file [message #300696 is a reply to message #145558] Sun, 17 February 2008 21:29 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
I'm also facing same problem, to call stored proc in batch file in Window OS, without putting in a separate sql file.
Re: How to call Stroed Procedure in Batch file [message #300697 is a reply to message #300695] Sun, 17 February 2008 21:50 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
ap_karthi wrote on Sun, 17 February 2008 22:16
... I want to schedule it in Windows OS...

Really...and just where did you mention that in any of your posts above ???

That's why it helps to read OraFAQ Forum Guide before posting.
Re: How to call stored procedures by batch file [message #300698 is a reply to message #300696] Sun, 17 February 2008 21:52 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
This thread is over 2 years old !!

Please do NOT cross-post.
Re: How to call stored procedures by batch file [message #300975 is a reply to message #300698] Mon, 18 February 2008 21:36 Go to previous messageGo to next message
ap_karthi
Messages: 87
Registered: October 2007
Location: Bangalore
Member
Hi e!Brian,

Its not over. The shell can't be used in the batch file in Windows OS. Before posting, generally I'll search everywhere and then I'll post. That didnt work out, which was posted long back.

bye
karthik
Re: How to call stored procedures by batch file [message #301048 is a reply to message #300975] Tue, 19 February 2008 01:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> The shell can't be used in the batch file in Windows OS
It can be. If you are using tools like cygwin / MKSToolkit.

C:\Documents and Settings\dbadmin>ksh
$ ps -ef | grep rotate
  SYSTEM   2220   2164  0   Feb 08 con  0:00 C:\hp\hpsmh\bin\rotatelogs.exe C:/hp/hpsmh/logs/error_log 5M
  SYSTEM   2228   2164  0   Feb 08 con  0:00 C:\hp\hpsmh\bin\rotatelogs.exe C:/hp/hpsmh/logs/access_log 5M
 dbadmin   4748   4224  0 02:12:29 con  0:00 grep rotate
$

>> to call stored proc in batch file in Window OS, without putting in a separate sql file.
While this requirement makes nothing more secure or better,
the only other workaround for you is to do something like this.
Create your sql file only fly , use it and remove it.
http://www.orafaq.com/scripts/win/dbstart.txt
Re: How to call stored procedures by batch file [message #301143 is a reply to message #300975] Tue, 19 February 2008 05:41 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I don't have a Windoze box in front of me to test this on, but this is a native way of doing it in Windows:

set hostvar=5
(
echo conn scott/tiger@dev
echo exec my_proc(%hostvar%);
) | sqlplus -s /nolog
Re: How to call stored procedures by batch file [message #301591 is a reply to message #301143] Thu, 21 February 2008 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I tried it and it works.
Thanks for this trick.

Regards
Michel
Re: How to call stored procedures by batch file [message #344116 is a reply to message #301591] Thu, 28 August 2008 16:00 Go to previous messageGo to next message
Sreelekha
Messages: 6
Registered: August 2008
Junior Member
I have tried below in my .bat file but it doesn't seem to work.
SP procedure name is my_proc and have 1 IN parameter.

set hostvar=5
(
echo conn a735APC/a735APC@d058
echo exec my_proc(%:123%);
) | sqlplus -s /nolog

Can anyone please let me know if I am doing anything wrong here.
Re: How to call stored procedures by batch file [message #344170 is a reply to message #344116] Fri, 29 August 2008 00:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sreelekha wrote on Thu, 28 August 2008 23:00
I have tried below in my .bat file but it doesn't seem to work.
SP procedure name is my_proc and have 1 IN parameter.

set hostvar=5
(
echo conn a735APC/a735APC@d058
echo exec my_proc(%:123%);
) | sqlplus -s /nolog

Can anyone please let me know if I am doing anything wrong here.

What is :123?

Regards
Michel

Re: How to call stored procedures by batch file [message #344329 is a reply to message #344170] Fri, 29 August 2008 07:19 Go to previous messageGo to next message
Sreelekha
Messages: 6
Registered: August 2008
Junior Member
:123 is a IN parameter value which I am passing into SP

[Updated on: Fri, 29 August 2008 07:22]

Report message to a moderator

Re: How to call stored procedures by batch file (merged) [message #344349 is a reply to message #145498] Fri, 29 August 2008 08:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
At the Operating system level, what does it know about ":123" & how?
Re: How to call stored procedures by batch file (merged) [message #344352 is a reply to message #344349] Fri, 29 August 2008 08:51 Go to previous messageGo to next message
Sreelekha
Messages: 6
Registered: August 2008
Junior Member
ok, so how should I pass the parameter values for my SP?

Thanks
Re: How to call stored procedures by batch file (merged) [message #344356 is a reply to message #145498] Fri, 29 August 2008 09:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Write the value of the parameter into a datafile
& then have the *bat file load the datafile into an environmental variable.
Re: How to call stored procedures by batch file (merged) [message #344367 is a reply to message #344356] Fri, 29 August 2008 09:41 Go to previous messageGo to next message
Sreelekha
Messages: 6
Registered: August 2008
Junior Member
Can you let me know why doesn't the below code work? I don't want to create another data file just for passing the parameter. my parameter value is set before I call the procedure in the batch.

set hostvar=5
(
echo conn a735sim/simuser@d058;
echo exec my_proc(%hostvar%);
commit;
) | sqlplus -s /nolog
Re: How to call stored procedures by batch file (merged) [message #344369 is a reply to message #145498] Fri, 29 August 2008 09:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

>Can you let me know why doesn't the below code work?

My car does not work.
Please tell me how to make my car go.
Re: How to call stored procedures by batch file (merged) [message #344371 is a reply to message #344369] Fri, 29 August 2008 09:55 Go to previous messageGo to next message
Sreelekha
Messages: 6
Registered: August 2008
Junior Member
I don't think I am being Sarcastic here. I just thought some of you guys may have encountered this problem before and have fixed it. I was asking this question as the above thread says it works. I was wondering may be I am missing something there.
Re: How to call stored procedures by batch file (merged) [message #344383 is a reply to message #344371] Fri, 29 August 2008 10:08 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, we MIGHT have encountered "this problem".

But we are not able to tell if we have encountered "this problem", since you haven't told us what "this problem" actually is.

"Doesn't work" is not enough of a problem description.

The posted script works for me syntactically. So I don't have a clue what the problem could be.

[Updated on: Fri, 29 August 2008 10:10]

Report message to a moderator

Re: How to call stored procedures by batch file (merged) [message #344389 is a reply to message #344383] Fri, 29 August 2008 10:21 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Ahhh... Now I think I see a problem

The windows method of piping with the brackets doesn't seem to work when there are additional brackets in the statement itself.
Re: How to call stored procedures by batch file (merged) [message #344394 is a reply to message #344389] Fri, 29 August 2008 12:22 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Well, thanks to Thomas' hint, you can try this:


set hostvar='5'
echo %hostvar%

pause

(
echo conn scott/tiger@dev;
echo set serveroutput on
echo exec dbms_output.put_line ^^^( %hostvar% ^^^) ;
) | sqlplus -s /nolog

pause


C:\>set hostvar='5'

C:\>echo '5'
'5'

C:\>pause
Press any key to continue . . .

C:\>(
echo conn scott/tiger@dev;
 echo set serveroutput on
 echo exec dbms_output.put_line ^( '5' ^) ;
)  | sqlplus -s /nolog
5

PL/SQL procedure successfully completed.


C:\>pause
Press any key to continue . . .
Re: How to call stored procedures by batch file (merged) [message #344399 is a reply to message #344394] Fri, 29 August 2008 12:35 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Seems to be the first closing ')' that causes the most problem:

E:\>type test.bat
@echo off
set hostvar='Test code'
(
echo conn test/test
echo set serverout on
echo exec dbms_output.put_line(%hostvar%^^^);
)| sqlplus -s /nolog
E:\>test
Test code

PL/SQL procedure successfully completed.

[Updated on: Fri, 29 August 2008 12:36]

Report message to a moderator

Re: How to call stored procedures by batch file (merged) [message #345423 is a reply to message #344399] Wed, 03 September 2008 09:42 Go to previous messageGo to next message
Sreelekha
Messages: 6
Registered: August 2008
Junior Member
Thanks everyone!! Below code does work.
c:
cd Program Files\client_10G\BIN

set hostvar=5
(
echo conn a735sim/usersim@d05;
echo exec my_proc(%hostvar%^^^);
) | sqlplus -s /nolog


Re: How to call stored procedures by batch file (merged) [message #345431 is a reply to message #345423] Wed, 03 September 2008 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Re: How to call stored procedures by batch file (merged) [message #432239 is a reply to message #145498] Mon, 23 November 2009 03:17 Go to previous messageGo to next message
gsarin
Messages: 4
Registered: November 2009
Location: Australia
Junior Member
Hi,

I'm stuck in a similar issue.

My oracle procedure is as below:

create or replace PROCEDURE ARC_POS AS
BEGIN
--do something few update statements
--do something
END;


I'm trying to call this procedure from a windows batch file, the contents of which are below:

SET BIN=D:\oracle\ora92\bin
D:
cd %BIN%
set MyDir=E:\Folder1\folder2
%BIN%\sqlplus.exe uname/password@Wdev @%MyDir%\ARCPOS.sql
exit

ARCPOS.sql is as follows:
BEGIN
schema_name.ARC_POS();
END;
/

I get the no error message after running the batch file. The batch file gets stuck after showing:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

For ARCPOS.sql I have also tried the following statement only without success (same result as above).
EXECUTE schema_name.ARC_POS();

The procedure runs file when I run it from Toad.

Any pointers will be helpful.

Thanks in advance.


Re: How to call stored procedures by batch file (merged) [message #432244 is a reply to message #432239] Mon, 23 November 2009 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put "exit" inside your script.

Regards
Michel
Re: How to call stored procedures by batch file (merged) [message #432293 is a reply to message #432244] Mon, 23 November 2009 08:22 Go to previous messageGo to next message
gsarin
Messages: 4
Registered: November 2009
Location: Australia
Junior Member
Thanks for the reply Michele..

Do you mean to say I should change my ARCPOS.sql to:

ARCPOS.sql is as follows:
BEGIN
schema_name.ARC_POS();
END;
exit;
/

Let me know if that is incorrect??

Thanks..
Re: How to call stored procedures by batch file (merged) [message #432305 is a reply to message #432293] Mon, 23 November 2009 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes this is what I meant.

Regards
Michel
Re: How to call stored procedures by batch file (merged) [message #432309 is a reply to message #432305] Mon, 23 November 2009 09:06 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I think Michel meant adding the exit after the pl/sql block. The trailing ';' after exit is optional.
BEGIN
schema_name.ARC_POS();
END;
/
exit;


exec is shorthand for begin/end in sqlplus, so this should work too.
exec schema_name.ARC_POS();
exit;
Re: How to call stored procedures by batch file (merged) [message #432374 is a reply to message #145498] Mon, 23 November 2009 19:06 Go to previous messageGo to previous message
gsarin
Messages: 4
Registered: November 2009
Location: Australia
Junior Member
Hi All,

Thanks for all the replies, but I'm still struggling to find the issue.

I was able to run the procedure after changing ARCPOS.sql file to

BEGIN
schema_name.ARC_POS();
END;
/
exit;

AND
exec schema_name.ARC_POS();
exit;

But the procedure only runs in two scenarios:

* when I grant execute/ Debug privilege to the user Id I'm using to run the procedure while the batch file is executing. This is a bit confusing because I'd already granted all privileges to the user id I'm using to run the procedure. This is true even when I use the user id of the owner of the procedure.


* It also runs when I run the following following statement in toad while the batch file is executing:
create or replace PROCEDURE ARC_POS AS
BEGIN
do something -- update statement
do something -- update statement
END;
/

Otherwise it just gets stuck after logging in as before:

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 24 10:58:33 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


just to give you more background. The procedure runs some updates on a table which is written by another process (called in the same batch file before the procedure is called).

Is this a commit problem??

Thanks in advance..
Previous Topic: Executing script using bat file
Next Topic: Error installing oracle 10g R2 in windows server 2008
Goto Forum:
  


Current Time: Thu Mar 28 12:36:00 CDT 2024