Creating variables in Oracle SQL

Question

I have over 1000 lines of sql query, in every few line I need to change the year. So, Im thinking if there is a way to set up a year in the beging of the sql query that I can change only one time instead of going through the entire code to make the changes. I am trying to a build sql query where I can assign a variable like this.

a in (2022,2023)

select * from transaction_table 
where Year_col in a

I know that it can be done in t-sql but not sure about Oracle sql.

Your assistance is greatly appreciated.

What tool will you be using to run the query? Many tools support either substitution variables (`&year`) or bind variables (`:year`). You might also have a CTE ('WITH clause') at the top of the query that you could then join to wherever you need to. For an in-list in a script, a substitution variable might be simplest, but it rather depends on how you are running your report. — William Robertson, Mar 21 '23 at 23:48

score 1 · Accepted Answer · answered Mar 21 '23 at 19:59

One option is to split variable's contents into rows.

SQL> var a varchar2(30)
SQL> exec :a := '10, 20'

PL/SQL procedure successfully completed.

SQL> select empno, ename, job, sal
  2  from emp
  3  where deptno in (select regexp_substr(:a, '[^,]+', 1, level)
  4                   from dual
  5                   connect by level <= regexp_count(:a, ',') + 1
  6                  );

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7369 SMITH      CLERK            860
      7566 JONES      MANAGER      3198.13
      7782 CLARK      MANAGER       2572.5
      7788 SCOTT      ANALYST         3225
      7839 KING       PRESIDENT       5250
      7876 ADAMS      CLERK         1182.5
      7902 FORD       ANALYST         3225
      7934 MILLER     CLERK           1365

8 rows selected.

SQL>

Not very elegant, but - it works.

score 0 · Answer 2 · answered Mar 21 '23 at 21:00

Dynamically constructed IN lists for binds is generally a bad practice and at higher volumes can really cause problems with the shared pool. The nature of the IN clause is such that to get cursor sharing it would need to remain at a fixed number of elements, which undercuts what IN is really for. Restrict IN to static, hard-coded values that don't change from query to query.

Instead, you are much better off using scalar variables, such as:

select * from transaction where year_col = :year

or

select * from transaction where year_col BETWEEN :startyear AND :endyear

score 0 · Answer 3 · answered Mar 21 '23 at 21:14

You could use a collection:

CREATE TYPE year_list IS TABLE OF NUMBER(4,0);

then:

SELECT *
FROM   transaction
WHERE  year_col MEMBER OF year_list(2022,2023);

If you are using a client application (i.e. Java, etc.) that supports bind variables then you can create an array in your client application and pass it to the collection as a bind variable (Java example):

SELECT *
FROM   transaction
WHERE  year_col MEMBER OF :your_bind_variable

score 0 · Answer 4 · answered Mar 23 '23 at 04:27

Common table expressions were created to avoid repetition, and they can be used to void repeating variables. Create a table using the WITH clause and then reference that table throughout the query.

with years as
(
    select 2022 the_year from dual union all
    select 2023 the_year from dual
)
select * from transaction_table where year_col in (select the_year from years)
union all
select * from another_table     where year_col in (select the_year from years)
...
;

As others mentioned, the best answer depends largely on the precise tool used to run this query.

Creating variables in Oracle SQL

4 Answers4