Overview
If you need to include PeopleCode or SQL code in your development documentation, you can cut and paste code from Application Designer. How can we automate this task? Create presentable results using a script to extract the data, and a few additional steps described below.
Step-by-Step Instructions
Follow these instructions to document PeopleCode or SQL code objects.
Open Script
This SQL script extracts the PeopleCode and SQL for supported object types included in a specified Application Designer Project.
/* extract sql and peoplecode */
with code_query as (
select * from (
Select
objecttype,
0 as seqnum,
decode(objecttype,
8,'REC.PC.',
30,decode(objectvalue2,'0','SQL.','1','APPENG.SQL.','2','REC.SQL.','SQL'),
43,'APPENG.PC.',
44,'PAGE.PC.',
45,'PAGE Field PeopleCode',
46,'COMP.PC.',
47,'COMP.REC.PC.',
48,'COMP.REC.PC.')
|| decode(objectvalue1,' ','',objectvalue1)||decode(objectvalue2,' ','','0','','1','','2','','.'||objectvalue2)||decode(objectvalue3,' ','','.'||objectvalue3)||decode(objectvalue4,' ','','.'||objectvalue4) As Object_Name,
'<b>' || decode(objecttype,
8,'REC.PC.',
30,decode(objectvalue2,'0','SQL.','1','APPENG.SQL.','2','REC.SQL.','SQL'),
43,'APPENG.PC.',
44,'PAGE.PC.',
45,'PAGE Field PeopleCode',
46,'COMP.PC.',
47,'COMP.REC.PC.',
48,'COMP.REC.PC.')
|| decode(objectvalue1,' ','',objectvalue1)||decode(objectvalue2,' ','','0','','1','','2','','.'||objectvalue2)||decode(objectvalue3,' ','','.'||objectvalue3)||decode(objectvalue4,' ','','.'||objectvalue4)
|| '</b>'
as object_code
from psprojectitem a, (select rownum t, s, r from (select rownum - 1 r from dual connect by rownum <= 10) r, (select rownum - 1 s from dual connect by rownum <= 4) s) t
where projectname = 'CTISS29731_MT'
and objecttype in (8,30,43,44,45,46,47,48)
union
Select
objecttype,
t as seqnum,
decode(objecttype,
8,'REC.PC.',
30,decode(objectvalue2,'0','SQL.','1','APPENG.SQL.','2','REC.SQL.','SQL'),
43,'APPENG.PC.',
44,'PAGE.PC.',
45,'PAGE Field PeopleCode',
46,'COMP.PC.',
47,'COMP.REC.PC.',
48,'COMP.REC.PC.')
|| decode(objectvalue1,' ','',objectvalue1)||decode(objectvalue2,' ','','0','','1','','2','','.'||objectvalue2)||decode(objectvalue3,' ','','.'||objectvalue3)||decode(objectvalue4,' ','','.'||objectvalue4) As Object_Name,
decode(objecttype,
8,(select to_char(dbms_lob.substr(b.pctext,3999,(3999*s)+1)) from pspcmtxt b where b.progseq = r and a.objectid1 = b.objectid1 and a.objectvalue1 = b.objectvalue1 and a.objectid2 = b.objectid2 and a.objectvalue2 = b.objectvalue2 and a.objectid3 = b.objectid3 and a.objectvalue3 = b.objectvalue3 and a.objectid4 = b.objectid4 and a.objectvalue4 = b.objectvalue4 ),
30,(select max(to_char(dbms_lob.substr(b.sqltext,3999,(3999*s)+1))) from pssqltextdefn b where b.seqnum = r and b.sqlid = a.objectvalue1 and b.effdt = (select max(c.effdt) from pssqltextdefn c where b.sqlid = c.sqlid and b.sqltype = c.sqltype and b.market = c.market and b.dbtype = c.dbtype)),
43,(select to_char(dbms_lob.substr(b.pctext,3999,(3999*s)+1)) from pspcmtxt b where b.progseq = r and a.objectid1 = b.objectid1 and a.objectvalue1 = b.objectvalue1 and a.objectid2 = b.objectid2 and a.objectid3 = b.objectid6 and a.objectvalue3 = b.objectvalue6 and a.objectid4 = b.objectid7 and a.objectvalue4 = b.objectvalue7 ),
44,(select to_char(dbms_lob.substr(b.pctext,3999,(3999*s)+1)) from pspcmtxt b where b.progseq = r and a.objectid1 = b.objectid1 and a.objectvalue1 = b.objectvalue1 and a.objectid2 = b.objectid2 and a.objectvalue2 = b.objectvalue2 and a.objectid3 = b.objectid3 and a.objectvalue3 = b.objectvalue3 and a.objectid4 = b.objectid4 and a.objectvalue4 = b.objectvalue4 ),
45,(select to_char(dbms_lob.substr(b.pctext,3999,(3999*s)+1)) from pspcmtxt b where b.progseq = r and a.objectid1 = b.objectid1 and a.objectvalue1 = b.objectvalue1 and a.objectid2 = b.objectid2 and a.objectvalue2 = b.objectvalue2 and a.objectid3 = b.objectid3 and a.objectvalue3 = b.objectvalue3 and a.objectid4 = b.objectid4 and a.objectvalue4 = b.objectvalue4 ),
46,(select to_char(dbms_lob.substr(b.pctext,3999,(3999*s)+1)) from pspcmtxt b where b.progseq = r and a.objectid1 = b.objectid1 and a.objectvalue1 = b.objectvalue1 and a.objectid2 = b.objectid2 and a.objectvalue2 = b.objectvalue2 and a.objectid3 = b.objectid3 and a.objectvalue3 = b.objectvalue3 and a.objectid4 = b.objectid4 and a.objectvalue4 = b.objectvalue4 ),
47,(select to_char(dbms_lob.substr(b.pctext,3999,(3999*s)+1)) from pspcmtxt b where b.progseq = r and a.objectid1 = b.objectid1 and a.objectvalue1 = b.objectvalue1 and a.objectid2 = b.objectid2 and a.objectvalue2 = b.objectvalue2 and a.objectid3 = b.objectid3 and a.objectvalue3 = b.objectvalue3 and a.objectid4 = b.objectid4 and a.objectvalue4 = b.objectvalue4 ),
48,(select to_char(dbms_lob.substr(b.pctext,3999,(3999*s)+1)) from pspcmtxt b where b.progseq = r and a.objectid1 = b.objectid1 and a.objectvalue1 = b.objectvalue1 and a.objectid2 = b.objectid2 and a.objectvalue2 = b.objectvalue2 and a.objectid3 = b.objectid3 and a.objectvalue3 = b.objectvalue3 and a.objectid4 = b.objectid4 and a.objectvalue4 = b.objectvalue4 ),
' ') as object_code
from psprojectitem a, (select rownum t, s, r from (select rownum - 1 r from dual connect by rownum <= 10) r, (select rownum - 1 s from dual connect by rownum <= 4) s) t
where projectname = 'CTISS29731_MT'
and objecttype in (8,30,43,44,45,46,47,48) ) x
where x.object_code is not null)
select objecttype, seqnum, object_name, object_code || case when a.seqnum = 0 or a.seqnum = (select max(b.seqnum) from code_query b where a.objecttype = b.objecttype and a.object_name = b.object_name) then '' else '~' end as object_code from code_query a
order by 1,3,2Supported objects include:
- Component PeopleCode
- Page PeopleCode
- Record PeopleCode
- Application Engine PeopleCode
- Standalone SQL Object
- Application Engine SQL
Update Script
Find the two instances of projectname and update the script with your Project name.
Run Script
Run the Script using your favorite SQL tool.
PeopleCode and SQL code are stored in CLOB fields, potentially split across multiple records. The script retrieves the appropriate CLOB field data and splits it into character strings segments at or under the 4000-character maximum, which must be combined with the following steps.
The script adds a row with the object name before each PeopleCode or SQL code segment. The object name includes HTML tags to bold it after the final paste. You may need to remove these tags for your purposes.
The script adds a tilde (~) at the end of each PeopleCode or SQL segment. Later, this is used to merge the segments back together.
Copy SQL Results to Excel
Cut and paste or use your SQL tool’s Copy to Excel options.
Moving to Excel first opens up pasting options in Word that prevent extra quotes from being inserted.
Copy Excel Results to Word
Use the Paste Special Option, Keep Source Formatting, to move results to Word.
If the results get pasted into a table, select the table and use the Table Layout > Data > Convert to Text option to remove text from any table or cell.
Combine Segments
Before any addition changes, two related segments will line up like the following example, with the tilda (~) introduced by the script.
Use the Home > Editing > Replace tool to combine the segments.
Enter the Find and Replace criteria and Replace All
- ~ represents the special character added by the script
- ^p represents a new paragraph line feed
- ^s represents a no-line-feed space
After completing the replace all, the two segments will be combined as in the sample below.
Cut and Paste to Final Target
Selected all text and copy.
Paste into the final target.
Leave a Reply
You must be logged in to post a comment.