How to declare variable and use it in the same Oracle SQL script?

Question

I want to write reusable code and need to declare some variables at the beginning and reuse them in the script, such as:

DEFINE stupidvar = 'stupidvarcontent';

SELECT stupiddata
FROM stupidtable
WHERE stupidcolumn = &stupidvar;

How can I declare a variable and reuse it in statements that follow such as in using it SQLDeveloper.

---

Attempts

• Use a DECLARE section and insert the following SELECT statement in BEGIN and END;. Acces the variable using &stupidvar.
• Use the keyword DEFINE and access the variable.
• Using the keyword VARIABLE and access the the variable.

But I am getting all kinds of errors during my tries (Unbound variable, Syntax error, Expected SELECT INTO...).

Largess

Note that the approach in the accepted answer by @APC can be used without PL/SQL, e.g. in a SQL Developer worksheet as per your question. Just declare the variable on one line (no semicolon), then the exec line to set its value (end with semicolon), then your select statement. Finally, run it as a script (F5), not as a statement (F9). – Bathy

Answer 1

There are a several ways of declaring variables in SQL*Plus scripts.

The first is to use VAR, to declare a bind variable. The mechanism for assigning values to a VAR is with an EXEC call:

SQL> var name varchar2(20)
SQL> exec :name := 'SALES'

PL/SQL procedure successfully completed.

SQL> select * from dept
  2  where dname = :name
  3  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

SQL>

A VAR is particularly useful when we want to call a stored procedure which has OUT parameters or a function.

Alternatively we can use substitution variables. These are good for interactive mode:

SQL> accept p_dno prompt "Please enter Department number: " default 10
Please enter Department number: 20
SQL> select ename, sal
  2  from emp
  3  where deptno = &p_dno
  4  /
old   3: where deptno = &p_dno
new   3: where deptno = 20

ENAME             SAL
---------- ----------
CLARKE            800
ROBERTSON        2975
RIGBY            3000
KULASH           1100
GASPAROTTO       3000

SQL>

When we're writing a script which calls other scripts it can be useful to DEFine the variables upfront. This snippet runs without prompting me to enter a value:

SQL> def p_dno = 40
SQL> select ename, sal
  2  from emp
  3  where deptno = &p_dno
  4  /
old   3: where deptno = &p_dno
new   3: where deptno = 40

no rows selected

SQL>

Finally there's the anonymous PL/SQL block. As you see, we can still assign values to declared variables interactively:

SQL> set serveroutput on size unlimited
SQL> declare
  2      n pls_integer;
  3      l_sal number := 3500;
  4      l_dno number := &dno;
  5  begin
  6      select count(*)
  7      into n
  8      from emp
  9      where sal > l_sal
 10      and deptno = l_dno;
 11      dbms_output.put_line('top earners = '||to_char(n));
 12  end;
 13  /
Enter value for dno: 10
old   4:     l_dno number := &dno;
new   4:     l_dno number := 10;
top earners = 1

PL/SQL procedure successfully completed.

SQL>

Cchaddie

All good, except for your use of the term "bind variable". The VAR declaration creates a bind variable, while ACCEPT or DEFINE creates a substitution variable. – Hulda

Is it possible to concatenate variables+strings? – Kidd

@Kidd - yes, in SQL Plus use period by default. Use SET CONCAT to define the character that separates the name of a substitution variable from alphanumeric characters that immediately follow the variable name. In PL/SQL or SQL use double pipe || to concatenate. – Eikon

If SQL is a standard language then why is it so hard to find a canonical reference that just works everywhere? WTF??? – Suddenly

@Suddenly - SQL is a standard but it doesn't always specify the exact syntax so different RDBMS products can implement things differently; date arithmetic is good example. Also the older database products like Oracle often introduced features before the Standard covered them: for instance the hierarchical CONNECT BY syntax. But in this case we are discussing SQL*Plus, which is a client tool and so not covered by the ANSI standard anyway. – Cchaddie

Just for clarification. With this method, you cannot create all kind of data types. E.g. it is not possible to create variables of type DATE or TIMESTAMP. – Matsuyama

Answer 2

Try using double quotes if it's a char variable:

DEFINE stupidvar = "'stupidvarcontent'";

or

DEFINE stupidvar = 'stupidvarcontent';

