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_comp lete_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_cod e,'GH','NR ','CP','NR ','SV','VN ',wr.tax_d istrict_co de) = 'VN'
and
wrt.wr_task_no = 106
and
wrt.task_status_code = 'C'
and
trunc(wrt.act_complete_dat e_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_comp lete_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_cod e,'GH','NR ','CP','NR ','SV','VN ',wr.tax_d istrict_co de) = 'VN'
and
wrt.wr_task_no = 106
and
wrt.task_status_code = 'C'
and
trunc(wrt.act_complete_dat e_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
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_comp
rpad(wr.wr_no,7, ' ') wrno,
rpad(wr.wr_status_code,5,'
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_cod
and
wrt.wr_task_no = 106
and
wrt.task_status_code = 'C'
and
trunc(wrt.act_complete_dat
UNION
-- FROM ARCHIVES TABLES
select
to_char(Sysdate, 'MM/DD/YYYY,HH:MI:SS') today1,
to_char(trunc(wrt.act_comp
rpad(wr.wr_no,7, ' ') wrno,
rpad(wr.wr_status_code,5,'
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_cod
and
wrt.wr_task_no = 106
and
wrt.task_status_code = 'C'
and
trunc(wrt.act_complete_dat
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
The simple way is to use plsql developer or TOAD.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
....
END;
/
spool off
set serveroutput off
host notepad c:\folog.txt
exit
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
Thanks again to all