34

I would like to declare and display a variable in Oracle.

In T-SQL I would do something like this

DECLARE @A VARCHAR(10) --Declares @A
SELECT @A = '12' --Assigns @A
SELECT @A --Displays @A

How can I do this in Oracle.

John Doyle
  • 7,475
  • 5
  • 33
  • 40
David
  • 5,403
  • 15
  • 42
  • 72

5 Answers5

52

If you're talking about PL/SQL, you should put it in an anonymous block.

DECLARE
    v_text VARCHAR2(10); -- declare
BEGIN
    v_text := 'Hello';  --assign
    dbms_output.Put_line(v_text); --display
END;
Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
  • Sorry to revive an old post. The part of this that doesn't address the original question - for me at least - is that `dbms_output.put_line` effectively does a `PRINT` statement in T-SQL. What David posted and what I was looking for was for the last `SELECT` statement to return a result set - which can be used by the client application. Do you know how to accomplish this? Thanks. – InbetweenWeekends Nov 20 '14 at 15:55
  • 1
    The `dbms_output.Put_line(v_text);` does the equivalent of a `PRINT @v_text;` in T-SQL... if you remember to connect your Dbms output window in SQL developer to the same Oracle conenction as your query! – Marcel Gosselin Apr 14 '15 at 13:11
  • 4
    If you'd rather have your Dbms output statements in your Script Output panel, this is a quick and easy tutorial. http://www.thatjeffsmith.com/archive/2012/05/enabling-dbms_output-by-default-in-sql-developer/ – MPaul Jan 12 '16 at 15:08
10

If using sqlplus you can define a variable thus:

define <varname>=<varvalue>

And you can display the value by:

define <varname>

And then use it in a query as, for example:

select *
from tab1
where col1 = '&varname';
John Doyle
  • 7,475
  • 5
  • 33
  • 40
3

If you are using pl/sql then the following code should work :

set server output on -- to retrieve and display a buffer

DECLARE

    v_text VARCHAR2(10); -- declare
BEGIN

    v_text := 'Hello';  --assign
    dbms_output.Put_line(v_text); --display
END; 

/

-- this must be use to execute pl/sql script

dben
  • 484
  • 1
  • 6
  • 21
Jahanzaib Mazhar
  • 41
  • 1
3

Make sure that, server output is on otherwise output will not be display;

sql> set serveroutput on;

declare
  n number(10):=1;
begin
  while n<=10
 loop
   dbms_output.put_line(n);
   n:=n+1;
 end loop;
end;
/

Outout: 1 2 3 4 5 6 7 8 9 10

Prasenjit Mahato
  • 1,174
  • 15
  • 10
-1

Did you recently switch from MySQL and are now longing for the logical equivalents of its more simple commands in Oracle? Because that is the case for me and I had the very same question. This code will give you a quick and dirty print which I think is what you're looking for:

Variable n number
begin
    :n := 1;
end;
print n

The middle section is a PL/SQL bit that binds the variable. The output from print n is in column form, and will not just give the value of n, I'm afraid. When I ran it in Toad 11 it returned like this

        n
---------
        1

I hope that helps

Matt
  • 275
  • 1
  • 3
  • 10