# Executing Procedure from shell script..

**URL:** <https://community.unix.com/t/executing-procedure-from-shell-script/284630>\
**Category:** Shell Programming and Scripting\
**Created:** [March 15, 2011, 6:26pm UTC](https://community.unix.com/t/executing-procedure-from-shell-script/284630 "2011-03-15T18:26:07Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![msrahman](https://community.unix.com/letter_avatar/msrahman/32/6_449c533f586939b6e5f246632a46421f.png) [@msrahman](https://community.unix.com/u/msrahman)\
**Post date:** [March 15, 2011, 6:26pm UTC](https://community.unix.com/t/executing-procedure-from-shell-script/284630/1 "2011-03-15T18:26:07Z")

</div>

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:

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

```

The shell script that calls this procedure is:

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

---

<div class="post-metadata">

**Author:** ![Scott](https://community.unix.com/letter_avatar/scott/32/6_449c533f586939b6e5f246632a46421f.png) [@Scott](https://community.unix.com/u/Scott)\
**Post date:** [March 15, 2011, 6:36pm UTC](https://community.unix.com/t/executing-procedure-from-shell-script/284630/2 "2011-03-15T18:36:40Z")

</div>

Hi.

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

```nohighlight
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.

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

```

---

<div class="post-metadata">

**Author:** ![msrahman](https://community.unix.com/letter_avatar/msrahman/32/6_449c533f586939b6e5f246632a46421f.png) [@msrahman](https://community.unix.com/u/msrahman)\
**Post date:** [March 15, 2011, 6:47pm UTC](https://community.unix.com/t/executing-procedure-from-shell-script/284630/3 "2011-03-15T18:47:38Z")

</div>

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

```auto
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

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

```

i have used "Schema name.procedure name".

```auto
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?

---

<div class="post-metadata">

**Author:** ![Scott](https://community.unix.com/letter_avatar/scott/32/6_449c533f586939b6e5f246632a46421f.png) [@Scott](https://community.unix.com/u/Scott)\
**Post date:** [March 15, 2011, 7:41pm UTC](https://community.unix.com/t/executing-procedure-from-shell-script/284630/4 "2011-03-15T19:41:01Z")

</div>

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:

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

```

or

```nohighlight
set serveroutput on size 1000000
execute zx01211.PDAC_UPDATE_ALLOW_DENY_SERV;

```

---

<div class="post-metadata">

**Author:** ![msrahman](https://community.unix.com/letter_avatar/msrahman/32/6_449c533f586939b6e5f246632a46421f.png) [@msrahman](https://community.unix.com/u/msrahman)\
**Post date:** [March 16, 2011, 9:53am UTC](https://community.unix.com/t/executing-procedure-from-shell-script/284630/5 "2011-03-16T09:53:57Z")

</div>

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;

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

```

---

<div class="post-metadata">

**Author:** ![Scott](https://community.unix.com/letter_avatar/scott/32/6_449c533f586939b6e5f246632a46421f.png) [@Scott](https://community.unix.com/u/Scott)\
**Post date:** [March 16, 2011, 4:05pm UTC](https://community.unix.com/t/executing-procedure-from-shell-script/284630/6 "2011-03-16T16:05:50Z")

</div>

I should think so.

Just add

```nohighlight
zx01211.PDAC_UPDATE_ALLOW_DENY_SERV;

```

before the closing /, or

```nohighlight
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:

```nohighlight
BEGIN
  ....
END;
/

```
