Oracle eBS經驗分享

Collection Type 有3種

1. PL/SQL Table : 只存在Memory 裡，效能較好

2. Nested Table：可以存在DB中，可以被DB Table 拿來宣告成DB column

3. VARRAYs ：可以存在DB中，可以被DB Table 拿來宣告成DB column

=Case=<PL/SQL Table>==

DECLARE
  TYPE para_tab IS TABLE OF fnd_descr_flex_col_usage_vl.form_left_prompt%TYPE   INDEX BY BINARY_INTEGER;
  para_list   para_tab;
BEGIN
   FOR ass_r IN (SELECT RANK () OVER (ORDER BY column_seq_num) seq  -- , form_left_prompt
                ,      UPPER ('P_' || REPLACE (form_left_prompt, ' ', '_') ) parameter
                   FROM fnd_descr_flex_col_usage_vl
                  WHERE descriptive_flexfield_name = '$SRS$.DITAR_PR2009005' AND application_id = 222)
   LOOP
      para_list (ass_r.seq) := ass_r.parameter;
   END LOOP;

   FOR x IN para_list.FIRST .. para_list.LAST
   LOOP
      DBMS_OUTPUT.put_line (para_list (x) );
   END LOOP;
END;