CODEWITHSUNDEEP
oracle-databaseplsql
alex_fields1
alex_fields1

Reputation: 71

PL/SQL compilation error with declaring variable

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!

Upvotes: 0

Views: 1765

Answers (1)

Rene
Rene

Reputation: 10541

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.

Upvotes: 2

Related Questions