Home » SQL & PL/SQL » SQL & PL/SQL » Accessing collection elements without Looping
Accessing collection elements without Looping [message #270596] Thu, 27 September 2007 11:14 Go to next message
thapa
Messages: 15
Registered: September 2007
Junior Member
Hi,

Is it possible to access specific element in the collection without looping?
I have created a type like below

CREATE TYPE tableofNames IS TABLE OF VARCHAR2(1000);

This type will be used as a parameter to the function shown below:

create or replace FUNCTION parseString(p_table_in tableofNames)
RETURN varchar2 IS l_return_string VARCHAR2(8000);
BEGIN
l_return_string := p_table_in(1); //This gives ERROR
FOR x IN 1..p_table_in.count
LOOP
l_return_string := p_table_in(x); //OK
END LOOP;
RETURN l_return_string;
END LongString;


If I hardcode to read "p_table_in(1)" (as shown in the function above) without a loop, I get ORA-06533: Subscript beyond count exception but if I run a loop and use a variable x to access the elements, it's fine.

Coming from a programming background, I find this little odd. Is there a way to access the specific element without looping ( say first element).
Re: Accessing collection elements without Looping [message #270604 is a reply to message #270596] Thu, 27 September 2007 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is there a first element?

Regards
Michel
Re: Accessing collection elements without Looping [message #270606 is a reply to message #270604] Thu, 27 September 2007 11:34 Go to previous messageGo to next message
thapa
Messages: 15
Registered: September 2007
Junior Member
Yes, There is always atleast one element
Re: Accessing collection elements without Looping [message #270608 is a reply to message #270596] Thu, 27 September 2007 11:34 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
How about running it both ways and cut and paste the sql session and the error(s) you are getting.
Re: Accessing collection elements without Looping [message #270610 is a reply to message #270608] Thu, 27 September 2007 11:36 Go to previous messageGo to next message
thapa
Messages: 15
Registered: September 2007
Junior Member
I am sorry what do you maen by "cut and paste the sql session" ?
Re: Accessing collection elements without Looping [message #270613 is a reply to message #270610] Thu, 27 September 2007 11:45 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
What don't you understand about cut & Paste ?

Create your function with the hardcoded p_table_in(1).
Cut and paste the create function.
Then select parseString(v_1).
Cut and past the result


Create your function without the hardcoded p_table_in(1).
Cut and paste the create function.
Then select parseString(v_1).
Cut and past the result


Pasting the actual code you are running along with the error oracle is returning is usually more helpful than you typing the error message in.

While you're at it read the posting guidelines.
http://www.orafaq.com/forum/t/88153/0/



[Updated on: Thu, 27 September 2007 11:47]

Report message to a moderator

Re: Accessing collection elements without Looping [message #270619 is a reply to message #270613] Thu, 27 September 2007 12:00 Go to previous messageGo to next message
thapa
Messages: 15
Registered: September 2007
Junior Member
Sorry MarcL. I have no idea what you are talking about when you say:
Create your function with the hardcoded p_table_in(1).
Cut and paste the create function.(WHERE?)
Then select parseString(v_1).( what is v_1)
Cut and past the result

If the duplicate lines were to illustrate cut and paste, thanks:-)

i am using a little sql console in my IntelliJ IDE. Maybe that's creating a confusion. Should I be using some oracle specific sql editor?

Thanks
Re: Accessing collection elements without Looping [message #270735 is a reply to message #270619] Thu, 27 September 2007 22:21 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
We want to see exactly what is happening. Set up your test so that it gets the error, and set up another test so that it does not get the error (using looping), then reply to this thread showing us the code AND the results as it appears on your screen. Omit nothing!.

Ross Leishman
Re: Accessing collection elements without Looping [message #270847 is a reply to message #270619] Fri, 28 September 2007 02:52 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The expectation is that if you don't tell us what tool you're using, then you're using SQL*Plus
It's part of the standard Oracle client side install.
Previous Topic: Please Help Me with Select ... Into Statement (PLS-00428)
Next Topic: Deletion of records taking too much time
Goto Forum:
  


Current Time: Thu Dec 08 00:38:03 CST 2016

Total time taken to generate the page: 0.09852 seconds