How to combine multiple entry sub fields into a single row via an SQL select

Let me explain what I am tring to do and see if I am looking at this wrong...

I have an application that I need to create auditing for.  It stores information about a process in about 23 tables.  Right now there is an audit table that just says that a job has been updated. Nothing about what was changed.  In certain circumstances it doesn't even report on the job, only the group of jobs and no reference to which job in the group was changed.

Before t he records are updated, the group (in the application) is locked which shows up in the audit log.  After update it is released, again in the audit log.

What I am looking to do is create a couple triggers.  When the group is locked, I will write all the jobs in that group (and all their details) to a table.
When it is unlocked, I will write them again to a different table and then call a procedure to compare the two tables to see what has been deleted (using a union), what was added (the opposite union) and what was changed (a comparison).  I will write these out to my own audit log.

The only way I could think to do this was by combining all 23 tables into a single row for the purpose of comparisons.  Otherwise how can i tell what was changed or removed?

Is there a better way?  I can not use table level triggers since  there would be so darn many of them!!

Thanks,

Scott

"Is there a better way?" I think so!  I think separate triggers on the 23 tables would be more efficient.  There are two problems with the approach you are trying to use:
1. It is difficult to build all of this information into one record, because some of the tables may have multiple records, and different numbers of records for some jobs than for other jobs.
2. Inserting all of this job plus detail information to a table before the change, then inserting the changed information will add a significant performance impact just for the inserts.  Then, after you are done comparing the info if you do individual deletes, that will be another significant performance impact.  Plus all of the inserts and updates will increase the size of the archived redo logs (assuming the database is in archivelog mode) so this will impact your time to recover, if you ever do need to do a recovery.

I would recommend creating a history table for each of the 23 tables with the same data structure as the data table, plus an extra column or two for the Oracle username and timestamp if these are not already being captured.  Then I would recommend a "before update for each row" trigger for each of these 23 tables that compares the value of each column by using "decode" to check the :new and :old column value, then only insert the changed column value (plus the username and timestamp) to the history table.  I can give you example of a trigger like that, if you want.   If the application allows deletes, you will need a "before delete for each row" trigger also, to save the entire row.

OK, pursuing this option, how do you build a record of the changes to the overall record set?  Forget the example I used in the opening of this quesiton, that was just an easy to understand nested relationship.  In my case, we are talking about scheduled batch processes or tasks (Think Windows Scheduler with lots more options).  The main table is the job table that defines what will execute and when.  The other 22 tables are for the options that can have 1 to many values (processes this one waits on, people to notify if it fails etc...).

If I trigger on each individual record, how do I come up with a history of changes for a specific job?  If 3 of the 23 records are updated at the same time, will they all have the same date/time so I can group thse changes into a single record?  How about if the whole record is deleted...  Won't I have 23 deletions instead of just reporting that one job was deleted?

Examples would be great!!

Scott

"How do you build a record of the changes to the overall record set?"  I think that is a problem for you no matter which approach you use, if you have to try to build this information via database triggers.

"If 3 of the 23 records are updated at the same time, will they all have the same date/time so I can group these changes into a single record?"  I don't know.  This depends on whether the application already populates a "timestamp" column, and if so, how and when it populates it for multiple values, or if your new trigger will assign the "timestamp" value.  If your new trigger does this, you could create an additional "before insert, update or delete" trigger for each table with no "for each row" clause that just gets the current timestamp, assigns that to a PL\SQL package variable, then your "for each row" triggers get it from here rather than from sysdate directly.  That way all records that are part of the same transaction get the same timestamp.  (This assumes that your application bundles the records for each job into one transaction!)

