CODEWITHSUNDEEP
sqloracle-databasevariables
Renato ZANOTELLI FILHO
Renato ZANOTELLI FILHO

Reputation: 1

Define in Select

I would like to know if there is any way to use the DEFINE command, where I perform a select to pass the value to it.

For example:

DEFINE data date = select max(date_mytable) from mytable;

Would there be any way to do this?

The idea is that during the next lines of code to be used, I call the variable instead of being consulted the maximum date of a specific table and having to insert it by hand.

I'm using Oracle SQL

I'm using SQLTools Client

Upvotes: 0

Views: 1289

Answers (3)

William Robertson
William Robertson

Reputation: 15991

In SQL*Plus, which your particular tools may or may not emulate exactly, you can define a substitution variable from a query using the column x new_value y construction, like this:

SQL> column max(object_id) new_value last_object_id

SQL> select max(object_id) from user_objects;

MAX(OBJECT_ID)
--------------
        168843

1 row selected.

SQL> def last_object_id
DEFINE LAST_OBJECT_ID  =     168843 (NUMBER)

This marks the specified column name to be tracked as a substitution variable.

Typically in scripts you would give the column an alias matching the desired variable name to avoid the need to copy an exact expression, so you will see things like

column today_date new_value today_date

followed by a query with a column aliased as today_date.

Also notice that it doesn't need to be a single-row query, it just takes the last value returned by query.

With date values, you have to choose the format carefully, because the substitution variable will be a plain text string and not an actual date type. For dates without a significant time I recommend using 'YYYY-MM-DD' format as it's easier to use it subsequently in date literals using date '&somevar':

SQL> column last_created new_value last_created

SQL> select to_char(max(created),'YYYY-MM-DD') as last_created from user_objects;

LAST_CREAT
----------
2021-12-20

1 row selected.

SQL> prompt Last object created on &last_created
Last object created on 2021-12-20

Now we can use &last_created in date arithmetic:

SQL> select date '&last_created' +10  from dual;

DATE'2021
---------
30-DEC-21

It's possible that none of this works in the tools you are using, though, as it's a SQL*Plus client feature and nothing to do with SQL itself.

Upvotes: 0

APC
APC

Reputation: 146179

We have to use var[iable] rather than def[ine] to declare a variable because we cannot populate it through use a SELECT statement using =. So in a SQL*Plus session we use variables like so:

var data varchar2(8)

select to_char(max(date_mytable))
into :data
from mytable;

select count(*) 
from some_other_table
where some_other_date >= to_date(:data, 'yyyymmdd');

Unfortunately SQL*Plus doesn't have a DATE datatype, hence the additional pfaffing around with type conversions.

SQL*Plus is an Oracle proprietary client for working with SQL and PL/SQL. Its syntax is not defined by a standard (unlike SQL) and consequently many 3rd party client products do not support it, or only support a subset of it. You may find it beneficial to use Oracle's own (free) tools like SQL Developer or SQL CL (the modern version of SQL*Plus).

Upvotes: 0

EJ Egyed
EJ Egyed

Reputation: 6084

If you are using PL/SQL, you can define the variable, then use a SELECT...INTO statement like this:

set serveroutput on
set feedback off

DECLARE
    l_date   DATE;
BEGIN
    SELECT MAX (created) INTO l_date FROM all_objects;
    dbms_output.put_line(l_date);
END;
/

Upvotes: 1

Related Questions