• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5000
  • Last Modified:

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
0
jande2
Asked:
jande2
2 Solutions
 
gattu007Commented:
The simple way is to use plsql developer or TOAD.
0
 
YANN0SCommented:
use the host command
> host notepad c:\folog.txt
0
 
gattu007Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mohammadzahidCommented:
you can accomplish this task using a .bat file

here is an example:
.bat file
sqlplus user1/password1@abcserver @c:\test1.sql
notepad c:\test.txt

.sql file will the one running the code you posted in your question
0
 
YANN0SCommented:
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
0
 
jande2Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now