Skip to main content
Share Your Experience: Take the 2024 Developer Survey
18 votes

"New query window" shortcut for Oracle SQL Developer

In Oracle SQL Developer Application Goto: Tools->SQL Worksheet OR ALT+F10
Arjun 's user avatar
  • 181
9 votes

Connecting to SQL Server using Oracle SQL Developer

For a named instance, under port use "1433;instance=INSTANCENAME". Adjust port number accordingly.
Scooter's user avatar
  • 101
7 votes

dbms_output hard limit in SQL Developer

In SQL Developer, SET SERVEROUTPUT ON SIZE UNLIMITED sets the buffer to the maximum value 1000000 instead of unlimited (tested with 12.1.0.2.161018 database and SQL Developer 4.1.5.21). You can ...
Balazs Papp's user avatar
  • 40.7k
6 votes

How to check if current SQL session has uncommited changes?

There is also SELECT dbms_transaction.local_transaction_id FROM dual; which will show a transaction id if there are pending commits.
mivk's user avatar
  • 545
6 votes

Unable to see list of tables in Oracle SQL Developer

For where you are looking, you can only see the tables that exist in the schema that belongs to the user you supplied in your connection details. If you want to see 'the tables' - you need to expand ...
thatjeffsmith's user avatar
5 votes

sqldeveloper ORA-12505. TNS: listener does not currently know of SID given in connect descriptor

I had the same problem. And I solved it by using SERVICE NAME (an alias) instead of SID: 1. Open file tnsnames.ora from your ORACLE_HOME folder. In case you don't know its location, do the following ...
Bien Do's user avatar
  • 51
5 votes

Oracle SQL Developer Textcursor disappearing

Adding AddVMOption -Dsun.java2d.opengl=true to ~/.sqldeveloper/<version>/product.conf fixed it for me.
andredl's user avatar
  • 51
5 votes
Accepted

Oracle SQL Developer: Set the <null> value when exporting to CSV

Just set the below in your session, as you would do in SQL*Plus: set null "null" Note: this will not affect how NULLs are displayed in the data grid (you can set that from the menu: Tools - ...
Balazs Papp's user avatar
  • 40.7k
5 votes

Writing a simple SELECT Stored Procedure in Oracle PL/SQL

You need to pass in a REFCURSOR for the procedure to use as its output (OUT) parameter. Quick procedure to test with: CREATE OR REPLACE PROCEDURE passenger_details (p_passenger_details OUT ...
Philᵀᴹ's user avatar
  • 31.8k
5 votes

Where can I find old versions of SQL Developer (4.x)?

https://download.oracle.com/otn/java/sqldeveloper/sqldeveloper-22.2.1.234.1810-x64.zip https://download.oracle.com/otn/java/sqldeveloper/sqldeveloper-22.2.1.234.1810-no-jre.zip https://download.oracle....
Gustavo's user avatar
  • 163
4 votes
Accepted

Multiset operators on nested tables with non scalar types

MULTISET EXCEPT The element types of the nested tables must be comparable. Comparison Conditions Two objects of nonscalar type are comparable if they are of the same named type and there is a ...
Balazs Papp's user avatar
  • 40.7k
4 votes
Accepted

sqlplus SP2-0341 (line overflow during variable substitution)

SQL*Plus is not a data loading tool. Use the proper tools. SQL Developer can create CSV files. Instead of SQL*Plus, use SQL*Loader to load the data. SQL*Plus Limits
Balazs Papp's user avatar
  • 40.7k
4 votes

Long query causes problems

DON'T DO THAT That code is horrible. You should place ALL of the values in a Global Temporary Table (GTT) and JOIN to that table. Call commit or rollback to auto-clear the GTT.
Michael Kutz's user avatar
  • 4,829
4 votes
Accepted

Where can I find old versions of SQL Developer (4.x)?

Oracle removed the links, but the files are still there. Example: http://download.oracle.com/otn/java/sqldeveloper/sqldeveloper-4.1.3.20.78-no-jre.zip Of course before using that direct link, you need ...
Balazs Papp's user avatar
  • 40.7k
3 votes
Accepted

ORA-01017: invalid username/password; logon denied from sql developer but sqlplus logins

The problem was that I had too many instant clients and Oracle clients on my machine (one oracle client 'x86'). The instant clients were too old. The only working instant client for me is the '12.1....
Cavva79's user avatar
  • 151
3 votes

Oracle tnsping.exe - where to get/download it

You have to download the Database Client. Please note that this is not the same package as the Instant Client. Go to the Oracle DB download page Click on "See all" on the right column, next ...
galeop's user avatar
  • 131
3 votes

SQL Developer code formatting

Has actually moved in v17.4 Can be found in: Oracle SQL Developer -> Preferences... -> Code Editor -> Format and Optionally -> Advanced Format -> Custom Format
Thronk's user avatar
  • 1,388
3 votes
Accepted

Oracle 12c: textual search over all PL/SQL code

Provided that the following is true: 1) The function is NOT in a package 2) The function call is NOT dynamic SQL Then you can simply see what its dependencies are from dba_dependencies: SELECT * ...
Kris Johnston's user avatar
3 votes

