Re: Public constants in package

From: Tim X <timx_at_nospam.dev.null>
Date: Wed, 20 Apr 2011 17:38:52 +1000
Message-ID: <8739ldcqyb.fsf_at_rapttech.com.au>



Walt <walt_askier_at_SHOESyahoo.com> writes:

> The documentation
> (http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/packages.htm)
> 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:
>
> ////////////////////////////////////////////////
> CREATE OR REPLACE PACKAGE mypackage IS
> myconstant CONSTANT VARCHAR2(8) := 'foo';
> Function get_myconstant return varchar2;
> END mypackage;
>
> Package created.
>
> CREATE OR REPLACE PACKAGE BODY mypackage AS
> 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;
>
>
> GET_MYCONSTANT
>
> --------------------------------------------------------------------------------
> 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.

declare

  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.

Tim  

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

Original text of this message