Change substitution variable prompt

[Alwyn]

PL/SQL Developer 13.0.4

Hello,

Is there a way to change the substitution variable prompt from '&' to ':' in the SQL Window? I mainly work with Apex which uses variables in the form of 1_YEAR, for example. When testing a query, it would be handy if I could use the query as is without replacing all the ':' with '&'.

- Alwyn

 

[Marco Kalter]

This is currently not possible. The Test Window does support bind variables though, which use the :varname syntax. In this case you can declare the variable names, types and values in the variable list at the bottom of the Test Window. The "Scan" button in the header of the variable list will scan the script for bind variables and will declare them all as string variables.

 

[Alwyn]

Thanks for your response, Marco. I'll try out the Test Window and see if it's more efficient to use than the SQL Window.

Will you please add ':' as a substitution variable prompt as a request for enhancement? Apex has been around for over a decade and isn't going away anytime soon.

- Alwyn

 

[Marco Kalter]

I have added this to the list of enhancement requests.

 

[YuvaOra]

Hi Marco,
Are there any plans to change the substitution variable character from & to ':'

 

[YuvaOra]

Hi Marco,
Are there any plans to change the substitution variable character from & to ':'

 

[Marco Kalter]

This is still on the list of enhancement requests. It did not make it into 14.0 though.

 

[Breukers]

Hi Marco,

Why aren't binds available in the SQL window? This is an important core feature of sql, because it will reuse existing execution plans instead of recreating new execution plans per value. This happens if you type it yourself or use substitution values which are both hard coding. For me the SQL window is unusable because of it. These bind variables already exists for decades in SQL.

It's also a part of the reason why other developers stick at Oracle SQL Developer or choose another tool. In general people won't know it can be done via test window, so they will think it isn't possible.

Best regards,
Ruud Breukers

 

[Kaspar Walter]

We are validating PL/SQL Developer as a replacement for Toad / SQL Developer in our company. Some of us work with APEX a lot so this issue with :bindvariables is a big deal. Please consider prioratising this issue

 

[maheshdm]

yes, this enhancement will definitely help lot of Apex developer especially. please consider this enhancement in next fix/release. - Thanks.

 

[El]

+1

This limitation is very frustrating in PL / SQL Developer.

Best regards,

 

[Marco Kalter]

This is on the list of enhancement requests. Note that the Test Window fully supports bind variables, and that it can execute both SQL select statements and PL/SQL Blocks.

 

[El]

Hello:

Now the doubt has come to me and I have the feeling that I have missed something!

In a Test Window you can execute something like this:

SELECT *
FROM user_tables t
WHERE t.table_name BETWEEN :1 AND :2;

Enter values in :1, :2 variables, execute and see rows returned like a SQL Window?

Best Regards,

 

[Marco Kalter]

Yes, that is indeed possible. After executing a statement that produces a result set, you need to switch to the "SQL Output" tab page to view the result set.

 

[El]

Hello:

I did not know that functionality of the test window. I do not like that the result is seen in another tab, but worse was not knowing that functionality.

Thanks.