Is it best practice to install client tools (like Oracle SQL Developer) on the database server or another machine?

Best practice would be to install and use it from client machines, since every tool running on the server is consuming some resources which will not be available to the Oracle server Installing it on ...
Akash's user avatar
  • 1,032
3 votes

SQL Developer suggesting I add aggregate to group by clause

Looks like a bug in (the parser of) the specific version of SQL Developer (4.1.5.21) you use. Update to the most recent version and if it shows the same behaviour, send a bug report to Oracle. You ...
ypercubeᵀᴹ's user avatar
3 votes
Accepted

Why does SQL Developer update Oracle Data Dictionaries while SQL*Plus does not?

SQL Developer automatically sets the below: alter session set plscope_settings='IDENTIFIERS:ALL'; You can use the same in SQL*Plus. (The default is IDENTIFIERS:NONE.)
Balazs Papp's user avatar
  • 40.7k
3 votes

SQLDeveloper: Unable to desc a table that exists

This must be because of MYTABLE being a synonym of a synonym, which is not supported on SQL Developer 4.1.3(must be a bug). Reference I have three users user3, user2 and user1 SQL> conn user3/...
atokpas's user avatar
  • 8,632
3 votes

SQLDeveloper: Unable to desc a table that exists

This error may happen when two objects are created with the same name. This problem occurs with at least version Version 17.4.1.054. For example, if a table and a trigger share the same name: ...
Jon Heller's user avatar
3 votes
Accepted

"Error while processing statement" running a query finding unique values with multiple values in other rows

I suspect you are using a Hive version that is before 0.13.0. In that case the problem is the AS keyword when giving alias to subquery. From HiveQL documentation Subqueries in the FROM Clause: ...
ypercubeᵀᴹ's user avatar
3 votes

SQL Developer: choose the tablespace where a new table will be stored

Check Advanced, and under Storage, Define Storage, where you can choose the tablespace.
Balazs Papp's user avatar
  • 40.7k
3 votes
Accepted

Missing right parenthesis after (or before) default 0, Oracle

The problem is with the ordering of the clauses. If you move the CHECK constraint after the DEFAULT 0 clause, then the table creates: CREATE TABLE ENCUENTROS ( elocal constraint ...
Albert Godfrind's user avatar
3 votes

See other schema's tables in SqlDeveloper

If you have the correct privileges to see the objects in other schemas you can see them by expanding the connection in the connections tab and looking for Other Users and you will be able to see all ...
Joe W's user avatar
  • 1,049
3 votes
Accepted

Ordering streets by their names and polysyllabic numbers in SQL query

I would separately introduce sorting based on the presence of special characters in HNR, in addition to sorting on the bits related to those characters that you have already implemented. You could try ...
Andriy M's user avatar
  • 23k

Only top scored, non community-wiki answers of a minimum length are eligible