Stored Procedure not executing

Shell Programming and Scripting
1 
 
 
  below code is not executing  the stored procedure,not sure what the issue.Even sqllog is blank.please help me its very urgent.
sqlplus -s $connect_str@$DB_ORACLE_SID >> ${SQL_LOG_FILE} << EOF
set serverout on
set feed off
set head off
set pages 0
set colsep ,
set tab off
set lin 150
WHENEVER SQLERROR EXIT 9
SPOOL ${SQL_LOG_FILE}
set timing on
DECLARE 
v_view_name VARCHAR2(100):='callcenter_reporting_test_proc';
v_day number(5):='3'; 
begin
dbms_output.put_line('Purging partition for table '|| v_table_name);
dbms_output.put_line('Purging partition for table '|| v_table_name||' Purging Days'|| v_ret_days);
MBSSMGR.Daily_Purge_Partitions(v_view_name,v_day);
COMMIT; 
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line( SQLCODE || '-' || SQLERRM);
end;
/
SPOOL OFF 
EOF`
2

How do you know that your Stored Procedure is not executing? Are you getting any errors or warnings?

Also is it programmed to return any results if ran successfully?

Try calling it from SQL*Plus and check the results:

SQL> CALL MBSSMGR.Daily_Purge_Partitions('callcenter_reporting_test_proc','3');
3

Please read the forum rules.

4

i didn't find the v_table_name and v_ret_days in the dbms_output.

---------- Post updated at 02:29 AM ---------- Previous update was at 02:28 AM ----------

Can run the manually for the Daily_Purge_Partitions proc

---------- Post updated at 02:42 AM ---------- Previous update was at 02:29 AM ----------

i tried with sample it's working for me. Can you try like

 #!/bin/sh
export ORACLE_SID=DEXTER
export ORACLE_HOME=/opt/oracle/11g/
export PATH=$PATH:/opt/oracle/11g/bin
SQL_LOG_FILE='/home/oracle/test.txt'
echo $SQL_LOG_FILE
ext_sta=`sqlplus -s training1/training1@orcl <<eof
set serverout on
set feed off
set head off
set pages 0
set colsep ,
set tab off
set line 150
WHENEVER SQLERROR EXIT 9
SPOOL $SQL_LOG_FILE
set timing on
DECLARE 
v_view_name VARCHAR2(100):='proc1';
v_day number(5):='3'; 
begin
dbms_output.put_line('Purging partition for table');
proc1;
COMMIT; 
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line( SQLCODE || '-' || SQLERRM);
end;
/
SPOOL OFF 
EOF`

Let me know