[SOLVED] How to declare a constant in Oracle SQL PLUS?

How to declare a constant in Oracle SQL PLUS?

I know how to declare a variable in Oracle sql plus but not sure with the constant declaration , Pls guide with that :

An example of variable declaration :

DEFINE v_target_table_name          = 'BUSINESS_UNIT_T'

Solution

SQL*Plus is a client application which is used as an interface for a user to talk to the database; it is not a database and has limited capability to create "variables".

```
DEFINE v_target_table_name = 'BUSINESS_UNIT_T'
```
DEFINE creates a substitution variable and whenever the client sees &v_target_table_name in an SQL statement then SQL*Plus will effectively do a find-replace and substitute the variable's value before sending the statement to the database for the database to process it (the database never sees the original text of the statement).
```
VARIABLE v_value VARCHAR2(20);
EXECUTE :v_value := 'BUSINESS_UNIT_T';
```
VARIABLE creates a bind variable and whenever the client sees the :v_value placeholder it will send the statement as-is to the database and also send the bind variable and then the database will parse the statement and bind the value in wherever it finds placeholder. (Note: bind variable are for values and not for trying to dynamically set identifiers.)

Neither of these commands have any syntax that supports making the value a constant.

If you want to use a constant then you could use PL/SQL (which will be evaluated by the database and is not specific to SQL*Plus):

DECLARE
  v_target_table_name CONSTANT VARCHAR2(20) := 'BUSINESS_UNIT_T';
BEGIN
  -- Do something with the variable
  NULL;
END;
/