Need help in Oracle cast varchar array query

Question

I am looking for a query similar to the below one.

SELECT  ROWNUM rnum,COLUMN_VALUE as dl
FROM    TABLE(CAST(varc('REGULAR','AD','PR') is varray(3) of varchar2(100)))

Output :

REGULAR 
AD
PR

PS: Type creation is not allowed in our oracle 11g by DBA. Using union is one option. But we are looking for list of array elements in the select query

Any suggestions please!

I know there is little point in arguing with a DBA but, for the record, their prohibition is uninformed, misguided and counter-productive. Feel free to quote me :-) — APC, Sep 01 '17 at 13:32

score 1 · Accepted Answer · answered Sep 01 '17 at 11:46

1

There are some handy collection types already supplied by Oracle - for example you could do:

select column_value
from table(SYS.DBMS_DEBUG_VC2COLL('REGULAR','AD','PR'));

answered Sep 01 '17 at 11:46

Tony Andrews

129,880
21
220
259

Thanks for your reply. It solves my problem ☺️ – Vicki Sep 01 '17 at 12:40
You should probably add a [disclosure](https://meta.stackoverflow.com/a/254283/1509264) to your answer that the link goes to your own blog. – MT0 Sep 01 '17 at 12:45

score 0 · Answer 2 · answered Sep 01 '17 at 12:25

0

You can use the SYS.ODCIVARCHAR2LIST type:

SELECT ROWNUM,
       COLUMN_VALUE
FROM   TABLE( SYS.ODCIVARCHAR2LIST( 'REGULAR', 'AD', 'PR' ) );

answered Sep 01 '17 at 12:25

MT0

143,790
11
59
117

Thanks for your reply – Vicki Sep 01 '17 at 12:41

Need help in Oracle cast varchar array query

2 Answers2