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.

How will the results be used? For a data migration, can you write the whole thing in PL/SQL, without relying on SQL*Plus features? — William Robertson
– William Robertson, Commented Jun 20, 2020 at 9:32
@WilliamRobertson I'm in the hunt of simple conversion of declare of Sybase in oracle, I want use my_date in multiple where clauses further — Shelly
– Shelly, Commented Jun 20, 2020 at 9:34
How will the results be used though? Are you just going to run a SQL*Plus script and have it print out some results? Or since you mentioned migration, will there be insert into x select from y type of logic? If so you could do it all in PL/SQL and not need any variable or print etc. — William Robertson
– William Robertson, Commented Jun 20, 2020 at 9:38
We don't have enough context information do you want only to convert SQL scripts with SQL and anonymous PL/SQL OR do you want to convert Transact-SQL to PL/SQL procedures, functions and packages ? — pifor
– pifor, Commented Jun 20, 2020 at 9:38

pifor · Accepted Answer · 2020-06-20 09:09:10Z

0

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>

edited Jun 20, 2020 at 9:09

answered Jun 20, 2020 at 8:57

pifor

7,9722 gold badges11 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Shelly Over a year ago

I'm using sql developer, and this sql script is getting called from java code hibernate

pifor Over a year ago

I don't know hibernate and I cannot tell if it calls SQLPlus or it can only execute only SQL and PL/SQL statements.

Shelly Over a year ago

it executes sql and pl/sql but the above thing is possible in sql?

pifor Over a year ago

It's SQL but it can only be executed by SQL*Plus.

William Robertson Over a year ago

@Shelly - you should know that what will run in SQL Developer, what will run in SQL*Plus and what you can call from Hibernate are three different things. You could spend a lot of time getting a SQL*Plus/SQL Developer script working how you want, and then find it's no use in Hibernate and you have to start again.

|

William Robertson · Accepted Answer · 2020-06-20 16:51:43Z

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.

Collectives™ on Stack Overflow

Declare variables and use them in query oracle

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related