SQL Navigator 6.2 and substitution variables

SQL Navigator
1

I’ve read a handful of threads in here and in the Beta forum discussing changes to how substitution variables are handled from 5.5 to 6.x. I’m hoping there are some workarounds for some of these issues so I’m creating this post.

One of the advantages of SQL Navigator 5.5 as compared with other SQL editing tools that we looked at is that it behaved very much like SQLPlus. In my group, developers use SQL Navigator to develop SQL scripts that are executed in our batch cycle using SQLPlus. The similar behavior of the two programs worked very well for us.

Issue #1: The ‘scan defines’ option is off by defaultIn SQL Navigator 5.5, it was possible to make the ‘scan defines’ option enabled by default, although there wasn’t an explicit option for this (you had to set it on and then close an editor window). In 6.2 this no longer appears to be possible, is there a way to have this option selected by default whenever you open a window?

I read in a different thread that this feature was removed because it would really need to be implemented for all options to be consistent. However the Scan Defines option is different because it mirrors a SQLPlus feature which is enabled by default. So without providing a way to have this enabled by default, SQL Navigator now behaves differently than SQLPlus.

Issue #2: Substitution variables aren’t shared across code windowsIn SQL Navigator 5.5, if you defined a substitution variable in one code window using the ‘DEFINE varname=value’ statement, that variable’s value propogated across all code windows. In 5.5 we used this functionality to define a set of around 50 variables in our startup script and these variables would be automatically available in all code windows for the developer. Some of these values represent dates that change on a daily basis.

I see that there is an option in 6.2 to save and load substitution variables in a file. Is there an option to load a substitution variable file automatically whenever SQL Navigator is started? I see that these are stored in C:\Documents and Settings\username\Application Data\Quest Software\SQL Navigator 6.2.1\Unified Editor\state.ini, but there are other things in this file so I couldn’t (for example) overwrite it on our developer’s workstations with the current day’s file every day.

2

Hi Derek,

Thank you for your feedback. We will raise a CR for the first issue to be addressed in the next version; we’ll notify you of the Beta and GA dates very soon. With the second issue, did you try to use the ‘Share variables among sessions’ option in the Substitutions window?

Regards,
Roman

3

For reference, the version of SQL Navigator I am using is 6.2.1.1521.

Regarding the sharing of variables across code windows, I have selected the option ‘Share variables among sessions’. I investigated this a little more and I now believe I’m seeing a bug. The steps to reproduce:

  1. In a new code window, turn the ‘Scan Defines’ option on.
  2. Add this code to the code window and run it using F9:

DEFINE TEST1=ASDF;

  1. Right-click in the code window and select ‘SQL/Script Options’ then ‘Show Substitutions’.
  2. The newly defined variable is not listed.

Now try this:

  1. In a new code window, turn the ‘Scan Defines’ option on.
  2. Add this code to the code window and run it using F9:

DEFINE TEST2=ASDF;
SELECT ‘&&TEST2’ FROM DUAL;

  1. Right-click in the code window and select ‘SQL/Script Options’ then ‘Show Substitutions’.
  2. The newly defined variable is listed.

It appears the act of using the variable is what adds it to the list of defined variables, the DEFINE statement alone doesn’t do it. In my group’s case, the SQL script that we’ve configured SQL Navigator to run at startup executes a series of DEFINE statements and then auto-closes which is why the variables aren’t getting defined.

I would work around this issue by adding a series of SELECT ‘&&VARNAME’ FROM DUAL statements to force them to be set but with the scan-defines option turned off on new code windows this won’t work.