Define in Select-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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