"How about if the whole record is deleted?"  You should be able to detect this condition (but you may need an "after delete" trigger with no "for each row clause" to delete the 23 normalized records if you don't need them.)

Here is an example of one of the audit triggers we use that creates a "sparse" history record, with only the key column plus the columns that actually changed getting populated:

(Note: you could use "case" instead of "decode" now, but when we developed this back in the Oracle8 days, "decode" was familiar to us and "case" was brand new.)

create or replace trigger ORDER_HEADERS_AU_ROW
after update on order_headers
for each row
begin
  -- This saves a "sparse" history record for each order_header update.
  -- Three fields: order_nbr, date_modifed, and modified_by are saved in all cases,
  --  plus only the field(s) changed.  If the field being changed was null,
  --  the following nvl's are applied:
  --   varchar2 fields = '?'
  --   number fields = 0
  --   date field = '01/01/2099'
  if :new.order_nbr <> :old.order_nbr then
    raise_application_error(-20201,'Changing the Order# is not allowed.');
  end if;
  if :new.created_by <> :old.created_by then
    raise_application_error(-20202,'Changing the "created_by" is not allowed.');
  end if;
  if :new.date_created <> :old.date_created then
    raise_application_error(-20203,'Changing the "date_created" is not allowed.');
  end if;
  if :new.ORDER_DATE <> :old.ORDER_DATE
   or :new.ORDER_LOC <> :old.ORDER_LOC
   or :new.CUS_NBR <> :old.CUS_NBR
   or nvl(:new.PO_NBR,'x') <> nvl(:old.PO_NBR,'x')
   or :new.SALESMAN_NBR <> :old.SALESMAN_NBR
   or :new.TERMS_ID <> :old.TERMS_ID
   or :new.BILL_ID <> :old.BILL_ID
   or :new.SHIP_ID <> :old.SHIP_ID
   or :new.SHIP_VIA_CODE <> :old.SHIP_VIA_CODE
   or nvl(:new.PLACED_BY,'x') <> nvl(:old.PLACED_BY,'x')
   or :new.FREIGHT_TERMS_CODE <> :old.FREIGHT_TERMS_CODE
   or nvl(:new.SHIP_TO_ATTENTION,'x') <> nvl(:old.SHIP_TO_ATTENTION,'x')
   or nvl(:new.RCV_DOCK,'x') <> nvl(:old.RCV_DOCK,'x')
   or nvl(:new.CUSTOMER_PLANT_CODE,'x') <> nvl(:old.CUSTOMER_PLANT_CODE,'x')
   or nvl(:new.CUST_REF_NBR,'x') <> nvl(:old.CUST_REF_NBR,'x') then
    insert into order_header_history
     (ORDER_NBR, DATE_MODIFIED, MODIFIED_BY,
      ORDER_DATE, ORDER_LOC, CUS_NBR,
      PO_NBR, SALESMAN_NBR, TERMS_ID,
      BILL_ID, SHIP_ID, SHIP_VIA_CODE,
      PLACED_BY, FREIGHT_TERMS_CODE, SHIP_TO_ATTENTION,
      RCV_DOCK, CUSTOMER_PLANT_CODE, CUST_REF_NBR)
    values (:old.ORDER_NBR, sysdate, user_pkg.show_user,
      decode(:new.ORDER_DATE,:old.ORDER_DATE,null,:old.ORDER_DATE),
      decode(:new.ORDER_LOC,:old.ORDER_LOC,null,:old.ORDER_LOC),
      decode(:new.CUS_NBR,:old.CUS_NBR,null,:old.CUS_NBR),
      decode(:new.PO_NBR,:old.PO_NBR,null,nvl(:old.PO_NBR,'?')),
      decode(:new.SALESMAN_NBR,:old.SALESMAN_NBR,null,:old.SALESMAN_NBR),
      decode(:new.TERMS_ID,:old.TERMS_ID,null,:old.TERMS_ID),
      decode(:new.BILL_ID,:old.BILL_ID,null,:old.BILL_ID),
      decode(:new.SHIP_ID,:old.SHIP_ID,null,:old.SHIP_ID),
      decode(:new.SHIP_VIA_CODE,:old.SHIP_VIA_CODE,null,:old.SHIP_VIA_CODE),
      decode(:new.PLACED_BY,:old.PLACED_BY,null,nvl(:old.PLACED_BY,'?')),
      decode(:new.FREIGHT_TERMS_CODE,:old.FREIGHT_TERMS_CODE,null,:old.FREIGHT_TERMS_CODE),
      decode(:new.SHIP_TO_ATTENTION,:old.SHIP_TO_ATTENTION,null,nvl(:old.SHIP_TO_ATTENTION,'?')),
      decode(:new.RCV_DOCK,:old.RCV_DOCK,null,nvl(:old.RCV_DOCK,'?')),
      decode(:new.CUSTOMER_PLANT_CODE,:old.CUSTOMER_PLANT_CODE,null,nvl(:old.CUSTOMER_PLANT_CODE,'?')),
      decode(:new.CUST_REF_NBR,:old.CUST_REF_NBR,null,nvl(:old.CUST_REF_NBR,'?')));
  end if;
end;

I tried this with my table but it is not writting anything.  Not sure if it is my trigger or something in the application (not doing updates but inserts or the like).

Also, I don't see any reference statements in your example for new and old...  I tried too add them on mine but ir rejects the old...

The following compiles but does not run:

CREATE OR REPLACE TRIGGER "EM620"."AU-TR_DEF_LNKI_P"
after update on DEF_LNKI_P
REFERENCING NEW AS new
--REFERENCING OLD AS old
for each row
begin

insert into AU_DEF_LNKI_P
 (TABLE_ID, JOB_ID, CONDITION, ODATE, AND_OR, PARENTHESES)
values(:new.TABLE_ID, :new.JOB_ID,
  decode(:new.CONDITION, :old.CONDITION, null, nvl(:old.ODATE,'?')),
  decode(:new.ODATE, :old.ODATE, null, nvl(:old.ODATE,'?')),
  decode(:new.AND_OR, :old.AND_OR, null, nvl(:old.AND_OR,'?')),
  decode(:new.PARENTHESES, :old.PARENTHESES, null, nvl(:old.PARENTHESES,0)));
end;

The "reference..." statements are optional, and the ones you have are just the defaults that Oracle will use anyway even if you don't have them (like we don't).

Did your update actually change something in the record?

Are you sure that you don't want to add some kind of activity_date or timestamp column?  If not, you may never be able to group or order these audit records reliably.

I will add more detail once I get the shell working.  Is the syntax correct?  I am afraid our app does inserts instead of updates...

If 'odate' is a date type, shud not the "decode(:new.ODATE, :old.ODATE, null, nvl(:old.ODATE,'?'))" give a data error since '?' is not a valid date-format?

Its stored as a varchar...

>> I am afraid our app does inserts instead of updates...

Use this : -

after insert or update on DEF_LNKI_P

Great, it is doing an insert, even though the record existed.  Can I do some checking in this trigger to see if it already existed since the decode won't work?

Here is what I got back...

 SELECT * FROM EM620.AU_DEF_LNKI_P 

 TABLE_ID     JOB_ID     CONDITION     ODATE     AND_OR     PARENTHESES    
 -----------    ---------      ------------      --------     ---------      --------------
 28                      2               ?                ?            ?                0      

Put a UNIQUE constraint on th table_id column if it can distinctly identify each record. Prefereably, have it populated from a sequence.

Sorry, that shud have been job_id. If i understand it correctly, there shud be a single record for every distinct job, so an attempt to insert an existing job shud throw an exception.

