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?

Answer 1

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