Re: Why can't I access package data from sqlplus?

Ken Denny wrote:
>
> I created a package called CONSTANTS to hold constant values sort of
> like using #define in C.
>
> create or replace
> PACKAGE constants AS
> audit_name VARCHAR2(16):='AUDIT';
> . .
> END constants;
> /
>
> But in sqlplus, if I do:
>
> select constants.audit_name from dual;
>
> I get ORA-00904: invalid column name
>
> I own the package, so I should automatically have all privileges for it.
> I tried to grant myself execute privilege but I got a message that I
> can't grant or revoke privileges to myself. I'm also worried that if I
> can't access it from sqlplus, I won't be able to use it in Oraperl.
>
> Can anyone help?
>
> Thanks
> Ken Denny
> kdenny_at_interpath.com

Ken,

   Unfortunately, although you can reference such values from within another procedure or function, within a SQL statement you may only refer to a function.

This provides the solution: define a function that returns the value of the constant (you could generalise it to accept the constant name and return the value of any specified constant of a given type). Then refer to this function in the SQL statement.

Hope this helps.

Chrysalis. Received on Thu Jun 12 1997 - 00:00:00 CDT