Home » SQL & PL/SQL » SQL & PL/SQL » Distinguish between global variables and private variable
Distinguish between global variables and private variable [message #207757] Wed, 06 December 2006 21:13 Go to next message
quytc
Messages: 81
Registered: November 2005
Location: Viet Nam
Member

Hi All,

please help me to find distinguish between global variable declared in package specification and private variable in package body.


can anybody show to me example using global variable declared in package specification

thanks
Re: Distinguish between global variables and private variable [message #207765 is a reply to message #207757] Wed, 06 December 2006 23:42 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Variables declared in package specification are visible to any other PL/SQL procedure in this schema (unless there are privileges granted to someone else, of course - in that case, he'll be able to use it too.

On the other hand, variables declared in package body are visible only to PL/SQL procedures that are part of this package.

Here's an example: First, create a package and package body. I'm creating two variables (global and local one) and a procedure:
SQL> CREATE OR REPLACE PACKAGE Pkg_Test IS
  2    g_var NUMBER := 123;
  3
  4    PROCEDURE Prc_Test;
  5  END;
  6  /

Package created.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY Pkg_Test IS
  2    b_var NUMBER := 456;
  3
  4    PROCEDURE Prc_Test IS
  5    BEGIN
  6      dbms_output.put_line(TO_CHAR(Pkg_Test.b_var));
  7    END;
  8  END;
  9  /

Package body created.

Now, let's test it: this will work:
SQL> begin
  2    dbms_output.put_line(pkg_test.g_var);
  3  end;
  4  /
123

PL/SQL procedure successfully completed.

This will not work (as local variable isn't visible to me):
SQL> begin
  2    dbms_output.put_line(pkg_test.b_var);
  3  end;
  4  /
  dbms_output.put_line(pkg_test.b_var);
                                *
ERROR at line 2:
ORA-06550: line 2, column 33:
PLS-00302: component 'B_VAR' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

But, local variable is visible to a procedure declared in our package:
SQL> execute pkg_test.prc_test;
456

PL/SQL procedure successfully completed.
Re: Distinguish between global variables and private variable [message #207788 is a reply to message #207757] Thu, 07 December 2006 00:56 Go to previous messageGo to next message
quytc
Messages: 81
Registered: November 2005
Location: Viet Nam
Member

Hi Littlefoot,
There is other distinction between global variable and private variable is "private variable is used to maintain throughout a session or across transactions and when declared as a global variable, the value of global variable gets inittialized in a session, the first time a contruct from the package is invoked"

I don't understand red statement. Can you show to me example for it
Re: Distinguish between global variables and private variable [message #207801 is a reply to message #207788] Thu, 07 December 2006 01:31 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!,
im not sure if this is a right example of a variable being initialize in a session, hopefully an expert will correct me after this he9x.


SQL> create or replace package t_p as
  2    v varchar2(1);
  3    procedure changeit;
  4  end;
  5  /

Package created


SQL> create or replace package body t_p as
  2    procedure changeit as
  3    begin
  4       dbms_output.put_line(t_p.v);
  5       t_p.v := 'N';
  6    end;
  7  begin
  8    t_p.v := 'Y';
  9  end;
 10  /

Package body created


SQL> exec t_p.changeit

Y

PL/SQL procedure successfully completed

SQL> exec t_p.changeit

N

PL/SQL procedure successfully completed

SQL> 



im not sure if i understand it also correctly, but here's what im trying to tell is that if you initialize a variable in the initialization section of a package, it will be only called once, so even if you call it again, it wont execute the initialization section again which assigns the value 'Y' to variable v.

HTH,
rhani
Re: Distinguish between global variables and private variable [message #207810 is a reply to message #207757] Thu, 07 December 2006 02:12 Go to previous messageGo to next message
quytc
Messages: 81
Registered: November 2005
Location: Viet Nam
Member

Hi rhali
In your example I move declare v varchar2(1) from package spec to package body. I get result the same your result. therefore i think your example don't eliminate Distinguish between global variables and private variable
Re: Distinguish between global variables and private variable [message #207832 is a reply to message #207810] Thu, 07 December 2006 03:00 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!,
ok, could you give the link where you have read the descript you just post regarding "global" and "private" variable, it's too general, for me there are also called "PUBLIC" and "PRIVATE" package level variables, which is


create package t as
   v number; --public variable which is as explained before is accessible to other program units.
end;

create package body t as
   x number; --private variable this is not accessible to other except for those program units inside this package.
end;   



hth,
rhani
Re: Distinguish between global variables and private variable [message #207839 is a reply to message #207757] Thu, 07 December 2006 03:14 Go to previous messageGo to next message
quytc
Messages: 81
Registered: November 2005
Location: Viet Nam
Member

Sory because the book'size very lager. I can not attach them. I can review a content of the book follow.

Place item in the declaration part of the package body when you must maintain them throughout a session or across transaction. For example declare a variable called NUMBER_EMP as a private variable, if each call to a procedure that uses variable needs to maintaied. When declared as a global variable in the package specification, the value of that variable gets initialized in a session the first time a contruct from package is invoked

Can you show for me example to explain this content!!!!!

[Updated on: Thu, 07 December 2006 04:02]

Report message to a moderator

Re: Distinguish between global variables and private variable [message #207867 is a reply to message #207757] Thu, 07 December 2006 04:02 Go to previous messageGo to next message
quytc
Messages: 81
Registered: November 2005
Location: Viet Nam
Member

up
Re: Distinguish between global variables and private variable [message #207887 is a reply to message #207867] Thu, 07 December 2006 04:29 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
patience please!

There seems to be some confusion here. Global variables are variables that have the package as scope, not a packaged procedure or function.
This has nothing to do with whether they are declared in the spec or the body; that fact distinguishes between private and public (global) variables.
Previous Topic: Avoiding multiple rows
Next Topic: doubts in delete statement
Goto Forum:
  


Current Time: Sun Dec 11 02:39:29 CST 2016

Total time taken to generate the page: 0.10763 seconds