CODEWITHSUNDEEP
oracle-databasevariablesoracle-sqldeveloper
LeleyX
LeleyX

Reputation: 53

Using variables in Oracle SQL Developer 3.2

I am extremely new to SQL, and manage to extract from some other queries we use the following syntax:

--VARIABLES
undefine PF_PROD --product;
undefine PF_PSG --shop;


--QUERY
SELECT *
FROM ET1250
WHERE PRODUCT=&&PF_PROD
AND PRICE_SHOP_GROUP=&&PF_PSG
ORDER BY PERIOD_YEAR desc,PERIOD_WEEK desc;

This works fine as long as I run the undefine statements first, is there a way to make the query always ask for these variables without me having to undefine them first?

Upvotes: 3

Views: 15346

Answers (2)

gavenkoa
gavenkoa

Reputation: 48723

There are two types of variable in SQL-plus: substitution and bind.

Substitution variables can replace SQL*Plus command options or other hard-coded text:

define a = 1;
select &a from dual;
undefine a;

Bind variables store data values for SQL and PL/SQL statements executed in the RDBMS; they can hold single values or complete result setsb:

var x number;
exec :x := 10;
select :x from dual;
exec select count(*) into :x from from dual;
exec print x;

SQL Developer support substitution variables, but when you execute query with bind :var syntax you are prompted for binding (in dialog box).

Reference:

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191235

Use a single &. This is covered briefly in the SQL Developer documentation:

For substitution variables, the syntax &&variable assigns a permanent variable value, and the syntax &variable assigns a temporary (not stored) variable value.

... and in more detail in the SQL*Plus documentation, which is largely relevant for both clients.

Note that if you define or accept a variable then it won't be prompted for even with a single ampersand, but that doesn't seem to be relevant to you at the moment.

Upvotes: 3

Related Questions