×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: SQL
Posted by: Jon Nickerson
Added: Nov 29, 2017 10:07 PM
Views: 4
Tags: no tags
  1.  CREATE OR REPLACE TYPE OISS_VIEW_REC IS OBJECT
  2.   (
  3. test_col varchar2(1000)
  4.   );
  5. --------
  6.   CREATE OR REPLACE TYPE OISS_VIEW_TAB IS TABLE OF OISS_VIEW_REC;
  7. --------
  8. CREATE OR REPLACE FUNCTION tfunc
  9. RETURN OISS_VIEW_TAB
  10. AS
  11.   vP600_TERM VARCHAR2(10);
  12.   vP600_LEVEL VARCHAR2(10);
  13.   vP600_WITHDRAWN VARCHAR2(2);
  14.   rec OISS_VIEW_REC;
  15.   tab OISS_VIEW_TAB;
  16. BEGIN
  17.   vP600_TERM := '201840';
  18.   vP600_LEVEL := 'UG';
  19.   vP600_WITHDRAWN := 'N';
  20. -- for i in (
  21.     SELECT CAST(
  22.         MULTISET (WITH stupop AS
  23.     (SELECT *
  24.      FROM (SELECT test_col FROM test_table
  25.              ) AS OISS_VIEW_TAB)
  26.       INTO tab
  27.       FROM dual;
  28.   RETURN tab;
  29.   END;