Re: Public constants in package

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Tue, 19 Apr 2011 09:55:18 -0700 (PDT)
Message-ID: <c7de5c9e-edca-457a-bcf6-a222766c7c6e_at_34g2000pru.googlegroups.com>



On Apr 19, 12:44 pm, John Hurley <hurleyjo..._at_yahoo.com> wrote:
> 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.

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> _at_mark

Package created.

CALL ME PL/SQL procedure successfully completed.

SQL> HTH -- Mark D Powell -- Received on Tue Apr 19 2011 - 11:55:18 CDT

Original text of this message