How can I get table column value to defined variable?

Question

How do I get the table column value to the defined variable? This is the code I tested:

 DEFINE R_EXPIRY_DAYS = '';
 SELECT NVL(EXPIRY_DAYS_AFTER,30) INTO R_EXPIRY_DAYS FROM USER_TABLE;

Answer 1

DECLARE R_EXPIRY_DAYS  VARCHAR2(64);

BEGIN
   SELECT my_column INTO R_EXPIRY_DAYS FROM USER_TABLE;
END;

Answer 2

Hm, why would you use a substitution variable for that?

• DEFINE explicitly creates a substitution variable
• VARIABLE declares a bind variable you can use in PL/SQL - that's what I'd use, which is also suggested by INTO R_EXPIRY_DAYS you posted - into means PL/SQL.

---

SQL> select * from user_table;

EXPIRY_DAYS_AFTER
-----------------
               20

SQL> var r_expiry_days number
SQL> exec select nvl(expiry_days_after,30) into :r_expiry_days from user_table;

PL/SQL procedure successfully completed.

SQL> print r_expiry_days

R_EXPIRY_DAYS
-------------
           20

SQL>

---

If you insist on a substitution variable and want to store result of the select statement into it, then you don't use define but column with new_value:

SQL> column exp_days_aft new_value r_expiry_days
SQL> select nvl(expiry_days_after, 30) exp_days_aft from user_table;

EXP_DAYS_AFT
------------
          20

SQL> define r_expiry_days
DEFINE R_EXPIRY_DAYS   =         20 (NUMBER)
SQL>

Right; r_expiry_days really contains 20.