Re: Validating in PL/SQL using associative arrays

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Tue, 14 Jan 2014 22:22:36 +0400
Message-ID: <CAOVevU5-EotqoMDNYWqZyF5yHTGeQPWF=Z7bAd7XK38DfPGVog_at_mail.gmail.com>

You can do it simply with
IF t_valid_schema.exists(p_owner) THEN

On Tue, Jan 14, 2014 at 9:23 PM, Rich Jesse < rjoralist3_at_society.servebeer.com> wrote:

> Hey all,
>
> In 11.2, I thought I'd try to validate parameters to a procedure using an
> associative array:
>
> CREATE OR REPLACE PROCEDURE test_assoc (p_owner IN VARCHAR2)
> AS
> TYPE tt_valid_schema IS TABLE OF BINARY_INTEGER INDEX BY
> VARCHAR2(30);
> t_valid_schema tt_valid_schema;
>
> BEGIN
> t_valid_schema('TEST01') := 1;
> t_valid_schema('TEST02') := 1;
> t_valid_schema('TEST08') := 1;
> t_valid_schema('TEST135') := 1;
>
> -- IF t_valid_schema(p_owner)%NOTFOUND THEN
> -- RETURN;
> -- END IF;
>
> END test_assoc;
> /
>
> I'm trying to do what's commented out, but that (obviously) won't work. It
> seems like I should be able to handle the ORA-1403 with an exception
> without
> spinning through the entire array (part of the whole idea of the array
> being
> associative), but I can't seem to find an example for this.
>
> Ideas anyone? (other than to put the data in a database table)
>
> TIA!
> Rich
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 14 2014 - 19:22:36 CET