Re: PL/SQL Associative Arrays - Index Not In Array

From: Chris \( Val \) <chrisval_at_bigpond.com.au>
Date: Thu, 09 Jun 2005 11:19:26 GMT
Message-ID: <2BVpe.11396$F7.10733@news-server.bigpond.net.au>

"absinth" <absinth_at_gmail.com> wrote in message news:1118304444.300506.264620_at_g44g2000cwa.googlegroups.com...
| Say I have an associative array 'blah' and it has members 'hello' and
| 'bye.'
|
| How can I instruct Oracle to return a NULL if I lookup the array and
| the value isn't found?
|
| E.g.
| blah('hello') might map to '1'
| But I want blah('Oracle') to return NULL.

I'm still new to PL/SQL, but this would be my attempt:

• Create a function to do the real work for us: CREATE OR REPLACE FUNCTION PrintValue( Key IN VARCHAR2 ) RETURN VARCHAR2 AS

  TYPE MyMapType IS TABLE OF VARCHAR2( 15 ) INDEX BY VARCHAR2( 15 );

  MyMap MyMapType;
  Value VARCHAR2( 15 );

BEGIN   IF Key IS NULL THEN

     RETURN 'Error: Source string is NULL';   END IF;

• Some test key/value pairs: MyMap( 'foo' ) := 'Foo Value'; MyMap( 'bar' ) := 'Bar Value'; MyMap( 'baz' ) := 'Baz Value';

  Value:= MyMap( Key );
  RETURN Value;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN NULL;
END;
/

• We can test our function as follows: DECLARE TYPE MyArray IS TABLE OF VARCHAR2( 15 ); Names MyArray := MyArray( '', 'Oracle', 'foo', 'bar', 'baz', '10g' ); BEGIN FOR Idx IN Names.FIRST .. Names.LAST LOOP DBMS_OUTPUT.PUT_LINE( NVL( PrintValue( Names( Idx ) ), 'NULL') ); END LOOP; END; /

Hope this help's.

Cheers,
Chris Val

PS: I would love to hear from the experts of ways

    that I can improve my PL/SQL code so that I can     write better code in PL/SQL - I'm coming from C++. Received on Thu Jun 09 2005 - 06:19:26 CDT