CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
David
David

Reputation: 5537

How to declare and display a variable in Oracle

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.

Upvotes: 36

Views: 226362

Answers (5)

Prasenjit Mahato
Prasenjit Mahato

Reputation: 1304

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

Upvotes: 4

Jahanzaib Mazhar
Jahanzaib Mazhar

Reputation: 41

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

Upvotes: 3

Matt
Matt

Reputation: 285

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

Upvotes: -1

John Doyle
John Doyle

Reputation: 7793

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';

Upvotes: 10

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

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;

Upvotes: 54

Related Questions