[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
Re: SQR, PL/SQL and DBMS_OUTPUT
Hi Gini,
I find this post interesting... Here's a brief synopsis for those who
aren't familiar with DBMS_OUTPUT... DBMS_OUTPUT is a seperate package
included with PL/SQL to provide some messaging functionality since
PL/SQL does not support File I/O... SET SERVEROUTPUT ON is a SQL*Plus
command used to view or spool the buffer output to a file... I don't
know of a way to access the Message Buffer outside of SQL*Plus... SQR
has the capacity to execute PL/SQL blocks within begin-sql and end-sql
statements... I don't believe you are sunk Gini... You may need to make
some slight modifications... Here's an example that shows an alternative
method that took me less than 15 minutes to produce...
First I created a table (using PeopleSoft's Data Designer) to simulate
the DBMS_OUTPUT buffer... then I wrote a quick SQR that executes a
PL/SQL block that reads a table and produces a message (instead of
DBMS_OUTPUT.put_line() I use a procedure declared within the block -
this updates the new table)... After the PL/SQL block completes I select
and display the messages... You can get a little more sophisticated then
this... using a stored procedure, different table structure, etc.
However you want... remember this is a "quick" sample... The Output is
included after the SQR Source...
I hope this gives you an idea at least... -Tony DeLia
!**********************************************************************
!* *
!* MODULE: TD_DBMS.SQR *
!* AUTHOR: TONY DELIA. *
!* DATE: 01/27/1999. *
!* SYSTEM: TD SQR UTILITY SERIES. *
!* DESC: SQR PL/SQL DBMS_OUTPUT SIMULATION (EXAMPLE). *
!* *
!**********************************************************************
!* *
!* NOTE: PS_DBMS_OUTPUT Table Structure *
!* *
!* Name Null? Type *
!* ------------------ -------- ---- *
!* DBMS_KEY NOT NULL VARCHAR2(15) *
!* DBMS_SEQ NOT NULL NUMBER(38) *
!* DBMS_LINE NOT NULL VARCHAR2(254) *
!* *
!**********************************************************************
!* *
!* LEGAL: CONFIDENTIALITY INFORMATION. *
!* *
!* This module is the original work of Tony DeLia. It *
!* can be considered ShareWare under the following *
!* conditions. *
!* *
!* A - The author's name (Tony DeLia) remains on any *
!* and all versions of this module. *
!* B - Any modifications must be clearly identified. *
!* C - A "vanilla" copy of this module must be kept *
!* alongside any revised versions. *
!* *
!* Questions/Comments: tdelia@erols.com *
!* *
!**********************************************************************
#include 'setenv.sqc' ! Set environment
!**********************************************************************
!* Setup Procedure *
!**********************************************************************
begin-setup
#Include 'setup02a.sqc' ! Printer and page-size init landscape
end-setup
!**********************************************************************
!* Mainline Processing *
!**********************************************************************
begin-report
do Init-DateTime
do Init-Routine
do Build-DBMS-Output
do Print-DBMS-Output
end-report
!**********************************************************************
!* Initialization Routine *
!**********************************************************************
begin-procedure Init-Routine
let $ReportTitle = 'PL/SQL DBMS_OUTPUT Simulation'
let $ReportId = 'TD_DBMS'
end-procedure
!**********************************************************************
!* Build DBMS_OUTPUT *
!**********************************************************************
begin-procedure Build-DBMS-Output
begin-sql
DECLARE
!* *---------------------------------------------------------------* */
!* * Declare Select Cursor * */
!* *---------------------------------------------------------------* */
cursor c1 is
select a.fieldvalue,
a.xlatlongname
from xlattable a
where a.fieldname = 'MONTHCD'
and a.language_cd = 'ENG'
and a.effdt =
(select max(a2.effdt)
from xlattable a2
where a2.fieldname = a.fieldname
and a2.language_cd = a.language_cd
and a2.fieldvalue = a.fieldvalue
and a2.effdt <= sysdate);;
!* *---------------------------------------------------------------* */
!* * Declare Database Fetch Variables * */
!* *---------------------------------------------------------------* */
a_fieldvalue xlattable.fieldvalue%TYPE;;
a_xlatlongname xlattable.xlatlongname%TYPE;;
!* *---------------------------------------------------------------* */
!* * Declare Work Variables * */
!* *---------------------------------------------------------------* */
w_key ps_dbms_output.dbms_key%TYPE;;
w_seq ps_dbms_output.dbms_seq%TYPE;;
w_line ps_dbms_output.dbms_line%TYPE;;
!* *---------------------------------------------------------------* */
!* * Put DBMS Output * */
!* *---------------------------------------------------------------* */
PROCEDURE Put_DBMS_Output (i_dbms_key VARCHAR2,
i_dbms_seq NUMBER,
i_dbms_line VARCHAR2)
IS
BEGIN
insert into ps_dbms_output
(dbms_key,
dbms_seq,
dbms_line)
values (i_dbms_key,
i_dbms_seq,
i_dbms_line);;
END Put_DBMS_Output;;
!* *---------------------------------------------------------------* */
!* * Delete DBMS Output * */
!* *---------------------------------------------------------------* */
PROCEDURE Delete_DBMS_Output (d_dbms_key VARCHAR2)
IS
BEGIN
delete
from ps_dbms_output
where dbms_key = d_dbms_key;;
commit;;
END Delete_DBMS_Output;;
!* *---------------------------------------------------------------* */
!* * Mainline Processing * */
!* *---------------------------------------------------------------* */
BEGIN
w_key := $ReportId;;
w_seq := 0;;
Delete_DBMS_Output(w_key);;
open c1;;
loop
fetch c1 into a_fieldvalue,
a_xlatlongname;;
exit when c1%NOTFOUND;;
w_seq := w_seq + 1;;
w_line := 'Month: ' || a_fieldvalue || ' ' || a_xlatlongname;;
Put_DBMS_Output(w_key,
w_seq,
w_line);;
end loop;;
!* *---------------------------------------------------------------* */
!* * Close Cursor and Commit Last Updates * */
!* *---------------------------------------------------------------* */
close c1;;
commit;;
END;;
!* *---------------------------------------------------------------* */
!* * End of PL/SQL Program * */
!* *---------------------------------------------------------------* */
end-sql
end-procedure
!**********************************************************************
!* Print DBMS_OUTPUT *
!**********************************************************************
begin-procedure Print-DBMS-Output
begin-select
u.dbms_key
u.dbms_seq
u.dbms_line
display &u.dbms_line
from ps_dbms_output u
where u.dbms_key = $ReportId
order by u.dbms_key,
u.dbms_seq
end-select
end-procedure
!**********************************************************************
!* Include Members: *
!**********************************************************************
#Include 'curdttim.sqc' !Get-Current-DateTime procedure
#Include 'datetime.sqc' !Routines for date and time formatting
!Init-DateTime procedure
!**********************************************************************
!* End of Program *
!**********************************************************************
Here's the SQR.LOG from this run... xlate values 01 - 12 for MONTHCD
used...
Month: 01 January
Month: 02 February
Month: 03 March
Month: 04 April
Month: 05 May
Month: 06 June
Month: 07 July
Month: 08 August
Month: 09 September
Month: 10 October
Month: 11 November
Month: 12 December
Cursor Status:
Cursor #1:
SQL = ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY'
Compiles = 1
Executes = 1
Rows = 0
Cursor #2:
SQL = DECLARE cursor c1 is select a.fieldvalue, a.xlatlongname from
xlattable a where a.fieldname = 'MONTHCD' and
a.language_cd = 'ENG' and a.effdt = (select
max(a2.effdt) from xlattable a2 where a2.fieldname =
a.fieldname and a2.language_cd = a.language_cd and a2.fieldvalue
= a.fieldvalue and a2.effdt <= sysdate); a_fieldvalue
xlattable.fieldvalue%TYPE; a_xlatlongname
xlattable.xlatlongname%TYPE; w_key
ps_dbms_output.dbms_key%TYPE; w_seq
ps_dbms_output.dbms_seq%TYPE; w_line
ps_dbms_output.dbms_line%TYPE; PROCEDURE Put_DBMS_Output
(i_dbms_key VARCHAR2, i_dbms_seq NUMBER, i_dbms_line
VARCHAR2) IS BEGIN insert into ps_dbms_output (dbms_key, dbms_seq,
dbms_line) values (i_dbms_key, i_dbms_seq, i_dbms_line); END
Put_DBMS_Output; PROCEDURE Delete_DBMS_Output (d_dbms_key
VARCHAR2) IS BEGIN delete from ps_dbms_output where dbms_key
= d_dbms_key; commit; END Delete_DBMS_Output; BEGIN w_key := :p1;
w_seq := 0; Delete_DBMS_Output(w_key); open c1; loop fetch c1 into
a_fieldvalue, a_xlatlongname; exit when c1%NOTFOUND; w_seq :=
w_seq + 1; w_line := 'Month: ' || a_fieldvalue || ' ' ||
a_xlatlongname; Put_DBMS_Output(w_key, w_seq, w_line); end loop;
close c1; commit; END;
Compiles = 1
Executes = 1
Rows = 1
Cursor #3:
SQL = select u.dbms_key, u.dbms_seq, u.dbms_line from
ps_dbms_output u
where u.dbms_key = :1 order by u.dbms_key, u.dbms_seq
Compiles = 1
Executes = 1
Rows = 12
Cursor #4:
SQL = select TO_CHAR(SYSDATE,'DD-MON-YYYY_HH24:MI:SS') FROM PSLOCK
Compiles = 1
Executes = 0
Rows = 0
SQRW: End of Run.
-Tony DeLia
PS - SQR is pretty versatile!
Gini Potts (ginip) wrote:
>
> I want to execute an PL/SQL program from an SQR. This particular PL/SQL
> program uses calls to DBMS_OUTPUT to write a report. When I run this report
> through SQLPlus, I SET SERVEROUTPUT ON and spool the output to a file. Is
> there a way to access the serveroutput with an SQR, or am I sunk?
> (Unfortunately, changing DBMS_OUTPUT to UTL_FILE is not an option.)
>
> Thanks.
>
> Gini
--
Tony DeLia
AnswerThink Consulting Group
PeopleSoft Solutions Practice - Delphi Partners
tdelia@erols.com