Re: SELECT FROM PACKAGE

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: 4 Aug 2003 00:42:01 -0700
Message-ID: <1a75df45.0308032342.7c83b5f3@posting.google.com>

mmontreaux_at_hotmail.com (Monty) wrote

> CREATE OR REPLACE PACKAGE errMessages IS
> err1 CONSTANT VARCHAR2(100) := 'Illegal error due to...';
> err2 VARCHAR2(100) := 'Invalid number in customer id...';
> err3 VARCHAR2(100) := 'PLease reenter details for customer id';
> END errMessages;
> /
>
> I want the select to return all the VARCHAR2 so I can see what they
> all are. But when I execute the below I get an error.
>
> SQL>
> SQL> SELECT errMessages.err1,errMessages.err2,errMessages.err3 FROM
> DUAL;
> SELECT errMessages.err1,errMessages.err2,errMessages.err3 FROM DUAL
> *
> ERROR at line 1:
> ORA-06553: PLS-221: 'ERR3' is not a procedure or is undefined
>

SQL> create or replace function err1 return varchar2 as   2 begin
  3 return( errmessages.err1 );
  4 end;
  5 /

Function created.

SQL> select err1 from dual;

ERRX

---

Illegal error due to...

You can create function with numeric input for the error number, and then use a giant switch/case statement in the function to determine what constant from errmessages to return.

Dirty.. not very maintainable.. but it works.

--
Billy

Received on Mon Aug 04 2003 - 02:42:01 CDT