Select a single column value and store it in variable oracle sql

Question

I want to grab a particular column value a.id and store it into a variable v_id. Then use this value to pass into a stored procedure.

DECLARE v_id a.id%TYPE;
BEGIN
SELECT id  into v_id from a where a.name='test' and rownum <2 order by id desc;
Print v_id;
doSomething(v_id);
END;
/

I'm getting this error in Oracle SQL Developer:

Error report: ORA-06550: line 3, column 7: PLS-00103: Encountered the symbol "V_ID" when expecting one of the following:

:= . ( @ % ; The symbol ":=" was substituted for "V_ID" to continue. 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Answer 1

If you want to use rownum and order by you have to put the order by in a sub-query. There is no other way to guarantee that you get the correct value.

It's also good practice to deal with the possibility that there may not be an id that matches your query. I've added an additional begin... end; block to deal with this.

declare
   v_id a.id%type;
begin

   begin
      select id into v_id 
        from ( select id
                 from a 
                 where name = 'test' 
                 order by id desc )
       where rownum < 2 
             ;
    exception when no_data_found then
      v_id := null;
    end;

   dbms_output.put_line(v_id);
   doSomething(v_id);

end;
/

As @raukh noted (whilst I was writing this!) the problem is print, which should be dbms_output.put_line()