Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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
3,123 views