Solved

plsqlArray_Count

Posted on 2011-09-29
8
281 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
 

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
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: 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
report returning null 21 81
ORA-12560: TNS:protocol adapter error 8 87
1 FROM DUAL wont work with additional columns ?? 4 26
use lov values 2 37
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now