Hi,
I created a script which connects to database and update a table.
This script is running fine when i run it manually but when i am trying to execute it scheduling in crontab.script is executing but Data is not getting updated.
below is my script
sqlplus test/##### >> test_feed.log <<!
prompt Disabling Trigger CHANGE_DATE_TRG...
ALTER TRIGGER CHANGE_DATE_TRG DISABLE;
prompt Updating Date in TEST_FEEDS Table..
UPDATE TEST_FEEDS SET NEXT_EXPECTED=trunc(SYSDATE-2),NEXT_APPLICABLE=trunc(SYSDATE-2) WHERE DATAFEED_APK IN ('OTC','OTW');
COMMIT;
prompt Date updated in Test_feeds table as below
select NEXT_EXPECTED,NEXT_APPLICABLE from TEST_FEEDS where DATAFEED_APK IN ('OTC','OTW');
prompt Enabling Trigger CHANGE_DATE_TRG...
ALTER TRIGGER CHANGE_DATE_TRG ENABLE;
!
Please help and suggest changes required in the script to get it execute through a cron.
Many thanks in advance.
A typical problem when using cron like this is that the environment that you have set up when you log in is not inherited by jobs run by cron. Assuming that sqlplus isn't on your system's default command search path, you can probably fix this by changing the sqlplus at the start of your cron job to an absolute pathname for sqlplus .
1 Like
Yes,
and you'll probably also need to export the ORACLE_HOME variable
(and add <ORACLE_HOME>/bin to the PATH).
1 Like
As suggested, my script should look like this can you please confirm
ORACLE_HOME=/test/orabase/product/9.2.0
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_HOME
export PATH
echo $ORACLE_HOME
echo $PATH
sqlplus test/##### >> test_feed.log <<!
prompt Disabling Trigger CHANGE_DATE_TRG...
ALTER TRIGGER CHANGE_DATE_TRG DISABLE;
prompt Updating Date in TEST_FEEDS Table..
UPDATE TEST_FEEDS SET NEXT_EXPECTED=trunc(SYSDATE-2),NEXT_APPLICABLE=trunc(SYSDATE-2) WHERE DATAFEED_APK IN ('OTC','OTW');
COMMIT;
prompt Date updated in Test_feeds table as below
select NEXT_EXPECTED,NEXT_APPLICABLE from TEST_FEEDS where DATAFEED_APK IN ('OTC','OTW');
prompt Enabling Trigger CHANGE_DATE_TRG...
ALTER TRIGGER CHANGE_DATE_TRG ENABLE;
!
If in sqlplus test/##### you don't use a TNS alias/service name/sid,
you'll need to add ORACLE_SID as well:
ORACLE_HOME=/test/orabase/product/9.2.0
PATH=$PATH:$ORACLE_HOME/bin
ORACLE_SID=<your sid>
export ORACLE_HOME ORACLE_SID PATH
printf 'ORACLE_HOME is %s\nORACLE_SID is: %s\nPATH set to: %s\n' \
"$ORACLE_HOME" "$ORACLE_SID" "$PATH"
"$ORACLE_HOME"/bin/sqlplus test/##### >> test_feed.log <<!
prompt Disabling Trigger CHANGE_DATE_TRG...
ALTER TRIGGER CHANGE_DATE_TRG DISABLE;
prompt Updating Date in TEST_FEEDS Table..
UPDATE TEST_FEEDS SET NEXT_EXPECTED=trunc(SYSDATE-2),NEXT_APPLICABLE=trunc(SYSDATE-2) WHERE DATAFEED_APK IN ('OTC','OTW');
COMMIT;
prompt Date updated in Test_feeds table as below
select NEXT_EXPECTED,NEXT_APPLICABLE from TEST_FEEDS where DATAFEED_APK IN ('OTC','OTW');
prompt Enabling Trigger CHANGE_DATE_TRG...
ALTER TRIGGER CHANGE_DATE_TRG ENABLE;
!
You may also want to redirect stderr to test_feed.log:
>> test_feed.log 2>&1
1 Like
I will try this code and get back to you. Meanwhile could you please help me to understand below i didnt get it.
You may also want to redirect stderr to test_feed.log:
---------- Post updated at 05:43 AM ---------- Previous update was at 05:27 AM ----------
How about doing it this way
ORACLE_HOME=/test/orabase/product/9.2.0
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_HOME
export PATH
echo $ORACLE_HOME
echo $PATH
sqlplus >> test_feed.log <<!
CONNECT test/#####@sid
With the following redirection test_feed.log will contain both stdout and stderr (standard output and standard error):
sqlplus >> test_feed.log 2>&1 <<!
CONNECT test/#####@<tns alias>
If you don't want to export the ORACLE_SID variable for whatever reason
and you want to use the above syntax, you'll need to pass a TNS service descriptor or the EZ-connect string (not the sid).