How to declare variable and use it in Oracle sql

Question

i want to delcare a variable and use it in the same oracle sql

suppose we have the following script which is working fine

select id
,NAME
,ADDRESS
,DATE
FROM MYTABLE
WHERE COMPANY= 'LENOVO'
AND DATEBETWEEN systimestamp - INTERVAL '10' MINUTE
AND SYSTIMESTAMP

At the end of this code i want to store the value of ID in a variable and then it to delete the record, i tried this but it's not working

declare
_name varchar(100);

select id
    ,NAME
    ,ADDRESS
    ,DATE
    FROM MYTABLE
    WHERE COMPANY= 'LENOVO'
    AND DATEBETWEEN systimestamp - INTERVAL '10' MINUTE
    AND SYSTIMESTAMP
Returning to _name;

delete _name;

Any help please, thank you

Where are you running the script - SQL*Plus, SQL Developer, some other client? What are you doing with the selected data before deleting, just displaying it? — Alex Poole
– Alex Poole, Commented Jun 18, 2014 at 18:08
I'm just displaying it to get the id and i'M using oracle sql developper 3.0.04 — user3745193
– user3745193, Commented Jun 18, 2014 at 18:21
If you're just displaying it to get the ID, do you actually need to display it (or rather, them, as I assume it can match more that one record with that interval) at all? I'm unclear why you aren't just dong a straight delete with the same where clause. The declare implies you want to do this in a PL/SQL block, possibly looping around a cursor to display and delete each matching record; is that the case? It's all a bit unclear at the moment. — Alex Poole
– Alex Poole, Commented Jun 18, 2014 at 22:32

Matthew Strawbridge · Accepted Answer · 2014-06-18 18:50:36Z

1

You need to use a SELECT INTO clause:

SELECT id
INTO _name
FROM MYTABLE;

answered Jun 18, 2014 at 18:50

Matthew Strawbridge

20.8k10 gold badges81 silver badges97 bronze badges

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

Collectives™ on Stack Overflow

How to declare variable and use it in Oracle sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related