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 Go to next message
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 #408148 is a reply to message #408147] Sun, 14 June 2009 21:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>function comma_to_cursor(instr IN varchar2, cursor_our OUT REF CURSOR)
Small Matter Of Programming (SMOP)
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm
start coding in PL/SQL
Re: Ref cursor and parsing the string question [message #408180 is a reply to message #408147] Mon, 15 June 2009 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What is your problem now?
Writing a query that returns the result?
Writing a procedure?
Writing a procedure returning a ref cursor?
...

Regards
Michel
Re: Ref cursor and parsing the string question [message #408199 is a reply to message #408147] Mon, 15 June 2009 04:36 Go to previous messageGo to next message
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 #408202 is a reply to message #408199] Mon, 15 June 2009 04:47 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
No regex...OP is on 9i.
Re: Ref cursor and parsing the string question [message #408347 is a reply to message #408180] Mon, 15 June 2009 19:26 Go to previous messageGo to next message
ktanya
Messages: 10
Registered: July 2007
Junior Member
Hi Michel,
Actually, I do not have any problems. I've seen many examples - but I did not see the example using the ref cursor...
If you have a solution, it will be nice to see.
Thanks a lot!
Re: Ref cursor and parsing the string question [message #408378 is a reply to message #408347] Tue, 16 June 2009 00:46 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Ref cursor and parsing the string question [message #408408 is a reply to message #408400] Tue, 16 June 2009 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

":str" means external/bind variable of name "str".
"str" means local variable (or parameter) of name "str".

Regards
Michel

[Updated on: Tue, 16 June 2009 23:12]

Report message to a moderator

Re: Ref cursor and parsing the string question [message #408570 is a reply to message #408378] Tue, 16 June 2009 21:26 Go to previous message
ktanya
Messages: 10
Registered: July 2007
Junior Member
Thank you Michel!!!
I appreciate and thank you for your help!!!
- T
Previous Topic: which one is more faster
Next Topic: DBMS_JOB.SUBMIT multiple next_date / intervals
Goto Forum:
  


Current Time: Tue Dec 03 15:56:05 CST 2024