There is a unique constraint...  it must be deleting the row first.  I will put in a delete trigger to check for that.  It is  the end of the day fomr me here.  I REALLY appreciate your help...  Will you be on line tomorrow for some more questions as I test this more?

Thanks!

Scott

Yes, i will be on EE from 9 to 6 daily. Close of business for me too :) G'nite ...

Wow!  If your application really does deletes of all of the detail records, then re-inserts all of the detail records whenever a change is made, then the designer(s) of that application either had no clue how to use Oracle efficiently, or didn't care at all what the performance impact would be on Oracle!

Yes, yes and yes I am afraid...  It was created in Israel and is maintained there.  It origonaly was written for Sybase and was ported to Oracle.  A lot of what they do still fits the Sybase model better than Oracle.

Well, if you are brave, and if you have a good test system, you could try renaming these 23 tables there to something else, then create 23 views with the original table names.  That gives you some possibilities.  You could add a column to each table with a name like "deleted_flag" or "deleted_time", then you could create "instead of delete" triggers that simply update this column instead of actually doing a delete.  Your views should only select records where this "delete_flag" or "delete_date" column is null, so the application won't get confused.  You will need "instead of insert" triggers to check for the existance of the record, and if found, update the column(s) that are actually different, if any, and clear the "deleted_flag" or "deleted_time".  Even though these triggers will add some overhead, it may be much less than all the deletes and inserts, plus give you an easy, reliable way (in the "instead of insert" trigger) to log what actually changed.

Understanding the potential performance impact, I am back to leaning towards combining the fields.  I can do this  a couple waays.  Inside the trigger which may impact the performance as you stated, or have the trigger call a stored proc that handles that, returning control back the the trigger immediatley using "pragma autonomous_transaction;" if I understand its use properly.  In that SP I can take a little more time to record the transaction.  I already have an SP that combines the tables (4 of them anyway) using temp tables as I didn't know any other way.  Can you show me a couple things?

1) How to call a SP from a Trigger
    Can the SP use Cursors and temp tables?
2) In your sample using functions, are those functions stand alone or are they in an SP?  Can they be used in a trigger?
3) is there a good way to compare two tables and see what is different? - If I compare the before to the after tables, how do I identify Deleted rows, Added rows and Changed rows?

Thanks for all your help.  If needed, I can open 3 more questions on these and award the points for each answer but it is easier to track just this one...

Scott

I love your idea in theory but I have a couple restraints...

1) Time.  I am trying to get something out this week.
2) Support.  If I change how the app works we will violate support and it will be hell on upgrades.  These triggers and SPs are headache enough
3) knowledge.  I am self tought.  I have some books and my experts-exchange account. No mentors or anything so I don't know what I don't know!!

Thats why I appreciate all your help!

I will look into the job option, it sounds like it has promise.

For now I am back to your origonal solution with functions...  

I am  trying to create one on a table that has a complex key.  Here is my function:

CREATE OR REPLACE FUNCTION "EM620"."FUN_Get_In_Cond"
(ic_TID NUMBER, ic_JID NUMBER)
RETURN varchar2
  IS
Incond VARCHAR2(5000) := NULL;
BEGIN
  for i in (select condition, odate, and_or, parentheses
     from def_lnki_p
     where (job_id = ic_JID and table_id = ic_TID))
     LOOP
     Incond := Incond || ';' || condition || ' ' || odate || ' ' || and_or || ' ' || parentheses;
  end loop;
RETURN SUBSTR(Incond,3);
END;

When I try to compile it in Aqua Studio I get the following errors:

Line       Position        Text
11      33      PLS-00201: identifier 'CONDITION' must be declared
11      6      PL/SQL: Statement ignored

Help!!

I rarely use implicit cursors, so I'm not familiar with the syntax for referencing a cursor variable that way, but I think it may be something like this:

    LOOP
     Incond := Incond || ';' || i.condition || ' ' || i.odate || ' ' || i.and_or || ' ' || i.parentheses;
  end loop;

(Also,  I thought the limit for a varchar2 variable was 4000, not the 5000 that you have.)

markgeer is correct, data fields in the cursor loop shud be referenced with the cursor record.
I had missed this in my 1st post.

I just copied the varchar size from your example...  

I made the cursor changeto i. and it compiles now.  Now I just need to test it!!

OK, now I can't call the function properly...

I tested it with:
SELECT JOB_NAME,
FUN_Get_In_Cond(TABLE_ID, JOB_ID) FROM def_job
and got:

>[SQL] Script lines: 1-4 ----------------------------
 SELECT JOB_NAME,
 FUN_Get_In_Cond(TABLE_ID, JOB_ID) FROM def_job 

>[Error] Script lines: 1-4 --------------------------
 ORA-00904: "FUN_GET_IN_COND": invalid identifier 

You created it in the EM620 schema, so when you call it, if you are not logged in as EM620, you either need to call it with the schema name (EM620.FUN_Get_In_Cond) or create a synonym for it so you can call it without using the schema name.

I am logged in as that account though...

Just try if u can do this : -

SELECT fun_get_in_cond (1, 1)
FROM DUAL

NOTE : - Replace (1, 1) with a valid combination of (table_id, job_id) that exists ur table/view def_lnki_p.

SQL>  SELECT fun_get_in_cond (13,1) from dual;
 SELECT fun_get_in_cond (13,1) from dual
        *
ERROR at line 1:
ORA-00904: "FUN_GET_IN_COND": invalid identifier


SQL>  SELECT EM620.fun_get_in_cond (13,1) from dual;
 SELECT EM620.fun_get_in_cond (13,1) from dual
        *
ERROR at line 1:
ORA-00904: "EM620"."FUN_GET_IN_COND": invalid identifier


