9 Replies Latest reply on Jul 3, 2015 12:09 AM by JPDominator

    startup script and substitution variables (ampersand)

    710129

      When i add a startup script via DB preferences i can then no longer seem to use substitution variables (ampersand) in scripts- why is this - even a blank script causes a 'substitution cancelled' script output message

        • 1. Re: startup script and substitution variables (ampersand)
          Gary Graham-Oracle

          This is a known bug in 4.1.0 / prior SQLcl versions.  Try the latest 4.1.1 patch and latest SQLcl (still a beta). As always, thanks for the feedback.

          • 2. Re: startup script and substitution variables (ampersand)
            Andy Balcombe

            Still appears to be a bug in 4.1.1 (when a startup script is specified).

            • 3. Re: startup script and substitution variables (ampersand)
              Gary Graham-Oracle

              My bad.  I had heard this would not go into the 4.1.1 patch, but as the patch got delayed slightly for some other higher priority fixes, I had hopes this too would get pushed into the patch.  Sorry for the inconvenience.

               

              Barring another full patch or specific fixes published via Help > Check for Updates, this may not be available until a 4.2 release.

              • 4. Re: startup script and substitution variables (ampersand)
                JPDominator

                I use @ at scripts very often and share those scripts with others I work with. This worked fine in 4.0.2 and I'm considering reverting back. I've been discouraging people I work with from upgrading.

                • 5. Re: startup script and substitution variables (ampersand)
                  Gary Graham-Oracle

                  Yes, the possibilities are...

                  1. Remain with 4.0.3 or an earlier release

                  2. Remove the login script from your preferences in 4.1.1, but run the @login script manually after making a connection.

                  3. Wait for a fix.  Perhaps requesting a one-off patch from Oracle Support could speed up that process.

                  • 6. Re: Re: startup script and substitution variables (ampersand)
                    JPDominator

                    I don't use them as startup scripts, I use them as scripts just as you would from the sqlplus command line.

                     

                    For example, I have a script to find all columns by an optional owner, optional table_name, and optional column_name.

                     

                    I just type @findtables; and I get prompted for input. With this update, I am no longer prompted for input.

                     

                    There is also a commit issued after I run the script - even though it does not contain a commit. I'd file another bug for that.

                     

                    -- Find columns matching owner, table, and/or column name

                    set verify off;

                    set feedback off;

                    ACCEPT OWNER PROMPT 'Owner: '

                    ACCEPT TABLE_NAME PROMPT 'Table Name: '

                    ACCEPT COLUMN_NAME PROMPT 'Column Name: '

                    variable OWNER varchar2(30);

                    exec :OWNER := '&OWNER';

                    variable TABLE_NAME varchar2(30);

                    exec :TABLE_NAME := '&TABLE_NAME';

                    variable COLUMN_NAME varchar2(30);

                    exec :COLUMN_NAME := '&COLUMN_NAME';

                     

                    select LOWER(OWNER||'.'||TABLE_NAME) as owner_table_name, LOWER(COLUMN_NAME) AS COLUMN_NAME

                    from all_tab_cols atc

                    where nvl(instr(upper(owner),upper(:OWNER)), 1) > 0

                    and nvl(instr(upper(table_name),upper(:TABLE_NAME)), 1) > 0

                    and nvl(instr(upper(column_name),upper(:COLUMN_NAME)), 1) > 0

                    and hidden_column = 'NO'

                    order by OWNER, TABLE_NAME, internal_column_id;

                    set feedback on;

                    • 7. Re: Re: startup script and substitution variables (ampersand)
                      Gary Graham-Oracle

                      Fine. In worksheet (4.1.1), with the Login.sql script removed from the preferences and disconnecting /  connecting to the HR schema....

                      @D:\Dev\sql\Login.sql

                      @D:\Dev\sql\Test.sql

                       

                      PL/SQL procedure successfully completed.

                       

                      old:select * from employees where employee_id = '&EMP.'

                      new:select * from employees where employee_id = '102'

                       

                      EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY COMMISSION_PCT

                      ----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- --------------

                      MANAGER_ID DEPARTMENT_ID

                      ---------- -------------

                              102 Lex                  De Haan                   LDEHAAN                   515.123.4569         13/01/01  AD_VP           17000               

                             100            90

                       

                       

                      Elapsed: 00:00:00.034

                      where Login.sql contains "set timing on" and Test.sql ... is

                      ACCEPT EMP PROMPT 'Employee: '

                      select * from employees where employee_id = '&EMP.';

                      If this workaround does not suit you, then you need to revert or wait for a fix.   

                      • 8. Re: startup script and substitution variables (ampersand)
                        Gary Graham-Oracle

                        As for the unwanted commit, see if Tools > Preferences > Database > Advanced > Autocommit got checked somehow.

                        • 9. Re: Re: startup script and substitution variables (ampersand)
                          JPDominator

                          Nope, that's not it. (Double-checked for good measure)

                           

                          To illustrate the issue, I've written up three examples and executed each in 4.0.2.15, 4.1.0.19 and 4.1.1.19.

                           

                          4.0.2.15 executes all three as expected.

                           

                          4.1.1.19 appears to have resolved the issue in Example 3 found in 4.1.0.19.

                           

                          There are 2 distinct issues.

                          1) Not prompted for variable replacement

                          2) A commit is being issued

                           

                          Example 1 contains a simple select statement.

                          4.0.2.15, 4.1.0.19 and 4.1.1.19 have the same behavior. All execute as expected.

                           

                          Example 2 contains a prompt for user input.

                          4.0.2.15 prompts the user with a popup window and returns the correct result set.

                          4.1.0.19 does not prompt and appears to fail at that line in the script and returns nothing.

                          4.1.1.19 does not prompt and appears to fail at that line in the script and returns nothing.

                          Screen capture of 4.0.2.15 prompt for input.

                          example2_prompt.png

                           

                          Example 3 contains a variable bind without a prompt

                          4.0.2.15 binds properly and returns the correct result set.

                          4.1.0.19 is binding a null value and returns nothing.

                          4.1.1.19 binds properly and returns the correct result set.

                           

                          Example 1

                          4.0.2.15

                          Start of Example 1

                                   1

                          ----------

                                   1

                          End of Example 1

                           

                          4.1.0.19

                          Start of Example 1

                                   1

                          ----------

                                   1

                          End of Example 1


                          4.1.1.19

                          Start of Example 1

                                   1

                          ----------

                                   1

                          End of Example 1

                           

                          Example 2

                          4.0.2.15

                          Start of Example 2

                          Passed accept enter_one prompt

                          Passed variable enter_one

                          passed exec enter_one

                                   1

                          ----------

                                   1

                          End of Example 2

                           

                          4.1.0.19

                          Start of Example 2

                          Commit


                          4.1.1.19

                          Start of Example 2

                          Commit

                           

                          Example 3

                          4.0.2.15

                          Start of Example 3

                          :ENTER_ONE

                          ----------

                                   1

                          IS_ENTER_ONE_NULL  

                          ---------------------

                          ENTER_ONE is not null

                                   1

                          ----------

                                   1

                          End of Example 3

                           

                          4.1.0.19

                          Start of Example 3

                          :ENTER_ONE

                          ----------

                                  

                           

                          IS_ENTER_ONE_NULL  

                          ---------------------

                          ENTER_ONE is Null   

                           

                          no rows selected

                           

                          End of Example 3


                          4.1.1.19

                          Start of Example 3

                          :ENTER_ONE

                          ----------

                                   1

                          IS_ENTER_ONE_NULL  

                          ---------------------

                          ENTER_ONE is not null

                                   1

                          ----------

                                   1

                          End of Example 3