Declare variables and use them in query oracle

Question

I'm in the middle of data migration, converting Sybase query in oracle 11g I've stuck on this thing from past 2 days

@Declare @myDate Datetime
Select @myDate = workingDate from MyTable

Then there are few sql statement in which the varaible myDate is getting used in where clause

For Ex

Select * form table1 
join table1 on table1.id = table.id
join table1 on table1.id =  mytable.id
where mytable.workingDate = my_date

// so at last what I want is to declare one variable which will gets its value from a select clause and use it in further sql statements in a complex query

I want to achieve the same in Oracle 11g without any added complexity

I'm new to oracle, your help is required.

Answer 1

Are you using SQLPlus: I don't think you can declare a SQLPlus TIMESTAMP variable.

Try instead a VARCHAR2 variable and convert to TIMESTAMP:

SQL> select banner from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> variable ko timestamp;
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
            VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
            NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
            REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]
SQL> --
SQL> variable my_date varchar2(50) ;
SQL> begin
  2  select workingDate  into :my_date from MyTable;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> print my_date;

MY_DATE
------------------------------
20-JUN-20 10.55.55.264286 AM

SQL> print :my_date;

MY_DATE
------------------------------
20-JUN-20 10.55.55.264286 AM

SQL> --
SQL> select * from mytable;

WORKINGDATE
------------------------------
20-JUN-20 10.55.55.264286 AM

SQL> select workingDate  from MyTable where workingDate = to_timestamp(:my_date, 'DD-MON-YY HH.MI.SSXFF AM');

WORKINGDATE
------------------------------
20-JUN-20 10.55.55.264286 AM

SQL> 

Answer 2

variable doesn't support dates or timestamps. Also your final query should use :my_date (indicating a host variable), not my_date.

If the aim is to populate tables using variables populated in a previous step, you could use PL/SQL for the whole task, e.g.

declare
    myDate date;
begin
    select some_col into myDate from wherever;

    insert into target_table (col1, col2, col2)
    select x, y, z
    from   source_table
    where  business_date = myDate;
end;

Or better still, define PL/SQL packages to encapsulate your processing logic.

Regarding the code you posted, from Oracle 12.1 onwards, you can declare a ref cursor within PL/SQL and have clients such as SQL*Plus simply call the block and print the results:

declare
    my_date timestamp;
    results sys_refcursor;
begin
    select systimestamp into my_date
    from   dual;

    open results for
        select my_date as my_date from dual;
    
    dbms_sql.return_result(results);
end;
/

PL/SQL procedure successfully completed.

ResultSet #1

MY_DATE
---------------------------------------------------------------------------
20-JUN-20 10.02.29.130000000

1 row selected.

For earlier Oracle versions you still have to declare the ref cursor as a host variable in SQL*Plus:

set autoprint on

var results refcursor

declare
    my_date timestamp;
begin
    select systimestamp into my_date
    from   dual;

    open :results for
        select my_date as my_date from dual;
end;
/

(Or set autoprint off and then print results explicitly.)

I don't know Java though, so I don't know whether any of the approaches above will work in your environment.