How to call a store procedure in bash script for Oracle

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.
LVL 1
mmingfeilamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
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.
mmingfeilamAuthor Commented:
I tried both options but same result, the procedure was not executed, and I don't see any error.
slightwv (䄆 Netminder) Commented:
Can you post /c/temp2/log.txt?
Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

johnsoneSenior Oracle DBACommented:
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

mmingfeilamAuthor 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 DBACommented:
Are you running this interactively or through cron?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Shell Scripting

From novice to tech pro — start learning today.