Answers
Jul 16, 2010 - 04:52 AM
Hi AvReddy,
Could you please be more specific about the question and is there any sql query you want to be modified ?
Could you please be more specific about the question and is there any sql query you want to be modified ?
Jul 17, 2010 - 03:03 AM
hi..
yes i want this should be done sql.can please split that and give the result query.thanks in advance...
yes i want this should be done sql.can please split that and give the result query.thanks in advance...
Jul 17, 2010 - 08:38 AM
Hello Avreddy,
You will need to create split_tbl type (step 1) and then create a split function (Step 2) as below;
then use it as mentioned in (Step 3)
Step 1:
create or replace type split_tbl as table of varchar2(32767);
/
show errors;
Step 2:
create or replace function split
(
p_list varchar2,
p_del varchar2 := '.'
) return split_tbl pipelined
is
l_idx pls_integer;
l_list varchar2(32767) := p_list;
AA
l_value varchar2(32767);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
pipe row(substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));
else
pipe row(l_list);
exit;
end if;
end loop;
return;
end split;
/
show errors;
With this function, I can run a query like this:
Step 3 :
SQL> select * from table(split('3000.MANAGER.ACCOUNTING.3.1'));
Result:
3000
MANAGER
ACCOUNTING
3
1
You will need to create split_tbl type (step 1) and then create a split function (Step 2) as below;
then use it as mentioned in (Step 3)
Step 1:
create or replace type split_tbl as table of varchar2(32767);
/
show errors;
Step 2:
create or replace function split
(
p_list varchar2,
p_del varchar2 := '.'
) return split_tbl pipelined
is
l_idx pls_integer;
l_list varchar2(32767) := p_list;
AA
l_value varchar2(32767);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
pipe row(substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));
else
pipe row(l_list);
exit;
end if;
end loop;
return;
end split;
/
show errors;
With this function, I can run a query like this:
Step 3 :
SQL> select * from table(split('3000.MANAGER.ACCOUNTING.3.1'));
Result:
3000
MANAGER
ACCOUNTING
3
1
Jul 17, 2010 - 11:29 AM
It's working fine,Thank you so much for the help.
Jul 17, 2010 - 11:39 AM
AvReddy,
Could you please award points and close this issue, so I can write a tutorial on this.
Thanks
Could you please award points and close this issue, so I can write a tutorial on this.
Thanks
Jul 26, 2010 - 02:31 AM
You will need to create split_tbl type (step 1) and then create a split function (Step 2) as below;
then use it as mentioned in (Step 3)
Step 1:
create or replace type split_tbl as table of varchar2(32767);
/
show errors;
Step 2:
create or replace function split
(
p_list varchar2,
p_del varchar2 := '.'
) return split_tbl pipelined
is
l_idx pls_integer;
l_list varchar2(32767) := p_list;
AA
l_value varchar2(32767);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
pipe row(substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));
else
pipe row(l_list);
exit;
end if;
end loop;
return;
end split;
/
show errors;
With this function, I can run a query like this:
Step 3 :
SQL> select * from table(split('3000.MANAGER.ACCOUNTING.3.1'));
Result:
3000
MANAGER
ACCOUNTING
3
1
then use it as mentioned in (Step 3)
Step 1:
create or replace type split_tbl as table of varchar2(32767);
/
show errors;
Step 2:
create or replace function split
(
p_list varchar2,
p_del varchar2 := '.'
) return split_tbl pipelined
is
l_idx pls_integer;
l_list varchar2(32767) := p_list;
AA
l_value varchar2(32767);
begin
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
pipe row(substr(l_list,1,l_idx-1));
l_list := substr(l_list,l_idx+length(p_del));
else
pipe row(l_list);
exit;
end if;
end loop;
return;
end split;
/
show errors;
With this function, I can run a query like this:
Step 3 :
SQL> select * from table(split('3000.MANAGER.ACCOUNTING.3.1'));
Result:
3000
MANAGER
ACCOUNTING
3
1
Add New Comment