CODEWITHSUNDEEP
sqloracle-database
Th3sandm4n
Th3sandm4n

Reputation: 809

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

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:

Upvotes: 6

Views: 45799

Answers (3)

Justin Cave
Justin Cave

Reputation: 231661

PRINT isn't a valid PL/SQL command, so that's going to create a problem. Perhaps you wanted

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

Upvotes: 2

Ben
Ben

Reputation: 52863

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()

Upvotes: 12

ruakh
ruakh

Reputation: 183301

This:

Print v_id;

is not valid. Probably you meant:

dbms_output.put_line(v_id);

? (Note that you may need to run

set serveroutput on;

beforehand in order for the above to have an effect.)

Upvotes: 4

Related Questions