SELECT stupiddata  
FROM stupidtable  
WHERE stupidcolumn = '&stupidvar'

upd:

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 25 17:13:26 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn od/od@etalon
Connected.
SQL> define var = "'FL-208'";
SQL> select code from product where code = &var;
old   1: select code from product where code = &var
new   1: select code from product where code = 'FL-208'

CODE
---------------
FL-208

SQL> define var = 'FL-208';
SQL> select code from product where code = &var;
old   1: select code from product where code = &var
new   1: select code from product where code = FL-208
select code from product where code = FL-208
                                      *
ERROR at line 1:
ORA-06553: PLS-221: 'FL' is not a procedure or is undefined

Impressive

Thank you for your answer, but if I include the var in double quotes, I get a ORA-01008: not all variables bound. – Largess

Sure! DEFINE num = 1; SELECT &num FROM dual; leads to: ORA-01008: not all variables bound – Largess

@Largess - I bet you are trying this not in SQL*Plus. – Eikon

This answer is a life saver! I've used DEFINE quite a bit in my report writing, and never had a problem. On an important project, I was getting errors, and I could tell it was because the variable was passed in as Number instead of String value. Thanks!! – Phenosafranine

Answer 3

In PL/SQL v.10

keyword declare is used to declare variable

DECLARE stupidvar varchar(20);

to assign a value you can set it when you declare

DECLARE stupidvar varchar(20) := '12345678';

or to select something into that variable you use INTO statement, however you need to wrap statement in BEGIN and END, also you need to make sure that only single value is returned, and don't forget semicolons.

so the full statement would come out following:

DECLARE stupidvar varchar(20);
BEGIN
    SELECT stupid into stupidvar FROM stupiddata CC 
    WHERE stupidid = 2;
END;

Your variable is only usable within BEGIN and END so if you want to use more than one you will have to do multiple BEGIN END wrappings

DECLARE stupidvar varchar(20);
BEGIN
    SELECT stupid into stupidvar FROM stupiddata CC 
    WHERE stupidid = 2;

    DECLARE evenmorestupidvar varchar(20);
    BEGIN
        SELECT evenmorestupid into evenmorestupidvar FROM evenmorestupiddata CCC 
        WHERE evenmorestupidid = 42;

        INSERT INTO newstupiddata (newstupidcolumn, newevenmorestupidstupidcolumn)
        SELECT stupidvar, evenmorestupidvar 
        FROM dual

    END;
END;

Hope this saves you some time

Precision

Answer 4

If you want to declare date and then use it in SQL Developer.

DEFINE PROPp_START_DT = TO_DATE('01-SEP-1999')

SELECT * 
FROM proposal 
WHERE prop_start_dt = &PROPp_START_DT

Unlock

In Oracle SQL Developer 24.1.349, this adaptation of your work springs a prompt to enter a value for PROPp_START_DT. Any thoughts? DEFINE id_num = '00022' SELECT * FROM proposal WHERE PLAN_N = &id_num; – Dyslogistic

Answer 5

Just want to add Matas' answer. Maybe it's obvious, but I've searched for a long time to figure out that the variable is accessible only inside the BEGIN-END construction, so if you need to use it in some code later, you need to put this code inside the BEGIN-END block.

Note that these blocks can be nested:

DECLARE x NUMBER;
BEGIN
    SELECT PK INTO x FROM table1 WHERE col1 = 'test';

    DECLARE y NUMBER;
    BEGIN
        SELECT PK INTO y FROM table2 WHERE col2 = x;

        INSERT INTO table2 (col1, col2)
        SELECT y,'text'
        FROM dual
        WHERE exists(SELECT * FROM table2);

        COMMIT;
    END;
END;

Flaming

Answer 6

The question is about to use a variable in a script means to me it will be used in SQL*Plus.

The problem is you missed the quotes and Oracle can not parse the value to number.

SQL> DEFINE num = 2018
SQL> SELECT &num AS your_num FROM dual;
old   1: SELECT &num AS your_num FROM dual
new   1: SELECT 2018 AS your_num FROM dual

  YOUR_NUM
----------
      2018

Elapsed: 00:00:00.01

This sample is works fine because of automatic type conversion (or whatever it is called).

If you check by typing DEFINE in SQL*Plus, it will shows that num variable is CHAR.

SQL>define
DEFINE NUM             = "2018" (CHAR)

