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

    Run SQL Plus in SQL Developer problem


      I want to execute SQL Plus from SQL Developer (Version 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



          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.\o.sqldeveloper.\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>


          Highlight it, hit F5 (Run script)

          SP2-0267: pagesize option 0 out of range (2 through 50000)
          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
          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



          SHOW ALL <F5>

          in your SQL worksheet, and there you are.





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

            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.



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

              please copy/paste what appears in the log tab