ORA-01704: string literal too long error while using regexp_substr

create table dynamic_query(test clob);
/
create or replace procedure proc_too_large is
v_empcode clob;
str clob;
v_count number;

begin
v_empcode:='CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
    'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
   'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
    'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
    'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
    'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
    'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
   'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
   'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
   'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
   'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
   'CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,CRIS4557606302014A0V0,'||
  'CRIS4557606302014A0V0,CRIS4557606302014A0V0';

v_count:=regexp_count(v_empcode,',');

for x  in 1.. v_count+1 loop
    str:=regexp_substr(v_empcode, '[^,]+',1,x);
    insert into dynamic_query values(str);
    end loop;
end;

/
exec proc_too_large
/
select * from dynamic_query;


Comments

Post a Comment

Popular posts from this blog

Table high water mark tips

  Question:  Can you please explain the concept of a table high water mark (HWM)?  I understand that the data is always below the high water mark.  Can you show a script to display the high water mark for a table? Answer:   The high water mark (HWM) for an Oracle table is a construct that shows the table at its greatest size.  Just as a lake has a high-water mark after a draught, an Oracle table has a high water mark that shows the greatest size of the table, the point at which it consumed the most extents. Also see this great script to  display all high water marks . As a table undergoes deletes and updates, rows shrink and table data blocks become empty.  For performance reasons, Oracle keeps the high water mark for a table rather than re-calculate the high water mark after blocks at the "end" of the table (the last extent) becomes empty. For example assume that you have a million row table that takes 30 seconds to read.  After deleting 900...
Read more

Create table of two consecutive months if date range belong to two months

select to_date('01-JUL-2019')+level-1 "JULY", CASE WHEN MONTHS_BETWEEN('09-AUG-2019','01-JUL-19')>1 THEN CASE WHEN LEVEL<=TO_NUMBER(TO_CHAR(TO_DATE('09-AUG-2019'),'DD')) THEN ADD_MONTHS('01-JUL-2019',1)+LEVEL-1 END END "August" FROM DUAL CONNECT BY LEVEL<=CASE WHEN MONTHS_BETWEEN('09-AUG-2019', '01-JUL-19')>1 THEN TO_NUMBER(TO_CHAR(LAST_DAY('01-JUL-2019'),'DD')) ELSE TO_DATE('09-AUG-2019')-TO_DATE('01-JUL-2019') +1 END ----------------------------------- ANOTHER METHOD ----------------------------------- SELECT "JULY", "AUGUST" FROM ( SELECT ROWNUM RN, CASE WHEN TO_CHAR(TO_DATE(MNTH),'MON')='JUL' THEN MONTH END "JULY" FROM ( SELECT TO_DATE('01-JUL-2019')+LEVEL-1 MNTH FROM DUAL CONNECT BY LEVEL<=TO_DATE('09-AUG-19')-T0_DATE('01-JUL-2019')+1 ...
Read more

Fill null values with last non-null amount - Oracle SQL

  I have a table that has 4 columns: Item, Year, Month, Amount. Some of the values for Amount are null and when that happens I want to fill those values in with the previous Amount value that is not null. I can easily do this with the LAG function when there is only one null value but when there are multiple in a row I am not sure how to approach it. Below is an example of what the table might look like with an added column for what I want to add in my query: Item | Year | Month | Amount | New_Amount AAA | 2013 | 01 | 100 | 100 AAA | 2013 | 02 | | 100 AAA | 2013 | 03 | 150 | 150 AAA | 2013 | 04 | 125 | 125 AAA | 2013 | 05 | | 125 AAA | 2013 | 06 | | 125 AAA | 2013 | 07 | | 125 AAA | 2013 | 08 | 175 | 175 I had two ideas which I can't seem to get to work to produce what I want. First I was going to use LAG but then I noticed when there are multiple null values in a row it won't satisfy that. Next I was going...
Read more