Declare a variable using PL/SQL developer

[garag]

Hi,
I'm new in this forum. I'm coming from different database technologies and I'm trying to familiarize with PL/SQL Developer.
My question is:
do there is a way with PL/SQL Developer to define a variable, assign to it a value and use this variable in a query?
I mean: it is possible to do something like this:

declare varname vartype;
set varname='bla bla bla';

select * from mytable where mycolomn = varname;

Sorry for the very easy quetion but I can't find anything googling it.

Thanks

 

[Marco Kalter]

The easiest method is to use a substitution variable. For example:

select * from mytable where mycolomn = '&varname';

You can refine things further by declaring a type and a default:

select * from mytable
where mycolomn = &<name    = "varname"
                   type    = "string"
                   default = "bla bla bla">

See chapter 12.3 ("Variables") in the User's Guide for more information.

 

[garag]

Dear Marco,

thank for your reply.

Unfortunately it is not what I was looking for.

If I run your code I get a window that ask me to insert a value.
Instead, I want to save a value in a variable and run the query useing the value saved in my variable not type after run the query.

I was tried to the following code but it does not work:

DECLARE
myvar varchar2(20);
BEGIN
myvar := 'XXX';
select * from mytable where mycol = :myvar;
end;

 

[Worker]

The following works in a PL/SQL Developer Command Window:

define x = 1
select 'it works!' from dual where &x = 1;

 

[Marco Kalter]

DECLARE
myvar varchar2(20);
BEGIN
myvar := 'XXX';
select * from mytable where mycol = :myvar;
end;

SQL and PL/SQL blocks with bind variables can be executed in the Test Window. The bind variable names, data types and values can be declared in the "Variables" section in the bottom pane of the Test Window.

 

[Glen Gerber]

-- when you declare a variable in a pl/sql block it can be used directly
-- in the block. When oracle processes the statement, it will be
-- interpreted as a bind variable.

DECLARE
lv_myvar varchar2(20);
BEGIN
lv_myvar := 'XXX';
select * from mytable where mycol = lv_myvar;
end;
/