Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Executing Procedure from shell script..

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Executing Procedure from shell script..
# 1  
Old 03-15-2011
Executing Procedure from shell script..
Hello,

I created a sql file to create a Procedure, and it was successfully created.
I created a sql file to execute the procedure, and it did without any errors, but i dont see the data been updated.

The Execute procedure.sql script is:
HTML Code:
BEGIN
set serveroutput on size 1000000
execute zx01211.PDAC_UPDATE_ALLOW_DENY_SERV;
END;
The shell script that calls this procedure is:
HTML Code:
#! /bin/sh
export ORACLE_BASE=/oracle
export ORACLE_HOME=/oracle/product/11.2.0
export LIBPATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:/usr/lib
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$PATH:$ORACLE_HOME/bin
export userName=`cat /PDAC/loaduser`
export ORACLE_SID=`cat /PDAC/loadsid`
export userName=`cat /PDAC/loaduser`
export ORACLE_SID=`cat /PDAC/loadsid`
sysname=`cat /PDAC/loadenv`
#/usr/local/pl/perlencrypt.pl -k /kda1/loaduser -d /sdr1/loaduser | sqlplus -s $userName @/download/proc1.sql
/usr/local/pl/perlencrypt.pl -k /kda1/loaduser -d /sdr1/loaduser | sqlplus -s $userName @/download/exec_proc.sql
#/usr/local/pl/perlencrypt.pl -k /kda1/loaduser -d /sdr1/loaduser | sqlplus -s $userName @/download/synonym.sql
Can anyone please explain to me as to where i am going wrong, that the data is not getting updated after executing the procedure.

Appreciate it.
# 2  
Old 03-15-2011
Hi.

I don't see any "procedure" declaration in your PL/SQL code. Is that an anonymous block?

Code:
BEGIN
set serveroutput on size 1000000
execute zx01211.PDAC_UPDATE_ALLOW_DENY_SERV;
END;

If so, you have to tell Oracle to run it. i.e.
Code:
BEGIN
set serveroutput on size 1000000
execute zx01211.PDAC_UPDATE_ALLOW_DENY_SERV;
END;
/

# 3  
Old 03-15-2011
I tried with '/' at the end of the script, and i am got this error;
HTML Code:
Enter password:
set serveroutput on size 1000000
    *
ERROR at line 2:
ORA-06550: line 2, column 5:
PL/SQL: ORA-00922: missing or invalid option
ORA-06550: line 2, column 1:
PL/SQL: SQL Statement ignored
Also in the script, line
HTML Code:
BEGIN
set serveroutput on size 1000000
execute zx01211.PDAC_UPDATE_ALLOW_DENY_SERV;
END;
/
i have used "Schema name.procedure name".
HTML Code:
BEGIN
set serveroutput on size 1000000
execute zx01211.PDAC_UPDATE_ALLOW_DENY_SERV;
END;
/
Is it the right way to use in the .sql script to execute the procedure?
# 4  
Old 03-15-2011
Sorry, I misread your post somewhat Smilie

set is an SQL setting, not a PL/SQL setting, and you don't need execute to run something within PL/SQL, which leaves you with either:

Code:
set serveroutput on size 1000000
BEGIN
zx01211.PDAC_UPDATE_ALLOW_DENY_SERV;
END;
/

or
Code:
set serveroutput on size 1000000
execute zx01211.PDAC_UPDATE_ALLOW_DENY_SERV;

# 5  
Old 03-16-2011
Is it possible to add "Execute procedure" script in my current existing "Create or Replace Procedure script".

I would like this to work simultaneously "create and execute the procedure"

The script to create procedure is;
HTML Code:
CREATE OR REPLACE PROCEDURE PDAC_UPDATE_ALLOW_DENY_SERV
IS
   tmpVar   NUMBER;
/******************************************************************************
   NAME:       UPDATE_ALLOW_DENY_SERV
   PURPOSE:
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        11/29/2010   Mohamed.S.Rahman       1. Created this procedure.
   NOTES:
   Automatically available Auto Replace Keywords:
      Object Name:     UPDATE_ALLOW_DENY_SERV
      Sysdate:         11/29/2010
      Date and Time:   11/29/2010, 11:14:18 AM, and 11/29/2010 11:14:18 AM
      Username:        rsa11355 (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)
******************************************************************************/
BEGIN
   tmpVar := 0;
   UPDATE DMERCLINE
      SET allow_serv = allow_serv / 1000
    WHERE sadmerc_receipt_dt BETWEEN TO_DATE ('06-Oct-09')
                                 AND TO_DATE ('30-Jun-10');
   UPDATE DMERCLINE
      SET deny_serv = deny_serv / 1000
    WHERE sadmerc_receipt_dt BETWEEN TO_DATE ('06-Oct-09')
                                 AND TO_DATE ('30-Jun-10');
