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

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

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 10Please enter Department number: 20SQL> select ename, sal  2  from emp  3  where deptno = &p_dno  4  /old   3: where deptno = &p_dnonew   3: where deptno = 20ENAME             SAL---------- ----------CLARKE            800ROBERTSON        2975RIGBY            3000KULASH           1100GASPAROTTO       3000SQL>

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 = 40SQL> select ename, sal  2  from emp  3  where deptno = &p_dno  4  /old   3: where deptno = &p_dnonew   3: where deptno = 40no rows selectedSQL>

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 unlimitedSQL> 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: 10old   4:     l_dno number := &dno;new   4:     l_dno number := 10;top earners = 1PL/SQL procedure successfully completed.SQL>

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 2010Copyright (c) 1982, 2005, Oracle.  All rights reserved.SQL> conn od/od@etalonConnected.SQL> define var = "'FL-208'";SQL> select code from product where code = &var;old   1: select code from product where code = &varnew   1: select code from product where code = 'FL-208'CODE---------------FL-208SQL> define var = 'FL-208';SQL> select code from product where code = &var;old   1: select code from product where code = &varnew   1: select code from product where code = FL-208select code from product where code = FL-208                                      *ERROR at line 1:ORA-06553: PLS-221: 'FL' is not a procedure or is undefined

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