plsqlArray_Count

Is there a better way to check if an array parameter has any record with "Y" or not?

I have a user who submits HTML form to this procedure. If the array does not have any flag with "Y" then i want to raise an exception.

I would like to do first thing before any processing (saving records).

is ther a better way?
PROCEDURE SAVE_ORDER (
  p_ord_date      IN   VARCHAR2      DEFAULT NULL,
  p_stockno       IN   TYPES.ARRAY    DEFAULT TYPES.EMPTY_ARRAY,
  p_flag          IN   TYPES.ARRAY    DEFAULT TYPES.EMPTY_ARRAY)
  
AS 

  missing_Y_items            exception;
  records_found            boolean   default  false;
 

BEGIN

/*
I need to check if there are any records submitted with flag set to "Y".
If there are no records, raise an exception with alert that no records are eligible.
*/



FOR i IN 1..p_stockno.COUNT 
   LOOP

    IF  p_flag(i) = 'Y' THEN
     save records.....
     records_found := TRUE;
    END IF;
   
   END LOOP;
   
   IF (records_found := FALSE) THEN
     RAISE missing_Y_items;
   END IF;
 
 END;

Open in new window

sam15Asked:
Who is Participating?
 
johanntagleCommented:
I personally prefer something like:

while v_exit = 0 loop
  if (condition to detect 'Y') then'
    v_exit := 1;
  end if;
end loop
0
 
johanntagleCommented:
Try:

IF p_flag.EXISTS('Y') THEN
  save records ....
  records_found := TRUE;
END IF;
0
 
sam15Author Commented:
I doubt it after reading this. You should pass the nth element position and it only checks if the element is there or not.

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm

Checking If a Collection Element Exists (EXISTS Method)
EXISTS(n) returns TRUE if the nth element in a collection exists. Otherwise, EXISTS(n) returns FALSE. Mainly, you use EXISTS with DELETE to maintain sparse nested tables. You can also use EXISTS to avoid raising an exception when you reference a nonexistent element. In the following example, PL/SQL executes the assignment statement only if element i exists:

IF courses.EXISTS(i) THEN courses(i) := new_course; END IF;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
johanntagleCommented:
Yeah sorry read the docs wrong.  I'm sure there's an easy way - maybe cast the collection as a table then just select count(*) where value='Y' ?
0
 
sam15Author Commented:
Do you have an example of how to case the array as a table and then do the count?

Does anyone have other way to do this too?
0
 
sam15Author Commented:
I think i will do two loops: one for data validation and one for transaction.

I just need to exit the 1st loop when I find the first value with "Y". Do you normally set a boolean variable to true when the first Y hits and add that condition to the EXIT loop statement like here

http://www.techonthenet.com/oracle/loops/exit.php

http://www.adp-gmbh.ch/ora/plsql/loops.html
0
 
sam15Author Commented:
so if the array has 100 variables with Ys, the while loop will stop on the first record with "Y". right?
0
 
johanntagleCommented:
Yes that's right.  The code just looks cleaner when you don't use an EXIT WHEn statement.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.