2

I have some MariaDB SQLs that are called by a bash script, this script set the start and end dates for the queries, but now the project will use Oracle DB.

So, I have something like this in MariaDB SQL:

SET @date_start := '2000-01-01';
SET @date_end := '2001-01-01';
SELECT * FROM user WHERE birth BETWEEN @date_start AND @date_end;

And I couldn't find anything like that in Oracle SQL, already tried DECLARE, DEFINE, WITH, but nothing works

Adrian Mole
  • 49,934
  • 160
  • 51
  • 83
rafaelvol
  • 33
  • 5
  • You can define variables for SQL*Plus/SQLCL scripts, accepted answer to [this question](https://stackoverflow.com/questions/3564283/how-to-declare-variable-and-use-it-in-the-same-oracle-sql-script) shows couple of options. – Petr Feb 02 '21 at 14:05

1 Answers1

0

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

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786