6 Replies Latest reply on Mar 10, 2016 9:21 AM by Bollineni86

    How to connect SQL * Plus from SQL Developer

    Bollineni86

      Hi All,

       

      Please let me know how to connect SQL * Plus from SQL Developer.

       

      we are connecting remote database.

       

      SQL Developer Version 4.1.3.20.

      Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit.


      Thanks,

      Bollineni

       

        • 1. Re: How to connect SQL * Plus from SQL Developer
          Selvakumar.Nagulan

          Hi,

           

          SQL Developer is a tool used to communicate with database. SQL* Plus is also a tool used for the same purpose. When you establish a connection and connect to the desired database, a work sheet can be opened (Right click on database connection and click "Open SQL worksheet") and all the commands that are used to work on SQL* Plus can be run using this work sheet. The results will be opened in the results window below the worksheet.

           

          See these notes for more details.

          http://www.oracle.com/technetwork/developer-tools/sql-developer/query-intro-1-161240.html

          http://www.thatjeffsmith.com/archive/2013/01/a-sql-developer-primer-open-a-new-worksheet/

           

          Thanks!

          Selvakumar Nagulan

          • 2. Re: How to connect SQL * Plus from SQL Developer
            Bollineni86

            Thanks for the update Selvakumar, But in SQL developer worksheet we will execute a single command in multiple lines, but in sql * plus will we will get an error. Blank lines also not consider in SQL developer, consider in SQL * plus...

             

            So no of differences are there execution of scripts in SQL Develoepr and SQL * Plus.

             

            That's why i am asking how to connect SQL* Plus from SQL developer or other way...

            I am using remote database, so when i type Connect command or sqlplus commands in my command promt it will give an error like unknown command..because database not installed in my machine.

             

            Main reason is we are developing scripts in SQL developer, moving scripts to higher environments they are executing scripts in SQL*Plus.. so my scripts are getting failed due some reasons like line break or blank lines or colon missing etc...

            To escape those errors adding below commands to my scripts but every time i am getting a new issue...

            set ver off

            set define off

            set timing on

            set trimspool on

            set echo on

            SET SQLBLANKLINES ON

             

            So please let me know how to connect sql*plus..to stop all these issues.

            • 3. Re: How to connect SQL * Plus from SQL Developer
              Selvakumar.Nagulan

              Hi,

              SQL developer worksheet we will execute a single command in multiple lines

              If you want to run a block of commands, for eg. see  below

              select name from v$database;

              select open_mode from v$database;

               

              Now we have 2 lines of code separated by ";". If you would like to run this as a single unit, then select both the lines and click F5 key in the keyboard or click "Run script" button near the Green "Run Statement" button in the SQL Developer. This will run the statement as a single script as you would be running in the SQL* Plus.

               

              As I already mentioned, both SQL* Plus and SQL Developer are separate tools. You can't use with the help of other. If you would like to run the SQL* Plus to connect to the database, you can download "Instant client" from Oracle downloads page and install in your client box to connect to the remote database.

              You can download from here http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

              Or if you would require a older version of client software, you may do so in the downloads page.

               

              Thanks!

              Selvakumar Nagulan

              1 person found this helpful
              • 4. Re: How to connect SQL * Plus from SQL Developer

                Thanks for the update Selvakumar, But in SQL developer worksheet we will execute a single command in multiple lines, but in sql * plus will we will get an error. Blank lines also not consider in SQL developer, consider in SQL * plus...

                 

                So no of differences are there execution of scripts in SQL Develoepr and SQL * Plus.

                Correct - as you were already told - those are TWO DIFFERENT tools designed to do two different jobs in two different ways.

                That's why i am asking how to connect SQL* Plus from SQL developer or other way...

                You can't - they are TWO DIFFERENT tools - you do NOT connect one to the other anymore than you can connect a wrench to a screwdriver.

                I am using remote database, so when i type Connect command or sqlplus commands in my command promt it will give an error like unknown command..because database not installed in my machine.

                Most likely you are getting that 'unknown error' because you are NOT providing the correct parameters/command for connecting to that remote database. But since you haven't shown us WHAT you are really doing or HOW you are doing it we can't really help with that.

                 

                Both Sql*plus and Sql Dev can connect to remote databases but they do it different ways.

                Main reason is we are developing scripts in SQL developer, moving scripts to higher environments they are executing scripts in SQL*Plus..

                The solution is: DON'T DO THAT!

                 

                Sql developer is NOT a tool for writing scripts for sql*plus. The newer versions DO include a scripting engine that can be run from the command-line but that app is NOT a 'duplicate' of sql*plus and has different commands.

                 

                To write scripts all you need is a text editor (notepad, wordpad, vi, etc). Then you need to test those scripts BEFORE you move them anywhere to be used.

                 

                To escape those errors adding below commands to my scripts but every time i am getting a new issue...

                set ver off

                set define off

                set timing on

                set trimspool on

                set echo on

                SET SQLBLANKLINES ON

                 

                So please let me know how to connect sql*plus..to stop all these issues.

                You can STOP all of those issues by using a text editor to write your scripts and sql*plus to test them. You will NEVER be successful writing/testing scripts in Sql Dev that will actually be executed in Sql*Plus unless those scripts ONLY contain the simplest commands that are supported by Sql Dev. Some sql*plus commands will likely NEVER be supported.

                 

                They are TWO DIFFERENT tools. They are NOT designed to work together in the way you describe.

                 

                Use one tool or use the other tool - using both will NOT work for what you are trying to do.

                • 5. Re: How to connect SQL * Plus from SQL Developer
                  Bollineni86

                  Thank you very much for your detailed description RP0428.

                  • 6. Re: How to connect SQL * Plus from SQL Developer
                    Bollineni86

                    Hi Selva,

                     

                    Thank you very much. I am able to download the instant client, from the link which you have shared.

                     

                    Thanks,

                    Bollineni