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

user_44293 0
Viewed 896617

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...).

declaration oracle sql variables
11 Answers

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.

user_22093040

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.

user_45738390

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.

user_115822700

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.

user_6035410

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.

user_122734010
Related