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

From: Chris \( Val \) <chrisval_at_bigpond.com.au>
Date: Thu, 09 Jun 2005 11:40:20 GMT
Message-ID: <EUVpe.11413$F7.3339@news-server.bigpond.net.au>

"Chris ( Val )" <chrisval_at_bigpond.com.au> wrote in message news:2BVpe.11396$F7.10733_at_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;
| /

[snip]

| 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++.

Responding to myself :-)

I refactored my code a little more - I removed one variable 'Value' and instead return a temporary varchar2. I also eliminated the check for NULL, and allow the PL/SQL exception handling mechanism trap it for me with the 'OTHERS' clause.

• BEGIN CODE --
• 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;

BEGIN

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

  RETURN MyMap( Key );

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN NULL;
    WHEN OTHERS THEN
      RETURN 'Error: Source string is NULL'; END;
/

• END CODE --

Is this better?

Cheers,
Chris Val Received on Thu Jun 09 2005 - 06:40:20 CDT