How to call a stored procedure inside a loop?

Shell Programming and Scripting
1

How to call a stored procedure inside a loop

export PATH=$ORACLE_HOME/bin:$PATH
ORACLE_SID=xxxx; export ORACLE_SID ORAENV_ASK=NO
TODAY_DATE=$(date +"%Y%m%d")
LOGFILE=/var/opt/gogd/cust/scripts/${TODAY_DATE}_sap_cust_rel.log
echo 'Connected'
PARCUST=sap_cust_rel.par
sqlldr parfile=${PARCUST} >> $LOGFILE
echo 'started' 
v_out_str=`sqlplus -S xxxxx/xxxxx << EOF
set serveroutput on 
set feedback on
declare 
l_err_msg varchar2(1000);
begin
sp_control_customer_insert('E');
sp_update_customer();
dbms_output.put_line('customer update');
for COUNTER in  1 2 3 4 5 6 7 8
do
sp_insert_customer_parent();
done
dbms_output.put_line('inserted missing parent');
sp_cust_hier_dnorm_2();
dbms_output.put_line('table denormalised');
sp_control_customer_insert('D');
exception
when others then 
l_err_msg:=sqlerrm;
dbms_output.put_line('Sql error '|| l_err_msg);
end;
/
set serveroutput off
set feedback off
set verify off
set heading off
exit;
EOF`
2

See if this change to your sql is what you want, Changes are in red:

set serveroutput on 
set feedback on
declare 
  l_err_msg varchar2(1000);
  v_counter simple_integer  := 0;
begin
  sp_control_customer_insert('E');
  sp_update_customer();
  dbms_output.put_line('customer update');
  loop
    v_counter  :=  v_counter + 1;
    exit when v_counter > 8;
    sp_insert_customer_parent();
  end loop;
  dbms_output.put_line('inserted missing parent');
  sp_cust_hier_dnorm_2();
  dbms_output.put_line('table denormalised');
  sp_control_customer_insert('D');
exception
  when others then 
    l_err_msg:=sqlerrm;
    dbms_output.put_line('Sql error '|| l_err_msg);
end;
/