Home » SQL & PL/SQL » SQL & PL/SQL » How to access specification's or body's same name constants. (XE database.)
How to access specification's or body's same name constants. [message #334069] Tue, 15 July 2008 06:47 Go to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

in Package, i created one constant in specification and one in body. These two
constant varibles are having same names. also one same name constant in local
to procedure.
we can access procedure's constant. but how can we access specification's or
body's constant value. please find code as below.
PACKAGE SPECIFICATION :
-----------------------
CREATE OR REPLACE PACKAGE pkg_test
IS
v1 CONSTANT varchar2(50):='Part of package spec';

PROCEDURE proc2;
END pkg_test;
/


PACKAGE BODY :
--------------
CREATE OR REPLACE PACKAGE BODY pkg_test
As
  v1 CONSTANT varchar2(50):='Part of package body';

PROCEDURE proc1
is
begin
  dbms_output.put_line('procedure one get executed.');
end proc1;

PROCEDURE proc2
IS
  v1 constant varchar2(50) :='==> inside procedure procedure2';
BEGIN
  dbms_output.put_line('procedure TWO get executed.');
  dbms_output.put_line(v1);
--dbms_output.put_line(pkg_test.v1);

END proc2;

END pkg_test;
/


Please suggest.
Re: How to access specification's or body's same name constants. [message #334076 is a reply to message #334069] Tue, 15 July 2008 06:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why would you do that? Why overrule the variable from the spec in the body?
The error is quite clear:
SQL> create or replace package faq_pck
  2  as
  3    g_variable constant varchar2(10) := 'SPEC';
  4    procedure tst;
  5  end;
  6  /

Package created.
SQL> create or replace package body faq_pck
  2  as
  3    g_variable constant varchar2(10) := 'BODY';
  4    procedure tst
  5    is
  6      g_variable constant varchar2(10) := 'PROCEDURE';
  7    begin
  8      dbms_output.put_line('Procedure: '||g_variable);
  9      dbms_output.put_line('Package: '||faq_pck.g_variable);
 10    end;
 11  end;
 12  /

Warning: Package Body created with compilation errors.

SQL> show err
Errors for PACKAGE BODY FAQ_PCK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/5      PL/SQL: Statement ignored
9/47     PLS-00371: at most one declaration for 'FAQ_PCK.G_VARIABLE' is
         permitted
Re: How to access specification's or body's same name constants. [message #334081 is a reply to message #334069] Tue, 15 July 2008 07:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm not convinced that you can access the package specification version of the variable.
Every time I try to compile the package with a reference to the package header variable, I get the error 'PLS-00371: at most one declaration for 'PKG_TEST.V1' is permitted'

If you comment out one of the spec or body variables, then it compiles correctly, and behaves as expected.

I think the answer is that you're not allowed to have two varables of the same name at the same level of Scope.

[Added more details]

[Updated on: Tue, 15 July 2008 07:06]

Report message to a moderator

Re: How to access specification's or body's same name constants. [message #334086 is a reply to message #334069] Tue, 15 July 2008 07:16 Go to previous messageGo to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

Quote:
I think the answer is that you're not allowed to have two varables of
the same name at the same level of Scope.


Means we can compile (without refering that variable in begin-end block) but we
can't use that variable/constant.

thats why if we are displaying package varible it is only showing specification's value.

declare
begin
   dbms_output.put_line(pkg_test.v1);
end;


Thanks.
Re: How to access specification's or body's same name constants. [message #334089 is a reply to message #334086] Tue, 15 July 2008 07:28 Go to previous messageGo to next message
jyothsna1612
Messages: 68
Registered: June 2008
Member
Hi,
That's fine.
SQL> exec pkg_test.proc2

procedure TWO get executed.
==> inside procedure procedure2


SQL> declare
  2  begin
  3     dbms_output.put_line(pkg_test.v1);
  4  end;
  5  /
Part of package spec

PL/SQL procedure successfully completed.


But what's the reason for getting the o/p in such a way..
please excuse me if this is a newbie question.

Thanks
Re: How to access specification's or body's same name constants. [message #334108 is a reply to message #334089] Tue, 15 July 2008 08:07 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In first case, you have the the value of local variable.
In second case, you have the value of spec, the only one accessible from outside the package.

Regards
Michel
Previous Topic: DB Link
Next Topic: Error Assoc Arrays Data Type as OUT Formal Parameter
Goto Forum:
  


Current Time: Sun Dec 04 06:55:24 CST 2016

Total time taken to generate the page: 0.11271 seconds