Nearly 7,000,000 Oracle professionals use SQL Developer on a regular basis. Have a question about Oracle SQL Developer? Searched this blog and couldn’t find the answer? Ask away!
If your question is about Oracle Database, SQL, PL/SQL, etc – go Ask Tom!
Feel free to ask anything you want, but I’ll feel free to send you to Support or our Forums if it goes sideways.
Note: This page has turned out way more successful(?) than I would have ever imagined. Please keep these things in mind when asking questions.
- I am NOT support. Don’t expect me to log bugs for you, or give you official timelines on bug fixes, enhancements, or product releases.
- I AM NOT SUPPORT. Don’t open an SR with My Oracle Support AND leave a question here. Pick one and go with it, and when in doubt, go to My Oracle Support.
- I try to answer questions as quickly as possible. If you don’t get an answer, ask me for an update. I may have just forgotten or overlooked your request.
Go!
7,788 Comments
Hi
We would like to protect our source in DEV from being edited by the developer until the source has been ‘checked out’. Is this a concept that SQL developer can support?
Thanks
Loz
I would not grant privs to your devs. Instead, they would run local instances with pl/sql they got from source control. They’d make their changes, and commit the source, and then you’d have automated ‘builds’ that updated the the DEV database PL/SQL. There’s a 3rd party extension that does this client side – blocks compiles on objects that are checked out, but it’s not cheap.
Hey Jeff,
Ok, confession time. I had reverted from 4.0 back to 3.2 after failing to get comfortable with the find/replace ribbon and a general loss in productivity.
Can you tell me if there’s a config option on 4 to use the find/replace as a popup instead of that sticky ribbon at the top? Maybe I can try again…
And yes, I know….petty. 🙁
Sorry, but no. Make sure you were trying 4.1 and not 4.0 as we made several tweaks to get the newer F&R up to snuff.
Tell me where it hurts, maybe I can provide some relief.
Just muscle memory I guess. You do something long enough…it’s hard to break the habit. ctrl-r/tab/tab/ctrl-v etc…
I find myself doing a lot of back-tracking in 4.1. Even after getting in the flow of the new tool – I relapse. Gotta retrain the old dog.
Thanks for the confirmation.
Cheers!
Hi Jeff,
I am brand new to SQL Developer and the small taste of power I have experienced so far is incredible.
I have been saving my favorite SQL statements and noticed the default save directory in the Create SQL File dialog box is not the same as what I specified in Database – Worksheet – Select default path to look for scripts.
Are these two different animals, is it possible to specify a default path to save new SQL files?
Your Top 10 Preferences to Tweak post was very helpful and a great place to start!
Thanks for keeping this great site going,
Jason
If you’re using version 4.1, then the directory/file pickers for opening/saving files should remember the last 10 or so locations PER editor.
So the worksheet should remember this when opening/saving files in the worksheet.
But the directory picker in the preferences for the worksheet would be a different animal…
>>Are these two different animals, is it possible to specify a default path to save new SQL files?
Yes, completely disjoined. But it should remember the last several directories you saved a SQL file to – shown on the left of the dialog.
hi Jeff,
Am a huge fan of your blogs, which are greatly informative and have the most accurate information.
I had a question for which I have been hesitating to ask, as it might sound too much to ask for.
I was reading through about the Code Templates in SQL Developer. Is there a way i can create a shortcut of code template say SS and parameterize it. So if am typing ss-employee , it should result in select * from employee (for example employee table here).
Today i am able to user SS which brings up, select * from on screen perfectly. I just had a thought if there was a way in which i could pass the table name as parameter along with ss and get the output as select * from table (from the parameter)
[ This is coming from the .kshrc file i had created on unix box and used $1 to substitute the input passed and that acts as a parameter. ]
Let me know if the above did make sense or not or I could rephrase it differently.
Hi Jeff Huge fan of your posts…. Can you please post more descriptive information about Connect By in Oracle…how to use it!! When to use it!!!
Hello,
I’m trying to format the headers of my SQL output so that some really long names can be printed over multiple lines and take up less width on the screen. I’m able to get it to work in the Script Output window:
SQL> column really_long_name clear;
SQL> select ‘short’ as really_long_name
from dual;
REALLY_LONG_NAME
——————————–
short
SQL> column really_long_name heading ‘Really|Long|Name’;
SQL> select ‘short’ as really_long_name
from dual;
Really
Long
Name
——————————–
short
But I’m not able to get this to work in the Query Result window. Is there a way to do something similar to this in that window? Ideally, I would have the column header names printing over multiple lines and export the data to my clipboard in tab-delimited format, which I would then paste into Excel.
I’m currently using version 4.1.2.20, with the pre-packaged Java.
Thanks!
the grids don’t support multi-line column headers – better to go shorter vs longer
Okay, I thought that might be the case, but figured I’d ask on the off chance that I was just missing something.
Thanks for taking the time to answer!
Hi Jeff,
When running “info v$ v_$ views” it doesn’t work at all or it the output is incomplete.
Ex:
SQL> info v$session
ORA-20005: ORU-10034: context argument must be integral, 0 to 9
ORA-06512: at “SYS.DBMS_UTILITY”, line 153
ORA-06512: at line 1
SQL> info v_$session
Columns
NAME DATA TYPE NULL DEFAULT COMMENTS
SADDR RAW(8 BYTE) Yes
SID NUMBER Yes
SERIAL# NUMBER Yes
AUDSID NUMBER Yes
PADDR RAW(8 BYTE) Yes
USER# NUMBER Yes
USERNAME VARCHAR2(30 BYTE) Yes
COMMAND NUMBER Yes
OWNERID NUMBER Yes
TADDR VARCHAR2(16 BYTE) Yes
LOCKWAIT VARCHAR2(16 BYTE) Yes
STATUS VARCHAR2(8 BYTE) Yes
SERVER VARCHAR2(9 BYTE) Yes
SCHEMA# NUMBER Yes
SCHEMANAME VARCHAR2(30 BYTE) Yes
OSUSER VARCHAR2(30 BYTE) Yes
PROCESS VARCHAR2(24 BYTE) Yes
what version of Oracle DB are you on George?
Jeff, thank you indeed for getting back to me so quickly.
Oracle DB version is 11.2.0.4.0
Hi,
Just a few quick questions. I’ve used lots of data modelling tools, including Designer 2000, way back when. I just want to use the ERD. If there is a source of answers for these types of questions, please just point me to it.
1. Is the info (definitions, diagrams) stored locally or can I point it at a server?
2. Can the data model be shared with other modellers?
3. Do we have any form of versioning?
er, that’s it.
regards
jsa
1. locally, although you can create a reporting repository in an Oracle Database if you want to query your designs
2. yes
3. yes, uses subversion under the covers
wow, thanks for such a prompt response, Jeff.
Hello Jeff, I found you post on font sizing but I am looking for changing interface icons size too. I do not like to use scaling on high-DPI monitor for many reasons and I would like to go native. Look-and-feel: Windows, doubled font size and doubled icons size would do exactly that, unfortunately only first two are possible. It generally helps with most applications. Is there a config file defining icons somewhere? I didn’t have the luck finding it so far.
I don’t think so.
Hmm. So you really think all icons are hard-coded? That means scaling is the only option how to run SQL Developer on modern screens, that’s quite a bummer.:-(
Hi,
I’ve just started using SQL Developer after previously using SQL Management Studio. One really useful feature of Management Studio was being able to set up query shortcuts (e.g. Ctrl+4 = select top 10* of highlighted code). Is this possible in SQL Developer – it is so useful for checking tables and joins, and saved me so much time in Management Studio.
Thanks for your help
Kinda, the closest analog would be our SQL Code Templates. You give them a name, and then they all are activated by the same key sequence.
Jeff,
I just upgraded to 4.1.3 and like the new feature to get a popup of the field list when connected to a 12c DB. The feature works when I open a new worksheet on a 12c connection, however, it doesn’t seem to work if I open an existing worksheet and then connect to a 12c connection. Is there a way to get the feature to work in my second scenario above?
Thanks,
David
Hi Jeff, I try to use the sqlldr in sql developer tool as you suggested. But I got the below error. Kindly request you to guide me in this regard.
Error Message
“The program can’t start because oraodm12.dll is missing from your computer. Try reinstalling the program to fix this problem”
Hi Jeff,
We have a problem querying a production database from SQL Developer.
Connected as user X we run a select * from X.TABLE from SQL Developer and the program hangs. From OEM, we saw the following query but we never see the original query.
SELECT :”SYS_B_0″ type, username owner, username object_name, null column_name, null column_id, null data_type
FROM all_users
WHERE rownum <=:"SYS_B_1" and username like :1 union all
SELECT object_type type, owner, object_name, null column_name, null column_id, null data_type
FROM all_objects
WHERE object_type =:"SYS_B_2" and object_name not like :"SYS_B_3" and rownum <=:"SYS_B_4" and object_name like :2 union all
SELECT object_type type, owner, object_name, null column_name, null column_id, null data_type
FROM all_objects
WHERE object_type =:"SYS_B_5" and object_name not like :"SYS_B_6" and rownum <=:"SYS_B_7" and object_name like :3
Nevertheless, if we run the same query from SQL PLus it runs well.
We are using SQL Developer 4.1.3 (latest version).
Regards,
Alejandro.
View > Log > Statements…you’ll see everything that goes across the JDBC driver to your database
Thank you Jeff,
We found that the problem could be related with caps and quotes in the query.
If we run this query it runs well:
select * from “X”.”MYTABLE”;
However, if we run the query without the quotes it hungs.
select * from X.MYTABLE;
We are connecting to a PDB in an Oracle 12c Database.
The problem exist only when connecting with the X user (PDB user) and not with CDB user (like system).
Regards,
Alejandro.
those are equivalent queries, in theory…so, weird.
you’ll want to file an SR with the database team I think though…and try it in SQL*Plus or with a THICK connection in SQLDev to see if it’s a db issue or maybe a JDBC driver issue
The query runs well with SQL Plus .
We also try the query from SQL Dev with the option Use Oracle Client and use OSI/Thick Driver, but we get the same behavior, the program hungs and the we didn’t get any result.
Do you still think is a db issue?
all we do is send the query over, so we’re the messenger here
do a session trace and see what’s going on IN the database
Hi Jeff,
We opened a SR with this issue.
They gave us this explanation about it, but we still some doubts about the answer and the one that you said:
“If granting DBA causes the query and all else to work, then that is a requirement for SQLDEV. SQLDEV has no privileges in and of itself since it is just a GUI tool for SQL.
Sometimes it is a matter of increasing the JVM heap, but if just a privilege grant solves the issue, it is unlikely that it is a configuration issue.
If you can discover which privileges from the DBA role are required, then you can follow this note to add them to your target users:
How to provide Data Dictionary access to SQL Developer users without granting the DBA role ( Doc ID 1274851.1 )”
The query we run doesn’t include CATALOG tables (select * from X.TABLE) but we see in the db a query to ALL_TABLES and ALL_OBJECTS tables (from EM).
Granting the SELECT_CATALOG_ROLE solves the problem but we think this is too much to resolve the issue.
Regards,
Alejandro.
Hi Jeff, I´m using Sql Developer Data Modeler 4.1.3
In my logical model there is a many-to-many-Relationship between two entities. This Relationship has one additional attribute.
Forward engineering of the logical model to a relational model leads to
1) two tables — one for each entity
2) one table for the many-to-many-relationship
3) two 1:n – foreign-key dependencies from each “entity”-table to the “relationship”-table
but
4) two 1:n foreign-key dependencies from one “entity”-table to the other “entity”-table
How to supress No. 4?
In the relational model No. 4 leads to adding primary key of one “entity”-table as foreign-key to the other “entity”-table and vice versa. In my opinion adding the primary keys of the “entity”-tables to the “relationship”-table is sufficient.
Please help
I’ve been using SQL Developer to try to export data for an old website. For options like JSON or CSV, however, SQL Developer doesn’t escape quotes, which you could imagine wreaks havoc on tag properties in HTML. Are there any settings on exports that could improve the situation?
I don’t know how you’re storing your websites, one big record, CLOB, per page/site, or many rows to build a page…but take a look at our built-in functions like REPLACE and REGEXP – you can manipulate your data so you can handle the quote characters any which way you’d like.
I was wondering how (or if it is possible) to have tab guidelines (ie for each tab space when set at x) there is a faint line to show the tab spaces ie at each x columns you have a tab guide.
our IDE doesn’t support that, sorry
Thanks for answering. There are a lot of settings and you never know where something might be hiding.
Hi.
I am using the 5.04 version of the mySQL driver with ver 4.03 SQL D on a Windows 8.1 laptop. I have this 32 bit version of Java installed, jre-7u25-windows-i586. I can run 2 simple queries against a simple table with 20 rows, for example select * from . On the third try I get this error, Query execution was interrupted. I looked online and here but can’t find out why this is occurring. Thanks.
mysql, sqldev, jdbc driver, and java are ALL pretty old
try upgrading the jdbc driver first, then mysql
Hi Jeff,
I am upgrading from SQL Dev 1 (yes 1!!) 32-bit to v 4-64 bit. and I exported the connections from the old version hoping to import them into version 4. The error I’m getting is: “import file is in old format” Now I understand that it is a very old version, but we have about 50 connections, is there a quick way to import them? Or do I have to recreate the connections? I’m dreading your reply 🙂
Regards
Vic
Not so bad news. Go get version 2.1 – you’ll have to click through the ‘older versions’ links on the download pages a few times. Upgrade to that, and then try upgrading from that to version 4.1.
And then don’t wait 9 years to upgrade again 🙂
Hi Jeff,
Thanks for that. Agree not too bad really. Don’t blame me!! I’m only the messenger!!
Vic
I am new to SQL Developer. Your website has greatly helped me get up and running. I am trying to join and query tables from two Oracle connections. I have tried the BRIDGE command, but get the error below. Is there a way in SQL Developer to query on tables from different connections at the same time? I am using Version 4.0.2.15; Java Platform
1.7.0_55.
Thanks!
ORA-00900: invalid SQL statement
00900. 00000 – “invalid SQL statement”
*Cause:
*Action:
At my work, we’ve gone total Virtual Desktop (VDI), using a Citrix Receiver, where all the applications are on layers. For some reason, my Network won’t allow me to utilize Oracle SQL Developer on a VDI layer, so I had to install SQL Developer onto my laptop, which works, but not when I’m at home and want to connect to the network, I have to use VDI. My only way to use SQL developer, is to leave my laptop at work and remote into it from home via VDI. Is there a trick to installing Oracle SQL Developer onto a VDI layer?
Hi Jeff,
How would you go about Data Modelling and Database Cloud Schema Service?
I mean, you can’t connect SQL Developer Data Modeler to Schema Cloud and reverse engineer or forward engineer, right?
I have tables in Schema Cloud and I would like to transform the datamodel into something normalized and so on.
Should I install Database Express for the datamodel locally and use a cart again to upoad the new? How do I import the tables into a DataModel?
Or any other tips?
Thanx,
Hasso Schaap
Today, if you wanted to model your schema service, you’d build it locally, and then upload it. You’re right, you can’t model it directly from the service.
Tomorrow, that limitation will be removed. We’re working on support to do exactly what you are wanting to do for that service.
I have screwed up my SQL Developer? I am seeing a series of unprintable characters when I open a SQL script in SQL Developer 4.0.3.16. I can open the same script in textpad and it displays fine. The file contains some set commands and a SQL script. I was changing things relative to exporting AL32UTF8 data output, so I suspect I brought this on myself. Just not sure how to get it back to normal?
not sure you’ve screwed up SQL Developer but maybe you’ve screwed up your file? make sure you’re using a Code Editor FONT that supports all the characters you’re using – try to match what you have textpad for example
What is the default Code Editor Font?
dialog something or other, why?
I found my problem on this link:
https://community.oracle.com/thread/714713?start=0&tstart=0
I was dealing with AL32UTF8 data from ASPAC and was trying to influence the output file to stop converting double byte characters into ?. I had changed the Tools> Preferences> Environment> Encoding setting to UTF32. That was a mistake, but everything worked and the data was output retaining the Chinese characters. That is when the issue with opening SQL scripts began, but a few days had passed, so I did not make the connection. Changing the encoding to UTF8 has solved my issue.
I’m replacing my desktops with 64-bit Windows 7, so I also upgraded to SQL Developer 4.1.3 with Java 8 included (up from v.1.5.5). Everything seems to running fine, except:
• I cannot open a table tab to save my life. Single-Click, Double-Click, Key Return… none of it works. Single-clicking an PL/SQL object in the connections tab opens its read-only tab without issue, and right-clicking –> “Edit” works perfectly too… but clicking on a table or view does nothing. I’ve renamed my appdata folder, reverted to default settings, and tried it with v.4.0.1 as well… no joy.
Could you help me out? Thank you in advance!
– Marc
you didn’t by any chance extract your 4.1.3 directory on top of your 1.5.5 directory did you? that can cause weird things like what you’re describing to happen. if so, take the zip and re-extract it to a FRESH directory.
No, I extracted it to a fresh directory, and since posting the question this morning, I verified the same issue on a coworker’s PC too. She’s been using this Windows install for months on an Intel i3 chipset, and my PCs are fresh installs a week old running on Intel i7 chipsets. We don’t even have the same underlying versions of Java installed, even though we’re using SQL Developer with Java 8 included.
– Marc
Hi Jeff,
I’m using Unit Test in SQL Developer and connecting it to Jenkins. I need to do everything from command line and although I don’t have any problems running test or uploading them, I don’t know how to delete them.
Could you tell me what is the command for deleting test from command line?
Should it be sth similar to:
“sdcli unittest -exp -teardown”?
For this I also do not know the proper way.
Thank you in advance.
if you don’t see a delete option in the CLI help, then you’ll need to write a function or run a DELETE against the repository table to manually delete the rows representing your tests
Hi Jeff,
I have copied a table (CAT_COL_DATA) as CAT_COL_DATA_TMP as first step to generate a temporary equivalent table.
I have manually changed names but I am getting a length error and I can not find why.
While previewing DDL for both tables I get the following error in the copied one:
— ERROR: Column CAT_COL_DATA_TMP.ITEM_CODE check constraint name length exceeds maximum allowed length(30)
ITEM_CODE column is defined as DOMAIN data type. It works fine for CAT_COL_DATA but not for CAT_COL_DATA_TMP.
If I change ITEM_CODE to logical NUMBERIC the error goes away so I suspect it is something related to the domain.
Any tips? Thanks a lot.
Note: ITEM CODE Domain is defined as NUMERIC Size 4 Values Range between 0000 and 9999.
CREATE TABLE CAT_COL_DATA
(
COL_DATA_ID NUMBER NOT NULL ,
ITEM_CODE NUMBER ,
ITEM_EAN13 NUMBER ,
COL_DATA_01 VARCHAR2 (4000) ,
COL_DATA_02 VARCHAR2 (4000) ,
COL_DATA_03 VARCHAR2 (4000) ,
COL_DATA_04 VARCHAR2 (4000) ,
COL_DATA_05 VARCHAR2 (4000) ,
COL_DATA_06 VARCHAR2 (4000) ,
COL_DATA_07 VARCHAR2 (4000) ,
COL_DATA_08 VARCHAR2 (4000) ,
COL_DATA_09 VARCHAR2 (4000) ,
COL_DATA_10 VARCHAR2 (4000) ,
COL_DATA_11 VARCHAR2 (4000) ,
COL_DATA_12 VARCHAR2 (4000) ,
COL_DATA_13 VARCHAR2 (4000) ,
COL_DATA_14 VARCHAR2 (4000) ,
COL_DATA_15 VARCHAR2 (4000) ,
COL_DATA_16 VARCHAR2 (4000) ,
COL_DATA_17 VARCHAR2 (4000) ,
COL_DATA_18 VARCHAR2 (4000) ,
COL_DATA_19 VARCHAR2 (4000) ,
COL_DATA_20 VARCHAR2 (4000) ,
CAT_DESCR_ID NUMBER NOT NULL
)
LOGGING ;
ALTER TABLE CAT_COL_DATA ADD CHECK ( ITEM_CODE BETWEEN 0000 AND 9999) ;
ALTER TABLE CAT_COL_DATA ADD CONSTRAINT CAT_COL_DATA_PK PRIMARY KEY ( COL_DATA_ID ) ;
ALTER TABLE CAT_COL_DATA ADD CONSTRAINT CAT_COL_DATA_EAN13_UN UNIQUE ( ITEM_EAN13 ) ;
ALTER TABLE CAT_COL_DATA ADD CONSTRAINT CAT_COL_DATA_CAT_DESCR_FK FOREIGN KEY ( CAT_DESCR_ID ) REFERENCES CAT_DESCR ( CAT_DESCR_ID ) NOT DEFERRABLE ;
CREATE SEQUENCE CAT_COL_DATA_COL_DATA_ID_SEQ START WITH 1 NOCACHE ORDER ;
CREATE OR REPLACE TRIGGER CAT_COL_DATA_COL_DATA_ID_TRG BEFORE
INSERT ON CAT_COL_DATA FOR EACH ROW WHEN (NEW.COL_DATA_ID IS NULL) BEGIN :NEW.COL_DATA_ID := CAT_COL_DATA_COL_DATA_ID_SEQ.NEXTVAL;
END;
/
CREATE TABLE CAT_COL_DATA_TMP
(
COL_DATA_ID NUMBER NOT NULL ,
ITEM_CODE NUMBER ,
ITEM_EAN13 NUMBER ,
COL_DATA_01 VARCHAR2 (4000) ,
COL_DATA_02 VARCHAR2 (4000) ,
COL_DATA_03 VARCHAR2 (4000) ,
COL_DATA_04 VARCHAR2 (4000) ,
COL_DATA_05 VARCHAR2 (4000) ,
COL_DATA_06 VARCHAR2 (4000) ,
COL_DATA_07 VARCHAR2 (4000) ,
COL_DATA_08 VARCHAR2 (4000) ,
COL_DATA_09 VARCHAR2 (4000) ,
COL_DATA_10 VARCHAR2 (4000) ,
COL_DATA_11 VARCHAR2 (4000) ,
COL_DATA_12 VARCHAR2 (4000) ,
COL_DATA_13 VARCHAR2 (4000) ,
COL_DATA_14 VARCHAR2 (4000) ,
COL_DATA_15 VARCHAR2 (4000) ,
COL_DATA_16 VARCHAR2 (4000) ,
COL_DATA_17 VARCHAR2 (4000) ,
COL_DATA_18 VARCHAR2 (4000) ,
COL_DATA_19 VARCHAR2 (4000) ,
COL_DATA_20 VARCHAR2 (4000) ,
CAT_DESCR_ID NUMBER NOT NULL
)
LOGGING ;
— ERROR: Column CAT_COL_DATA_TMP.ITEM_CODE check constraint name length exceeds maximum allowed length(30)
ALTER TABLE CAT_COL_DATA_TMP ADD CHECK ( ITEM_CODE BETWEEN 0000 AND 9999) ;
ALTER TABLE CAT_COL_DATA_TMP ADD CONSTRAINT CAT_COL_DATA_TMP_PK PRIMARY KEY ( COL_DATA_ID ) ;
ALTER TABLE CAT_COL_DATA_TMP ADD CONSTRAINT CAT_COL_DATA_EAN13_TMP_UN UNIQUE ( ITEM_EAN13 ) ;
ALTER TABLE CAT_COL_DATA_TMP ADD CONSTRAINT CAT_COL_DATA_CAT_DESCR_TMP_FK FOREIGN KEY ( CAT_DESCR_ID ) REFERENCES CAT_DESCR_TMP ( CAT_DESCR_ID ) NOT DEFERRABLE ;
CREATE SEQUENCE CAT_COL_DATA_TMP_COL_DATA_ID START WITH 1 NOCACHE ORDER ;
CREATE OR REPLACE TRIGGER CAT_COL_DATA_TMP_COL_DATA_ID BEFORE
INSERT ON CAT_COL_DATA_TMP FOR EACH ROW WHEN (NEW.COL_DATA_ID IS NULL) BEGIN :NEW.COL_DATA_ID := CAT_COL_DATA_TMP_COL_DATA_ID.NEXTVAL;
END;
/