How to define and use a variable in Oracle SQL Script in SQL Developer in the simplest way

Question

I just want to define and then use a variable in Oracle SQL in SQL Developer (Version 18.1.0.095) (Background: We have some legacy production SQL script that are run periodically through jBPM).

In SQL Developer, I want to avoid the 'Enter Binds' prompt window.

Surprisingly, I found this is not that straight-forward to do. Am I missing something obvious?

CREATE TABLE Test_Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255)
);

INSERT INTO Test_Persons
    (PersonID,LastName,FirstName)
    values(1,'LN_1','FN_1');

INSERT INTO Test_Persons
    (PersonID,LastName,FirstName)
    values(2,'LN_2','FN_2');

commit;

// --- method 1: not working and shows the prompt window (which I try to avoid)--------
var last_name_input varchar2(20);
select 'LN_2' into :last_name_input from dual;
select * from Test_Persons tp where tp.LASTNAME = :last_name_input;

// --- method 2: not working and shows the prompt window (which I try to avoid) --------
declare
    var last_name_input varchar2(20);
    var first_name_output varchar2(20);
begin
    select 'LN_2' into :last_name_input from dual;
    select tp.FIRSTNAME into first_name_output from Test_Persons tp where tp.LASTNAME = :last_name_input;
--    dbms_output.Put_line('here: ' || :first_name_output);
end;

// --- method 3: working and shows the prompt window (which I try to avoid) --------
var last_name_input varchar2(20);
exec :last_name_input := 'LN_2';
select * from Test_Persons tp where tp.LASTNAME = :last_name_input;

Kaushik Nayak · Accepted Answer · 2018-07-04 03:53:03Z

1

You may use either of these two methods to avoid prompts in SQL developer.

Set the value of the variable from exec and run as script (F5)

var   last_name_input varchar2(20);
EXEC  :last_name_input := 'LN_2'
select * from Test_Persons tp where tp.LASTNAME = :last_name_input;

Another method is to use a substitution variable( & by default, you may also change it using SET DEFINE )

define last_name_input = 'LN_2'
select * from Test_Persons tp where tp.LASTNAME = '&last_name_input';

answered Jul 4, 2018 at 3:53

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

1 Comment

Stochastika Over a year ago

Thanks! Kaushik, this works. to be perhaps over-precise for anyone who wants to know: 'set define &' or 'set define on' will work here.

shrek · Accepted Answer · 2018-07-03 20:13:51Z

1

Your method2 works after removing ":" on Oracle 12c -

CREATE TABLE Test_Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255)
);

INSERT INTO Test_Persons
    (PersonID,LastName,FirstName)
    values(1,'LN_1','FN_1');

INSERT INTO Test_Persons
    (PersonID,LastName,FirstName)
    values(2,'LN_2','FN_2');

commit;


set serverout on;

declare
    last_name_input varchar2(20);
    first_name_output varchar2(20);
begin
    select 'LN_2' into last_name_input from dual;
    select tp.FIRSTNAME into first_name_output from Test_Persons tp where tp.LASTNAME = last_name_input;
    dbms_output.Put_line('here: ' || first_name_output);
end;

Output -

Table TEST_PERSONS created.


1 row inserted.


1 row inserted.


Commit complete.

here: FN_2


PL/SQL procedure successfully completed.

answered Jul 3, 2018 at 20:13

shrek

8876 silver badges12 bronze badges

4 Comments

Stochastika Over a year ago

thanks! this does work. However, can it work without the Begin End block? and still avoiding the 'Enter Bind' prompt window in SqlDeveloper?

shrek Over a year ago

Variables are allowed only within PL/SQL block.

shrek Over a year ago

You can write your query directly as - select tp.FIRSTNAME from Test_Persons tp where tp.LASTNAME = 'LN_2';

shrek Over a year ago

But this does not work anywhere outside SQL Developer or SQL*Plus .... to capture the data within your application you need provision (like a variable) to hold it and then the value can be used.

Collectives™ on Stack Overflow

How to define and use a variable in Oracle SQL Script in SQL Developer in the simplest way

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related