Link to home
Start Free TrialLog in
Avatar of jande2
jande2

asked on

Oracle sqlplus script to open notepad or wordpad in Windows to view report output.

I am using an Oracle sqlplus script to run reports and I need to have the spooled text file output open notepad or wordpad in windows when the script is complete so the report can be viewed and or printed.


PROMPT FIELD ORDER ISSUED FOR Van Nuys DISTRICT ON A DESIRED DATE

SET echo off
SET verify off
SET serveroutput on size 1000000
SET linesize 123

CLEAR BUFFER
CLEAR COLUMNS
CLEAR BREAKS


ACCEPT s_date char FORMAT A10 PROMPT 'Enter the desired date in MM/DD/YYYY: '


spool c:\folog.txt /notepad.exe


DECLARE

      v_fo_count number(7);

      --FROM ACTIVE TABLES

      cursor v_fo_cur IS
      select
      to_char(Sysdate, 'MM/DD/YYYY') today1,
      to_char(trunc(wrt.act_complete_date_time), 'MM/DD/YYYY') cdate,
      rpad(wr.wr_no,7, ' ') wrno,
      rpad(wr.wr_status_code,5,' ') status,
      rpad(wr.wr_type_code,5,' ') type,
      rpad(wr.street_no||' '||
        wr.address_1||' '||wr.street_type_code||
        ' '||wr.unit_no, 30, ' ') address,
      '        ' crew,
      '               |               |' cs
      from
      work_request wr,
      wr_task wrt
      where
      wr.wr_no = wrt.wr_no
      and
      decode(wr.tax_district_code,'GH','NR','CP','NR','SV','VN',wr.tax_district_code) = 'VN'
      and
      wrt.wr_task_no = 106
      and
      wrt.task_status_code = 'C'
      and
      trunc(wrt.act_complete_date_time) = to_date('&s_date', 'MM/DD/YYYY')
      UNION
      -- FROM ARCHIVES TABLES
      select
      to_char(Sysdate, 'MM/DD/YYYY,HH:MI:SS') today1,
      to_char(trunc(wrt.act_complete_date_time), 'MM/DD/YYYY') cdate,
      rpad(wr.wr_no,7, ' ') wrno,
      rpad(wr.wr_status_code,5,' ') status,
      rpad(wr.wr_type_code,5,' ') type,
      rpad(wr.street_no||' '||
        wr.address_1||' '||wr.street_type_code||
        ' '||wr.unit_no, 30, ' ') address,
      '        ' crew,
      '               |               |' cs

      from
      a_work_request wr,
      a_wr_task wrt
      where
      wr.wr_no = wrt.wr_task_no
      and
      decode(wr.tax_district_code,'GH','NR','CP','NR','SV','VN',wr.tax_district_code) = 'VN'
      and
      wrt.wr_task_no = 106
      and
      wrt.task_status_code = 'C'
      and
      trunc(wrt.act_complete_date_time) = to_date('&s_date', 'MM/DD/YYYY')
      order by 1,2,3,4,5;

      fo_rec v_fo_cur%rowtype;

BEGIN
      DBMS_OUTPUT.PUT_LINE ('LIST OF WORK REQUEST WITH FIELD ORDER ISSUED ON ' ||
        to_date('&s_date', 'MM/DD/YYYY')|| ' FOR Van Nuys DISTRICT');
        DBMS_OUTPUT.PUT_LINE ('RUN DATE AND TIME: ' ||to_char(SysDate, 'MON-DD-YYYY, HH:MI:SS'));
        DBMS_OUTPUT.PUT_LINE (CHR(5));
      DBMS_OUTPUT.PUT_LINE ('| REQ 106                                                                                      COMPLETION DATE  ');
        DBMS_OUTPUT.PUT_LINE ('| COMP DATE     WR NO    STATUS   TYPE     ADDRESS                            CREW NO           SERVICE  |  METER ');
        DBMS_OUTPUT.PUT_LINE ('|----------- - ------- - ------ - ----- - ------------------------------- - ------------ - ------------------------------|');
        DBMS_OUTPUT.PUT_LINE ('|            |         |        |       |                                 |              |               |               |');

        IF NOT v_fo_cur%ISOPEN
        THEN
        OPEN v_fo_cur;
        END IF;

      FETCH
      v_fo_cur INTO fo_rec;
      v_fo_count := 0;

      WHILE v_fo_cur%FOUND

      LOOP
            v_fo_count := v_fo_count+1;

            DBMS_output.put_line ('');
            DBMS_output.put ('| '||fo_rec.cdate||' '||'|');
            DBMS_output.put (' '||fo_rec.wrno||' '||'|');
            DBMS_output.put (' '||fo_rec.status||'  '||'|');
            DBMS_output.put (' '||fo_rec.type||' '||'|');
            DBMS_output.put (' '||fo_rec.address||'  '||'|');
            DBMS_output.put (fo_rec.crew||'      '||'|');
            DBMS_output.put (fo_rec.cs||' ');
            DBMS_output.put_line ('');
            DBMS_output.put ('|____________|_________|________|_______|_________________________________|______________|_______________|_______________|');
            DBMS_output.put_line ('');
            DBMS_output.put ('|            |         |        |       |                                 |              |               |               |');
            DBMS_OUTPUT.NEW_LINE;
      FETCH v_fo_cur INTO fo_rec;
      END LOOP;
      CLOSE v_fo_cur;
            DBMS_output.put_line ('|');
            DBMS_output.put_line ('***** '||v_fo_count||' FIELD ORDER TOTAL');

END;
/
spool off

set serveroutput off

exit
Avatar of gattu007
gattu007

The simple way is to use plsql developer or TOAD.
ASKER CERTIFIED SOLUTION
Avatar of YANN0S
YANN0S

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I use plsql developer...

once you see the output... then u will also see an option "Export query results to"  you can save the results in CSV, TSV, HTML or XML format.

If you want to do only from sqlplus... then spool the outpt and then you will be able to print

example:

SQL> spool test.out
SQL> select ..................
sql > spool off
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
To avoid any misunderstandings 'host' is an SQL/Plus command. So you simply need to add to your script:


....


END;
/
spool off

set serveroutput off
host notepad c:\folog.txt
exit
Avatar of jande2

ASKER

Special thanks to Yannos and Mohammadzahid, the examples were very important and pointed to exactly what I am working with.  Since I am at a novice level the examples showing what I need to do in my posted code helps tremendously.  The users I am helping will be pleased.
Thanks again to all