Issue with unpivot and oracle objects

Question

CREATE TABLE unpivot_test 
(
    unique_id NUMBER(9) PRIMARY key,
    cusip VARCHAR2(30 CHAR),
    sedol VARCHAR2(30 CHAR),
    isin VARCHAR2(30 CHAR),
    ticker VARCHAR2(30 CHAR),
    currency VARCHAR2(3 CHAR)
);
INSERT INTO  unpivot_test VALUES (1, '1234', NULL, NULL, NULL, 'USD');

When i use the unpivot function, it works as expected.

SELECT Identifier, scheme,  unique_id, currency
FROM unpivot_test
UNPIVOT (Identifier FOR scheme IN (
    cusip AS '1', sedol AS '2', isin AS '3', ticker AS '4'));

Now replace the table with a user define type.

CREATE OR REPLACE TYPE obj_unpivot_test AS OBJECT (
    unique_id NUMBER(9) ,
    cusip VARCHAR2(30 CHAR),
    sedol VARCHAR2(30 CHAR),
    isin VARCHAR2(30 CHAR),
    ticker VARCHAR2(30 CHAR),
    currency VARCHAR2(3 CHAR)
);
CREATE OR REPLACE TYPE obj_unpivot_test_array AS TABLE OF obj_unpivot_test;

The following query will fail with no data found error.

DECLARE
   l_obj obj_unpivot_test_array := obj_unpivot_test_array(obj_unpivot_test(1, '1234',     NULL, NULL, NULL, 'USD'));
   l_IDENTIFIER VARCHAR2(30 CHAR);
   l_SCHEME NUMBER(1);
   l_UNIQUE_ID number(9);
BEGIN
   SELECT identifier, scheme, UNIQUE_ID
   INTO l_IDENTIFIER, l_SCHEME, l_UNIQUE_ID
   FROM
       TABLE(l_obj) o
   UNPIVOT  (IDENTIFIER FOR SCHEME IN (cusip AS 1, sedol AS 2, isin AS 3, ticker AS 4));
   Dbms_Output.put_line('l_IDENTIFIER:'||l_IDENTIFIER);
   Dbms_Output.put_line('l_SCHEME:'||l_SCHEME);
END;

What am i missing?

Answer 1

SET serveroutput ON
DECLARE
     l_IDENTIFIER VARCHAR2(30 CHAR);
     l_SCHEME     NUMBER(1);
     l_UNIQUE_ID  NUMBER(9);
BEGIN
     SELECT identifier,
          scheme,
          UNIQUE_ID
     INTO l_IDENTIFIER,
          l_SCHEME,
          l_UNIQUE_ID
     FROM
          (SELECT *
          FROM TABLE(obj_unpivot_test_array(obj_unpivot_test(1, '1234',NULL, NULL, NULL, 'USD')))
          ) UNPIVOT (IDENTIFIER FOR scheme IN ( cusip AS '1', sedol AS '2', isin AS '3', ticker AS '4'));
     Dbms_Output.put_line('l_IDENTIFIER:'||l_IDENTIFIER);
     Dbms_Output.put_line('l_SCHEME:'||l_SCHEME);
END;