Solved

plsqlArray_Count

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

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.

Join & Write a Comment

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.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

705 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

15 Experts available now in Live!

Get 1:1 Help Now