1 Reply Latest reply on Oct 18, 2018 8:25 PM by rp0428

    SQLcl Changes Working Directory on Windows when a .SQL script calls a script in another directory

    dkutyna_cl

      I wrote the below Python script to demonstrate the behavior.

      I found the issue in SQLcl 18.3.0.259.2029

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

      """Demonstration of odd SQLcl current working directory behavior on Windows.

       

      This script expects both sqlplus and sql.exe to be on the path.

       

      Setup:

        top.SQL - the main driving .SQL script in the root directory

        same.SQL - a .SQL script in the root directory to be called by top.SQL

        sub.SQL - a .SQL script in a sub-directory to be called by top.SQL

       

      Events:

        Execute top.SQL with either SQL*Plus or SQLcl.

        top.SQL calls an empty same.SQL.

        top.SQL calls sub.SQL.

        top.SQL completes.

        Add HOST call to same.SQL.

        Execute top.SQL again.

        top.SQL calls same.SQL.

        top.SQL calls sub.SQL.

        top.SQL completes.

       

      Odd Behavior:

        If top.SQL calls the non-empty same.SQL before calling sub.SQL,

        then the last HOST CD call displays the root directory, otherwise it

        displays the sub-directory.

      """

       

      import os

      import subprocess

       

      def run_for_exec(executable):

          print(f'{executable} top.SQL running sub-directory script first:')

          print()

       

          # Create top-level .SQL script

          with open('top.SQL', 'w+t', encoding='UTF-8') as top:

              # Write to top-level .SQL script

              top.writelines([

                  f'prompt {executable} top: current working directory\n',

                  'HOST CD\n',

                  '@same.SQL\n',

                  f'prompt {executable} top: current working directory after running same.SQL\n',

                  'HOST CD\n',

                  '@sub/sub.SQL\n',

                  f'prompt {executable} top: current working directory after running sub/sub.SQL\n',

                  'HOST CD\n'

              ])

             

          # Create empty same-level .SQL script

          with open('same.SQL', 'w+t') as same:

              None

             

          # Create sub-directory

          os.mkdir('sub')

         

          # Create empty sub-level .SQL script

          with open('sub/sub.SQL', 'w+t', encoding='UTF-8') as top:

              # Write to sub-level .SQL script

              top.writelines([

                  f'prompt {executable} sub: current working directory\n',

                  'HOST CD\n',

              ])

         

          # Execute the top-level script

          arg_list = [executable, '-S', '/nolog', '@top.SQL']

         

          exit_code = subprocess.run(args=arg_list, input='EXIT', encoding='UTF-8').returncode

         

          if exit_code != 0:

              raise RuntimeError(f'{executable} top.SQL returned exit code: ', exit_code)

         

          # Write HOST command to the same-level .SQL script

          with open('same.SQL', 'w+t', encoding='UTF-8') as same:

              same.writelines([

                 f'prompt {executable} same: current working directory\n',

                  'HOST CD\n',

              ])

             

          print(f'{executable} top.SQL running same-directory script first:')

          print()

       

          # Execute the top-level script again

          arg_list = [executable, '-S', '/nolog', '@top.SQL']

         

          exit_code = subprocess.run(args=arg_list, input='EXIT', encoding='UTF-8').returncode

         

          if exit_code != 0:

              raise RuntimeError(f'{executable} top.SQL returned exit code: ', exit_code)

         

          # Delete files

          os.remove('top.SQL')

          os.remove('same.SQL')

          os.remove('sub/sub.SQL')

          os.rmdir('sub')

       

      run_for_exec('sqlplus')

      run_for_exec('sql.exe')