END PDAC_UPDATE_ALLOW_DENY_SERV;
/
# 6  
Old 03-16-2011
I should think so.

Just add
Code:
zx01211.PDAC_UPDATE_ALLOW_DENY_SERV;

before the closing /, or
Code:
execute zx01211.PDAC_UPDATE_ALLOW_DENY_SERV;

after it.

If you just want to execute without the "baggage" of creating a procedure, simply use an anonymous block:
Code:
BEGIN
  ....
END;
/

This User Gave Thanks to Scott For This Post:
msrahman
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Error while running Procedure in shell script

Hi All, I am running the below proc in unix by connecting through sqlplus and the procedure is completing successfully. But when i am trying to run through shell scripting by using function. I am getting the error as follows. Please guide me where I am going wrong. #!/bin/sh opera () {... (6 Replies)
Discussion started by: bhas85
6 Replies

2. Shell Programming and Scripting

Why i can't execute the procedure in shell script?

i have the following code inside a shell script .prog in oracle server when i call the program DBMS_OUTPUT.PUT_LINE(x_return_status|| ln_rep_req_id); will return 0 , it is very strange , i try to submit the concurrent request in oracle , and it can successfully executed, what am i missing ? i... (1 Reply)
Discussion started by: feilhk
1 Replies

3. Shell Programming and Scripting

Stored Procedure not executing

below code is not executing the stored procedure,not sure what the issue.Even sqllog is blank.please help me its very urgent. sqlplus -s $connect_str@$DB_ORACLE_SID >> ${SQL_LOG_FILE} << EOF set serverout on set feed off set head off set pages 0 set colsep , set tab off set lin 150... (3 Replies)
Discussion started by: katakamvivek
3 Replies

4. Shell Programming and Scripting

SP2-0642 error while executing procedure from shell script

hi all, i have a unix script where i am calling a database procedure from it. while executing the procedure i am getting an error: but when i tried to call the same procedure manually then it ran successfully, i goggled this issue and found timezone.dat file missing at... (0 Replies)
Discussion started by: lovelysethii
0 Replies

5. Shell Programming and Scripting

Executing procedure using script

Hi, I want to have a automted script to exceute 20 procedures one by one. Suppose below is one procedure once it get executed script will write "PL/SQL procedure successfully completed." to a log for ex- exec dbms_stats.gather_table_stats(); Now later procedure starts executing... (1 Reply)
Discussion started by: sv0081493
1 Replies

6. Shell Programming and Scripting

How to get OUT parameter of a stored procedure in shell script?

I am invoking a SQL script from shell script. This SQL script will invoke a stored procedure(which has the OUT parameter). I want to have the OUT parameter in the shell script as a variable. Is this possible? (6 Replies)
Discussion started by: vel4ever
6 Replies

7. Shell Programming and Scripting

Executing oracle procedure using cronjob

Hi, Below is the code to execute the procedure "dbms_job.broken" from the shell script. on executing manually, it works properly without any error. but it is not working when scheduled using the cronjob. #!/usr/bin/bash user_name="oracdb" password="ora123" tns="localdb"... (2 Replies)
Discussion started by: milink
2 Replies

8. Shell Programming and Scripting

How to execute the stored procedure from shell script

How to execute the stored procedure from shell script and is there any possibility to print the dbms output in a log file. (2 Replies)
Discussion started by: dineshmurs
2 Replies

9. Shell Programming and Scripting

Shell Script for call a procedure in Oracle DB

Hi everyone! I'm new with Shell Scripting, and I have to do a shell script to call a procedure, which have 2 input parameters, the directory(from server) and the txt file (which have informations to update/insert in DB). I have to create a shell script to execute that procedure for each txt... (5 Replies)
Discussion started by: renatoal
5 Replies

10. Shell Programming and Scripting

Oracle procedure is not executing in uix

Hi Guys, I am trying to tun a oracle proedure throgh unix shell script but it is not running i dont know why ? i have tested this procedure in sqlplus and it was working fine. can you see the script and sql file and let me know where is my mistake. script:bm_chart_table_loading.sh ... (3 Replies)
Discussion started by: shary
3 Replies
Login or Register to Ask a Question