Skip to Main Content
  • Questions

  • ORA-01704: string literal too long error while using regexp_substr, Any alternative solution?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rajendra.

Asked: May 17, 2017 - 8:55 am UTC

Last updated: May 29, 2017 - 7:14 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

We are getting - ORA-01704: string literal too long ->

Following query is built dynamically and will get executed. If it is going beyond 4000 then it is giving "ORA-01704: string literal too long". Is there any alternative for this?

SELECT regexp_substr(
'CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0'
,'[^,]+',1,LEVEL) IDNTFR
FROM dual
CONNECT BY regexp_substr(
'CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0'
,'[^,]+',1,LEVEL) IS NOT NULL ;

and Connor said...

I assume you are trying to parse out the items. Use a clob and a global temp table, eg


SQL> drop table T purge;

Table dropped.

SQL> drop table gtt purge;

Table dropped.

SQL>
SQL> create table T ( c clob );

Table created.

SQL>
SQL> declare
  2    v clob :=
  3  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  4  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  5  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  6  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  7  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  8  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  9  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 10  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 11  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 12  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 13  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 14  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 15  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 16  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 17  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 18  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 19  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 20  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 21  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 22  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 23  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 24  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 25  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 26  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 27  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 28  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 29  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 30  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
 31  'CRIS4557606302014A0V0,CRIS4557606302014A0V0';
 32
 33  begin
 34    insert into t values (v);
 35    commit;
 36  end;
 37  /

PL/SQL procedure successfully completed.

SQL>
SQL> create global temporary table gtt ( item varchar2(100))
  2  /

Table created.

SQL>
SQL> declare
  2    l_big_chunk varchar2(32767);
  3    l_leftover varchar2(200);
  4    l_offset pls_integer;
  5    l_len pls_integer;
  6
  7    type t_rows is table of varchar2(100) index by pls_integer;
  8    l_rows t_rows;
  9
 10    l_comma pls_integer;
 11  begin
 12  for i in ( select rownum r, c from t ) loop
 13    l_offset := 1;
 14
 15    loop
 16      l_big_chunk := dbms_lob.substr(i.c,32700,l_offset);
 17      l_len := length(l_big_chunk);
 18      l_offset := l_offset + l_len;
 19      l_big_chunk := l_leftover || l_big_chunk;
 20
 21      dbms_application_info.set_client_info(i.r||'-'||l_offset);
 22
 23      loop
 24        l_comma := instr(l_big_chunk,',');
 25        exit when l_comma = 0 or l_big_chunk is null;
 26        l_rows(l_rows.count+1) := substr(l_big_chunk,1,l_comma-1);
 27        l_big_chunk := substr(l_big_chunk,l_comma+1);
 28      end loop;
 29      l_leftover := l_big_chunk;
 30
 31      exit when l_len < 32700;
 32    end loop;
 33
 34  end loop;
 35    forall i in 1 .. l_rows.count
 36       insert into gtt values ( l_rows(i) ) ;
 37
 38    dbms_output.put_line('rows = '||sql%rowcount);
 39
 40  end;
 41  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select * from gtt;

ITEM
----------------------------------------------------------------------------------------------------
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0

197 rows selected.

SQL>


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

use bind variables

Rajeshwaran, Jeyabal, May 30, 2017 - 7:59 am UTC

Other option would be to use BIND variables.

demo@ORA12C> $type d:\script.sql
variable x clob
begin
        :x := 'CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0,  ' ||
                        ' CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0,  ' ||
                        ' CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0,CRIS4557606302014A0V0, ' ||
                        ' CRIS4557606302014A0V0,CRIS4557606302014A0V0, CRIS4557606302014A0V0' ;
end;
/

select length(:x) , regexp_count( :x,',') from dual;


select trim(regexp_substr(:x,'[^,]+',1,level)) tokens
from dual
connect by level <= regexp_count( :x,',');


demo@ORA12C>


While executing this script got this.

demo@ORA12C> @d:\script.sql

PL/SQL procedure successfully completed.


LENGTH(:X) REGEXP_COUNT(:X,',')
---------- --------------------
      4513                  197


TOKENS
--------------------------------------------------------------------------------
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0
CRIS4557606302014A0V0

197 rows selected.

demo@ORA12C>

Classic Example and solution

Rajendra Sowani, June 09, 2017 - 5:05 am UTC

Thanks a ton for support for resolution.
It is very useful for us.
Regards,
Rajendra Sowani.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here