4 Replies Latest reply: May 31, 2012 7:39 AM by PaoloM RSS

    Easy Connect and sqldev.conn issues

    PaoloM
      Hello everybody,
      while trying to add SQL*Plus as an external tool in SQL Developer 3.1, I noticed something very weird.

      When using the *${sqldev.conn}* macro in the Arguments field, I noticed that it will return a different result depending on how the DB connection was defined.

      If I define a DB connection with Connection Type = Basic, then *${sqldev.conn}* will return a string like "*server:port:SID*", while if I define the connection as TNS (thus referencing an existing entry in my tnsnames.ora), then ${sqldev.conn} will return the ENTIRE string stored in the tnsnames file for the active DB instance. I believe that a DB connection using "Custom JDBC URL" will return exactly the URL you enter (jdbc:oracle:thin...etc.), while I do not have an LDAP setup to verify what it will spit out in that case.

      I think that this kind of behaviour is a little unpredictable and that not all the external tools will understand the different kind of strings returned by ${sqldev.conn}, like it's the case with SQL*Plus. Anyway knowing it in advance will make it easier to choose how to define your DB connection, but of course this behaviour is not documented anywhere (please correct me if I am wrong).

      Now the main problem is that with a Basic connection type, I would expect SQL Developer to return an EasyConnect string, so that the external tool can directly understand it (assuming that your sqlnet configuration is correct), while this is not the case. You can easily verify that with Basic connection type, the *${sqldev.conn}* macro returns "*server:port:SID*" (that is NOT understood by Sql*Plus), while the correct Easyconnect syntax would be "*server:port/SID*" (note the slash in place of the last colon).

      Did anybody else see this bug? Can it be fixed in the next release?

      Currently to workaround the issues above, I will have to:
      1) redefine all my DB connections to use the Basic connection type;
      2) write a DOS batch script wrapper that does some black magic to convert "server:port:SID" string to an Easyconnect compatible string and pass it to Sql*Plus;
      but I would very much appreciate a built-in solution that makes your life easier at least with Sql*Plus.

      Any comments?

      Thanks,
      Paolo

      Edited by: PaoloM on 16-mag-2012 10.04
        • 1. Re: Easy Connect and sqldev.conn issues
          867504
          I am having the same issue with starting sql plus from sql developer. Any updates?
          • 2. Re: Easy Connect and sqldev.conn issues
            PaoloM
            Hi Jayesh,
            it seems that nobody else needs to start a SQL script in SQL*Plus from SQL Developer.

            Anyway here is the windows batch script I created to start the script opened in the current window in SQL*Plus:
            @echo off
            REM.-- Prepare the Command Processor --
            SETLOCAL ENABLEEXTENSIONS
            SETLOCAL ENABLEDELAYEDEXPANSION
            
            ::: -- Set the window title --
            set title=Running the selected script in Sql*Plus...
            TITLE %title%
            
            ::: Parameters are
            ::: %1 is the username
            ::: %2 is the password
            ::: %3 is the connection string in SQLDeveloper Basic format
            ::: %4 is the script file name with fully qualified path
            echo.
            set dbuser=%1
            set dbpwd=%2
            set dbconn=%3
            set sqlscript=%4
            
            echo.
            for /f "tokens=1,2,3 delims=: " %%a in ("%dbconn%") do set server=%%a&set port=%%b&set sid=%%c
            echo.DB Server: %server%
            echo.DB Port  : %port%
            echo.DB SID   : %sid%
            
            echo.
            echo.Executing script...
            sqlplus -S -L %dbuser%/%dbpwd%@%server%:%port%/%sid% @%sqlscript%
            
            echo.Script execution terminated.
            ::: -- End of script --
            To make my life easier, I have put this script in the directory where SQL*Plus executable resides and then I have created a new entry in the external tools as follows:
             
            Program Executable = C:\ORACLE\10.2.0\bin\run_in_sqlplus.cmd
            Arguments = ${sqldev.dbuser} ${promptl:label=Password} ${sqldev.conn} ${file.path}
            Run Directory = ${file.dir}
            If you want to interact with the script (e.g. for entering parameters), then you can set it up as follows instead:
             
            Program Executable = C:\WINDOWS\system32\cmd.exe
            Arguments = /K start cmd /K C:\ORACLE\10.2.0\bin\run_in_sqlplus.cmd ${sqldev.dbuser} ${promptl:label=Password} ${sqldev.conn} ${file.path}
            Run Directory = ${file.dir}
            It's not perfect, but it may give you a temporary solution.

            Hope it's helpful,
            Paolo
            • 3. Re: Easy Connect and sqldev.conn issues
              Gary Graham-Oracle
              Hi Paolo,

              Thanks for posting the script. Your approach for EZConnect, along with an earlier post that works for the TNS connection type,
              Re: sqlplus vs sqldeveloper

              covers the most common use cases, both for local and remote DBs. However, note this degenerate case for Basic / EZConnect:

              1. SQL Net names.directory_path specifies EZConnect
              2. Database is local
              3. Connection type is Basic
              4. The external tools definition works without including *@${sqldev.conn}* in the arguments section

              A bit of history:
              1. Prior to 1.5.x, a SQL*Plus context menu option was available for connections to a local database
              2. A general External Tools capability rolled out with 1.5.x, replacing the hard-wired SQL*Plus context menu option
              3. Both of these worked (I believe) by taking advantage of the degenerate case, but did not work for the TNS connection type
              4. At some point (not sure exactly when) the TNS support got fixed.

              In terms of typical SQL*Plus usage, the TNS alias and "no TNS alias" degenerate cases are the most important. I think general EZConnect support would be a real bonus, and something worth logging a feature request for on the SQL Developer Exchange. It would also be nice to have explicit documentation in the SQL Developer Help for external tools that run using a command line UI rather than a GUI.

              Regards,
              Gary Graham
              SQL Developer Team
              • 4. Re: Easy Connect and sqldev.conn issues
                PaoloM
                Gary,
                thanks again for your feedback.

                I agree that the different behaviour of the *${sqldev.conn}* macro should be at least mentioned in the documentation, because I believe it should not be up to the enterprising developer to "discover" how to start properly SQL*Plus from SQLDeveloper.

                It would be even better to have the possibility to choose explicitly which kind of output the *${sqldev.conn}* macro returns, but probably that's asking too much.

                Anyway I just created a feature request on the SQL Developer Exchange portal to require support for the EZConnect string, I hope it will be voted and added to the product soon:

                http://apex.oracle.com/pls/apex/f?p=43135:7:793243750822001::NO:RP,7:P7_ID:33681

                Thanks again,
                Paolo