PL/SQL compilation error with declaring variable

Question

I am trying to create a basic PL/SQL query where I am using a certain SKU as a parameter so that I can reference it without typing in the sku each time.

When compiling my code I get the error:

Error report: ORA-06550: line 6, column 1: PLS-00428: an INTO clause is expected in this SELECT statement 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Here is my code:

declare
myitem number (20);
begin 
myitem := 1000956;

select f.order_no
from fdt_maptool f
where f.item = myitem;

end; 
/

I thought that maybe it had to do with me using VARCHAR(20) instead of NUMBER, So I tried that as well.

declare
myitem number;
begin 
myitem := 1000956;

select f.order_no
from fdt_maptool f
where f.item = myitem;

end; 
/

And then received this error:

Error report: ORA-06550: line 6, column 1: PLS-00428: an INTO clause is expected in this SELECT statement 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

I am fairly new to PL/SQL so if please go easy on me!

You need to select the results of the query into a variable. E.g. select f.order_no into v_order_no from ..., assuming that f.item is a unique column. — Boneist
I actually do not have administrative clearance on this Database, It will not let me create/delete/insert/ etc... on any of the databases. Is this the only way to set a reference variable? — alex_fields1
If you can select from the table, then you can create an anonymous PL/SQL block that contains a variable that you can select into. Although if all you want to do is to run a repeatable query with different parameters, then perhaps you need just the sql statement, and a where clause of ...where f.item = :p_item or maybe ...where f.item = &item. — Boneist
Is there a way to just use &myitem after is has been declared in the declaration statement at the beginning? — alex_fields1
What exactly is it you're trying to achieve with this procedure that you couldn't do with straight sql statements? — Boneist

Rene Rene · Accepted Answer · 2018-03-15T14:21:54

Within PLSQL you need to select into

declare
   myitem number(20);
   myorder number(20);
begin 
   myitem := 1000956;

   select f.order_no
   into myorder
   from fdt_maptool f
   where f.item = myitem;

end; 
/

Now there's two standard things that might go wrong. You may not find a record or you may find more than one record. You need exception handlers to handle these cases.

declare
   myitem number(20);
   myorder number(20);
begin 
   myitem := 1000956;

   select f.order_no
   into myorder
   from fdt_maptool f
   where f.item = myitem;

exception
   when no_data_found then
    dbms_output.put_line('No record with this ID');

   -- Only needed when not selecting a unique column.
   when too_many_rows then
    dbms_output.put_line('More than one record with this ID');

end; 
/

Note that the too_many_rows exception is usually already covered by the fact that you are selecting an ID column that has a unique constraint defined on it.

PL/SQL compilation error with declaring variable

1 Answers