MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Object Table Function View

with 2 comments

Somebody was trying to create a striped view based on a table’s start_date and end_date temporal columns. They asked for some help, so here are the steps (a two-minute tech-tip).

Basically, you create a user-defined data type, or structure:

1
2
3
4
CREATE OR REPLACE TYPE item_structure IS OBJECT
( id      NUMBER
, lookup  VARCHAR2(30));
/

Then, you create a list (an Oracle table) of the structure, like:

1
2
CREATE OR REPLACE TYPE item_lookup IS TABLE OF item_structure;
/

Lastly, you create an object table function, like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
CREATE OR REPLACE FUNCTION get_item_types RETURN item_lookup IS
 
  -- Declare a variable that uses the record structure.
  lv_counter      PLS_INTEGER := 1;
 
  -- Declare a variable that uses the record structure.
  lv_lookup_table  ITEM_LOOKUP := item_lookup();
 
  -- Declare static cursor structure.
  CURSOR c IS
    SELECT   cl.common_lookup_id AS lookup_id
    ,        SUBSTR(cl.common_lookup_meaning,1,60) AS lookup_meaning
    FROM     common_lookup cl
    WHERE    cl.common_lookup_table = 'ITEM'
    AND      cl.common_lookup_column = 'ITEM_TYPE'
    AND      SYSDATE BETWEEN cl.start_date AND NVL(cl.end_date,TRUNC(SYSDATE) + 1)
    ORDER BY cl.common_lookup_meaning;
 
BEGIN
 
  FOR i IN c LOOP
    lv_lookup_table.EXTEND;
 
    /* The assignment pattern for a SQL collection is incompatible with
       the cursor return type, and you must construct an instance of the
       object type before assigning it to a collection. */
    lv_lookup_table(lv_counter) := item_structure( i.lookup_id
                                                 , i.lookup_meaning );
 
    lv_counter := lv_counter + 1;
  END LOOP;
 
  /* Call an autonomous function or procedure here! It would allow you to
     capture who queried what and when; and acts like a pseudo trigger for
     queries. */
 
  RETURN lv_lookup_table;
END;
/

Now you can embed the object table function in a view, like this:

1
2
3
CREATE OR REPLACE VIEW item_lookup_view AS
  SELECT *
  FROM   TABLE(get_item_types);

Why not simply use an embedded query in the view, like the following?

SQL> CREATE OR REPLACE VIEW normal_view AS
  2    SELECT   cl.common_lookup_id AS lookup_id
  3      ,        SUBSTR(cl.common_lookup_meaning,1,60) AS lookup_meaning
  4      FROM     common_lookup cl
  5      WHERE    cl.common_lookup_table = 'ITEM'
  6      AND      cl.common_lookup_column = 'ITEM_TYPE'
  7      AND      SYSDATE BETWEEN cl.start_date AND NVL(cl.end_date,TRUNC(SYSDATE) + 1)
  8      ORDER BY cl.common_lookup_meaning;

My guess is that it was too easy but who knows, maybe they found a real need. The only need that I see occurs when you’re enforcing something like HIPPA and you want to capture unauthorized queries along with who performed them.

Naturally, I hope this helps those looking to resolve syntax errors when they have a need to do the more complex solution.

Written by maclochlainn

March 17th, 2013 at 10:59 pm

2 Responses to 'Object Table Function View'

Subscribe to comments with RSS or TrackBack to 'Object Table Function View'.

  1. Can some one simplyfy this query?

    SELECT
      a.employee_id,
      p.full_name                                   AS employee_name,
      p.employee_number,
      a.approval_level,
      b.currency_code,
      TO_NUMBER(prof.profile_option_value)          AS org_id,
      h.name                                        AS business_unit,
      c.business_area,
      b.capital_exp_int_limit,
      b.capital_exp_ext_limit,
      b.exp_int_limit,
      b.exp_ext_limit,
      b.expenses_limit,
      a.inv_exp_flag,
      a.cg_view_id,
      a.nt_logon,
      a.project_exception,
      a.value_exception,notes,
      a.project_except_capital,
      a.value_except_capital,
      p.email_address,
      s.ass_attribute2                              AS grade
    FROM
      cgb_authorised_signatories         a,
      cgb_approval_levels                b,
      cgb_business_areas_v               c,
      per_all_people_f                   p,
      per_all_assignments_f              s,
      cgb_profile_option_values          prof,
      hr_all_organization_units          h
    WHERE c.organization_id (+)= s.organization_id
    AND   a.approval_level = b.approval_level
    AND   c.business_area = b.business_area (+)
    AND   a.employee_id = p.person_id
    AND   SYSDATE BETWEEN p.effective_start_date AND p.effective_end_date
          -- Reluctantly, we have to perpetuate the hard coding of 121
          -- because in the custom UK database, the foreign key link
          -- between per_all_people_f and per_person_types has been broken
    AND   p.person_type_id = 121
    AND   s.person_id = p.person_id
    AND   SYSDATE BETWEEN s.effective_start_date AND s.effective_end_date
    AND   h.organization_id = s.organization_id
    AND   prof.profile_option_name = 'ORG_ID';

    Ramesh

    16 Jul 13 at 6:16 am

  2. Recalling how the Oracle E-Business Suite works, why doesn’t the per_all_people_f.person_type resolve correctly?

    The only other thing that I notice immediately is: Is it likely the effective_start_date and effective_end_date in both the per_all_people_f and per_all_assignments_f tables hold the same data for the same person? Wouldn’t that only occur when the employee has a single assignment within the scope of all employment at that company?

    maclochlainn

    16 Jul 13 at 6:38 pm

Leave a Reply