Home » SQL & PL/SQL » SQL & PL/SQL » Working with Arrays
Working with Arrays [message #241289] Tue, 29 May 2007 02:06 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

I want to use arrays in Oracle. I have three values. ('one,seven,nine')
I want to put it in a array.
Then I want to fetch the comma seperated words from this array in a loop.

Please advice.

Thanks,
Mona
Re: Working with Arrays [message #241291 is a reply to message #241289] Tue, 29 May 2007 02:09 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There are some useful functions in DBMS_UTILITY that handle this for you.

Ross Leishman
Re: Working with Arrays [message #241298 is a reply to message #241291] Tue, 29 May 2007 02:38 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
Please give me a simple example of as how to extract the comma seperated values from a string like ('one,seven,nine') in a loop.

Regards,
Mona
Re: Working with Arrays [message #241312 is a reply to message #241298] Tue, 29 May 2007 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use DBMS_UTILITY.COMMA_TO_TABLE procedure.
Otherwise, it is not difficult to write such a procedure using SUBSR and INSTR functions.

Regards
Michel
Re: Working with Arrays [message #241409 is a reply to message #241312] Tue, 29 May 2007 08:52 Go to previous messageGo to next message
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
Senior Member
You
can use varrays for this.

http://www.dba-oracle.com/tips_oracle_varray.htm

Regards,
Dinesh
Re: Working with Arrays [message #241766 is a reply to message #241298] Wed, 30 May 2007 10:28 Go to previous message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Check out the sample code at SQL Snippets: String to Columns - Separated Values - 10g+. It shows you how to do this in SQL, not PL/SQL, but it shouldn't be too hard to apply the "rtrim( regexp_substr ..." logic in PL/SQL.

--
Joe Fuda
SQL Snippets

[Updated on: Wed, 30 May 2007 10:29]

Report message to a moderator

Previous Topic: How to integrate SUM in WHERE?
Next Topic: Refrential Integrity Query
Goto Forum:
  


Current Time: Sun Dec 04 14:36:50 CST 2016

Total time taken to generate the page: 0.10331 seconds