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:

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

The shell script that calls this procedure is:

#! /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.

Hi.

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

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.

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

I tried with '/' at the end of the script, and i am got this error;

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

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

i have used "Schema name.procedure name".

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?

Sorry, I misread your post somewhat :o

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:

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

or

set serveroutput on size 1000000
execute zx01211.PDAC_UPDATE_ALLOW_DENY_SERV;

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;

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;
/

I should think so.

Just add

zx01211.PDAC_UPDATE_ALLOW_DENY_SERV;

before the closing /, or

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:

BEGIN
  ....
END;
/
1 Like