It is not a problem in this case, because Oracle can deal with parsing string to number if it would be a valid number.

When the string can not parse to number, than Oracle can not deal with it.

SQL> DEFINE num = 'Doh'
SQL> SELECT &num AS your_num FROM dual;
old   1: SELECT &num AS your_num FROM dual
new   1: SELECT Doh AS your_num FROM dual
SELECT Doh AS your_num FROM dual
       *
ERROR at line 1:
ORA-00904: "DOH": invalid identifier

With a quote, so do not force Oracle to parse to number, will be fine:

17:31:00 SQL> SELECT '&num' AS your_num FROM dual;
old   1: SELECT '&num' AS your_num FROM dual
new   1: SELECT 'Doh' AS your_num FROM dual

YOU
---
Doh

So, to answer the original question, it should be do like this sample:

SQL> DEFINE stupidvar = 'X'
SQL>
SQL> SELECT 'print stupidvar:' || '&stupidvar'
  2  FROM dual
  3  WHERE dummy = '&stupidvar';
old   1: SELECT 'print stupidvar:' || '&stupidvar'
new   1: SELECT 'print stupidvar:' || 'X'
old   3: WHERE dummy = '&stupidvar'
new   3: WHERE dummy = 'X'

'PRINTSTUPIDVAR:'
-----------------
print stupidvar:X

Elapsed: 00:00:00.00

There is an other way to store variable in SQL*Plus by using Query Column Value.

The COL[UMN] has new_value option to store value from query by field name.

SQL> COLUMN stupid_column_name new_value stupid_var noprint
SQL> SELECT dummy || '.log' AS stupid_column_name
  2  FROM dual;

Elapsed: 00:00:00.00
SQL> SPOOL &stupid_var.
SQL> SELECT '&stupid_var' FROM DUAL;
old   1: SELECT '&stupid_var' FROM DUAL
new   1: SELECT 'X.log' FROM DUAL

X.LOG
-----
X.log

Elapsed: 00:00:00.00
SQL>SPOOL OFF;

As you can see, X.log value was set into the stupid_var variable, so we can find a X.log file in the current directory has some log in it.

Eikon

Answer 7

In Toad I use this works:

declare 
    num number;
begin 
    ---- use 'select into' works 
    --select 123 into num from dual;

    ---- also can use :=
    num := 123;
    dbms_output.Put_line(num);
end;

Then the value will be print to DBMS Output Window.

Reference to here and here2.

Integration

Answer 8

Here's your answer:

DEFINE num := 1;       -- The semi-colon is needed for default values.
SELECT &num FROM dual;

Spleen

Same with me. I use ODT and run : DEFINE num := 1; SELECT num FROM dual; And what I get is : ORA-00904: "NUM": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: Error at Line: 2 Column: 8 – Clyve

Answer 9

You can use a with clause and move filter criteria from a where to a join.

It helps here: Oracle SQL alternative to using DEFINE.

with
 mytab as (select 'stupidvarcontent' as myvar from dual)
SELECT
 stupiddata
FROM
  stupidtable a 
 inner join
  mytab b
 on
  a.stupidcolumn = b.myvar
WHERE ...;

It works in Oracle 12R2.
It works for one SQL command only.
It is standard ANSI notation.
I'm using it in SQL Developer.

Autry

Answer 10

One possible approach, if you just need to specify a parameter once and replicate it in several places, is to do something like this:

SELECT
  str_size  /* my variable usage */
  , LPAD(TRUNC(DBMS_RANDOM.VALUE * POWER(10, str_size)), str_size, '0') rand
FROM
  dual  /* or any other table, or mixed of joined tables */
  CROSS JOIN (SELECT 8 str_size FROM dual);  /* my variable declaration */

This code generates a string of 8 random digits.

Notice that I create a kind of alias named str_size that holds the constant 8. It is cross-joined to be used more than once in the query.

Downcomer

Answer 11

Sometimes you need to use a macro variable without asking the user to enter a value. Most often this has to be done with optional script parameters. The following code is fully functional

column 1 noprint new_value 1
select '' "1" from dual where 2!=2;
select nvl('&&1', 'VAH') "1" from dual;
column 1 clear
define 1

Similar code was somehow found in the rdbms/sql directory.

Tallman

I get: ORA-00900: invalid SQL statement – Gaitskell