Is it possible to create bind variables without a colon?

Question

I generated this code via CHATGPT to better understand Bind variables in Oracle SQL:

DECLARE
  v_employee_id NUMBER := 1001;
  v_employee_name VARCHAR2(50);
  v_salary NUMBER;
BEGIN
  -- SQL statement with bind variables
    SELECT employee_name, salary
  INTO v_employee_name, v_salary
  FROM employees
  WHERE employee_id = v_employee_id;

  -- Output the results
  DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id);
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
  DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/

Other resources I've used claim that a colon before the name is required to designate something as a bind variable. I realize CHATGPT is only so accurate. Would I need to put colons before the variables in this code to designate them as bind variables?

No, you don't need - because there are no bind variables in your code! And another proof how bad and stupid ChatGPT is. — Wernfried Domscheit, Jul 30 '23 at 16:44
In PL/SQL the bind variables must not have a colon. In SQL they must have a colon. That's just the way it is. So, in PL/SQL place the variables in the query without preceding them with a colon. They are bind variables nonetheless. You could say, from the viewpoint of the PL/SQL engine these are just normal variables, and from the viewpoint of the SQL engine that is given the query by the PL/SQL engine these are bind variables. — Thorsten Kettner, Jul 30 '23 at 17:35
@ThorstenKettner That is not quite correct. PL/SQL variables do not need a colon but placeholders (bind variables) do need a colon; that is the same in both SQL (i.e. `SELECT :value FROM DUAL`) and PL/SQL (i.e. `BEGIN DBMS_OUTPUT.PUT_LINE(:value) END; /`). The OP's issue is that they are confusing locally-defined PL/SQL variables with placeholders (a.k.a. externally-defined bind variables). — MT0, Jul 30 '23 at 18:08
In PL/SQL you only use the colon to mark placeholders in dynamic SQL (EXECUTE IMMEDIATE) that your bind variables will bind to. In normal static SQL, the bind variable and placeholder are consolidated in one place, which is where you specify a PL/SQL variable in your SQL statement. So normally you do not see colons. DBAs however will see the colon in these locations when looking at the shared pool, since internally the SQL gets parsed and the PL/SQL variable replaced with a :placeholder. But the programmer generally doesn't see this. — Paul W, Jul 30 '23 at 18:21

MT0 · Answer 1 · 2023-07-30T18:05:03.363

Placeholder (bind) variables take their input from a source external to the statement being executed and Oracle expects them to be named and prefixed by a colon :.

From the Oracle Expression documentation:

placeholder ::=

...

placeholder

:host_variable

Name of a variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. Do not put space between the colon (:) and host_variable.

:indicator_variable

Name of an indicator variable declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. (An indicator variable indicates the value or condition of its associated host variable. For example, in the Oracle Precompiler environment, an indicator variable can a detect null or truncated value in an output host variable.) Do not put space between host_variable and the colon (:) or between the colon and indicator_variable. This is correct:
:host_variable:indicator_variable

Some Oracle drivers (including JDBC, ODBC) allow anonymous (unnamed, positional) placeholder (bind) variables identified by a single question mark ?.

As for your question:

Would I need to put colons before the variables in this code to designate them as bind variables?

Technically, yes. If you wanted to use an externally-defined placeholder (bind) variable rather than a local-defined PL/SQL variable then, yes, you would need to prefix the bind variable with a :.

However, in your code, you are using a locally-defined PL/SQL variable and, in that case, you do not need a colon : prefixing the PL/SQL variable because the variable is defined locally and is NOT an externally-defined placeholder (bind) variable. To continue using locally-defined PL/SQL variables then you do not need to change the code.

To see the difference between the two:

BEGIN
  DBMS_OUTPUT.ENABLE();

  EXECUTE IMMEDIATE
    'DECLARE plsql_variable NUMBER := 1; BEGIN DBMS_OUTPUT.PUT_LINE(plsql_variable); END;';
END;
/

Outputs 1 and there is no : required as the PL/SQL variable is defined and assigned a value within the scope of the statement executed with EXECUTE IMMEDIATE.

However:

BEGIN
  DBMS_OUTPUT.ENABLE();

  EXECUTE IMMEDIATE
    'BEGIN DBMS_OUTPUT.PUT_LINE(:named_bind_variable); END;'
    USING 2;
END;
/

Outputs 2 and a : is needed as the value is passed from the scope that calls EXECUTE IMMEDIATE to the inner scope where a colon : is required before the placeholder.

fiddle

Is it possible to create bind variables without a colon?

1 Answers1

`placeholder ::=`

placeholder