SQL & PL/SQL

Hi,

I was trying to run a unix script so that I can update one of the table with current system date, but the script works when run in Toad (which is a tool similar to SQL Developer), but does not do the update when done via unix script ..


SQL_DBTYPE=ORACLE
SQL_USERNAME=username
SQL_PASSWORD=password
export SQL_DBTYPE SQL_USERNAME SQL_PASSWORD
export ORACLE_HOME=/u000/app/oracle/product/11g
export PATH=/usr/ccs/bin:$ORACLE_HOME/bin:/etc:/user/bin:$PATH
export LIBPATH=$ORACLE_HOME/ocommon/nls/admin/data
export ORACLE_BASE=/u000/app/oracle
export ORACLE_SID=ACTB
echo "started running sql to update VVAR with refresh date"

return_value=`sqlplus -s username/password << EOF
set serveroutput on;
declare
message varchar2(30);
l_refdate varchar2(10);
l_eodate varchar2(10);
message2 varchar2(5);
l_sysdate varchar2(10);
l_database varchar2(20);
begin

select rtrim(pkvars.database_name) into l_database from dual ;
dbms_output.put_line('database is ' || l_database);
dbms_output.put_line('sysdate is ' || sysdate);
dbms_output.put_line('eodate is ' || pkdates.eodate);


dbms_output.put_line('before update');
l_refdate := pkdates.refrdate;

dbms_output.put_line(l_refdate);
dbms_output.put_line('after select');

pkvars.vvar_alpha_set('&$REFR_DATE',TO_CHAR(pkdates.ora_sysdate,'FM000000'));

dbms_output.put_line('after update');



dbms_output.put_line('refdate is ' || pkdates.refrdate);
COMMIT;
dbms_output.put_line('after commit refdate is ' || pkdates.refrdate);

exception
when OTHERS then
dbms_output.put_line('errors ');
dbms_output.put_line('sqlerrm: '||SQLERRM);
dbms_output.put_line('backtrace: '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);


end;
/
set serveroutput off;
exit 0;

EOF`


sms_message=`echo "$return_value"`

echo "$sms_message"

----- the output when script is run is
onp@amptruasd02:/admin/scripts>update_vvar.sh
started running sql to update VVAR with refresh date
database is ACTB
sysdate is 03-JAN-12
eodate is 077435
before update
077429
after select
after update
refdate is 077429
after commit refdate is 077429

PL/SQL procedure successfully completed.
*********************
it does not seem to update the refdate, as it is supposed to be updated to a value of 077434.
when same script is run within TOAD, the output is

sysdate is 03-JAN-12
eodate is 077435
before update
077429
after select
after update
refdate is 077434
after commit refdate is 077434
PL/SQL procedure successfully completed.


pkdates.refrdate is a package that simply gets the value of the column from the table.
pkvars.vvar_alpha_set('&$REFR_DATE',TO_CHAR(pkdates.ora_sysdate,'FM000000')) is a routine that merely updates the particular column to a particular value that is passed in the 2nd parameter'.


Is there something I need to check on ? I checked the SYSTEM.PRODUCT_USER_PROFILE, but there are no records;
Thank you.