Search code examples
sqloracle-databaseplsqldeveloper

Terminating a Substitution Variable Name in PL/SQL Developer

I am trying to write a query that has text immediately following a substitution variable. I know it can be done in SQL Plus but I'm not sure if it is possible in PL/SQL Developer without using dynamic SQL. As a simple example, I create this table with test data:

CREATE TABLE TEST_TABLE AS
SELECT 1 AS COL_1 FROM DUAL
UNION
SELECT 1.1 AS COL_1 FROM DUAL
UNION
SELECT 11 AS COL_1 FROM DUAL

From what I've read, the end of a substitution variable is indicated by a period (.). However, this only seems to work correctly in SQL Plus.

SQL> SELECT * FROM TEST_TABLE
  2  WHERE COL_1 = &SUB_VAR.1;
     COL_1
----------
        11

When I run the above query and enter 1 when prompted for the value of SUB_VAR, I get 11 returned as desired.

However, when I run the same query in a SQL window in PL/SQL Developer, I get 1.1 returned instead, which shows that it is treating the period as a decimal point instead.

Is there a way to replicate the same behavior in a SQL window in PL/SQL Developer as in SQL Plus?

Solution

  • I figured it out. All I needed to do was wrap the substitution variable name in double quotes.

    SELECT * FROM TEST_TABLE
WHERE COL_1 = &"SUB_VAR"1