Solved

plsqlArray_Count

Posted on 2011-09-29
8
287 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:sam15
  • 4
  • 4
8 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 36861229
Try:

IF p_flag.EXISTS('Y') THEN
  save records ....
  records_found := TRUE;
END IF;
0
 

Author Comment

by:sam15
ID: 36893140
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
 
LVL 24

Expert Comment

by:johanntagle
ID: 36894277
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:sam15
ID: 36895153
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
 

Author Comment

by:sam15
ID: 36898176
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
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 36898197
I personally prefer something like:

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

Author Comment

by:sam15
ID: 36898315
so if the array has 100 variables with Ys, the while loop will stop on the first record with "Y". right?
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36898334
Yes that's right.  The code just looks cleaner when you don't use an EXIT WHEn statement.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question