Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Loop Through array in stored procedure

Re: Loop Through array in stored procedure

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Tue, 19 Sep 2006 17:46:39 GMT
Message-ID: <J5upE1.3zB@igsrsparc2.er.usgs.gov>


jpabich wrote:
> How can I loop through an array in a stored procedure to get the last
> value in the array?
> Here is my SP so far:
>
> CREATE OR REPLACE PROCEDURE prPM_TIMER_RESULT_ST_TZ
> (
> i_order_id VARCHAR2,
> i_batch_id VARCHAR2,
> i_timer VARCHAR2,
> ts OUT VARCHAR2
> )
> AS
> tz VARCHAR2;
> STR_ARRAY VARCHAR2(4000);
> i INTEGER;
>
> BEGIN
> SELECT STOP_TIMESTAMP INTO tz FROM PM_TIMER_RESULT_ST
> WHERE
> order_id = i_order_id AND
> batch_id = i_batch_id AND
> timer_name = i_timer;
>
>
> STR_ARRAY := split(tz, '-');
>
>
> for (i=0;i<count(STR_ARRAY);i++)
> {
> ts = STR_ARRAY[i];
> }
>
> END;
> /
>

Instead of trying to use a string variable to hold the contents of an array, have you considered a different datatype? Maybe a VARRAY?

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i46425

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown
Received on Tue Sep 19 2006 - 12:46:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US