How to Use Variables in Oracle SQL Developer

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>

Declare Variables in Oracle (PL/SQL Developer)

In Plsql you have the option to declare a variable based on existing datatype of table column.

Suppose you have a table table1 column Current_week as date datatype then you can declare it like this.

declare
weeknumber table1.current_week%type;
begin
select week into weeknumber from table1;
end;

This is best practice to declare variables in Oracle PL/SQL to avoid any datatype issues.

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

Define variable in Oracle SQL developer

SQL> help var

 VARIABLE
 --------

 Declares a bind variable that can be referenced in PL/SQL, or
 lists the current display characteristics for a single variable
 or all variables.

VAR[IABLE] [variable [type]]

 where type represents one of the following:

     NUMBER         CHAR          CHAR (n [CHAR|BYTE])
     NCHAR          NCHAR (n)     VARCHAR2 (n [CHAR|BYTE])
     NVARCHAR2 (n)  CLOB          NCLOB
     REFCURSOR      BINARY_FLOAT  BINARY_DOUBLE

As you can see there is no DATE type here. I guess the whole

variable   COB_DATE date

is ignored.

As a workaround you can define COB_DATE as varchar2 and convert it to DATE in the sql

variable   COB_DATE varchar2(30)
variable   v_mode varchar2(20)
exec :COB_DATE := '14-JUL-2016';
exec :v_mode := 'MAG';

select * 
    FROM DF_RISK_SIT2_OWNER.recon_ts_rs
    WHERE SRC_HUB = 'DBRS'
    AND TRD_SRC_SYS in :v_mode 
    AND DSET_COB_DT = TO_DATE(:COB_DATE, 'DD-MON-YYYY')

or rely on implicit conversion using your original query

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 set a variable and use it in a select query in Oracle PL/SQL?

Use a cursor:

DECLARE
    p_name VARCHAR2(100);
    p_cur  SYS_REFCURSOR;
BEGIN
    p_name := 'Strawberry';
    OPEN p_cur FOR
    SELECT * FROM products where product_name=p_name;

    -- do something with the cursor.
END;
/

Or use a SQL/Plus-style bind variable declaration:

VARIABLE p_name VARCHAR2;

BEGIN
  :p_name := 'Strawberry';
END;
/

SELECT * FROM products where product_name=:p_name;

If only one row will ever be returned from your query (i.e. product_name is UNIQUE) then you can use SELECT ... INTO ...:

DECLARE
    p_name   VARCHAR2(100);
    p_value1 products.value1%TYPE;
    p_value2 products.value2%TYPE;
    p_value3 products.value3%TYPE;
BEGIN
    p_name := 'Strawberry';

    SELECT value1, value2, value3
    INTO   p_value1, p_value2, p_value3
    FROM   products
    WHERE  product_name=p_name;

    -- do something with the values.
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL;
    -- Handle the exception
  WHEN TOO_MANY_ROWS THEN
    NULL;
    -- Handle the exception
END;
/

---

Related Topics