Home » SQL & PL/SQL » SQL & PL/SQL » Ref cursor and parsing the string question (Oracle 9i)
Ref cursor and parsing the string question [message #408147] |
Sun, 14 June 2009 19:49 |
ktanya
Messages: 10 Registered: July 2007
|
Junior Member |
|
|
Hi,
I got the question on one of the interviews for Oracle dev. position:
We have a string 'Blank:1,3,17'
How to parse it as follows
Blank: 1
Blank: 3
Blank: 17
They wanted to have the output as ref.curson or wanted to know how to convert it to the cursor...
How to convert this into a cursor?
function comma_to_cursor(instr IN varchar2, cursor_our OUT REF CURSOR)
The alternative answer for the function without the cursor that could return the array was not sufficient.
Thanks,
- t
|
|
|
|
|
Re: Ref cursor and parsing the string question [message #408199 is a reply to message #408147] |
Mon, 15 June 2009 04:36 |
karthick_arp
Messages: 13 Registered: February 2006 Location: hyderabad
|
Junior Member |
|
|
With the very less info provided i have constructed this SQL
SQL> with t
2 as
3 (
4 select 'Blank:1,3,17' str from dual
5 )
6 select regexp_substr(str, '.*:') ||regexp_substr(str,'[url=/wiki/:digit:]:digit:[/url]+',1,LEVEL) str
7 from t
8 connect by level <= length(regexp_replace(str,'[^,]'))+1
9 /
STR
------------------
Blank:1
Blank:3
Blank:17
You can use this in a procedure or use it with a cursor or do any thing. Hope you are in a version that supports regular expression.
|
|
|
|
|
Re: Ref cursor and parsing the string question [message #408378 is a reply to message #408347] |
Tue, 16 June 2009 00:46 |
|
Michel Cadot
Messages: 68718 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
I don't understand why you post a question if you have no problem.
SQL> var str varchar2(50)
SQL> exec :str := 'Blank:1,3,17'
PL/SQL procedure successfully completed.
SQL> with
2 lines as (select level line from dual connect by level <= 3),
3 data as (
4 select substr(:str,1,instr(:str,':')) prefix,
5 ','||substr(:str,instr(:str,':')+1)||',' value
6 from dual
7 )
8 select prefix||' '||
9 substr(value, instr(value,',',1,line)+1,
10 instr(value,',',1,line+1)-instr(value,',',1,line)-1)
11 value
12 from data, lines
13 /
VALUE
--------------------------------------
Blank: 1
Blank: 3
Blank: 17
3 rows selected.
SQL> create or replace procedure p (str in varchar2, cur out sys_refcursor)
2 as
3 begin
4 open cur for
5 'with
6 input as ( select :str str from dual ),
7 lines as ( select level line from dual connect by level <= 3 ),
8 data as (
9 select substr(str,1,instr(str,'':'')) prefix,
10 '',''||substr(str,instr(str,'':'')+1)||'','' value
11 from input
12 )
13 select prefix||'' ''||
14 substr(value, instr(value,'','',1,line)+1,
15 instr(value,'','',1,line+1)-instr(value,'','',1,line)-1)
16 value
17 from data, lines'
18 using str;
19 end;
20 /
Procedure created.
SQL> var c refcursor
SQL> set autoprint on
SQL> exec p(:str,:c)
PL/SQL procedure successfully completed.
VALUE
--------------------------------
Blank: 1
Blank: 3
Blank: 17
3 rows selected.
Regards
Michel
|
|
|
Re: Ref cursor and parsing the string question [message #408400 is a reply to message #408378] |
Tue, 16 June 2009 02:17 |
stefen054
Messages: 1 Registered: June 2009
|
Junior Member |
|
|
Hi Michel
I had tried another way of spliting the the string as you see below code,
create or replace procedure splt (str in varchar2,cur out sys_refcursor)
as
begin
str:= ','||to_char(substr(:str,instr(:str,':')+1));
for counter in 1..length(str) loop
if substr(str,counter,1)=',' then
select (substr(str,counter+1,1)) from dual;
end if;
end loop;
end;
I got error message as
4/27 PLS-00049: bad bind variable 'STR'
4/38 PLS-00049: bad bind variable 'STR'
could you please on this?
Regards,
Stefen
|
|
|
|
|
Goto Forum:
Current Time: Tue Dec 03 15:56:05 CST 2024
|