Solved

plsqlArray_Count

Posted on 2011-09-29
8
285 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

840 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