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    amp 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  amp 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

User · Answer

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 · Answer

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    amp PROPp START DT

User · Answer

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 · Answer

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

User · Answer

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

User · Answer

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   amp  amp 1    VAH    1  from dual  column 1 clear define 1   Similar code was somehow found in the rdbms sql directory

User · Answer

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 gt  var name varchar2 20  SQL gt  exec  name     SALES   PL SQL procedure successfully completed   SQL gt  select   from dept   2  where dname    name   3         DEPTNO DNAME          LOC ---------- -------------- -------------         30 SALES          CHICAGO  SQL gt    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 gt  accept p dno prompt  Please enter Department number    default 10 Please enter Department number  20 SQL gt  select ename  sal   2  from emp   3  where deptno    amp p dno   4    old   3  where deptno    amp p dno new   3  where deptno   20  ENAME             SAL ---------- ---------- CLARKE            800 ROBERTSON        2975 RIGBY            3000 KULASH           1100 GASPAROTTO       3000  SQL gt    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 gt  def p dno   40 SQL gt  select ename  sal   2  from emp   3  where deptno    amp p dno   4    old   3  where deptno    amp p dno new   3  where deptno   40  no rows selected  SQL gt    Finally there s the anonymous PL SQL block   As you see  we can still assign values to declared variables interactively   SQL gt  set serveroutput on size unlimited SQL gt  declare   2      n pls integer    3      l sal number    3500    4      l dno number     amp dno    5  begin   6      select count      7      into n   8      from emp   9      where sal  gt  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     amp dno  new   4      l dno number    10  top earners   1  PL SQL procedure successfully completed   SQL gt

User · Answer

Here s your answer   DEFINE num    1        -- The semi-colon is needed for default values  SELECT  amp num FROM dual

User · Answer

Try using double quotes if it s a char variable   DEFINE stupidvar     stupidvarcontent      or  DEFINE stupidvar    stupidvarcontent    SELECT stupiddata   FROM stupidtable   WHERE stupidcolumn     amp 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 gt  conn od od etalon Connected  SQL gt  define var     FL-208    SQL gt  select code from product where code    amp var  old   1  select code from product where code    amp var new   1  select code from product where code    FL-208   CODE --------------- FL-208  SQL gt  define var    FL-208   SQL gt  select code from product where code    amp var  old   1  select code from product where code    amp 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

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to variables

Examples related to declaration