But the function shows as valid!!

OK, I am REALLY confused here...

SQL> select object_name from all_objects where object_type = 'FUNCTION' and OBJECT_NAME like 'FUN%';

OBJECT_NAME
------------------------------
FUN_Get_In_Cond

SQL> grant execute on FUN_Get_In_Cond to public;
grant execute on FUN_Get_In_Cond to public
                 *
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist

Ok i thinnk i got it --- u created the function as "FUN_GET_IN_COND" which in Oracle is different from FUN_GET_IN_COND.
Either re-create it as just FUN_GET_IN_COND or use "FUN_GET_IN_COND" in ur queries and try again.

That didn't change anything...

SQL> grant execute on "FUN_GET_IN_COND" to public;
grant execute on "FUN_GET_IN_COND" to public
                 *
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist

OK, my database must be case sensitive.  I tried again by re-creating the function with its name in caps and it works (the grant).


Now for the testing...

WOOHOO!!!!

select job_name, FUN_GET_IN_COND(TABLE_ID,JOB_ID) from def_job 

 JOB_NAME     FUN_GET_IN_COND(TABLE_ID,JOB_ID)                  
 -----------  --------------------------------------------------
 Daily15      aily13-->Daily15 ODAT A  ;Daily23-->Daily15 ODAT A
 Daily22      aily3-->Daily22 ODAT A                            
 Daily7       aily17-->Daily7 ODAT A                            
 Daily13      aily12-->Daily13 ODAT A                            
 Daily24      aily6-->Daily24 ODAT A    

So far so good!

I suppose it became case sensitive since u used those double-quotes; try and avoid them as far as possible.

Ur output shows --- aily13-->Daily15 ODAT A  ;Daily23-->Daily15 ODAT A

Ur 1st char 'D' is getting clipped. Make sure u have the ';' in the right place and the SUBSTR is passing the proper no.

Good call, I adjusted the substring and it captures correctly now!

(I was away for a couple hours.)  The case-sensentivity problem was caused by using double quotes.  Avoid using double quotes in Oracle for object names, unless you have to work with objects that were imported from SQL Server or created by some ODBC-based tools that may have created them with mixed-case or lower case names.  Usually Oracle objects are all upper-case, and they can then be used or called in SQL*Plus, TOAD, or other tools in any case as long as no double-quotes are used.

welcome bac, mark :)

The Double Quote was added by Aqua Studio.  It just puts them right back.  I started creating these functions from SQL directly to avoid this but I am making them upper as a standard now.

Now I have a complex function that joins two tables so I have to use aliases on the column names.  This I think is causing my latest issue...  Suggestions?

CREATE OR REPLACE FUNCTION "EM620"."FUN_GET_DOCOND"
(ic_TID NUMBER, ic_JID NUMBER)
RETURN varchar2
  IS
Return_Var VARCHAR2(4000) := NULL;
BEGIN
  for i in (select  dd.ACTION, dc.CONDITION, dc.ODATE, dc.SIGN
     from DEF_DO dd, DEF_DO_COND dc
     where (dd.JOB_ID = ic_JID and dd.TABLE_ID = ic_TID)
       AND (dd.JOB_ID = dc.JOB_ID and dd.TABLE_ID = dc.TABLE_ID)
       AND (dd.IF_NO = dc.IF_NO and dd.DO_NO = dc.DO_NO)
     LOOP
     Return_Var := Return_Var || ';' || 'ON ' || i.ACTION || ' DO ' || i.CONDITION || ' ' || i.ODAT || ' ' || i.sign;
  end loop;
RETURN SUBSTR(Return_Var,2);
END;

Line         Position      Text
12      6      PL/SQL: ORA-00933: SQL command not properly ended
7      12      PL/SQL: SQL Statement ignored
13      117      PLS-00103: Encountered the symbol ";" when expecting one of the following:

   loop

I'm not sure if this will solve the problem or not, but I would write the function like this:

CREATE OR REPLACE FUNCTION FUN_GET_DOCOND
(ic_TID NUMBER, ic_JID NUMBER)
RETURN varchar2
  IS
Return_Var VARCHAR2(4000) ;
BEGIN
  for i in (select  dd.ACTION, dc.CONDITION, dc.ODATE, dc.SIGN
     from DEF_DO dd, DEF_DO_COND dc
     where (dd.JOB_ID = ic_JID and dd.TABLE_ID = ic_TID)
       AND (dd.JOB_ID = dc.JOB_ID and dd.TABLE_ID = dc.TABLE_ID)
       AND (dd.IF_NO = dc.IF_NO and dd.DO_NO = dc.DO_NO)
     LOOP
     Return_Var := Return_Var  || 'ON ' || i.ACTION || ' DO ' || i.CONDITION || ' ' || i.ODAT || ' ' || i.sign|| ';';
  end loop;
RETURN Return_Var;
END;

I tried it...

No change.

12      6      PL/SQL: ORA-00933: SQL command not properly ended
7      12      PL/SQL: SQL Statement ignored
13      117      PLS-00103: Encountered the symbol ";" when expecting one of the following: loop

The problem is in the FOR loop SELECT stmt. U r missing a ')' there.

for i in (select  dd.ACTION, dc.CONDITION, dc.ODATE, dc.SIGN
     from DEF_DO dd, DEF_DO_COND dc
     where (dd.JOB_ID = ic_JID and dd.TABLE_ID = ic_TID)
       AND (dd.JOB_ID = dc.JOB_ID and dd.TABLE_ID = dc.TABLE_ID)
       AND (dd.IF_NO = dc.IF_NO and dd.DO_NO = dc.DO_NO)) --- 1 more )

Ahh....  We are cooking now!!!

Me breaking for lunch now ... Can already smell the cheese lasagne ;-) ... C ya in 20 mins.

