Need help in Oracle cast varchar array query [duplicate]

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
– APC, Commented Sep 1, 2017 at 13:32

Tony Andrews · Accepted Answer · 2017-09-01 11:46:23Z

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 1, 2017 at 11:46

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vicki Over a year ago

Thanks for your reply. It solves my problem ☺️

MT0 Over a year ago

You should probably add a disclosure to your answer that the link goes to your own blog.

MT0 · Accepted Answer · 2017-09-01 12:25:59Z

0

You can use the SYS.ODCIVARCHAR2LIST type:

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

answered Sep 1, 2017 at 12:25

MT0

173k12 gold badges70 silver badges136 bronze badges

1 Comment

Vicki Over a year ago

Thanks for your reply

Collectives™ on Stack Overflow

Need help in Oracle cast varchar array query [duplicate]

2 Answers 2

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Linked

Related