|
|
|
|
| Re: How to call stored procedures by batch file [message #145504 is a reply to message #145500] |
Thu, 03 November 2005 09:29   |
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 |
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 #145558 is a reply to message #145535] |
Thu, 03 November 2005 16:02 |
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 |
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 stored procedures by batch file [message #344116 is a reply to message #301591] |
Thu, 28 August 2008 16:00 |
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 (merged) [message #344367 is a reply to message #344356] |
Fri, 29 August 2008 09:41 |
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 #344383 is a reply to message #344371] |
Fri, 29 August 2008 10:08 |
ThomasG
Messages: 3212
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 #344394 is a reply to message #344389] |
Fri, 29 August 2008 12:22 |
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 #432239 is a reply to message #145498] |
Mon, 23 November 2009 03:17 |
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 #432374 is a reply to message #145498] |
Mon, 23 November 2009 19:06 |
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..
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
