Can I Create a variable that is useful in SQL Statement and PL SQL Script as well?

Question

I have defined a variable in sql script as below:

DEFINE USER_NAME="TEST_DB";
DEFINE PASSWORD="abc";

Now in my script I have few sql statement where I can use above variable with &USER_NAME and it's serve my purpose. Now I have PL SQL block also:

DECLARE
BEGIN
CONST_MASTER_USER varchar2(100) := 'TEST_DB';
-- FEW CODE
END

Now I want to use USER_NAME variable in this PL/SQL Block, but when I replace 'TEST_DB' with &USER_NAME, SQL Developer gives me following error:

PLS-00201: identifier 'TEST_DB' must be declared

As I am not expert in SQL, do we have any solution where we can define a variable once and use it in SQL as well as PLSQL statements?

Answer 1

It sounds like you removed the quotes, which are still needed with substitution variables:

DECLARE
   CONST_MASTER_USER varchar2(100) := '&USER_NAME.';
BEGIN
-- FEW CODE
END