Variable in sql script (oracle)

Hi,

is it possible to use variable in sql script?

It does not work:

define value1 = 'sysdate'

SELECT &&value1 from dual;


Define word is marked as invalid ('statement expected - got define'). When I run query without defining variable (with && placeholder) I get:

[42000][1745] ORA-01745: invalid host/bind variable name

I can use '?' parameter but this does not allow me to rerun query with the same parameters and use parameter more than once in query.

Thanks

2 comments
Comment actions Permalink

Hello Piotr,

Please set needed parameter pattern in File | Settings | Database | User Parameters settings and disable Always review parameters before execution option in File | Settings | Database | General tab serttings, so that IDE will not show you the paramaters dialog on every consequent execution. Then use the syntax according to configured pattern to specify the named parameter in your sql query. You can review existing parameters in Paramaters pane of SQL console tool window at any moment.

0
Comment actions Permalink

I was able to reuse variable twice with single prompting with zero configurations, just like so

-- login='paul' substitution
SELECT *
FROM ATTRS a
WHERE a.ID = (
SELECT u.ID
FROM USERS u
WHERE u.LOGIN = $(login?)
)
;
UPDATE ATTRS a
SET a.VALUE = 7
WHERE a.ID = (
SELECT u.ID
FROM USERS u
WHERE u.LOGIN = $(login?)
)
AND a.NAME LIKE 'prefix%'
;

 

0

Please sign in to leave a comment.