I have few SQL queries which has very low query running performance and I want to check the query execution plan for this query. I am trying to execute the below query but its not showing any query execution plan. Its only display message plan FOR succeeded. I dont know is there any settings that we have to do in oracle sql developer to vies explain plan for query :

    EXPLAIN PLAN FOR 
    Select SO.P_OPTION_ID FROM
    SIMSIM 
       JOIN P_TYPE PT on PT.KEY=SIM.P_TYPE_KEY JOIN P_CONFIG PC ON PC.ID=PT.PRODUCT_CONFIG_ID
    JOIN P_OPTION PO ON PO.OPTION_KEY=PC.DEFAULT_PRODUCT_OPTIONS JOIN S_OPTION SO ON SO.SERVICE_ID=SIM.ASSIGNED_TO_SERVICE_ID
    JOIN AVV_NO AN ON SIM.ASSIGNED_ANUMBER_ID = AN.ID
     where SO.STATUS_ID IN (20,40) 
     and SO.ID &lt; to_char(SYSDATE - numtodsinterval (  1,&#39;MINUTE&#39; ), &#39;YYYYMMDDHH24MISS&#39;)||&#39;0000&#39;
     and SO.ID &gt; to_char(SYSDATE - numtodsinterval (  1, &#39;HOUR&#39; ), &#39;YYYYMMDDHH24MISS&#39;)||&#39;0000&#39;
    and NOT EXISTS(SELECT ID from TEMP_BPL T WHERE T.ID = SO.ID );

How do I view the Explain Plan in Oracle Sql developer?

Is there a way to display all the views currently set on an oracle database via sql developer?

Thanks.

Display all views on oracle database

I want to insert special character `&amp;` in my insert statement. My insert is:

    INSERT INTO STUDENT(name, class_id) VALUES (&#39;Samantha&#39;, &#39;Java_22 &amp; Oracle_14&#39;);

If I try to run this query I am getting a popup and it asks me to enter value for `Oracle_14`. 

How can I enter special characters like `&amp;` in the insert statement for oracle db?



How to enter special characters like &quot;&amp;&quot; in oracle database?

I&#39;m trying to connect to a database using SQLDeveloper and I get the following error:

    An Error was Encountered performing The requested operation:
    
    IO Error: Conection reset
    
    Vendor code 17002.

Could you please help me?

Vendor code 17002 to connect to SQLDeveloper

I just installed Oracle 11g, and tried to start Oracle SQL developer so as to start writing queries.

It is asking me:

    Enter the full pathname for the java.exe file .

Where do I find this? I did a global search for `java.exe` and am sure did not get some Oracle related pdf files. Also my Oracle is installed out of users/vas.


Where to get this Java.exe file for a SQL Developer installation

In **Oracle SQL Developer**, one can list the data in a table using the Data tab when viewing a table. there where can add, delete, edit or just viewing records of table currently viewd

the problem is that we often really need to view more than one table at once, then we need more than one tab opened, thing that i dont know how to do that, any sugestion. tq in advance

Oracle SQL Developer multiple table views

I&#39;m using Oracle SQL Developer 3.0. Trying to figure out how to export a query result to a text file (preferably CSV). Right clicking on the query results window doesn&#39;t give me any export options.

How to export query result to csv in Oracle SQL Developer?

By default, Oracle SQL developer displays date values as `15-NOV-11`. I would like to see the time part (hour/minute/second) by default. 

Is there a way to configure this within Oracle SQL Developer?

How can I set a custom date time format in Oracle SQL Developer?

In [Oracle SQL Developer][1], if I&#39;m viewing the information on a table, I can view the constraints, which let me see the foreign keys (and thus which tables are referenced by this table), and I can view the dependencies to see what packages and such reference the table. But I&#39;m not sure how to find which tables reference the table.

For example, say I&#39;m looking at the `emp` table. There is another table `emp_dept` which captures which employees work in which departments, which references the `emp` table through `emp_id`, the primary key of the `emp` table. Is there a way (through some UI element in the program, not through SQL) to find that the `emp_dept` table references the `emp` table, without me having to know that the `emp_dept` table exists?


  [1]: http://www.oracle.com/technology/products/database/sql_developer/index.html

How can I find which tables reference a given table in Oracle SQL Developer?

Here&#39;s what SQL Develoepr is giving me, both in the results window and when I export:

    CREATION_TIME       
    ------------------- 
    27-SEP-12
    27-SEP-12
    27-SEP-12

Here&#39;s what another piece of software running the same query/db gives:

    CREATION_TIME       
    ------------------- 
    2012-09-27 14:44:46 
    2012-09-27 14:44:27 
    2012-09-27 14:43:53 

How do I get SQL Developer to return the time too?


SQL Developer is returning only the date, not the time. How do I fix this?

I&#39;ve been given a user account to a SQL Server database that only has privileges to execute a stored procedure. I added the JTDS SQL Server JDBC jar file to SQL Developer and added it as a Third Party JDBC driver. I can successfully log in to the SQL Server database.  I was given this syntax for running the procedure:

    EXEC proc_name &#39;paramValue1&#39; &#39;paramValue2&#39;

When I run this as either a statement or a script, I get this error:

    Error starting at line 1 in command:
    EXEC proc_name &#39;paramValue1&#39; &#39;paramValue2&#39;
    Error report:
    Incorrect syntax near the keyword &#39;BEGIN&#39;.

I tried wrapping the statement in `BEGIN/END`, but get the same error.  Is it possible to call the procedure from SQL Developer?  If so, what syntax do I need to use?

How to Execute SQL Server Stored Procedure in SQL Developer?

Many of my users do not use SQLPlus. I cannot give them alter user. We expire passwords every 60 days. 

I can&#39;t get the SQLPlus command &quot;password&quot; to work in SQL developer.


1. When I hit run, I get an invalid command error
2. When I hit run script, nothing happens.

I don&#39;t want to write them a package to change their passwords since we have a lot of databases. Do I have a better option? 

Changing password with Oracle SQL Developer

I am running a non-english Windows 7 system, and apparently Oracle SQL Developer (version 3.0.04.34, 64-bit) tries to auto-guess my preferred language based on the OS. Is there any way to change the language to english?

I&#39;ve tried the tip found at http://misteratmisis.com/infotech/how-change-oracle-sql-developer-interface-japanese-english, namely adding the VM options

&lt;pre&gt;
AddVMOption -Duser.language=en
AddVMOption -Duser.country=US
&lt;/pre&gt;

to file ide.conf in directory

&lt;pre&gt;
/ide/bin
&lt;/pre&gt;

within the SQL Developer installation directory, as suggested, but that didn&#39;t help.

**Solution (edited on 13.09.2012):**

I decided to try again the solution proposed by Alex K. and this time it worked, namely adding

    AddVMOption  -Duser.language=en

to the file `sqldeveloper.conf` file located in `sqldeveloper\bin\` folder. I presume that it did work now but not before, because meanwhile I have changed my UAC (user access control) settings in Windows 7 so that I am administrator by default. What happened before was probably that even though the changes appeared to be there, the configuration file read by the program was another one (shadow copy).

**Solution for Mac OS X (added on 26.02.2016):**

Edit file at

`/Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin/sqldeveloper.conf` 

and add the line

    AddVMOption  -Duser.language=en

(Tested with SQL Developer 4.1.3.20)

How can I change the language (to english) in Oracle SQL Developer?

I am evaluating [Oracle SQL Developer][1].

My `tnsnames.ora` is populated, and a `tnsping` to a connection defined in `tnsnames.ora` works fine. Still, SQL Developer does not display any connections.

[Oracle SQL Developer Soars ][2] mentions, that if
&gt; you have Oracle client software and a tnsnames.ora file already installed on your machine, Oracle SQL Developer will automatically populate the Connections navigator from the net service names defined in tnsnames.ora.

I also tried to set my `TNS_ADMIN` environment variable, but after restarting SQL Developer there are still no connections displayed.

Any ideas?&lt;br&gt;
Anyone successfully working with SQL Developer and tnsnames.ora?

  [1]: http://www.oracle.com/technology/products/database/sql_developer/index.html
  [2]: http://www.oracle.com/technology/oramag/oracle/06-may/o36sql.html

Use tnsnames.ora in Oracle SQL Developer

When I try to execute this statement in Oracle SQL Developer 2.1 a dialog box *&quot;Enter Substitution Variable&quot;* pops up asking for a replacement value for *TOBAGO*,

    update t set country = &#39;Trinidad and Tobago&#39; where country = &#39;trinidad &amp; tobago&#39;;

How can I avoid this without resorting to `chr(38)` or `u&#39;trinidad \0026 tobago&#39;` which both obscure the purpose of the statement?

How to avoid variable substitution in Oracle SQL Developer

I have installed Oracle Database 11g Express Edition on my pc (windows 7)
and I have installed Oracle SQL Developer as well. 

I want to create a simple database to start with, maybe with one table or two and then use Oracle SQL Developer to insert data and query it.

When I open Oracle SQL Developer, it asks me to create a new connection, therefore it assumes that a database has already been created. 

So my question is, how do I create an initial database in Oracle 11g?




How to create a new database after initally installing oracle database 11g Express Edition?

I wanted to print the value of a particular variable which is inside an anonymous block. I am using Oracle SQL Developer. I tried using `dbms_output.put_line`. But it is not working. The code which I am using is shown below.

    SET SERVEROUTPUT ON

    DECLARE

      CTABLE USER_OBJECTS.OBJECT_NAME%TYPE;
      CCOLUMN ALL_TAB_COLS.COLUMN_NAME%TYPE;
      V_ALL_COLS VARCHAR2(500);
  
      CURSOR CURSOR_TABLE
        IS
        SELECT OBJECT_NAME 
        FROM USER_OBJECTS 
        WHERE OBJECT_TYPE=&#39;TABLE&#39;
        AND OBJECT_NAME LIKE &#39;tb_prm_%&#39;;
      
      CURSOR CURSOR_COLUMNS (V_TABLE_NAME IN VARCHAR2)
        IS
        SELECT COLUMN_NAME
        FROM ALL_TAB_COLS
        WHERE TABLE_NAME = V_TABLE_NAME;
   
    BEGIN
    
      OPEN CURSOR_TABLE;

      LOOP
        FETCH CURSOR_TABLE INTO CTABLE;
        EXIT WHEN CURSOR_TABLE%NOTFOUND;
    
        OPEN CURSOR_COLUMNS (CTABLE);
    
        V_ALL_COLS := NULL;
    
        LOOP
          FETCH CURSOR_COLUMNS INTO CCOLUMN;
          V_ALL_COLS := V_ALL_COLS || CCOLUMN;
          IF CURSOR_COLUMNS%FOUND THEN
            V_ALL_COLS := V_ALL_COLS || &#39;, &#39;;
          ELSE
            EXIT;
          END IF;
        END LOOP;
     
        DBMS_OUTPUT.PUT_LINE(V_ALL_COLS);
    
      END LOOP;
      CLOSE CURSOR_TABLE;

    END;

And I am getting the output only as `anonymous block completed`. 


Printing the value of a variable in SQL Developer

I am using Oracle SQL (in SQLDeveloper, using the SQL Worksheet).  I would like to print a statement before my select, such as

    PRINT &#39;Querying Table1&#39;;
    SELECT * from Table1;

What do I use to Print / show text output?  It&#39;s not Print, because that gives me the error: Bind Variable `Table1` is NOT DECLARED.  DBMS_OUTPUT.PUT_LINE is an unknown command.  (Obviously, I&#39;m an inexperienced SQLDeveloper and Oracle user.  There must be some synonym for Print, but I&#39;m having trouble finding help on it without knowing what it is.)



 



Print text in Oracle SQL Developer SQL Worksheet window

While attempting to execute SQL insert statements using [Oracle SQL Developer][1] I keep generating an &quot;Enter substitution value&quot; prompt:

    insert into agregadores_agregadores 
    (
     idagregador,
     nombre,
     url
    ) 
    values 
    (
     2,
     &#39;Netvibes&#39;,
     &#39;http://www.netvibes.com/subscribe.php?type=rss\&amp;amp;url=&#39;
    );
I&#39;ve tried [escaping the special character in the query][2] using the &#39;\&#39; above but I still can&#39;t avoid the ampersand, &#39;&amp;&#39;, causing a string substitution.


  [1]: http://www.oracle.com/technology/products/database/sql_developer/index.html
  [2]: http://download.oracle.com/docs/cd/B10501_01/text.920/a96518/cqspcl.htm

Oracle SQL escape character (for a &#39;&amp;&#39;)

I&#39;m trying to connect to an oracle database with SQL Developer. 

I&#39;ve installed the .Net oracle drivers and placed the `tnsnames.ora` file at  
`C:\Oracle\product\11.1.0\client_1\Network\Admin`

I&#39;m using the following format in tnsnames.ora:

    dev =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.XXX.XXX)(PORT = XXXX))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = idpdev2)
        )
      )

In SQL Developer, when I try to create a new connection, no TNS-names show up as options.

Is there something I&#39;m missing?


Oracle TNS names not showing when adding new connection to SQL Developer

I am trying to run a stored procedure that has multiple in and out paramaters. The procedure can only be viewed in my Connections panel by navigating Other Users | &lt;user&gt; | Packages | &lt;package&gt; | &lt;procedure&gt;

If I right click &lt;procedure&gt;, the menu items are &quot;Order Members By...&quot; and &quot;Create Unit Test&quot; (greyed out). The ability to &quot;Run&quot; the procedure does not seem possible when it&#39;s accessed by user.

I have been trying to find an example of how to create an anonymous block so that I can run the procedure as a SQL file, but haven&#39;t found anything that works.

Does anyone know how I can execute this procedure from SQL Developer? I am using Version 2.1.1.64.

Thanks in advance!

**EDIT 1:**

The procedure I want to call has this signature:

    user.package.procedure(
       p_1 IN  NUMBER,
       p_2 IN  NUMBER,
       p_3 OUT VARCHAR2,
       p_4 OUT VARCHAR2,
       p_5 OUT VARCHAR2,
       p_6 OUT NUMBER)

If I write my anonymous block like this:

    DECLARE
       out1 VARCHAR2(100);
       out2 VARCHAR2(100);
       out3 VARCHAR2(100);
       out4 NUMBER(100);
    BEGIN
       EXECUTE user.package.procedure (33,89, :out1, :out2, :out3, :out4);
    END;

I get the error:

    Bind Varialbe &quot;out1&quot; is NOT DECLCARED
    anonymous block completed

I&#39;ve tried initializing the out* variables:

       out1 VARCHAR2(100) := &#39;&#39;;

but get the same error:

**EDIT 2:**

Based on Alex&#39;s answer, I tried removing the colons from in front of the params and get this:

    Error starting at line 1 in command:
    DECLARE
       out1 VARCHAR2(100);
       out2 VARCHAR2(100);
       out3 VARCHAR2(100);
       out4 NUMBER(100);
    BEGIN
       EXECUTE user.package.procedure (33,89, out1, out2, out3, out4);
    END;
    Error report:
    ORA-06550: line 13, column 17:
    PLS-00103: Encountered the symbol &quot;USER&quot; when expecting one of the following:

       := . ( @ % ; immediate
    The symbol &quot;:=&quot; was substituted for &quot;USER&quot; to continue.
    06550. 00000 -  &quot;line %s, column %s:\n%s&quot;
    *Cause:    Usually a PL/SQL compilation error.
    *Action:


Run Stored Procedure in SQL Developer?

I want to use Oracle SQL Developer to generate an ER diagram for my DB tables but I am new to Oracle and this tool.

What is the process for creating an ER diagram in SQL Developer?

How to generate an entity-relationship (ER) diagram using Oracle SQL Developer

I have a csv unload process i am running in sqldeveloper, a process window popped up showing the progress of the export and it had an option to run the process in background, I picked the option but now how do I know if the process is complete?

How to see if the background process is complete in SQLDEVELOPER

I downloaded Windows 64-bit with JDK 8 included of SQL Developer 18.1.

To run Oracle SQL Developed I just unzipped the file and run the sqldeveloper.exe contained in the folder.

Unfortunately, as you can see in the image the size of everything is extremely small and very difficult to see.[![enter image description here][1]][1]

This issue appears exclusively with this software.

How can I resolve it? 

  [1]: https://i.stack.imgur.com/g3Znw.png

How to increase size of GUI/font/everything of Oracle SQL Developer 18.1

I&#39;ve just made the switch from MS sql server to Oracle.  The query tool I am using is Oracle SQL Developer.  The problem I am having is that I cannot get the query results window to stick around. The query results window being the Grid view for the results.  It seems to show up arbitrarily when I open a new &quot;sql worksheet&quot;.  But not always.  Then once I run a query, it disappears the next time I run one.

Can anyone point me to a consistent way to leave this results grid active? Thanks so much.

Oracle SQL Developer - Query results window with grid missing

I&#39;m trying to optimize a query but don&#39;t quite understand some of the information returned from **Explain Plan**. Can anyone tell me the significance of the OPTIONS and COST columns? In the OPTIONS column, I only see the word FULL. In the COST column, I can deduce that a lower cost means a faster query. But what exactly does the cost value represent and what is an acceptable threshold?

Understanding the results of Execute Explain Plan in Oracle SQL Developer

I&#39;m trying to connect to a PostgreSQL 9.1 database using Oracle SQL Developer 3.0.04, but I&#39;m not having any success so far.

First, if I add a third party driver on preferences, when adding a new connection there&#39;s no tab for PostgreSQL (it works fine for MySQL though). I&#39;m using a JDBC4 version 9.1 driver, but I tried a JDBC3 of the same version and still get the same thing.

Second, there&#39;s nothing like manual configuration tab when adding a new connection. The closest is the Advanced option on Oracle tab, where I can provide a custom URL, but it fails because complains about the selected Driver (of course).

Finally, I got connected importing a connection from an XML file (contents below), but it displays only my schemas and doesn&#39;t show my tables inside them.

So, my question is: does Orable SQL Developer support PostgreSQL connections? Is there any other way to have my tables being displayed in the ObjectViewer?

    &lt;?xml version = &#39;1.0&#39; encoding = &#39;UTF-8&#39;?&gt;
    &lt;References xmlns=&quot;http://xmlns.oracle.com/adf/jndi&quot;&gt;
        &lt;Reference name=&quot;Lumea&quot; className=&quot;oracle.jdeveloper.db.adapter.DatabaseProvider&quot; credentialStoreKey=&quot;Lumea&quot; xmlns=&quot;&quot;&gt;
            &lt;Factory className=&quot;oracle.jdeveloper.db.adapter.DatabaseProviderFactory&quot;/&gt;
            &lt;RefAddresses&gt;
                &lt;StringRefAddr addrType=&quot;user&quot;&gt;
                    &lt;Contents&gt;lumea&lt;/Contents&gt;
                &lt;/StringRefAddr&gt;
                &lt;StringRefAddr addrType=&quot;subtype&quot;&gt;
                    &lt;Contents&gt;thirdParty&lt;/Contents&gt;
                &lt;/StringRefAddr&gt;
                &lt;StringRefAddr addrType=&quot;customUrl&quot;&gt;
                    &lt;Contents&gt;jdbc:postgresql://localhost:5432/versates&lt;/Contents&gt;
                &lt;/StringRefAddr&gt;
                &lt;StringRefAddr addrType=&quot;SavePassword&quot;&gt;
                    &lt;Contents&gt;true&lt;/Contents&gt;
                &lt;/StringRefAddr&gt;
                &lt;StringRefAddr addrType=&quot;password&quot;&gt;
                    &lt;Contents&gt;myencryptedpass&lt;/Contents&gt;
                &lt;/StringRefAddr&gt;
                &lt;StringRefAddr addrType=&quot;driver&quot;&gt;
                    &lt;Contents&gt;org.postgresql.Driver&lt;/Contents&gt;
                &lt;/StringRefAddr&gt;
                &lt;StringRefAddr addrType=&quot;DeployPassword&quot;&gt;
                    &lt;Contents&gt;true&lt;/Contents&gt;
                &lt;/StringRefAddr&gt;
            &lt;/RefAddresses&gt;
        &lt;/Reference&gt;
    &lt;/References&gt;

Oracle SQL Developer and PostgreSQL

I created the table Test_Project2 in Oracle SQL Developer. After that I realized that the column proj_name is of a small size, so I decided to modify the column using the follwoing statement

	ALTER TABLE TEST_PROJECT2 MODIFY proj_name VARCHAR2(300);
	
but for some reason Oracle SQL Developer underscores the semi-colon with red and I do not what is mistake and how to correct it

**Test_Project2**:

	CREATE TABLE Test_Project2 (
	proj_id number(30),
	proj_name VARCHAR2 (30),
	proj_desc VARCHAR2(300)
	);

how to modify the size of a column

I have Oracle SQL Developer already installed and am able to connect to and query Oracle databases.

Using Help -&gt; Check for Updates I was able to install the Oracle MySQL Browser extension but there are no connection options for MySQL databases.

How do you connect to a MySQL database using Oracle SQL Developer?

I have exported database from Oracle SQL developer tool into .sql file. Now I want to run this file which is of size 500+ MB.

I read about running scripts [here][1], but I didn&#39;t understand the way. Is there any command or query by which we can run this sql script by providing path?


  [1]: https://stackoverflow.com/questions/13137258/oracle-sql-developer-how-to-dynamicly-run-sql-scripts

How to run .sql file in Oracle SQL developer tool to import database?

How to export Oracle DB data using SQL Developer? I need all data, tables, constraints, structure and so on.

All Oracle Sqldeveloper Solutions on Gang of Coders

Total of 31 Oracle Sqldeveloper Solutions