Print from PL/SQL script

just spool the output in your shell script., i guess you already have an one right?

after connecting to sqlplus, just type the following command

sqlplus -s username/password <<EOF
set serveroutput on size 10000
spool log.log
--now paste your db script here..
spool off
exit
EOF

>>using nohup directing out put to a file<<

Create a shell script to call your pl/sql procedure.

For example, we'll call it call_proc.sh.
sqlplus << EOF
username/password@host
set echo on
set serveroutput on
set timing on
@procedure_filename
exit
EOF

Where the procedure_filename contains your pl/sql script (note - requires an extension of .sql, otherwise extension must be included in the filename).

Then, from the unix (I assume) prompt enter -
nohup ./call_proc.sh > output.log

Is there any way that I can flush the buffer. I am running the script using nohup and spooling the output to a file. I have 20K records that needs to be updated. can I flush buffer (to write to spooling file) after every update..??

Thanks,

i guess you dont need to!!

if you've used file writing mechanisms in pl/sql, then you may need an one, but not in spooling..

I don't see anything written to output file....

Shell Script that I run:

> nohup.out
date > out
sqlplus epi_forum/ef96775@test01 << EOF
SET SERVEROUTPUT ON FORMAT WRAP size 10000
set head off;
spool gailes.lst
@GALES_URL_APPEND.sql   -- Script
spool off
exit
EOF
date >> out

Nothing is wriiten to gailes.lst..!!!

Partial SQL Script:


   FOR url_rec IN url_cur LOOP
          url_count:= url_count+1;
        dbms_output.put_line('URLs Appended (Total 20832): '||url_count);  

          Select url_rec.URL into v_1 from dual;
        Select url_rec.ARTICLE_PATH into v_2 from dual;
        Select url_rec.ARTICLE_NUMBER into v_3 from dual;
        Select url_rec.TIMESTAMP into v_4 from dual;
        
        OPEN holding_cur(v_2,v_3,v_4);       
        LOOP
         EXIT WHEN NOT holding_cur%FOUND;  
                   FETCH holding_cur INTO holding_rec;       
                    select holding_rec.NEW_ARTICLE_ID into v_5 from dual;
                    select holding_rec.MESSAGE into v_6 from dual;                   
                    UPDATE EPI_FORUM_HOLDING_ST SET message = v_6||CHR(10)||CHR(13)||v_1 WHERE NEW_ARTICLE_ID = v_5;
                       commit;                     
              END LOOP;
              CLOSE holding_cur;
     commit;

   END LOOP;

Thanks,

venkat80,
    johnsone finally said what I would suggest - UTL_FILE.  Just open the file wth append, write the record and close it.  Then you can see intermediate results:

procedure log_this(line in varchar2, logfile in varchar2, logfile_loc in varchar2) is
   file_handle utl_file.file_type;
begin
   file_handle := utl_file.fopen(logfile_loc, logfile, 'a');
   utl_file.put_line(file_handle, line);
   fclose(file_handle);
end;

You would need to open the file once (the first time) in 'w' mode to create it.

Good luck!
DrSQL