automic on oracle sql challenge : "pre_process , process , post_process" info in one table

[Olgun Onur Ozmen]

Hi Community,

I want to get all the jobs in automic installed in "oracle sql" database as a single list table in separate columns of "pre process, process, post process" tabs. better if the columns of the table are as follows: 

 

Columns:

client  , objectname  , pre_process  , process , post_process

I know that all these are kept in the OX_CONTENT column in the OX table. but I literally could never pull it properly in the above table format. as you know, pulling blob fields in oracle sql is a challenge in itself. So I would like to buy coffee at the first opportunity to the friend , who will tell a magical method or query :)

 

Thanks.

------------------------------
Olgun Onur Ozmen
https://www.linkedin.com/in/olgunonurozmen/
------------------------------

[Michael A. Lowry]

What do you want to appear in the three columns? The entire content of the respective scripting tabs?

[Philipp Elmer]

Hi Olgun,

OX contains XML exports of version management, so the old versions of your objects.

To get the data you want for the latest versions, you need OH and OT.
OT_Type tells you which process tab the row belongs to.

This query will give you all the data, but not in separate columns per ProcessTab:

select OH_Client, OH_Name, 
case OT_Type
    when 1 then 'PreProcess'
    when 0 then 'Process'
    when 2 then 'PostProcess'
end as ProcessTab
, OT_Lnr, OT_Content
from OH
inner join OT on OH_IDNR = OT_OH_IDNR
where OH_Deleteflag = 0
and OH_Client > 0
and OH_Otype = 'JOBS'
order by OH_Client, OH_Name, OT_Type, OT_Lnr;

This one will generate the columns as requested, but it'll be more "expensive" (i.e. cause more load):

--generate "N" with a reasonable amount of lines
with maximum_lines as (
    select OH_Idnr, OH_Client, OH_Name, row_number() over (partition by OH_Client, OH_Name order by (select 0 from dual)) as N
    from OH
    inner join OT on OH_Idnr = OT_OH_IDnr --this join just generates lines for calculating "N"
    where OH_Deleteflag = 0
    and OH_Client > 0
    and OH_Otype = 'JOBS'
),
--limit "N" to the exact amount of lines needed, to avoid lines w/o any Process content
--this yields "N" lines per Job, where "N" is the maximum amount of lines for any Process-Tab of the job
required_job_lines as (
    select *
    from maximum_lines
    where N <= (select max(OT_Lnr) from OT where OH_Idnr = OT_OH_IDnr)
)
--Add columns for the Process Tabs to the above
select OH_Client, OH_Name, 
    pre.OT_Lnr as PreLnr, pre.OT_Content as PreProcessTab, 
    pro.OT_Lnr as ProLnr, pro.OT_Content as ProcessTab, 
    post.OT_Lnr as PostLnr, post.OT_Content as PostProcessTab
from required_job_lines
left join OT pre on OH_Idnr = pre.OT_OH_Idnr and pre.OT_Type = 1 and pre.OT_Lnr = N
left join OT pro on OH_Idnr = pro.OT_OH_Idnr and pro.OT_Type = 0 and pro.OT_Lnr = N
left join OT post on OH_Idnr = post.OT_OH_Idnr and post.OT_Type = 2 and post.OT_Lnr = N
order by OH_Client, OH_Name;

Looking forward to a cozy cup of coffee with you ;-)

Regards, Philipp

------------------------------
Philipp Elmer

Become a member!
https://membership.philippelmer.com
------------------------------