raym@hrshcx.UUCP (Ray Morin) (09/01/89)
Hello ,
Please excuse me for posting this to comp.databases but a previous
attempt to post to comp.sources.misc was unsuccessful. I posted a notice
of the posting to comp.sources.misc here so people are probably searching
for it.
The following is source for an RPT based trigger documentation
reporting tool for ORACLE*sqlforms
* NOTE WORKS UP TO VERSION 2.3 OF FORMS FOR VERSION 2.3 and up USE form23.rpt
This is one of many responses to a call for help on the net
for tools to document SQL*FORMS.
Ray Morin
(305)977-4453
P.S
For all tools of this nature search for key words ORACLE, DOCUMENT,
SQLFORMS
------------------- cut here cut here -------------------------------
.rem **************************************************************************
.rem * This RPT application is designed to provide hardcopy documentation
.rem * for specific SQL*FORMS applications by using the Oracle dictionary.
.rem * The output might be helpful for debugging SQL*FORMS applications.
.rem *
.rem * Usage: RPT FORMS FORMS username/password
.rem * RPF FORMS FORMS
.rem *
.rem * Note: Requires RPT Version 1.0.8 due to use of the TELL command.
.rem *
.rem * Author: Dennis Cochran Date: June 20, 1986
.rem * Modified: 9/19/86 to incorporate changes in SQL*FORMS 2.0.9
.rem * 12/22/86 to incorporate COMMENT support for V2.0.16
.rem **************************************************************************
.page 1 54
.rem ****** Declare IAPAPP table related variables ******
.DECLARE appid 99999 .rem Application ID number
.DECLARE appowner A30 .rem Application Owner's Oracle user name
.DECLARE appname A30 .rem Short Application name
.DECLARE apptitle A80 .rem Title used for main IAP menu
.rem [appwksize] context area size in K bytes
.rem ****** Declare IAPBLK table related variables ******
.DECLARE blkname A30 .rem Block name
.DECLARE blkdesc A60 .rem Menu line description for this block
.rem [blkhide] Y = do not display desc. in menu
.DECLARE blkseq 999 .rem Sequence number of block within application
.DECLARE blkunqkey A3 .rem Y = check for uniqueness of primary key
.rem [blkctrl] Y = Control Block
.rem [blktowner] base table owner || to blktname
.DECLARE blktname A61 .rem Name of the base table. NULL=Control Block
.DECLARE blknorec 99 .rem Number of rows to display
.DECLARE blknobuf 999 .rem Number of rows to buffer
.DECLARE blkblin 99 .rem Base line
.DECLARE blklnrc 99 .rem Number of lines per logical record
.rem [blkobysql] dflt ORDER BY pointer. Use sqtno
.rem ****** Declare all IAPCOMMENT table related variables ******
.rem Primary Key values need not be declared again.
.rem [cmtline] for order by only
.DECLARE cmttext A80 .rem Comment line of text
.rem ****** Declare all IAPFLD table related variables ******
.DECLARE fldname A30 .rem Field name
.DECLARE fldseq 99 .rem Sequence number of field within block
.DECLARE fldtype A7 .rem Field datatype
.DECLARE fldlen 99 .rem Field length
.rem [flddlen] Field display length not used
.DECLARE fldqlen 99 .rem Query length
.DECLARE fldbtab A3 .rem Y = Database field
.DECLARE fldkey A3 .rem Y = field part of primary
.rem [fldckblk] block name || to fldckfld
.DECLARE fldckfld A61 .rem field name from which to copy key
.DECLARE flddflt A80 .rem default value
.DECLARE flddisp A3 .rem Y = displayed field
.rem [fldpage] page number
.rem [fldline] line number
.rem [fldcol] column number
.DECLARE fldprompt A80 .rem field prompt
.rem [fldprabov] display prompt above field
.rem [fldprrpt] repeat prompt for each record
.DECLARE fldenter A3 .rem Y = enterable field
.DECLARE fldquery A3 .rem Y = queryable field
.DECLARE fldupdate A3 .rem Y = updatable field
.DECLARE fldupdnul A3 .rem Y = updateable if NULL field
.DECLARE fldmand A3 .rem Y = mandatory field
.DECLARE fldfixed A3 .rem Y = fixed length field
.DECLARE fldskip A3 .rem Y = skip to next field when full
.DECLARE fldhide A3 .rem Y = no echo of keyed values to screen
.DECLARE fldautohlp A3 .rem Y = auto display HELP on field entry
.DECLARE fldupper A3 .rem Y = convert to upper case
.rem [fldlovt] name of table || fldlovc
.DECLARE fldlovc A80 .rem name of the list-of-values column
.DECLARE fldlow A30 .rem low value
.DECLARE fldhi A30 .rem high value
.DECLARE fldhelp A80 .rem field help message
.rem ****** Declare IAPTRIGGER related variables ******
.DECLARE trigtype A30 .rem Type of trigger, macro
.DECLARE trigdesc A20 .rem Trigger description for key display
.DECLARE trighide A3 .rem Y = Display trigger in key display
.rem ****** Declare IAPTRG (Trigger step) related variables ******
.DECLARE trgseq 9999 .rem Trigger step number
.DECLARE trglabel A30 .rem Statement label
.rem [trgsql] Pointer to SQL text. Use sqtno
.DECLARE trgcurs A1 .rem Y = maintain a separate cursor
.DECLARE trgmve A1 .rem Y = abort trigger if step fails
.DECLARE trginv A1 .rem Y = reverse return code
.DECLARE trgroll A1 .rem Y = return failure when aborting trig
.DECLARE trgslab A30 .rem Success label
.DECLARE trgflab A30 .rem Failure label
.DECLARE trgmsg A80 .rem Message delivered on failure
.rem ****** Declare IAPSQLTXT related variables ******
.DECLARE sqtno 999 .rem Trigger number assigned from all levels
.rem [sqtline] order by only
.DECLARE sqttext A80 .rem Text for all triggers
.rem ****** Declare IAPMAP table related variables ******
.DECLARE mappage 999 .rem Boilerplate page number
.DECLARE mapline 999 .rem Boilerplate line number
.DECLARE maptext A80 .rem Boilerplate text
.DECLARE pagecnt 999 .rem counter for boilerplate logic
.SET pagecnt 0
.DECLARE linecnt 999 .rem counter for boilerplate logic
.rem ****** Other variable declarations ******
.DECLARE blocks A30 .rem Name of Block to document (or ALL)
.DECLARE mapflag A1 .rem 'Y' turns on Boilerplate Display
.SET mapflag 'Y'
.Rem Main Table - Application Level
#DT 1 5 75 #
.Rem Main Table - Block Level
#DT 2 10 75 #
.Rem Main Table - Field Level
#DT 3 15 75 #
.Rem Nested Field Validation Table
#DT 4 1 10 12 29 30 34 36 0 #
.Rem Nested Block Attributes Table
#DT 5 1 21 23 25 34 59 60 0 #
.Rem Nested Field Attributes Table
#DT 6 1 16 18 20 38 53 54 0 #
.Rem Nested Trigger Step Attributes Table
#DT 7 1 1 2 30 31 0 #
.Rem Nested Trigger Step Message Table
#DT 8 1 14 15 0 #
.Rem Boiler Plate Display Table
#DT 9 1 80 #
.rem ****** Define the APPLICATION level report select macro ******
.DEFINE appsel
SELECT appid,apptitle
INTO appid,apptitle
FROM system.iapapp
WHERE upper(appowner) = upper(&appowner)
AND upper(appname) = upper(&appname)
..
.rem ****** Define the SELECT macro for APPLICATION level comments ******
.DEFINE appcmtsel
SELECT cmttext INTO cmttext FROM system.iapcomment
WHERE cmtappid = &appid
AND cmtblk IS NULL AND cmttrgtyp IS NULL
ORDER BY cmtline
..
.rem ****** Define the SELECT macro for BLOCK level comments ******
.DEFINE blkcmtsel
SELECT cmttext INTO cmttext FROM system.iapcomment
WHERE cmtappid = &appid
AND cmtblk = &blkname AND cmtfld IS NULL
AND cmttrgtyp IS NULL
ORDER BY cmtline
..
.rem ****** Define the SELECT macro for FIELD level comments ******
.DEFINE fldcmtsel
SELECT cmttext INTO cmttext FROM system.iapcomment
WHERE cmtappid = &appid AND cmtblk = &blkname
AND cmtfld = &fldname AND cmttrgtyp IS NULL
ORDER BY cmtline
..
.rem ****** SELECT macro for APPLICATION level TRIGGER comments ******
.DEFINE apptrigcmtsel
SELECT cmttext INTO cmttext FROM system.iapcomment
WHERE cmtappid = &appid
AND cmtblk IS NULL AND cmttrgtyp = &trigtype
AND cmttrgseq = 0
ORDER BY cmtline
..
.rem ****** SELECT macro for BLOCK level TRIGGER comments ******
.DEFINE blktrigcmtsel
SELECT cmttext INTO cmttext FROM system.iapcomment
WHERE cmtappid = &appid
AND cmtblk = &blkname AND cmtfld IS NULL
AND cmttrgtyp = &trigtype AND cmttrgseq = 0
ORDER BY cmtline
..
.rem ****** SELECT macro for FIELD level TRIGGER comments ******
.DEFINE fldtrigcmtsel
SELECT cmttext INTO cmttext FROM system.iapcomment
WHERE cmtappid = &appid AND cmtblk = &blkname
AND cmtfld = &fldname AND cmttrgtyp = &trigtype
AND cmttrgseq = 0
ORDER BY cmtline
..
.rem ****** Generic SELECT macro for TRIGGER STEP comments ******
.DEFINE trgcmtsel
SELECT cmttext INTO cmttext FROM system.iapcomment
WHERE cmtappid = &appid AND nvl(cmtblk,' ') = &blkname
AND nvl(cmtfld,' ') = &fldname AND cmttrgtyp = &trigtype
AND cmttrgseq = &trgseq
ORDER BY cmtline
..
.rem ****** Define the SELECT macro for APPLICATION level triggers ******
.DEFINE apptrigsel
SELECT nvl(trigblk,' '),nvl(trigfld,' '),
trigtype,nvl(trigdesc,'NONE'),
decode(trighide,'Y','YES','NO')
INTO blkname,fldname,trigtype,trigdesc,trighide
FROM system.iaptrigger
WHERE trigappid = &appid AND trigblk is NULL
ORDER BY trigtype
..
.rem ****** Define the SELECT macro for BLOCK level triggers ******
.DEFINE blktrigsel
SELECT nvl(trigfld,' '),trigtype,NVL(trigdesc,'NONE'),
decode(trighide,'Y','YES','NO')
INTO fldname,trigtype,trigdesc,trighide
FROM system.iaptrigger
WHERE trigappid = &appid AND trigblk = &blkname
AND trigfld is NULL
ORDER BY trigtype
..
.rem ****** Define the SELECT macro for FIELD level triggers ******
.DEFINE fldtrigsel
SELECT trigtype,nvl(trigdesc,'NONE'),
decode(trighide,'Y','YES','NO')
INTO trigtype,trigdesc,trighide
FROM system.iaptrigger
WHERE trigappid = &appid AND trigblk = &blkname
AND trigfld = &fldname
ORDER BY trigtype
..
.rem ****** Define the generic SELECT macro for trigger steps ******
.DEFINE stepsel
SELECT trgseq,trglabel,trgsql,
decode (trgcurs,'Y','*',' '),decode(trginv,'Y','*',' '),
decode (trgmve,'Y','*',' '),decode(trgroll,'Y',' ','*'),
trgslab,trgflab,trgmsg
INTO trgseq,trglabel,sqtno,trgcurs,trginv,trgmve,trgroll,
trgslab,trgflab,trgmsg
FROM system.iaptrg
WHERE trgappid = &appid AND nvl(trgblk,' ') = &blkname
AND nvl(trgfld,' ') = &fldname AND trgtype = &trigtype
ORDER BY trgseq
..
.rem ****** Define the generic SELECT macro for trigger text ******
.DEFINE trgtxtsel
SELECT sqttext INTO sqttext FROM system.iapsqltxt
WHERE sqtappid = &appid AND sqtno = &sqtno
ORDER BY sqtline
..
.rem ****** Define the BLOCK level report select macro (ALL BLOCKS) ******
.DEFINE blksel
SELECT nvl(blkname, ' '),nvl(blkdesc,'none'),blkseq,
decode(blkunqkey,'Y','YES','NO'),
decode(blktname,NULL,'Control Block',
decode(blktowner,NULL,NULL,blktowner||'.')||blktname),
blknorec,blknobuf,blkblin,blklnrc,nvl(blkobysql,0)
INTO blkname,blkdesc,blkseq,blkunqkey,blktname,
blknorec,blknobuf,blkblin,blklnrc,sqtno
FROM system.iapblk
WHERE blkappid = &appid
ORDER BY blkseq
..
.rem ****** Define the BLOCK level report select macro (ONE BLOCK) ******
.DEFINE oneblksel
SELECT blkname,nvl(blkdesc,'none'),blkseq,
decode(blkunqkey,'Y','YES','NO'),
decode(blktname,NULL,'Control BLock',
decode(blktowner,NULL,NULL,blktowner||'.')||blktname),
blknorec,blknobuf,blkblin,blklnrc,nvl(blkobysql,0)
INTO blkname,blkdesc,blkseq,blkunqkey,blktname,
blknorec,blknobuf,blkblin,blklnrc,sqtno
FROM system.iapblk
WHERE blkappid = &appid
AND upper(blkname) = upper(&blocks)
..
.rem ****** Define the FIELD level report select macro ******
.DEFINE fldsel
SELECT fldname,fldseq,fldtype,fldlen,fldqlen,decode(fldbtab,'Y','YES','NO'),
decode(fldkey,'Y','YES','NO'),
decode(fldckblk,NULL,NULL,fldckblk||'.')||fldckfld,flddflt,
decode(flddisp,'Y','YES','NO'),decode(fldenter,'Y','YES','NO'),
decode(fldquery,'Y','YES','NO'),
decode(fldupdate,'Y','YES','NO'),decode(fldupdnul,'Y','YES','NO'),
decode(fldmand,'Y','YES','NO'),decode(fldfixed,'Y','YES','NO'),
decode(fldskip,'Y','YES','NO'),decode(fldhide,'Y','YES','NO'),
decode(fldautohlp,'Y','YES','NO'),decode(fldupper,'Y','YES','NO'),
decode(fldlovt,NULL,NULL,fldlovt||'.')||fldlovc,fldlow,fldhi,fldhelp
INTO fldname,fldseq,fldtype,fldlen,fldqlen,fldbtab,fldkey,fldckfld,
flddflt,flddisp,fldenter,fldquery,fldupdate,fldupdnul,fldmand,
fldfixed,fldskip,fldhide,fldautohlp,fldupper,fldlovc,fldlow,
fldhi,fldhelp
FROM system.iapfld
WHERE fldappid = &appid AND fldblk = &blkname
ORDER BY fldseq
..
.rem ****** Define the SELECT macro for BoilerPlate Text ******
.DEFINE mapsel
SELECT mappage,mapline,maptext
INTO mappage,mapline,maptext
FROM system.iapmap
WHERE mapappid=&appid
ORDER BY mappage,mapline
..
.rem ****** Define select macro to get logged on user name (appowner) *****
.DEFINE getuser
SELECT user INTO appowner FROM system.iapapp
..
.rem ****** Define the APPLICATION level report body ******
.DEFINE appbody
#T 1
#S 2
***********************************************************************
***********************************************************************
#S 1
#CEN DOCUMENTATION FOR SQL*FORMS APPLICATION
.PRINT appname
#
#S 1
#NC -----------------------------------------------------------------------
#NC Title:
.PRINT apptitle
#NC #RR Owner:
.PRINT appowner
.REPORT appcmtsel cmtbody cmthead foot
.REPORT apptrigsel apptrigbody apptrighead
***********************************************************************
***********************************************************************
#TE
.IF "&blocks is null" then allblocks
.REPORT oneblksel blkbody
.goto endblocks
.&allblocks
.REPORT blksel blkbody
.&endblocks
.IF "&mapflag != 'Y'" then finis
#T 9
.REPORT mapsel mapbody
#TE
.&finis
..
.rem ****** Define the APPLICATION-TRIGGER level report head ******
.DEFINE apptrighead
#I 16
-------[ TRIGGERS ]-------
#TE
.apptrigbody
..
.rem ****** Define the BLOCK-TRIGGER level report head ******
.DEFINE blktrighead
#I 16
-------[ TRIGGERS ]-------
#TE
.blktrigbody
..
.rem ****** Define the FIELD-TRIGGER level report head ******
.DEFINE fldtrighead
#I 17
-------[ TRIGGERS ]-------
#TE
.fldtrigbody
..
.rem ****** Define the APPLICATION level macro for trigger display ******
.DEFINE apptrigbody
#RR Type:
.PRINT trigtype
#N Description:
.PRINT trigdesc
\ \ \ Hide:
.PRINT trighide
#NC
.REPORT apptrigcmtsel cmtbody cmthead foot
#S 1
.REPORT stepsel stepbody
..
.rem ****** Define the BLOCK level macro for trigger display ******
.DEFINE blktrigbody
#RR Type:
.PRINT trigtype
#N Description:
.PRINT trigdesc
\ \ \ Hide:
.PRINT trighide
#NC
.REPORT blktrigcmtsel cmtbody cmthead foot
#S 1
.REPORT stepsel stepbody
..
.rem ****** Define the FIELD level macro for trigger display ******
.DEFINE fldtrigbody
#RR Type:
.PRINT trigtype
#NC Description:
.PRINT trigdesc
\ \ \ Hide:
.PRINT trighide
#NC
.REPORT fldtrigcmtsel cmtbody cmthead foot
#S 1
.REPORT stepsel stepbody
..
.rem ****** Define the Generic Header macro for comment text display ******
.DEFINE cmthead
#NC Comment:
.I 10
#RR
.cmtbody
..
.rem ****** Define the Generic Body macro for comment text display ******
.DEFINE cmtbody
.PRINT cmttext
..
.rem ****** Define the Generic Foot macro to close and open table ******
.DEFINE foot
#TE
..
.rem ****** Define the Generic Body macro for trigger step display ******
.DEFINE stepbody
#T 8
Step =
.PRINT trgseq
#NC #RR Label:
.PRINT trglabel
#TE
.REPORT trgcmtsel cmtbody cmthead foot
#T 7
.PRINT trgmve
#NC Abort trigger when step fails #NC #NC
.PRINT trginv
#NC Reverse return code #NC #NC
.PRINT trgroll
#NC Return success on abort
#NC #RR Success Label:
.PRINT trgslab
#NC
.PRINT trgcurs
#NC Separate cursor data area
#NC #RR Failure Label:
.PRINT trgflab
#TE
#T 8
Fail Message: #NC #RR
.PRINT trgmsg
#TE
#S 1
.REPORT trgtxtsel trgtxtbody
#S 1
..
.rem ****** Define the Generic Body macro for trigger text display ******
.DEFINE trgtxtbody
#RR
.PRINT sqttext
..
.rem ****** Define the BLOCK level report body ******
.DEFINE blkbody
#T 1
#S 1
=======================================================================
#RR
.PRINT blkname
- Block
.PRINT blkseq
#NC =======================================================================
#TE
#T 2
#N Description:
.PRINT blkdesc
#N Table Name:
.PRINT blktname
.REPORT blkcmtsel cmtbody cmthead foot
#I 15
-------[ ATTRIBUTES ]-------
#TE
#T 5
Check for Unique Key: #NC
.PRINT blkunqkey
#NC Number of Rows Displayed: #NC
.PRINT blknorec
#NC #NC #NC Number of Rows to Buffer: #NC
.PRINT blknobuf
.IF &blknorec=1 THEN label1
#NC Number of Lines/Row: #NC
.PRINT blklnrc
#NC Base Line: #NC
.PRINT blkblin
.&label1
#TE
.IF "&sqtno = 0" then noorderby
#I 12
-------[ DEFAULT ORDER BY ]-------
#TE
.REPORT trgtxtsel trgtxtbody
.&noorderby
.REPORT blktrigsel blktrigbody blktrighead
==================================================================
#TE
.REPORT fldsel fldbody
..
.rem ****** Define the FIELD level report body ******
.DEFINE fldbody
#T 2
#S 1 #RR
.PRINT fldname
- Field
.PRINT fldseq
#NC ==================================================================
#TE
#T 3
#NC Datatype:
.PRINT fldtype
.REPORT fldcmtsel cmtbody cmthead foot
#I 16
-------[ ATTRIBUTES ]-------
#TE
#T 6
Database Field: #NC
.PRINT fldbtab
#NC Primary Key: #NC
.PRINT fldkey
#NC Displayed: #NC
.PRINT flddisp
.IF &flddisp='NO' THEN label1
#NC Query Allowed: #NC
.PRINT fldquery
#NC Input Allowed: #NC
.PRINT fldenter
.IF &fldenter='NO' THEN label1
#NC Update Allowed: #NC
.PRINT fldupdate
#NC Update if NULL: #NC
.PRINT fldupdnul
#NC Fixed Length: #NC
.PRINT fldfixed
#NC Mandatory: #NC
.PRINT fldmand
#NC Autoskip: #NC
.PRINT fldskip
#NC No echo: #NC
.PRINT fldhide
#NC Auto help: #NC
.PRINT fldautohlp
#NC Uppercase: #NC
.PRINT fldupper
.&label1
#TE
#I 16
-------[ VALIDATION ]-------
#TE
#T 6
Field Length: #NC
.PRINT fldlen
#NC Query Length: #NC
.PRINT fldqlen
#TE
.IFNULL fldckfld label2
Copy Key From:
.PRINT fldckfld
.&label2
#NC Default Value:\ \
.PRINT flddflt
#T 4
Range Low: #NC
.PRINT fldlow
#NC High: #NC
.PRINT fldhi
#TE
.IFNULL fldlovc label3
#NC List of Values Table:
.PRINT fldlovc
.&label3
#NC #RR Help:
.PRINT fldhelp
.REPORT fldtrigsel fldtrigbody fldtrighead
#TE
..
.rem ****** Define the Boiler Plate Body macro ******
.DEFINE mapbody
.IF "&pagecnt=&mappage" THEN label1
#S 1
PAGE
.PRINT mappage
#NC
.EQUAL pagecnt mappage
#S 1
.SET linecnt 1
.&label1
.IF "&linecnt=&mapline" THEN label2
.IF "&linecnt+1=&mapline" THEN label3
#S 1
.&label3
.ADD linecnt linecnt 1
.GOTO label1
.&label2
#CL
.PRINT maptext
#
#NC
..
.rem ****** Logic to identify the application and start report ******
.TELL " "
.EXECUTE getuser
.rem .ASK "Name of the SQL*FORMS application owner: " appowner
.ASK "Name of the SQL*FORMS application: " appname
.ASK "Name of the desired block (<CR> for all blocks): " blocks
.&label1
.EXECUTE appsel
.IFNULL appid label2
.IF "&blocks is null" THEN label5 ELSE label3
.&label2
.TELL "Couldn't find application... Try again or Ctrl-Y to quit "
.rem .ASK "Application owner: " appowner
.ASK "Application name: " appname
.GOTO label1
.&label3
.EXECUTE oneblksel
.IFNULL blkname label4
.GOTO label5
.&label4
.TELL "Couldn't find block... Try again or Ctrl-Y to quit "
.ASK "Block name: " blocks
.IF "&blocks is not null" then label3
.&label5
.ASK "Display boilerplate text? (Y) " mapflag
.REPORT appsel appbody