Solved

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

Posted on 2006-11-06
48
1,169 Views
Last Modified: 2008-02-20
I an application that was normalized so that now a single entry resides in 23 tables.  I need a query that will return all of these in a single row (comma sperated for the multi value entries).

For the sake of argument say I have 4 tables.

LOCATION: (1 Per Account)
     Location_Code - Primary Key
     Location Name

USER: (1 per account)
     Location_Code - Foreign Key
     User_ID - promary Key
     User_Name
     User_SSN
     User_Address
     User_Zip
     User_Phone

Vehicle: (0 to many entries)
     Location_Code - Foreign Key
     User_ID - Foreign key
     Veh_ID - Primary Key
     Veh_Make
     Veh_Model

Magazines: (0 to many entries)
     Location_Code - Foreign Key
     User_ID - Foreign key
     Mag_ID - Primary key
     Mag_Name
     Mag_Subscription

What I want is a select statement that will return:
     

Location, User_Name, User_SSN, User_Address, User_ZIP, User_Phone, Veh_Make Veh_Model, Mag_Name Mag_Subscription

So, A single user with multiple cars and magazines might look like:

Florida, Scott, 333-44-3445, 1234 my street, 23221, 555-5656, Ford Bronco; Chevy Trailblazer, Time anual; People Anual; TV Guide Anual

Thanks!!
0
Comment
Question by:JRamos1200
  • 24
  • 14
  • 10
48 Comments
 
LVL 18

Accepted Solution

by:
Jinesh Kamdar earned 250 total points
ID: 17883146
A similar question but in a diff. context was asked a few days back. But 23 tables is too big a no.
I guess u have will have to make functions for fields that may return multiple values for the same user since u want to have them concatenated in the same result row.

SELECT location_name             || ' ' ||
            user_name                  || ' ' ||
            user_ssn                     || ' ' ||
            user_address               || ' ' ||
            user_zip                      || ' ' ||
            user_phone                  || ' ' ||
            get_vehicles(user_id)    || ' ' ||
            get_magazines(user_id)
FROM user u, location l
WHERE l.location_code = u.location_code;

FUNCTION get_vehicles(p_user_id NUMBER)
RETURN VARCHAR2 IS
l_veh_str VARCHAR2(5000) := NULL;
BEGIN
FOR i in (SELECT veh_make, veh_model FROM vehicle WHERE user_id = p_user_id) LOOP
       l_veh_str := l_veh_str || '; ' || veh_make || ' ' || veh_model;
END LOOP;
RETURN SUBSTR(l_veh_str, 3);
END get_vehicles;

FUNCTION get_magazines(p_user_id NUMBER)
RETURN VARCHAR2 IS
l_mag_str VARCHAR2(5000) := NULL;
BEGIN
FOR i in (SELECT mag_name, mag_subscription FROM magazines WHERE user_id = p_user_id) LOOP
       l_mag_str := l_mag_str || '; ' || mag_model || ' ' || mag_subscription;
END LOOP;
RETURN SUBSTR(l_mag_str, 3);
END get_magazines;
0
 

Author Comment

by:JRamos1200
ID: 17883318
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
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 17884583
"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.
0
 

Author Comment

by:JRamos1200
ID: 17884691
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
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 17884975
"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;


0
 

Author Comment

by:JRamos1200
ID: 17885058
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;
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 17885227
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.
0
 

Author Comment

by:JRamos1200
ID: 17885252
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...
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 17885266
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?
0
 

Author Comment

by:JRamos1200
ID: 17885281
Its stored as a varchar...
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 17885461
>> I am afraid our app does inserts instead of updates...

Use this : -

after insert or update on DEF_LNKI_P
0
 

Author Comment

by:JRamos1200
ID: 17885574
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      
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 17885595
Put a UNIQUE constraint on th table_id column if it can distinctly identify each record. Prefereably, have it populated from a sequence.
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 17885612
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.
0
 

Author Comment

by:JRamos1200
ID: 17885717
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
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 17885727
Yes, i will be on EE from 9 to 6 daily. Close of business for me too :) G'nite ...
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 17892645
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!
0
 

Author Comment

by:JRamos1200
ID: 17893285
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.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 17893374
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.
0
 

Author Comment

by:JRamos1200
ID: 17893380
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
0
 

Author Comment

by:JRamos1200
ID: 17893394
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!
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 250 total points
ID: 17893436
"In that SP I can take a little more time to record the transaction."
No.

If you call a stored procedure from a trigger, the stored procedure still has to execute in its entirety before the trigger can finish.  An "autonomous transaction" would allow you to commit SQL work inside a stored procedure independent of (and maybe slightly before) the trigger finishes, but this does not allow the procedure to continue to work later than the trigger.  The only way to have a stored procedure be independent of the trigger is to have the trigger create a job (using dbms_job) or insert or update a "flag" record somewhere that an existing job that is running on very short intervals (like maybe every second or two) will find and process.  The job and the stored procedure called from the job can then be independent of the trigger.
0
 

Author Comment

by:JRamos1200
ID: 17898169
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!!
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 17898227
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.)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 17898266
markgeer is correct, data fields in the cursor loop shud be referenced with the cursor record.
I had missed this in my 1st post.
0
 

Author Comment

by:JRamos1200
ID: 17898303
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 
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 17898331
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.
0
 

Author Comment

by:JRamos1200
ID: 17898341
I am logged in as that account though...
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 17898631
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.
0
 

Author Comment

by:JRamos1200
ID: 17899266
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!!
0
 

Author Comment

by:JRamos1200
ID: 17899466
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
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 17899534
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.
0
 

Author Comment

by:JRamos1200
ID: 17899565
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
0
 

Author Comment

by:JRamos1200
ID: 17899630
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...
0
 

Author Comment

by:JRamos1200
ID: 17899643
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!
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 17899841
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.
0
 

Author Comment

by:JRamos1200
ID: 17899866
Good call, I adjusted the substring and it captures correctly now!
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 17900010
(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.
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 17900022
welcome bac, mark :)
0
 

Author Comment

by:JRamos1200
ID: 17900066
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
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 17900141
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;
0
 

Author Comment

by:JRamos1200
ID: 17900274
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
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 17900288
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 )
0
 

Author Comment

by:JRamos1200
ID: 17900359
Ahh....  We are cooking now!!!
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 17900465
Me breaking for lunch now ... Can already smell the cheese lasagne ;-) ... C ya in 20 mins.
0
 

Author Comment

by:JRamos1200
ID: 17902251
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;
/
0
 

Author Comment

by:JRamos1200
ID: 17902285
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
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 17902292
Great job (clap) !!!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now