Skip to Main Content

enable placeholder character (colon) notation for SQL bind variables in PL/SQL

Rainer StenzelFeb 12 2019

Wouldn't it be useful to mark SQL bind variables with a placeholder character in PL/SQL too ?

SQL statements could be run/tested easier in IDE's as Toad or SQLDeveloper by simply providing the bind values instead of having to modify the statement before.

Additionally this would prevent code from failing silently after adding SQL column name PL/SQL variable name conflicts as

DECLARE

status1 EVEN_MORE_IMPORTANT.STATUS%TYPE := 1;

status2 EVEN_MORE_IMPORTANT.STATUS%TYPE := 2;

SELECT .. FROM ...EVEN_MORE_IMPORTANT... WHERE .. STATUS = status1..;

-- will fail gleefully years later

-- when adding the famous status1:

-- ALTER TABLE EVEN_MORE_IMPORTANT ADD (status1 NUMBER);

-- so a clear bind variable identification would be more readable and secure

-- SELECT .. FROM ...EVEN_MORE_IMPORTANT... WHERE .. STATUS = :status1..;

Comments
Post Details
Added on Feb 12 2019
28 comments
1,238 views