Re: Public constants in package

From: Tim X <>
Date: Wed, 20 Apr 2011 17:38:52 +1000
Message-ID: <>

Walt <> writes:

> The documentation
> (
> says that "items" declared in the package declaration are publicly
> accessible. But I find that when I declare constants in the package
> declaration they are not visible from outside the package. I can write
> accessor functions to get the constants' values, but I can't access the
> constant directly.
> Is there a simple way to expose public constants, or do I have to write
> get() methods for each one? (Oracle 10g, W2k3) Is there some reason
> why the constants are not exposed?
> Here's what the docs say:
> "Items declared in the spec ... are visible outside the package. Any
> PL/SQL code can reference the exception invalid_salary. Such items are
> called public.
> To maintain items throughout a session or across transactions, place
> them in the declarative part of the package body. For example, the value
> of number_hired is kept between calls to hire_employee within the same
> session. The value is lost when the session ends.
> To make the items public, place them in the package specification. For
> example, emp_rec declared in the spec of the package is available for
> general use."
> Here's example code:
> ////////////////////////////////////////////////
> myconstant CONSTANT VARCHAR2(8) := 'foo';
> Function get_myconstant return varchar2;
> END mypackage;
> Package created.
> Function get_myconstant return varchar2
> AS
> begin
> return myconstant;
> end;
> END mypackage;
> Package body created.
> select mypackage.myconstant from dual;
> select mypackage.myconstant from dual
> *
> Error at line 1
> ORA-06553: PLS-221: 'MYCONSTANT' is not a procedure or is undefined
> select mypackage.get_myconstant from dual;
> --------------------------------------------------------------------------------
> foo
> 1 row selected.
> ///////////////////////////////////////////////////

Your confusing the SQL world and the PL/SQL world. Have a look at the sections from the sql reference and the pl/sql reference dealing with calling functions from sql and creating functions in pl/sql that can be embedded in sql queries.

In pl/sql, you can access the constants easily i.e.


  rslt VARCHAR2(8);
BEGIN    rslt := mypackage.myconstant;
   dbms_output.put_line('rslt = '||rslt); end;

will print 'rslt = foo'

but if you want to access those constants from sql, you will have to put a function wrapper around them.


tcross (at) rapttech dot com dot au
Received on Wed Apr 20 2011 - 02:38:52 CDT

Original text of this message