Thanks for the question, Mohit.

Asked: December 23, 2011 - 2:00 am UTC

Last updated: December 29, 2011 - 11:34 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I'm stuck at a Place where I need to use the value of a Global variable declared and populated inside one Package to be used in other package.How do I do that. I don't to dbms_output it. I need to print that VALUE of this Variable in the Other package I'm creating. Is there a way to do this in PL/SQL?

P.S. The Value of this Variable is a a big Merge Statement which I need to print in a different package. (It's a Code Generator creating a new Package)

Thanks,
Mohit

and Tom said...

if the global variable is declared in the package specification - eg:

create package my_pkg
as
global_variable number;
....

then you would just access it as "my_pkg.global_variable" wherever you wanted.

If the variable was defined in the package body - not the specification - then it is private to the package body and cannot be accessed directly outside of the package body. You would have to provide a function in that package that returned that value.

global variables are in general a really bad idea. I suggest you provide a function in your generated code.

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, December 23, 2011 - 9:57 am UTC

<quote>

... global variables are in general a really bad idea

</quote>

Is this bad idea for a global constants as well?

I normally have the all the constants in a specification and reference it application wide.

December 23, 2011 - 10:03 am UTC

global constants are fine, they are not "variable" - no one can write to them.

the problem with global variables is that anyone that can read them can write them - leading to really hard to understand/maintain code.

How exactly to Print the value of the variable

Mohit Bindroo, December 24, 2011 - 1:46 am UTC

Thank You. Global variable explanation is of great help. Even I had thought about the same. However, I still do not understand that when I generate the code, How do I output the value of this variable.
I do not want to print/output 'v_merge_query' from baseline package but the value which it contains into the new Generated package.

The value is 'Merge into table A using .......'

December 24, 2011 - 8:05 am UTC

just do whatever you want with it in the other package as if it were a local variable.

ops$tkyte%ORA11GR2> create or replace package pkg1
  2  as
  3          g_global varchar2(200) := 'merge into table a using...';
  4  end;
  5  /

Package created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace package pkg2
  2  as
  3          procedure p;
  4  end;
  5  /

Package created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace package body pkg2
  2  as
  3          procedure p
  4          is
  5          begin
  6                  dbms_output.put_line( pkg1.g_global );
  7          end;
  8  end;
  9  /

Package body created.

ops$tkyte%ORA11GR2> exec pkg2.p
merge into table a using...

PL/SQL procedure successfully completed.

I'm not sure why this is a question -- it is rather straightforward. Is there something more to your question that I'm missing?

Mohit Bindroo, December 24, 2011 - 10:37 am UTC

I want the value of the variable outputted inside a new package...I need something like this

> create or replace package body pkg2
2 as
3 procedure p
4 is
5 begin
6 MERGE INTO TABLE A....(Whole Value of Variable )
end;
8 end;
9 /

Package body created.

December 24, 2011 - 11:20 am UTC

then you actually need to make is a global variable in the specification of pkg2 - not the body.

right now, I have no clue where you defined it - but if it was like this:

create or replace package pkg2
as
   g_merge_variable number;
   procedure p;
end;
/

> create or replace package body pkg2
  2  as
  3          procedure p
  4          is
  5          begin
  6                 MERGE INTO TABLE A.... g_merge_variable;
         end;
  8  end;
  9  /

then you can access pkg2.g_merge_variable anywhere you want.

IF it isn't like that, then you cannot - plain and simple, you'll have to figure out something else - like changing the code generator to generate code that actually meets your requirement.

Use a function

Belly, December 29, 2011 - 8:09 am UTC

In my humble opinion, do NOT use a global variable, but keep it local.
Then inside the package containing the variable, create a function that does nothing but return the value of the variable.
Then expose the function to the outside world in the package specification.
Use the function inside the package that needs the value of the variable.

create or replace package pck_1
is
  function get_variable
  return varchar2;
  
  -- All other public things
end;
/


create or replace package pck_1
is
  g_variable   VARCHAR2(32767);
  --
  function get_variable
  return varchar2
  is
  begin
    return('g_variabele');
  end;
  --
  -- All other functionality, among which the populating of the variable
end;
/

create or replace package pck_2
is
  procedure use_variable;
end;
/

create or replace package pck_2
is
  procedure use_variable
  is
  begin
    dbms_output.put_line (pck_1.get_variable);
    -- or do whatever you want with the value
  end;
  --
end;
/

December 29, 2011 - 11:34 am UTC

I agree 100% - globals like that are a bad idea in general.

that was stated in the original answer :)

global variables are in general a really bad idea. I suggest you provide a function in your generated code.

A reader, December 29, 2011 - 12:29 pm UTC

Thank You...It helped a lot..

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

How exactly to Print the value of the variable

Use a function

More to Explore

PL/SQL demos

PL/SQL docs

How to print a variable value from one PL/SQL package into a different one

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

How exactly to Print the value of the variable

Use a function

More to Explore

PL/SQL demos

PL/SQL docs