How to call a store procedure in bash script for Oracle

mmingfeilam
mmingfeilam used Ask the Experts™
on
I have a bash shell script that I want to call a Oracle DB stored procedure with (procedure is in a package with public interface), I am running it on Cygwin:

#!/bin/bash

if [[ $1 = '' ]]; then

  echo
  echo "usage : copy_client_div.sh <master conect string> "
  exit 0
fi

test_db1=`$ORACLE_HOME/bin/sqlplus -s $1 << EOF
set heading off
set pages 0
set feedback off
select 999 from dual;

EOF`

test_db=`echo $test_db1`
echo test_db: ${test_db::-1}
echo ${test_db}


if [[ ${test_db::-1} != 999 ]]; then

  echo "Can NOT connect to database with the given connect string"
  echo
  exit 0

fi

CONNECT_STRING=$1

`$ORACLE_HOME/bin/sqlplus -s $CONNECT_STRING << EOF > /c/temp2/log.txt

set heading off
set pages 0
set feedback off

begin
      --GRANT EXECUTE ON COPY_STUDIES.ins_trial2 TO tsm10;
      execute PackageName.procedure_name(name=>'trialname',client_div_source=>'source1',client_div_target=>'target1',ftuser=>'user1');
      commit;
      exit;
end;
EOF
`

If I run the procedure by itself in a sql script, it works.  In the bash script, nothing happens and no error gets returned.  Can someone tell me what I am missing?  Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
johnsoneSenior Oracle DBA

Commented:
This:
begin
      --GRANT EXECUTE ON COPY_STUDIES.ins_trial2 TO tsm10;
      execute PackageName.procedure_name(name=>'trialname',client_div_source=>'source1',client_div_target=>'target1',ftuser=>'user1');
      commit;
      exit;
end;

Open in new window

Should simply be this:
     --GRANT EXECUTE ON COPY_STUDIES.ins_trial2 TO tsm10;
      execute PackageName.procedure_name(name=>'trialname',client_div_source=>'source1',client_div_target=>'target1',ftuser=>'user1');
      commit;
      exit;

Open in new window


Either remove the BEGIN/END, or remove the EXECUTE (which is a SQL*Plus shortcut for BEGIN/END).  If you remove the EXECUTE, then you have to do a couple other things.  Removing the BEGIN/END is the easiest option.  You really don't have a need for the anonymous block.

Author

Commented:
I tried both options but same result, the procedure was not executed, and I don't see any error.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Can you post /c/temp2/log.txt?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

johnsoneSenior Oracle DBA

Commented:
What exactly did you run and was there any error messages?  Were there any messages in /c/temp2/log.txt?

I managed to set up your script and made the changes suggested as well as a few others to make the initial connection test work (not sure why you need this).  This is the exact code that I ran and it worked fine.  No errors, no messages in the log and the procedure ran.
#!/bin/bash

if [[ $1 = '' ]]; then

  echo 
  echo "usage : copy_client_div.sh <master conect string> "
  exit 0
fi

test_db=`$ORACLE_HOME/bin/sqlplus -s $1 << EOF
set heading off
set pages 0
set feedback off
select 999 from dual;
exit
EOF`

if [[ ${test_db} -ne 999 ]]; then

  echo "Can NOT connect to database with the given connect string"
  echo
  exit 0

fi

CONNECT_STRING=$1

`$ORACLE_HOME/bin/sqlplus -s $CONNECT_STRING << EOF > /c/temp2/log.txt

set heading off
set pages 0
set feedback off

      execute PackageName.procedure_name(name=>'trialname',client_div_source=>'source1',client_div_target=>'target1',ftuser=>'user1');
      commit;
      exit;
EOF
`

Open in new window

Author

Commented:
That's the strange thing, no errors, log is empty.  The procedure is public and if I run it on sql script, it works.
johnsoneSenior Oracle DBA

Commented:
Are you running this interactively or through cron?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial