?
Solved

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

Posted on 2007-07-25
6
Medium Priority
?
4,740 Views
Last Modified: 2013-12-18
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
Comment
Question by:jande2
6 Comments
 
LVL 7

Expert Comment

by:gattu007
ID: 19566486
The simple way is to use plsql developer or TOAD.
0
 
LVL 8

Accepted Solution

by:
YANN0S earned 400 total points
ID: 19566560
use the host command
> host notepad c:\folog.txt
0
 
LVL 7

Expert Comment

by:gattu007
ID: 19566573
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 11

Assisted Solution

by:mohammadzahid
mohammadzahid earned 100 total points
ID: 19566644
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
 
LVL 8

Expert Comment

by:YANN0S
ID: 19566701
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
 

Author Comment

by:jande2
ID: 19566777
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question