0

I come from a sql server background. Frequently when trying to build some reports or answer questions from the database I would write queries that use a variable several times within the query. Rather than update the value 3-4 times each time I want to change the query I would use a variable so I can update the value only once. For brevity I have written a simple query below that illustrates what I would do in SQL Server when trying to pull a report. 

declare @test numeric(3)
select @test = 683
select myColumn from myTable where myColumn = @test

What is the equivalent of this in oracle? I tried to write something similar but got some error messages about requiring an into statement. From what I can gather you can select something into a variable in oracle but you cant use a variable in a read only fashion like I have done above. Is it even possible to write a query similar to the one above in oracle?

Hunter Nelson
  • 1,707
  • 3
  • 20
  • 37
  • Look at http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12017.htm. Please note that this only really works interactively. Programmatically there are other ways. – T Gray Apr 07 '16 at 17:10

2 Answers2

3

Declare:

SQL> variable x number

Assign:

SQL> begin :x := 3; end;
  2  /
PL/SQL procedure successfully completed.

Use: 

SQL> select :x from dual;
        :X
----------
         3
1 row selected.

Note: In Oracle, : is used instead of @, as in :x instead of @x; and the assignment operator is :=, not =.

  • Ive seen a number of oracle questions have their answer include this SQL> before the script. I am using sql navigator and I don't have anything like that prepending lines of query langauge. Do I actually need to type out SQL> ? – Hunter Nelson Apr 07 '16 at 18:34
  • No, you don't. That is the console prompt if you use SQL*Plus, Oracle's simplest client-side interface to the database. You don't need the prompt if you write your queries in SQL Developer, Toad, etc. –  Apr 07 '16 at 18:43
  • I'm not getting any errors when I run that in sql navigator, but the result of the query is a blank column, not the number. I suspect this might be issue with sql navigator or the installation, not the query though :-/ – Hunter Nelson Apr 07 '16 at 18:51
  • Uhm... sorry, I think "variable x number" is a SQL*Plus command. I am not familiar with SQL Navigator; someone who uses it may be able to help. –  Apr 07 '16 at 19:00
  • See if the reply in this old threat at Toadworld helps: https://www.toadworld.com/products/sql-navigator/f/51/t/4583 –  Apr 08 '16 at 19:12
1

If you are using SQL Developer, write the query and use for variables ':'

select myColumn from myTable where myColumn = :test

When you start (Ctrl-Enter) the query, the SQL Developer will ask for the value of :test.

If you want to change the select statement dynamically, see this answer:

define value1 = 'sysdate+3 as mydate';
SELECT &&value1 from dual;
Community
  • 1
  • 1
Mottor
  • 1,938
  • 3
  • 12
  • 29