Declaring variable and assigning value for re-usability in oracle

Question

In Oracle I need to use ID in several tables to update the value, I am trying to get value like below, and then assigning to a variable and then trying to use, but I am getting an error

declare myVal varchar2;
BEGIN
    begin
    myVal := select ID from USER where USER_NUMBER = 53;
    end;
Update myOtherTable1 SET VAL = myVal;
Update myOtherTable2 SET VAL = myVal
Update myOtherTable3 SET VAL = myVal
Update myOtherTable4 SET VAL = myVal
Update myOtherTable5 SET VAL = myVal
...........
END

Error

ORA-06550: line 4, column 14: PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

( - + case mod new not null continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe

Aleksej Aleksej · Accepted Answer · 2018-07-26T12:25:47

You need:

DECLARE
    myVal    VARCHAR2(100); -- you need a size
BEGIN
    BEGIN
        SELECT ID
          INTO myVal
          FROM USER
         WHERE USER_NUMBER = 53;
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            ...
        WHEN TOO_MANY_ROWS
        THEN
            ...
    END;

    UPDATE myOtherTable1
       SET VAL    = myVal;

    ...
END;

I added some exception handling here, it's up to you to decide if you need it, what to do, ....

A different way, with SQLPlus and with no PL/SQL could be like :

SQL> variable myVal varchar2;
SQL> select 1 into :myval from dual;

         1
----------
         1

SQL> update someTable set n = :myval;

Declaring variable and assigning value for re-usability in oracle

1 Answers