MacLochlainns Weblog

1
2
3
4

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

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

1
2

CREATE OR REPLACE TYPE item_lookup IS TABLE OF item_structure;
/

CREATE OR REPLACE TYPE item_lookup IS TABLE OF item_structure; /

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;
/

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; /

1
2
3

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

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

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;

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;