Clever Geek Handbook

Select one column value and store it in oracle sql variable

I want to get a specific value of the a.id column and store it in the v_id variable. Then use this value to navigate to the 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 am getting this error in Oracle SQL Developer:

Bug report: ORA-06550: line 3, column 7: PLS-00103: character "V_ID", expecting one of the following:

: =. (@%; Symbol ": =" replaced with "V_ID" to Continue. 06550. 00000 - "row% s, column% s: \ n% s" * Reason: Usually a PL / SQL compilation error. * Action:

+3


source to share


3 answers


If you want to use rownum

and order by

, you must place the order in a subquery. There is no other way to ensure that you get the correct value.

It is also good practice to deal with the possibility that there might not be id

that matches your request. I've added an extra block begin... end;

to handle 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 pointed out (while I was writing this!) The problem was print

that it should bedbms_output.put_line()

+10


source


It:

Print v_id;

not valid. You probably meant:

dbms_output.put_line(v_id);


? (Note that you may need to run

set serveroutput on;

in advance so that what is said may have an effect.)

+4


source


PRINT

is not a valid PL / SQL command, so there is 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;
/
+2


source






More articles:


All Articles