CODEWITHSUNDEEP
oracle-databasesqlplus
made_in_india
made_in_india

Reputation: 2279

Declare bind variables in SQL*Plus

I am using SQL*Plus. When I am using the below query, it is giving error

Error report:
ORA-06550: line 4, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement

Query

declare 
id varchar2(80) :='test123'; 
begin
select test_quote,test_id from order_link where id = 'test123';
end;

Upvotes: 3

Views: 65846

Answers (2)

Alex Poole
Alex Poole

Reputation: 191315

Not sure why you're using a PL/SQL block for that. You aren't using the id you declare, and it would be better to give it a name different to the column name to avoid confusion.

You can declare a bind variable in SQL*Plus though, and select into that:

var l_test_quote varchar2(80); -- or whatever type/size you need
var l_test_id varchar2(80);

declare 
    l_id varchar2(80) :='test123'; 
begin
    select test_quote, test_id
    into :l_test_quote, :l_test_id
    from order_link
    where id = l_id;
end;
/

print l_test_quote
print l_test_id

Note the : before the references to the variables defined outside the block, indicating they are bind variables. l_id is declared inside the block so it does not have a preceding :.

In this case you could also define l_id outside the block, and avoid PL/SQL while still using a bind variable for that:

var l_id varchar2(80);

exec :l_id := 'test123';

select test_quote, test_id
from order_link
where id = :l_id;

Because the main query isn't PL/SQL any more (although the exec is; that's just a shorthand for a one-line anonymous block), you don't need to select ... into so you don't need to declare those variables.

Upvotes: 15

schurik
schurik

Reputation: 7928

try this:

declare 
id varchar2(80) :='test123'; 
  v_test_quote order_link.test_quote%type;
  v_test_id    order_link.test_id%type;
begin
  select  test_quote,test_id 
  into    v_test_qoute, v_test_id 
  from    order_link 
  where   id = 'test123';
end;

Upvotes: 1

Related Questions