Public constants in package

[Walt]

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.

///////////////////////////////////////////////////

[John Hurley]

Walt:

# select mypackage.myconstant from dual

Pretty basic concept here ... when you execute SQL ( like select
column from dual ) you are executing SQL ... not PLSQL.

You can via a context switch have the SQL statement invoke a PLSQL
function ( but of course that takes overhead and not nearly as fast as
real SQL even real SQL with the Oracle extensions of SQL ).

Oracle SQL cannot peer inside packages and see the constants. It does
not work that way.

Oracle PLSQL outside the package could see the constant.

[Mark D Powell]

While I was preparing my example John posted but here is the example,
anyway.

-- create package specification with constant
create or replace package mpowel01.DEMO as
--
-- Package: mpowel01.DEMO
--
-- Purpose: This package will demostrate defining a globally
-- available constant
--
--
-- Specification Modification Log
-- Date Work# Programmer Description of Change
--
-----------------------------------------------------------------------
-- 00/00/00 s.... ace programmer allow update
--
--
v_myconstant varchar2(07) := 'CALL ME';
--
procedure loadme;
--
end DEMO;
/

-- reference package constant
set serveroutput on
declare
v_fld varchar2(10);
begin
v_fld := demo.v_myconstant;
dbms_output.put_line(v_fld);
end;
/
"mark.sql" 32 lines, 606 characters

SQL> @mark

Package created.

CALL ME

PL/SQL procedure successfully completed.

SQL>

HTH -- Mark D Powell --

[Tim X]

Walt <walt_...@SHOESyahoo.com> writes:

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

[Walt]

Thanks. It makes sense now.

//Walt