Well, thanks to you two I have it working.  Not the audit portion (yet) but the display all tables in a single row...  In case you are curious, it took 11 Functions and a nasty long select statement to do it!!

select  b.DATA_CENTER,
        b.SCHED_TABLE,
        a.APPLICATION,
        a.GROUP_NAME,
        a.MEMNAME,
        a.JOB_NAME,
        a.DESCRIPTION,
        a.AUTHOR,
        a.OWNER,
        a.PRIORITY,
        a.CRITICAL,
        a.TASK_TYPE,
        a.CYCLIC,
        a.NODE_ID,
        a.DOC_LIB,
        a.DOC_MEM,
        a.INTERVAL,
        a.OVER_LIB,
        a.MEM_LIB,
        a.CMD_LINE,
        a.CONFIRM_FLAG,
        a.DAYS_CAL,
        a.WEEKS_CAL,
        a.CONF_CAL,
        a.RETRO,
        a.MAX_WAIT,
        a.MAX_RERUN,
        a.AUTO_ARCHIVE,
        a.MAX_DAYS,
        a.MAX_RUNS,
        a.FROM_TIME,
        a.TO_TIME,
        a.DAY_STR,
        a.W_DAY_STR,
        a.MONTH_1,
        a.MONTH_2,
        a.MONTH_3,
        a.MONTH_4,
        a.MONTH_5,
        a.MONTH_6,
        a.MONTH_7,
        a.MONTH_8,
        a.MONTH_9,
        a. MONTH_10,
        a.MONTH_11,
        a.MONTH_12,
        a.DATES_STR,
        a.RERUN_MEM,
        a.DAYS_AND_OR,
        a.CTLD_CATEGORY,
        a.SHIFT,
        a.SHIFT_NUM,
        a.PDS,
        a.MIN_PDS_TRACKS,
        a.PREVENT_NCT2,
        a.SYSOPT,
        a.FROM_CLASS,
        a.PARM,
        a.SYSDB,
        a.DUE_OUT,
        a.RETEN_DAYS,
        a.RETEN_GEN,
        a.TASK_CLASS,
        a.JOB_NO,
        a.PREV_DAY,
        a.ADJUST_COND,
        a.JOBS_IN_GROUP,
        a.LARGE_SIZE,
        a.IND_CYCLIC,
        a.CREATION_USER,
        a.CREATION_DATE,
        a.CREATION_TIME,
        a.CHANGE_USERID,
        a.CHANGE_DATE,
        a.CHANGE_TIME,
        a.JOB_RELESE,
        a.JOB_VERSION,
        a.TAG_RELATIONSHIP,
        a.TIME_REF,
        a.TIME_ZONE,
        a.APPL_TYPE,
        a.APPL_VER,
        a.APPL_FORM,
        a.CM_VER,
        a.STATE_MASK,
        a.MULTY_AGENT,
        a.ACTIVE_FROM,
        a.ACTIVE_TILL,
        a.SCHEDULE_ENV,
        a.SYS_AFFINITY,
        a.REQ_NJE_NODE,
        a.STAT_CAL,
        a.INSTREAM_JCL,
        a.USE_INSTREAM_JCL,
        a.DUE_OUT_DAYSOFFSET,
        a.FROM_DAYSOFFSET,
        a.TO_DAYSOFFSET,
        FUN_GET_IN_COND(a.TABLE_ID,a.JOB_ID) as IN_CONDITIONS,
        FUN_GET_OUT_COND(a.TABLE_ID,a.JOB_ID) as OUT_CONDITIONS,
        FUN_GET_DO_ACTIONS(a.TABLE_ID,a.JOB_ID) as DO_ACTIONS,
        FUN_GET_TAGS(a.TABLE_ID,a.JOB_ID) as TAGS,
        FUN_GET_JOBTAGS(a.TABLE_ID,a.JOB_ID) as TAG_DEFS,
        FUN_GET_PIPES(a.TABLE_ID,a.JOB_ID) as PIPES,
        FUN_GET_QRES(a.TABLE_ID,a.JOB_ID) as QUANT_RES,
        FUN_GET_CRES(a.TABLE_ID,a.JOB_ID) as CONTROL_RES,
        FUN_GET_SETVAR(a.TABLE_ID,a.JOB_ID) as SETVAR,
        FUN_GET_SHOUT(a.TABLE_ID,a.JOB_ID) as SHOUTS,
        FUN_GET_STEP_RANGE(a.TABLE_ID,a.JOB_ID) as STEP_RANGE
from def_job a, def_tables b where a.table_id = b.table_id


--
-- AUDIT_FUNCTION_DEFINITIONS.sql
-- This file will create all the functions needed to
-- display a full job definition on one row.
--

CREATE OR REPLACE FUNCTION FUN_GET_CRES
(ic_TID NUMBER, ic_JID NUMBER)
RETURN varchar2
  IS
Return_Var VARCHAR2(4000) := NULL;
BEGIN
  for i in (select RESOURCE_, TYPE
     from DEF_LNKI_C
     where (job_id = ic_JID and table_id = ic_TID))
     LOOP
     Return_Var := Return_Var || ';' || i.RESOURCE_ || ' ' || i.TYPE;
  end loop;
RETURN SUBSTR(Return_Var,2);
END;
/



CREATE OR REPLACE FUNCTION FUN_GET_DO_ACTIONS
(ic_TID NUMBER, ic_JID NUMBER)
RETURN varchar2
  IS
