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

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

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

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

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

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 gt  DEFINE num   2018 SQL gt  SELECT  amp num AS your num FROM dual  old   1  SELECT  amp 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 gt 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 gt  DEFINE num    Doh  SQL gt  SELECT  amp num AS your num FROM dual  old   1  SELECT  amp 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 gt  SELECT   amp num  AS your num FROM dual  old   1  SELECT   amp 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 gt  DEFINE stupidvar    X  SQL gt  SQL gt  SELECT  print stupidvar        amp stupidvar    2  FROM dual   3  WHERE dummy     amp stupidvar   old   1  SELECT  print stupidvar        amp stupidvar  new   1  SELECT  print stupidvar       X  old   3  WHERE dummy     amp 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 gt  COLUMN stupid column name new value stupid var noprint SQL gt  SELECT dummy      log  AS stupid column name   2  FROM dual   Elapsed  00 00 00 00 SQL gt  SPOOL  amp stupid var  SQL gt  SELECT   amp stupid var  FROM DUAL  old   1  SELECT   amp stupid var  FROM DUAL new   1  SELECT  X log  FROM DUAL  X LOG ----- X log  Elapsed  00 00 00 00 SQL gt 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

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

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

[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