How to Declare Variable and Use It in the Same Oracle SQL Script

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

How to Declare, Populate and Use a Variable using Oracle PL/SQL

The basic issue is a variable scope problem. You're declaring MaxPeriod within the context of a PL/SQL anonymous block, so it will disappear (fall out of scope) when the block ends on line 4.

You could put your entire query inside the PL/SQL block, but there's not an easy way to return an entire result set from a PL/SQL block, so I don't think you want that.

I don't know how your Oracle driver handles native queries, but this might work:

var MaxPeriod number; -- bind variable declared as global scope for this script

Begin -- one of several ways to assign values to bind variables
    :MaxPeriod := 201904;
End;
/

Select
Case
    When 201904 = :MaxPeriod Then 'Match'
    Else 'No Match'
End As dteChk
From Dual;

If the var syntax doesn't work for you to declare a SQL bind variable, then you may have to look into some other way of passing a bind variable for the query string. You could probably pass a null value (for a number datatype, anyway) and then overwrite it in the SQL script.

Alternately, in your original example code, I think I'd use a CTE or an inline view instead of a variable anyway.

With f_data As (Select 201904 As cal_period from dual)
Select
Case
      When Mod(MaxDate,100) < 12 Then MaxDate+1
     Else (MaxDate+100) - (Mod(MaxDate,100)-1)
End As dt
from (Select Max(cal_period) as MaxDate From f_data) mp

How to use a local variable in oracle SQL?

In order to use parameters in Oracle, you have to use PL/SQL. However, if you just want to parameterize a single query, you can use a CTE:

WITH params AS (
      SELECT DATE '2000-01-01' as date_start,
             DATE '2001-01-01' as date_end
      FROM dual
     )
SELECT u.*
FROM params CROSS JOIN
     user u
WHERE u.birth BETWEEN params.date_start AND params.date_end;

Similar logic works in just about any database (although dual may not be needed and the date constants might have different formats).

Oracle - How to use & without being asked about the value?

Execute

SQL> set define off

before running your code.

---

SQL> select '&test' from dual;

'&TES
-----
&test

SQL>

---

If you want to "declare" it, then use var:

SQL> var test varchar2(200);
SQL> exec :test := 'some value';

PL/SQL procedure successfully completed.

SQL> print test

TEST
----------------------------------------------------------------------------------------------------
some value

SQL>

---

In dynamic SQL: I won't lock anyone, but - I'll change my password.

SQL> connect scott/tiger
Connected.
SQL> var test varchar2(200);
SQL> exec :test := 'lion';

PL/SQL procedure successfully completed.

SQL> print test

TEST
----------------------------------------------------------------------------------------------------
lion

SQL> begin
  2    execute immediate 'alter user scott identified by ' || :test;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> connect scott/tiger
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect scott/lion
Connected.
SQL>

Defining variables Oracle script

Whenever you run a stored query or script, SQL*Plus substitutes the value of variable for each substitution variable referencing variable (in the form &variable or &&variable). SQL*Plus will not prompt you for the value of variable in this session until you UNDEFINE variable.

If the value of a defined variable extends over multiple lines (using the SQL*Plus command continuation character), SQL*Plus replaces each continuation character and carriage return with a space. For example, SQL*Plus interprets

DEFINE TEXT = 'ONE-
TWO-
THREE'

as

DEFINE TEXT = 'ONE TWO THREE'

You should avoid defining variables with names that may be identical to values that you will pass to them, as unexpected results can occur. If a value supplied for a defined variable matches a variable name, then the contents of the matching variable are used instead of the supplied value.

Some variables are predefined when SQL*Plus starts. Enter DEFINE to see their definitions.

Examples

To assign the value MANAGER to the variable POS, type:

DEFINE POS = MANAGER

If you execute a command containing a reference to &POS, SQL*Plus substitutes the value MANAGER for &POS and will not prompt you for a POS value.

To assign the CHAR value 20 to the variable DEPARTMENT_ID, type:

DEFINE DEPARTMENT_ID = 20

Even though you enter the number 20, SQL*Plus assigns a CHAR value to DEPARTMENT_ID consisting of two characters, 2 and 0.

To list the definition of DEPARTMENT_ID, enter

DEFINE DEPARTMENT_ID
DEFINE DEPARTMENT_ID = "20" (CHAR)

This result shows that the value of DEPARTMENT_ID is 20.

Oracle SQL: Declaring a variable and using the variable in other queries

With SQLPlus you can try to declare a SQLPlus variable (this should also work with any GUI tool that is compatible with this kind of variable declaration such as SQL Developer, TOAD, ...):

variable caseID number;
BEGIN
   SELECT case_id INTO :caseID FROM cases WHERE user_id = 'test';
END;
/
select * from version where case_id = :caseID;

Another possibility that does not use special client syntax but only PL/SQL:

DECLARE
caseID number;
v version%ROWTYPE;
BEGIN
   SELECT case_id INTO caseID FROM cases WHERE user_id = 'test';
   SELECT * INTO v FROM version WHERE case_id = caseID;
END;
/

But in this case you have to code everything in PL/SQL and make sure to process output of SELECT statements.

Reusing a variable in an Oracle SQL query

DECLARE the variable at the start of the PL/SQL block and then just re-use it in the same PL/SQL block and it works:

DECLARE
  v_months PLS_INTEGER;
BEGIN
  SELECT (total_due/monthly_amount) INTO v_months FROM TABLE1;

  update TABLE2 set paid = 'YES' where sequence between v_months and 48;
END;
/

If you are trying to re-use it between different PL/SQL blocks then it will not work using a PL/SQL variable.

db<>fiddle here

---

If you want to use a variable in multiple statements then you can use bind variables:

VARIABLE v_months NUMBER

BEGIN
  SELECT (total_due/monthly_amount) INTO :v_months FROM TABLE1;
END;
/

update TABLE2 set paid = 'YES' where sequence between :v_months and 48;

How to define and use a variable in Oracle SQL Script in SQL Developer in the simplest way

You may use either of these two methods to avoid prompts in SQL developer.

Set the value of the variable from exec and run as script (F5)

var   last_name_input varchar2(20);
EXEC  :last_name_input := 'LN_2'
select * from Test_Persons tp where tp.LASTNAME = :last_name_input;

Another method is to use a substitution variable( & by default, you may also change it using SET DEFINE )

define last_name_input = 'LN_2'
select * from Test_Persons tp where tp.LASTNAME = '&last_name_input';

---

Related Topics