3 Replies Latest reply on Sep 9, 2015 11:17 PM by Gaz in Oz

    Run SQL Plus in SQL Developer problem

    1273365

      I want to execute SQL Plus from SQL Developer (Version 4.1.1.19). I added it as an external
      tool using the following command line for the Arguments box.

       

      /K start cmd /k sqlplus.exe ${sqldev.dbuser}@${sqldev.conn} @${file.path}

       

      The problem is the macros don't seem to work. SQL Plus starts but won't log
      in. I also noticed posts about SQL Plus being on the Tools Menu but its not on mine

       

      Any help would be appreciated.

      Thank you.

        • 1. Re: Run SQL Plus in SQL Developer problem
          Gaz in Oz

          Hi,

           

          The "external tools" "macros" are actually context/focus sensitive.For example setting up an external tool,... sqlplus.exe, exactly like you have, If my cursor is not in the SQL Worksheet, then all the macros seem to yield null, i.e: when the SQL worksheet i'm logged into is NOT in focus I get this in the log, cmd does start, but, of course i don't get logged into sqlplus:

          C:\Users\xxxxxxx\AppData\Roaming\SQL Developer\system4.1.0.19.07\o.sqldeveloper.12.2.0.19.7\projects>cmd.exe /K start cmd /k sqlplus.exe @

          i.e blank macro return values.

           

          How ever when my SQL worksheet is in focus, cmd fires up, sqlplus starts, and i am prompted for the password and get logged in. (exiting sqlplus drops me back to the cmd prompt, exiting again closes cmd. (due to the second /k as opposed to /c as its cmd option)

          NOTE: there's a checkbox in the "Tools" - "External Tools.." - "Edit" - "Integration" setup to :Log output to Messages log"

           

          That macro $file.path} is null even with my SQL Worksheet "Focus"'d. (not sure when it is in focus). Your exmple (if it worked) would pass a directory to sqlplus to execute, should be a file too, ${file.name}. Hard code it and see if you can get it to execute that way...@X:\Path\To\File\File.sql instead of using macros.

          Barry MCGillin posted about something similar a while back: Barry McGillin: SQLPLus as an external tool in SQLDeveloper

          maybe that'll help too.

           

          BTW sqldeveloper recognizes many sqlplus commands, SET/SHOW, EXECUTE, etc. Also you can run a .sql file directly in a SQL Worksheet by using @<path\filename.ext>

          @C:\Oracle\SQL\uptimenixlike.sql
          
          
          
          
          

          Highlight it, hit F5 (Run script)

          SP2-0267: pagesize option 0 out of range (2 through 50000)
          TO_CHAR(SYSDATE,'""DYDD-MON-RRHH24:MI:SS""')||INSTANCE_NAME||':'||REGEXP_REPLACE
          --------------------------------------------------------------------------------
          Thu 03-Sep-15 14:01:27 dbname: up 25 days, 05:55,  sessions 79           
          
          
          
          
          
          

           

          my script (below) has "SET PAGES 0" to switch off headings, pagination,... that works in sqlplus but, as you can see there, sqldeveloper implementation PAGESIZE differently with 2 as the minimum! so like i said sqldeveloper supports some sqlplus commands, differently, exactly, or not at all.

          /* Mimick the LINUX uptime command, showing db stuff
             This is the nix format:
             $ oracle> uptime
              11:05:03 up 25 days,  5:21,  1 user,  load average: 0.09, 0.11, 0.12
             ###time         days, hh:mm, No. users, system load avg 1,    5,   15mins
          */
          SET FEEDBACK OFF PAGES 0
          SELECT TO_CHAR(SYSDATE, '" "Dy DD-Mon-RR HH24:MI:SS" "')||instance_name||':'||
                 REGEXP_REPLACE(CAST(INTERVAL '1' DAY * (SYSDATE - startup_time) AS
                                     INTERVAL DAY(3) TO SECOND(0)),
                                '^\+0{1,}([1-9]{1,}) ([0-9]{2}:[0-9]{2}):([0-9]{2})',
                                ' up \1 days, \2, ')||
                (SELECT ' sessions '||COUNT(*) FROM v$session)
          FROM v$instance
          /
          
           Thu 03-Sep-15 14:23:50 dbnamex: up 32 days, 20:33,  sessions 39
          
          
          

           

          Try

          SHOW ALL <F5>

          in your SQL worksheet, and there you are.

           

          Cheers,

           

          Gaz.

          • 2. Re: Run SQL Plus in SQL Developer problem
            1273365

            Thanks for the reply but I still can't get SQL Plus to login. I copied the arguments exactly from Barry McGillin's post and it still doesn't work.

             

            Thnaks

            • 3. Re: Run SQL Plus in SQL Developer problem
              Gaz in Oz

              please copy/paste what appears in the log tab