0

I have a shell script which executes a pl/sql block, I'm printing output to output file but it's not keeping old information and not displaying time and date when the script was executed.

My script below.

#!/bin/bash

sqlplus -s $DATABASE > output << EOSQL 

SET FEEDBACK ON

declare

veventSource varchar2(20);
verrorcode integer;
verrorMessage varchar2(255);

begin

for rec in (select substr(customer,3) as pid, substr(account_num,1,2) as 
countrycode from account 
where substr(account_num,1,2) = substr(account_num,3,2)
and customernot in (select customer from custsource) )

loop

veventSource := rec.countrycode||'_'||rec.pid;
IPGDDL.LINKTOAC(rec.countrycode,rec.LID,veventSource,
'01-JAN-1971',null,'F',null,null,null,null,null,verrorcode,verrorMessage);
dbms_output.put_line(verrorcode);
dbms_output.put_line(verrorMessage);
commit;

end loop;

end;
/
EXIT

EOSQL

After executing the script I do have a log file, which only prints PL/SQL Procedure completed successfully.

I want sql output to be printed in output.txt file in the directory, with date and time when cron job run with this script, and also keep all the old data in the log.

Can anyone help on this please.

1

To have sqlplus print out the output of your script you can use:

set serveroutput on

From SQL*Plus® User's Guide and Reference:

Controls whether to display output (that is, DBMS_OUTPUT.PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus.

To have the output appended to output.txt you may use the >> redirection operator:

sqlplus -s $DATABASE >>output.txt << EOSQL
...

The > operator truncates the file on its right hand, if it exists, to a zero size.
Reference: Bash Reference Manual (assuming Bash based on the #!/bin/bash shebang of your script).

To include the date and time of execution of your cron jobs you may add

date >>output.txt

to your script, before the invocation of sqlplus.
Refer to the documentation for the date command installed on your system (e.g. man date) for the date format options available to you.

Alternatively, you may drop the -s option to sqlplus: its output will then include a timestamp, saving you the need for any further command to record the execution date/time (but it will also include a number of possibly unwanted messages).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.