Return_Var VARCHAR2(4000) ;
BEGIN
  -- Do Action (RERUN, NOTOK etc...
  for i in (select  do.STMT, do.CODE, do.PGM_STEP, do.PROC_STEP, dc.ACTION
     from DEF_ON do, DEF_DO dc
     where (do.JOB_ID = ic_JID and do.TABLE_ID = ic_TID)
       AND (do.JOB_ID = dc.JOB_ID and do.TABLE_ID = dc.TABLE_ID)
       AND (do.IF_NO = dc.IF_NO))
     LOOP
     Return_Var := Return_Var  || ';' || 'ON STATEMENT ' || i.STMT || ' CODE ' || i.CODE || ' DO ACTION '
                               || i.ACTION;
  end loop;

  -- DO CONDITION
  for i in (select  do.STMT, do.CODE, do.PGM_STEP, do.PROC_STEP, dc.CONDITION, dc.ODATE, dc.SIGN
     from DEF_ON do, DEF_DO_COND dc
     where (do.JOB_ID = ic_JID and do.TABLE_ID = ic_TID)
       AND (do.JOB_ID = dc.JOB_ID and do.TABLE_ID = dc.TABLE_ID)
       AND (do.IF_NO = dc.IF_NO))
     LOOP
     Return_Var := Return_Var  || ';' || 'ON STATEMENT ' || i.STMT || ' CODE ' || i.CODE || ' DO CONDITION '
                               || i.CONDITION || ' ' || i.ODATE || ' ' || i.sign || ' ' || i.PGM_STEP
                               || ' ' || i.PROC_STEP;
  end loop;

  -- DO DOCTBRULE
  for i in (select  do.STMT, do.CODE, do.PGM_STEP, do.PROC_STEP, dc.RULE_NAME, dc.PARAMETERS
     from DEF_ON do, DEF_DO_CTBRULE dc
     where (do.JOB_ID = ic_JID and do.TABLE_ID = ic_TID)
       AND (do.JOB_ID = dc.JOB_ID and do.TABLE_ID = dc.TABLE_ID)
       AND (do.IF_NO = dc.IF_NO))
     LOOP
     Return_Var := Return_Var  || ';' || 'ON STATEMENT ' || i.STMT || ' CODE ' || i.CODE || ' DO CTBRULE '
                               || i.RULE_NAME || ' ' || i.PARAMETERS || i.PGM_STEP || ' ' || i.PROC_STEP;
  end loop;

  -- DO FORECEJ
  for i in (select  do.STMT, do.CODE, do.PGM_STEP, do.PROC_STEP, dc.SCHED_TABLE, dc.JOB, dc.DSN, dc.ODATE
     from DEF_ON do, DEF_DO_FORCEJ dc
     where (do.JOB_ID = ic_JID and do.TABLE_ID = ic_TID)
       AND (do.JOB_ID = dc.JOB_ID and do.TABLE_ID = dc.TABLE_ID)
       AND (do.IF_NO = dc.IF_NO))
     LOOP
     Return_Var := Return_Var  || ';' || 'ON STATEMENT ' || i.STMT || ' CODE ' || i.CODE || ' DO FORECEJ '
                               || i.SCHED_TABLE || ' ' || i.JOB || ' ' || i.DSN || ' ' || i.ODATE
                               || i.PGM_STEP || ' ' || i.PROC_STEP;
  end loop;

  -- DO IFRERUN
  for i in (select  do.STMT, do.CODE, do.PGM_STEP, do.PROC_STEP, dc.CONFIRM_FLAG,
                    dc.FROM_PGMSTEP, dc.FROM_PROCSTEP, dc.TO_PGMSTEP, dc.TO_PROCSTEP
     from DEF_ON do, DEF_DO_IFRERUN dc
     where (do.JOB_ID = ic_JID and do.TABLE_ID = ic_TID)
       AND (do.JOB_ID = dc.JOB_ID and do.TABLE_ID = dc.TABLE_ID)
       AND (do.IF_NO = dc.IF_NO))
     LOOP
     Return_Var := Return_Var  || ';' || 'ON STATEMENT ' || i.STMT || ' CODE ' || i.CODE || ' DO IF_RERUN'
                               || i.CONFIRM_FLAG || ' ' || i.FROM_PGMSTEP || ' ' || i.FROM_PROCSTEP
                               || ' ' || i.TO_PGMSTEP || ' ' || i.TO_PROCSTEP || i.PGM_STEP || ' '
                               || i.PROC_STEP;
  end loop;

  -- DO DOMAIL
  for i in (select  do.STMT, do.CODE, do.PGM_STEP, do.PROC_STEP, dc.URGENCY, dc.DESTINATION,
                    dc.CC_DESTINATION, dc.SUBJECT_LINE, dc.MESSAGE
     from DEF_ON do, DEF_DO_MAIL dc
     where (do.JOB_ID = ic_JID and do.TABLE_ID = ic_TID)
       AND (do.JOB_ID = dc.JOB_ID and do.TABLE_ID = dc.TABLE_ID)
       AND (do.IF_NO = dc.IF_NO))
     LOOP
     Return_Var := Return_Var  || ';' || 'ON STATEMENT ' || i.STMT || ' CODE ' || i.CODE || ' DO DO_MAIL'
                               || i.URGENCY || ' ' || i.DESTINATION || ' ' || i.CC_DESTINATION
                               || ' ' || i.SUBJECT_LINE || ' ' || i.MESSAGE || ' ' || i.PGM_STEP
                               || ' ' || i.PROC_STEP;
  end loop;

  -- DO SETVAR
  for i in (select  do.STMT, do.CODE, do.PGM_STEP, do.PROC_STEP,  dc.NAME, dc.VALUE, dc.GLOBAL_IND
     from DEF_ON do, DEF_DO_SETVAR dc
     where (do.JOB_ID = ic_JID and do.TABLE_ID = ic_TID)
       AND (do.JOB_ID = dc.JOB_ID and do.TABLE_ID = dc.TABLE_ID)
       AND (do.IF_NO = dc.IF_NO))
     LOOP
     Return_Var := Return_Var  || ';' || 'ON STATEMENT ' || i.STMT || ' CODE ' || i.CODE || ' DO SETVAR '
                               || i.NAME || ' ' || i.VALUE || ' ' || i.GLOBAL_IND || ' ' || i.PGM_STEP
                               || ' ' || i.PROC_STEP;
  end loop;

  -- DO SHOUT
  for i in (select  do.STMT, do.CODE, do.PGM_STEP, do.PROC_STEP, dc.URGENCY, dc.MESSAGE, dc.DESTINATION
     from DEF_ON do, DEF_DO_SHOUT dc
     where (do.JOB_ID = ic_JID and do.TABLE_ID = ic_TID)
       AND (do.JOB_ID = dc.JOB_ID and do.TABLE_ID = dc.TABLE_ID)
       AND (do.IF_NO = dc.IF_NO))
     LOOP
     Return_Var := Return_Var  || ';' || 'ON STATEMENT ' || i.STMT || ' CODE ' || i.CODE || ' DO SHOUT '
                               || i.URGENCY || ' ' || i.MESSAGE || ' ' || i.DESTINATION || ' '
                               || i.PGM_STEP || ' ' || i.PROC_STEP;
  end loop;

  -- DO SYSOUT
  for i in (select  do.STMT, do.CODE, do.PGM_STEP, do.PROC_STEP, dc.SYSOPT, dc.PARM, dc.FROM_CLASS
     from DEF_ON do, DEF_DO_SYSOUT dc
     where (do.JOB_ID = ic_JID and do.TABLE_ID = ic_TID)
       AND (do.JOB_ID = dc.JOB_ID and do.TABLE_ID = dc.TABLE_ID)
       AND (do.IF_NO = dc.IF_NO))
     LOOP
     Return_Var := Return_Var  || ';' || 'ON STATEMENT ' || i.STMT || ' CODE ' || i.CODE || ' DO SYSOUT '
                               || i.SYSOPT || ' ' || i.PARM || ' ' || i.FROM_CLASS || ' ' || i.PGM_STEP
                               || ' ' || i.PROC_STEP;
  end loop;

  -- DO ACTION
  for i in (select  do.STMT, do.CODE, do.PGM_STEP, do.PROC_STEP, dc.ACTION
     from DEF_ON do, DEF_DO dc
     where (do.JOB_ID = ic_JID and do.TABLE_ID = ic_TID)
       AND (do.JOB_ID = dc.JOB_ID and do.TABLE_ID = dc.TABLE_ID)
       AND (do.IF_NO = dc.IF_NO))
     LOOP
     Return_Var := Return_Var  || ';' || 'ON STATEMENT ' || i.STMT || ' CODE ' || i.CODE || ' DO ACTION '
                               || i.ACTION  || ' ' || i.PGM_STEP || ' ' || i.PROC_STEP;
  end loop;

RETURN SUBSTR(Return_Var,2);
END;
/



CREATE OR REPLACE FUNCTION FUN_GET_IN_COND
(ic_TID NUMBER, ic_JID NUMBER)
RETURN varchar2
  IS
Return_Var VARCHAR2(4000) := NULL;
BEGIN
  for i in (select CONDITION, ODATE, AND_OR, PARENTHESES
     from DEF_LNKI_P
     where (job_id = ic_JID and table_id = ic_TID))
     LOOP
     Return_Var := Return_Var || ';' || i.CONDITION || ' ' || i.ODATE || ' ' || i.AND_OR || ' ' || i.PARENTHESES;
  end loop;
RETURN SUBSTR(Return_Var,2);
END;
/



CREATE OR REPLACE FUNCTION FUN_GET_JOBTAGS
(ic_TID NUMBER, ic_JID NUMBER)
RETURN varchar2
  IS
Return_Var VARCHAR2(4000) := NULL;
BEGIN
  for i in (select TAG_NAME
     from DEF_JOB_TAGS
     where (job_id = ic_JID and table_id = ic_TID))
     LOOP
     Return_Var := Return_Var || ';' || i.TAG_NAME;
  end loop;
RETURN SUBSTR(Return_Var,2);
END;
/



CREATE OR REPLACE FUNCTION "EM620"."FUN_GET_OUT_COND"
(ic_TID NUMBER, ic_JID NUMBER)
RETURN varchar2
  IS
Return_Var VARCHAR2(4000) := NULL;
BEGIN
  for i in (select condition, odate, sign
     from def_lnko_p
     where (job_id = ic_JID and table_id = ic_TID))
     LOOP
     Return_Var := Return_Var || ';' || i.condition || ' ' || i.odate || ' ' || i.sign;
  end loop;
RETURN SUBSTR(Return_Var,2);
END;
/



CREATE OR REPLACE FUNCTION FUN_GET_OUT_COND
(ic_TID NUMBER, ic_JID NUMBER)
RETURN varchar2
  IS
Return_Var VARCHAR2(4000) := NULL;
BEGIN
  for i in (select CONDITION, ODATE, SIGN
     from DEF_LNKO_P
     where (job_id = ic_JID and table_id = ic_TID))
     LOOP
     Return_Var := Return_Var || ';' || i.CONDITION || ' ' || i.ODATE || ' ' || i.SIGN;
  end loop;
RETURN SUBSTR(Return_Var,2);
END;
/




CREATE OR REPLACE FUNCTION FUN_GET_PIPES
(ic_TID NUMBER, ic_JID NUMBER)
RETURN varchar2
  IS
Return_Var VARCHAR2(4000) := NULL;
BEGIN
  for i in (select FLAG, PIPE_NAME
     from DEF_PIPES
     where (job_id = ic_JID and table_id = ic_TID))
     LOOP
     Return_Var := Return_Var || ';' || i.FLAG || ' ' || i.PIPE_NAME;
  end loop;
RETURN SUBSTR(Return_Var,2);
END;
/



CREATE OR REPLACE FUNCTION FUN_GET_QRES
(ic_TID NUMBER, ic_JID NUMBER)
RETURN varchar2
  IS
Return_Var VARCHAR2(4000) := NULL;
BEGIN
  for i in (select RESOURCE_, QUANT
     from DEF_LNKI_Q
     where (job_id = ic_JID and table_id = ic_TID))
     LOOP
     Return_Var := Return_Var || ';' || i.RESOURCE_ || ' ' || i.QUANT;
  end loop;
RETURN SUBSTR(Return_Var,2);
END;
/



CREATE OR REPLACE FUNCTION FUN_GET_SETVAR
(ic_TID NUMBER, ic_JID NUMBER)
RETURN varchar2
  IS
Return_Var VARCHAR2(4000) := NULL;
BEGIN
  for i in (select NAME, VALUE, GLOBAL_IND
     from DEF_SETVAR
     where (job_id = ic_JID and table_id = ic_TID))
     LOOP
     Return_Var := Return_Var || ';' || i.NAME || ' ' || i.VALUE || ' ' || i.GLOBAL_IND;
  end loop;
RETURN SUBSTR(Return_Var,2);
END;
/



CREATE OR REPLACE FUNCTION FUN_GET_SHOUT
(ic_TID NUMBER, ic_JID NUMBER)
RETURN varchar2
  IS
Return_Var VARCHAR2(4000) := NULL;
BEGIN
  for i in (select WHEN_COND, SHOUT_TIME, URGENCY, DESTINATION, MESSAGE
     from DEF_SHOUT
     where (job_id = ic_JID and table_id = ic_TID))
     LOOP
     Return_Var := Return_Var || ';' || i.WHEN_COND || ' ' || i.SHOUT_TIME || ' ' || i.URGENCY || ' ' || i.DESTINATION
                              || ' ' || i.MESSAGE;
  end loop;
RETURN SUBSTR(Return_Var,2);
END;
/



CREATE OR REPLACE FUNCTION FUN_GET_STEP_RANGE
(ic_TID NUMBER, ic_JID NUMBER)
RETURN varchar2
  IS
Return_Var VARCHAR2(4000) := NULL;
BEGIN
  for i in (select NAME, FROM_PGMSTEP, FROM_PROCSTEP, TO_PGMSTEP, TO_PROCSTEP
     from DEF_STEP_RANGE
     where (job_id = ic_JID and table_id = ic_TID))
     LOOP
     Return_Var := Return_Var || ';' || i.NAME || ' ' || i.FROM_PGMSTEP || ' ' || i.FROM_PROCSTEP
                              || ' ' || i.TO_PGMSTEP || ' ' || i.TO_PROCSTEP;
  end loop;
RETURN SUBSTR(Return_Var,2);
END;
/



CREATE OR REPLACE FUNCTION FUN_GET_TAGS
(ic_TID NUMBER, ic_JID NUMBER)
RETURN varchar2
  IS
Return_Var VARCHAR2(4000) := NULL;
BEGIN
  for i in (select TAG_NAME, MAX_WAIT, TAG_RELATION_DC_WC, MONTH_1, MONTH_2, MONTH_3, MONTH_4, MONTH_5, MONTH_6,
            MONTH_7, MONTH_8, MONTH_9, MONTH_10, MONTH_11, MONTH_12, DAYS_CAL, WEEKS_CAL, CONF_CAL, SHIFT, SHIFT_NUM,
            RETRO, DATES_STR, W_DAY_STR, ACTIVE_FROM, ACTIVE_TILL
     from DEF_TAGS
     where (job_id = ic_JID and table_id = ic_TID))
     LOOP
     Return_Var := Return_Var || ';' || i.TAG_NAME || ' ' || i.MAX_WAIT || ' ' || i.TAG_RELATION_DC_WC
                              || ' ' || i.MONTH_1 || ' ' || i.MONTH_2 || ' ' || i.MONTH_3 || ' ' || i.MONTH_4
                              || ' ' || i.MONTH_5 || ' ' || i.MONTH_6 || ' ' || i.MONTH_7 || ' ' || i.MONTH_8
                              || ' ' || i.MONTH_9 || ' ' || i.MONTH_10 || ' ' || i.MONTH_11 || ' ' || i.MONTH_12
                              || ' ' || i.DAYS_CAL || ' ' || i.WEEKS_CAL || ' ' || i.CONF_CAL || ' ' || i.SHIFT
                              || ' ' || i.SHIFT_NUM || ' ' || i.RETRO || ' ' || i.DATES_STR || ' ' || i.W_DAY_STR
                              || ' ' || i.ACTIVE_FROM || ' ' || i.ACTIVE_TILL;
  end loop;
RETURN SUBSTR(Return_Var,2);
END;
/
quit;
/

I split the points as I got a lot of help from you both!  I used jinesh_kamdar's solution for the origonal question and am following markgeer's idea on the auditing portion.

Thanks for all your help, couldn't have done it without you two!

Scott

Great job (clap) !!!