7 Replies Latest reply on Jan 21, 2016 6:35 PM by Craig Elliott

    SQLCL change paths using CD

    Craig Elliott

      Hi,

       

      Anyone know of a way to transpose a windows path to a linux/mac path automagically for use in SQLCL tool using "cd" command?

      Someone sends me a script to run and they are on Windows, I have to go and alter the path manually each time before I can run it from my Mac or from the Linux .

       

      They will send "\Volumes\migration\uat\Project765\runAll.sql"

      I need "/Volumes/migration/uat/Project765/runAll.sql"

       

      Typically I want to CD into the path and then run scripts without having to retype a bunch of paths (trying to be efficient)

       

      Thanks.

       

      Craig

        • 1. Re: SQLCL change paths using CD
          thatJeffSmith-Oracle

          they send you a script(s) with Windows directory style notation?

           

          CD will actually change the directory that SQLcl reads/writes from/to

           

          But maybe the new script command would come in handy for you...

          • 2. Re: SQLCL change paths using CD
            Craig Elliott

            Hi Jeff

             

            How would the "script" command do that?

             

            script \Volumes\migration\uat\Project765\runAll.sql

            Could not load file: \Volumes\migration\uat\Project765\runAll.sql

             

            Could not load Scripting Engine for :sql

             

             

            Maybe I am missing something from my config?

             

            Thanks,

            Craig

            • 3. Re: SQLCL change paths using CD
              thatJeffSmith-Oracle

              you would use the script command to run some javascript to change up the .sql files to fix the paths...just an idea

               

              i'm still not exactly sure what your problem/question is though, just guessing based on your post

               

              'alter the path' - environment variable, hardcoded in the scripts, what exactly?

              1 person found this helpful
              • 4. Re: SQLCL change paths using CD
                Craig Elliott

                HI Jeff,

                 

                Thanks, I will look into the .js idea.

                I realized that was what the "script" reference was after I had replied.

                I will attempt to put something together that will either "alter the path", or enable me to run the sql using the Windows directory style notation from my Macbook terminal session, or my Linux machine, so that I do not have to manually change paths each time.

                 

                Thanks,

                Craig

                • 5. Re: SQLCL change paths using CD
                  Craig Elliott

                  Hi Jeff,

                   

                  This is what I ended up doing:

                  /* sqlcl_runsql.js */
                  //set prompt
                  var ps1= 'set sqlprompt "_DATE _USER@_CONNECT_IDENTIFIER - SQL> "'
                  sqlcl.setStmt(ps1);
                  sqlcl.run();
                  //
                  var sql="";
                  // adds the "@" and also quotes the path and filename in case of spaces
                  // use str.trim() to remove whitespaces before and after string
                  sql = "@'" + args[1].replace(/\\/g,'\/').replace(/smb:\/\/your_servername/gi,'').replace(/\/mainfolder/gi,'').replace(/\/dropoff/gi,'').trim() +"'"
                  // rebuild and display the sql from the arguments
                  ctx.write("Command:\n\t"+ sql + "\n\n");
                  sqlcl.setStmt(sql);
                  // run it
                  sqlcl.run();
                  //blank lines
                  ctx.write(" \n\n");
                  //done
                  
                  

                   

                   

                  Testing:

                  script sqlcl_runsql /p9/Dropoff/uat_project1/runme.sql

                  script sqlcl_runsql \MainFolder\p9\uat\uat_project7\runme.sql

                  script sqlcl_runsql \p3\Dropoff\runMe.sql

                  script sqlcl_runsql smb://SMBSERVER/MainFolder/projectX/Dropoff/runMe.sql

                  script sqlcl_runsql \p1\dropoff\uat\filename to run.sql

                   

                  Command:

                  @'/p9/uat_project1/runme.sql'

                  @'/p7/uat/uat_project7/runme.sql'

                  @'/p3/runMe.sql'

                  @'/projectX/runMe.sql'

                  @'/p1/uat/filename to run.sql'

                   

                  Still work in progress but gets me what I need today, since now I can just copy and past the path/script instead of retyping each time (and it lets me learn sqlcl script .js files ).

                   

                  Looking forward to when the alias command can be used to replace typing "script sqlcl_runsql".

                   

                  Thanks,

                  Craig

                  • 6. Re: SQLCL change paths using CD
                    Kris Rice-Oracle

                    The args[1] will only have the first argument which if there's a space will be upto that space.  You should rebuild the args into your script for spaces like this

                     

                    var cmd = "";

                    // skip 0 as it's the script name

                    for (var i=1; i < args.length; i++) {

                        cmd = cmd + " "  + args[i];

                    }

                    1 person found this helpful
                    • 7. Re: Re: SQLCL change paths using CD
                      Craig Elliott

                      Hi Kris,

                       

                      Thanks, I got that in my latest iteration:

                       

                      // set prompt
                      var ps1= 'set sqlprompt "_DATE _USER@_CONNECT_IDENTIFIER - SQL> "'
                      sqlcl.setStmt(ps1);
                      sqlcl.run();
                      //
                      // Loop in case of spaces in filename or path
                      var sql="";
                      for(var i=1;i<args.length;i++){
                        sql = sql + " " + args[i];
                      }
                      // adds the "@" and also quotes the path and filename in case of spaces
                      // use str.trim() to remove whitespaces before and after string
                      sql = "@'" + sql.replace(/\\/g,'\/').replace(/smb:\/\/your_servername/gi,'').replace(/\/mainfolder/gi,'').replace(/\/dropoff/gi,'').trim() +"'"  
                      // rebuild and display the sql from the arguments
                      ctx.write("Command:\n\t"+ sql + "\n\n");
                      sqlcl.setStmt(sql);
                      //
                      // run it
                      sqlcl.run();
                      //blank lines after
                      ctx.write(" \n\n");
                      //done
                      

                       

                       

                      Thanks,

                      Craig