| CLOB to Table [message #572293] |
Mon, 10 December 2012 03:15  |
 |
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 #572296 is a reply to message #572295] |
Mon, 10 December 2012 03:54  |
|
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 |
 |
Michel Cadot
Messages: 54126
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 |
|
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 |
|
Michel Cadot
Messages: 54126
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
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
