Home » SQL & PL/SQL » SQL & PL/SQL » CLOB to Table (Oracle SE 11.2.0.3.0)
CLOB to Table [message #572293] Mon, 10 December 2012 03:15 Go to next message
lucienlazar
Messages: 62
Registered: November 2010
Location: Romania
Member
Hello, I am running out of memory when calling a function that transforms a string in rows. If the string is varchar2 the function works fine but for CLOB it eventually runs out of memory. Is there any solution to keep using this approach for the function or should I use INSTR/SUBSTR or XML for parsing? The build-in comma-to-table is not helping me as I want a generic CLOB to rows function.

Thanks,
Lucian

create or replace type "tabletype_charmax" force as table of varchar2(32767);

create or replace function list_to_table(pi_string in clob, pi_separator in varchar2 default ',') return tabletype_charmax as
  v_result tabletype_charmax;
begin
  select regexp_substr(pi_string, '[^'||pi_separator||']+', 1, level) bulk collect into v_result from dual connect by regexp_substr(pi_string, '[^'||pi_separator||']+', 1, level) is not null;
  return v_result;
end;

declare v_result tabletype_charmax; begin v_result := list_to_table('a,b,c'); end;
Re: CLOB to Table [message #572295 is a reply to message #572293] Mon, 10 December 2012 03:52 Go to previous messageGo to next message
Michel Cadot
Messages: 57602
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unreadable!
Learn to format your code.

Regards
Michel

[Updated on: Mon, 10 December 2012 03:53]

Report message to a moderator

Re: CLOB to Table [message #572296 is a reply to message #572295] Mon, 10 December 2012 03:54 Go to previous messageGo to next message
lucienlazar
Messages: 62
Registered: November 2010
Location: Romania
Member
For such a small piece of code I prefer not using beautifier but ok, here it is with it:

create or replace type "tabletype_charmax" force as table of varchar2(32767);

create or replace function list_to_table(pi_string    in clob,
                                         pi_separator in varchar2 default ',')
  return tabletype_charmax as
  v_result tabletype_charmax;
begin
  select regexp_substr(pi_string, '[^' || pi_separator || ']+', 1, level) bulk collect
    into v_result
    from dual
  connect by regexp_substr(pi_string,
                           '[^' || pi_separator || ']+',
                           1,
                           level) is not null;
  return v_result;
end;

declare
  v_result tabletype_charmax;
begin
  v_result := list_to_table('a,b,c');
end;
Re: CLOB to Table [message #572300 is a reply to message #572296] Mon, 10 December 2012 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 57602
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
For such a small piece of code I prefer not using beautifier


In this case do not ask others to read it.

SQL> create or replace type "tabletype_charmax" force as table of varchar2(32767);
  2
  3  /

Type created.

SQL> create or replace function list_to_table(pi_string    in clob,
  2                                           pi_separator in varchar2 default ',')
  3    return tabletype_charmax as
  4    v_result tabletype_charmax;
  5  begin
  6    select regexp_substr(pi_string, '[^' || pi_separator || ']+', 1, level) bulk collect
  7      into v_result
  8      from dual
  9    connect by regexp_substr(pi_string,
 10                             '[^' || pi_separator || ']+',
 11                             1,
 12                             level) is not null;
 13    return v_result;
 14  end;
 15
 16  /

Warning: Function created with compilation errors.

SQL> sho err
Errors for FUNCTION LIST_TO_TABLE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
3/10     PLS-00201: identifier 'TABLETYPE_CHARMAX' must be declared

I don't think it works fine.

Regards
Michel
Re: CLOB to Table [message #572303 is a reply to message #572300] Mon, 10 December 2012 04:30 Go to previous messageGo to next message
lucienlazar
Messages: 62
Registered: November 2010
Location: Romania
Member
Sorry about the quotation marks at the type name, they were auto-generated:

create or replace type tabletype_charmax force as table of varchar2(32767);
/
create or replace function list_to_table(pi_string    in clob,
                                         pi_separator in varchar2 default ',')
  return tabletype_charmax as
  v_result tabletype_charmax;
begin
  select regexp_substr(pi_string, '[^' || pi_separator || ']+', 1, level) bulk collect
    into v_result
    from dual
  connect by regexp_substr(pi_string,
                           '[^' || pi_separator || ']+',
                           1,
                           level) is not null;
  return v_result;
end;
/
declare
  v_result tabletype_charmax;
begin
  v_result := list_to_table('a,b,c');
end;
Re: CLOB to Table [message #572304 is a reply to message #572303] Mon, 10 December 2012 04:32 Go to previous message
Michel Cadot
Messages: 57602
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Combining regexp and hierarchical query is not a good idea, above all with very long string.
Both use huge CPU resource and connect by huge memory size.
Just use a simple loop on your string for this; it will be far faster and will use far less memory.

Regards
Michel
Previous Topic: Subquery SQL
Next Topic: Display Week number as per financial year(Apr - Mar)
Goto Forum:
  


Current Time: Wed Apr 16 13:12:26 CDT 2014

Total time taken to generate the page: 0.06687 seconds