I will definitely be playing with this. I remember using Paradox 5 and they had a function called? split, that did almost the same.
Thanks Scott
Discussion on:
View:
Show:
Excellent. Helps to make Oracle reporting services reports that use multi-value lists easy.
...and poc in (select * from table(split_str(POC_V)))...
Where POC_V is the multi-value (comma delimited) string passed in from RS.
Oracle equivalent to the fn_MVParam function.
...and poc in (select * from table(split_str(POC_V)))...
Where POC_V is the multi-value (comma delimited) string passed in from RS.
Oracle equivalent to the fn_MVParam function.
You need to close the cursor, or you will get a "Maximum open cursors"-error.
Insert after "end loop;":
close p_cursor; -- == Close the cursor after use!!!
Insert after "end loop;":
close p_cursor; -- == Close the cursor after use!!!
Your article is great and instructive. I'll use part of this code to generate a table with an id field and a concatenated text field. For the momment I got the following erros:
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
my code looks like that:
CREATE TABLE my_table AS (
SELECT ID, JOIN(CURSOR(SELECT field_name|| ':' || COUNT(*)
FROM table_1 dl
WHERE dl.ID = dl2.ID
GROUP BY field_name,ID )) subs FROM table_1 dl2 GROUP BY ID
);
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
my code looks like that:
CREATE TABLE my_table AS (
SELECT ID, JOIN(CURSOR(SELECT field_name|| ':' || COUNT(*)
FROM table_1 dl
WHERE dl.ID = dl2.ID
GROUP BY field_name,ID )) subs FROM table_1 dl2 GROUP BY ID
);
The Split function throws error in Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 where as it seems to running fine in previous versions. The error is ORA-00600: internal error code, arguments: [15201], [], [], [], [], [], [], []
This is in metalink. Appears to be a problem with 10.2.0.3 when copying a schema containing pipeline functions using package types. Drop the "SYS_PLSQL_%" types used by pipeline functions and recompile the packages. You may have to drop and recreate the packages (recompile sometimes fails).
Try this:
select 'drop type ' || ut.type_name || ';' sql_str, regexp_replace(ut.type_name, 'SYS_PLSQL_([0-9]+)(.)*', '\1')
, ut.*
from user_types ut
where type_name like 'SYS_PLSQL%'
and not exists (
select 1 from user_objects uo
where uo.object_id = regexp_replace(ut.type_name, 'SYS_PLSQL_([0-9]+)(.)*', '\1')
)
;
Try this:
select 'drop type ' || ut.type_name || ';' sql_str, regexp_replace(ut.type_name, 'SYS_PLSQL_([0-9]+)(.)*', '\1')
, ut.*
from user_types ut
where type_name like 'SYS_PLSQL%'
and not exists (
select 1 from user_objects uo
where uo.object_id = regexp_replace(ut.type_name, 'SYS_PLSQL_([0-9]+)(.)*', '\1')
)
;
maybe you won't be interested but you can optimize the split function by using bulk or global temporary table 
like that: (I didint try it so there may be some mistakes)
create or replace
global_temporary_table tmp_table
(
item varchar2(4000);
)
create or replace function split
(
p_list varchar2,
p_del varchar2 := ','
) return split_tbl
is
pragma atonomous_transaction;
l_idx pls_integer;
l_list varchar2(32767) := p_list;
return_array split_tbl;
AA
l_value varchar2(32767);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
insert into tmp_table
values (substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));
else
insert into tmp_table
values (l_list);
exit;
end if;
end loop;
select *
bulk collect into return_array
from tmp_table;
rollback;
return return_array;
end split;
like that: (I didint try it so there may be some mistakes)
create or replace
global_temporary_table tmp_table
(
item varchar2(4000);
)
create or replace function split
(
p_list varchar2,
p_del varchar2 := ','
) return split_tbl
is
pragma atonomous_transaction;
l_idx pls_integer;
l_list varchar2(32767) := p_list;
return_array split_tbl;
AA
l_value varchar2(32767);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
insert into tmp_table
values (substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));
else
insert into tmp_table
values (l_list);
exit;
end if;
end loop;
select *
bulk collect into return_array
from tmp_table;
rollback;
return return_array;
end split;
It's a great contribution. By the I would like to use a regular expression instead of comma. It should be the following one: /[;1]{3,30};/
I have been trying with it for a long time unsucessfully.
I would appreciate any Help.
Thanks
Ed
I have been trying with it for a long time unsucessfully.
I would appreciate any Help.
Thanks
Ed
Thanks a lot dude...it was really really helpful
If the parent query returns more than 50 rows, you get the error 'Maximum open cursors exceeded',
Sunil
Sunil
The split function is ok, but i have a SQL statement like the following one:
select col1,col2,col3 from tab1 where col1 in (select * from table(split('81,82,83,84')))
where col1 is in the split result, how to do? thanks.
the SQL statement can not work, would you please help me on this? thanks.
select col1,col2,col3 from tab1 where col1 in (select * from table(split('81,82,83,84')))
where col1 is in the split result, how to do? thanks.
the SQL statement can not work, would you please help me on this? thanks.
Hi,
I am a newbie with SP/Functions. When i use your Split Function i am getting this error "PLS-00201: identifier 'TYPE_SPLIT_PIPELINED' must be declared".
Your help will be appreciated.
Thanks
Sam
I am a newbie with SP/Functions. When i use your Split Function i am getting this error "PLS-00201: identifier 'TYPE_SPLIT_PIPELINED' must be declared".
Your help will be appreciated.
Thanks
Sam
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
