Skip to Main Content
  • Questions

  • Declaring/Referencing bind/global variables

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Peter.

Asked: March 29, 2012 - 10:52 am UTC

Last updated: March 30, 2012 - 10:19 am UTC

Version: 11.2.0.2.0

Viewed 10K+ times! This question is

You Asked

Hello,

I would like to be able to define a variable in PL/SQL and be able to reference it both PL/SQL and SQL*Plus, another words:
1. Create a bind variable in PL/SQL (instead on VARIABLE command in SQL*Plus) and then referenced the variable in PL/SQL and SQL*Plus
OR
2. Create global variable in a package in PL/SQL and be able to reference the global variable in PL/SQL and SQL*Plus
Thank you in advance,

Peter

and Tom said...

1) you cannot, they are separate and distinct things, not even a tiny bit related.

There is in fact no way to "create a bind variable" in plsql. Not one that exists outside of the realm of plsql in any case.


2) easy, you know how to reference it in plsql already hopefully. In sqlplus, just reference package_name.variable_name. If you want to reference it in SQL however (which is what I think you really mean), you'll want to bind it in.


variable x varchar2(20)
exec :x := my_pkg.my_variable;
select * from emp where ename = :x;


for example. Or, you could:

variable x refcursor
exec open :x for select * from emp where ename = my_pkg.my_variable;
print x


but in any case, since sqlplus is not a language, is not part of the database (it is a client application - you could have written it yourself, it isn't special in any way) - you will use sqlplus commands to set up bind variables and do stuff.


Rating

  (4 ratings)

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

Comments

package variables in sql

Tom, March 29, 2012 - 3:46 pm UTC 

Couldn't we steal the idea of getter functions from java and select the getter function, instead of requiring a bind variable?

SQL> CREATE OR REPLACE package my_pkg as
  2    my_variable varchar2(20) := 'this is a test';
  3
  4    function get_my_variable return varchar2;
  5  end my_pkg;
  6  /

Package created.

SQL>
SQL> create or replace package body my_pkg as
  2    function get_my_variable return varchar2
  3    is
  4    begin
  5      return my_variable;
  6    end get_my_variable;
  7  end my_pkg;
  8  /

Package body created.

SQL>
SQL> select my_pkg.get_my_variable from dual;

GET_MY_VARIABLE
--------------------------------------------------------

this is a test

SQL>

Tom Kyte
March 29, 2012 - 4:34 pm UTC

how did java get the "idea" of getter and setter functions?

I was doing that in Ada way before Java was invented.
I was doing that in C++ after Ada, but again - before Java.

I'm pretty sure Java didn't invent the "getter"/"setter" concept. It sort of borrowed it.

And given that PLSQL predates java... And I was doing this in version 7.3 of Oracle (released the same year java was....). That was the release of the database that allowed you to call PLSQL from SQL (if I recall correctly - it may have been earlier....) and had lots of people doing the getter stuff ;)


and if you are going to do that (call plsql from sql), please read:

http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html



A reader, March 30, 2012 - 7:38 am UTC

Tom -

I remember you mentioning in one of the answer/ article that you are not a big fan of using "Global variable" in a package .


Tom Kyte
March 30, 2012 - 8:19 am UTC

I am not, that is correct.

A reader, March 30, 2012 - 9:04 am UTC

Tom,

Thank you for the response. I am thinking instead of saying SQL*Plus I should have said outside of the realm of PL/SQL.
I need something accessible from both inside and outside of PL/SQL being at the same time able to define this something inside PL/SQL or both inside and outside.
I did some thinking and I think I found it - context. What would you say about setting the context within BEGIN section of a package body and referencing it from both inside and outside of PL/SQL?
Here is an example, of where I would like to use it. I like dynamic SQL (I know you don't), I should be binding variables during the execution, the issue is, during debugging I print out a SQL command with ":a" in it. I know of no simple way to print the SQL command with the value of ":a". I am thinking if I replace ":a" with the context I should be in business. What do you say?
Thank you,

Pedro

Tom Kyte
March 30, 2012 - 10:19 am UTC

the context will be specific to a session - unless you use a global context in which case you'd need to add session identifiers to all calls and .... it would not be practical.


tracing invoked via dbms_monitor will dump bind variables for you, why not just use that (and use binds!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!)

It is not a matter of "liking" versus "not liking" binds - it is a matter of "doing it right" versus "doing it wrong, non-scalable, non-performant, insecure, wrong wrong wrong"

thanks

A reader, July 29, 2012 - 11:53 am UTC

thanks
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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