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,791 Comments
Jeff,
This is a Oracle SQL Developer Data Modeler question.
Is there a way or a dialog that will allow me to (re)define a relation between two tables, that are in my logical model, using columns that already exist in both tables and will not rename the existing columns on related tables?
For instance
Yesterday I made PERSON and USER table.
Each has respective PERSON_ID and USER_ID as primary key.
I made no relationship between PERSON and USER of any kind.
Lastly I used USER_ID twice as foreign key on many tables (for ex TABLE_A.CREATED_USER_ID and TABLE_A.UPDATED_USER_ID)
Today, I would like to define the relationship between PERSON and USER as Identifying (i.e. Person_ID 5 is User_ID 5) without impacting the existing column names on the USER table or USER child tables.
Thanks,
Shawn
use the fk properties dialog and click on the column field to set it to the existing column instead – it will then prompt you to delete the one it created for the key
example here
sqlcl question
How can I get DDL to replace existing files in stead of appending to it?
SQL> help save
SAVE
—-
Saves the contents of the SQL buffer in a script. The
buffer has no command history list and does not record SQLcl commands.
SAV[E] [FILE] file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]
SQL> help ddl
DDL
—
DDL generates the code to reconstruct the object listed. Use the type option
for materialized views. Use the save options to save the DDL to a file.
DDL [ [] [SAVE ]]
Would have been nice to be able to get the same options for DDL as well.
And another strange behaviour I just noticed, is when I try to use DDL and SAVE for partitioned tables.
The SAVE option doesn’t seem to work for partitioned tables (or indexes)
SQL> help DDL
DDL
—
DDL generates the code to reconstruct the object listed. Use the type option for materialized views. Use the save options to save the DDL to a file.
DDL [ [] [SAVE ]]
Ok, so I try this: DDL TAB_NAME TABLE SAVE tab_name.sql
SQL> DDL TAB_NAME TABLE SAVE tab_name.sql
Object TABLE SAVE TAB_NAME.SQL TAB_NAME not found
And, without the Object type, like this:
SQL> DDL TAB_NAME SAVE tab_name.sql
Multiple objects exist with the name TAB_NAME. Please specify one of the following types: TABLE,TABLE SUBPARTITION,TABLE SUBPARTITION,TABLE SUBPARTITION,… (x the number of subpartitions this table has)
But if I try without SAVE it Works like a dream:
SQL> DDL TAB_NAME TABLE
CREATE TABLE “TEST”.”TAB_NAME”
( “ID” NUMBER(15,0) NOT NULL ENABLE,
…
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “TEST_DATA”
PARTITION BY RANGE (“ID”)
SUBPARTITION BY LIST (“SUBLIST”)
…
Thanks, sounds like def bugs, will take a look at these on Monday.
As a workaround, use SPOOL.
Thanks, Jeff.
I have a couple of other observations for you as well.
The first is that when creating scripts for all tables in a schema it seems to run out of open cursors. I had to increase this in order to get it to run without errors. Perhaps the cursors are not closed until the end?
The other has to do with passing arguments to .js-script when running from command line in windows. These arguments does not seem to be available within the script when accessing them like this:
for(var arg in args) {
ctx.write(arg + “:” + args[arg]);
ctx.write(“\n”);
}
>>The first is that when creating scripts for all tables in a schema it seems to run out of open cursors.
How are you creating the scripts?
The scripts are made in .js-files.
A little shortened, but the essence is like this:
script
sqlcl.setStmt(“SET DDEL PRETTY ON”)
sqlcl.run();
…
sqlcl.setStmt(“SET DDL TABLESPACE ON”);
sqlcl.run()
var instance_project =util.executeReturnOneCol(“SELECT SUBSTR(UPPER(INSTANCE_NAME,5,4) AS INSTANCE_NA;E FROM V$INSTANCE”);
var user = util.executeReturnOneCol(“SELECT user from DUAL”);
var binds = {};
binds.owner = user;
binds.project = instance_project;
var sql = “SELECT o.owner, o.object_name, o.object_type, o.created, …. “+
” FROM DBA_OBJECTS o WHERE … “;
var ret = util.executeReturnList(sql,binds);
for (i=0; i<ret.length; i++) {
var row = ret[i];
sqlcl.setStmt("DDL "+row.OWNER+"."row.OBJECT_NAME+" SAVE "+row.OBJECT_NAME+".sql");
sqlcl.run();
}
/
exit
is this the actual code? it has a few typos
sqlcl.setStmt(“SET DDEL PRETTY ON”) — should be
sqlcl.setStmt(“SET DDL PRETTY ON”)
var instance_project =util.executeReturnOneCol(“SELECT SUBSTR(UPPER(INSTANCE_NAME,5,4) AS INSTANCE_NA;E FROM V$INSTANCE”); — should be
var instance_project =util.executeReturnOneCol(“SELECT SUBSTR(UPPER(INSTANCE_NAME,5,4) AS INSTANCE_NAME FROM V$INSTANCE”);
the UTIL stuff is setup to close cursors as it goes, auto-magically. What version of SQLcl are you running?
Sorry, no – I just typed it in from a different computer.
The code runs ok, but uses all open curors so I had to increase the value quite a bit.
Here is the actual code. It basically generates DDL for all tables changed after 01.01.2016.
script
var ddl_path = “C:/GIT/dvh/db-repo”;
sqlcl.setStmt(“SET DDL PRETTY ON”);
sqlcl.run();
sqlcl.setStmt(“SET DDL STORAGE OFF”);
sqlcl.run();
sqlcl.setStmt(“SET DDL REF_CONTSTRAINTS OFF”);
sqlcl.run();
sqlcl.setStmt(“SET DDL CONSTRAINTS_AS_ALTER ON”);
sqlcl.run();
sqlcl.setStmt(“SET DDL SEGMENT_ATTIBUTES OFF”);
sqlcl.run();
sqlcl.setStmt(“SET DDL TABLESPACE ON”);
sqlcl.run();
var instance_project = util.executeReturnOneCol(“SELECT SUBSTR(UPPER(INSTANCE_NAME),5,4) as INSTANCE_NAME FROM V$INSTANCE”);
var user = util.executeReturnOneCol(“SELECT user FROM DUAL”);
var avgrensing_dato = util.executeReturnOneCol(“SELECT ‘01012016’ as dato FROM DUAL”);
var binds = {};
binds.owner = user;
binds.project = instance_project;
binds.object_type = ‘TABLE’;
binds.deploy_date = avgrensing_dato;
var sql = “SELECT o.owner, o.object_name, o.object_type, o.created, o.last_ddl_time, o.timestamp, :project AS project “+
” FROM dba_objects o WHERE o.object_type = :object_type “+
” AND o.owner = :owner “+
” AND o.last_ddl_time > to_date(:deploy_date,’DDMMYYYY’) “+
” ORDER BY o.object_name”;
var ret = util.executeReturnList(sql,binds);
ctx.write(“Tables…\n”);
// loop the results
for (i = 0; i < ret.length; i++) {
var row = ret[i];
var v_path = "/"+row.PROJECT+"/"+row.OWNER+"/"+row.OBJECT_TYPE+"/"+row.OBJECT_NAME+".sql ";
ctx.write("DDL "+row.OWNER+"."+row.OBJECT_NAME+" "+row.OBJECT_TYPE+" SAVE "+ddl_path+"/"+row.PROJECT+"/"+row.OWNER+"/"+row.OBJECT_TYPE+"/"+row.OBJECT_NAME+".sql\n");
sqlcl.setStmt("DDL "+row.OWNER+"."+row.OBJECT_NAME+" SAVE "+ddl_path+"/"+row.PROJECT+"/"+row.OWNER+"/"+row.OBJECT_TYPE+"/"+row.OBJECT_NAME+".sql ");
sqlcl.run();
}
/
exit
The sqlcl-version is 4.2.0.16.260.1205
I have a couple of subtypes in my logical model. They are supposed to inherit the attributes of the supertype, but they are not in the list. Attributes from other types of relationships (foreign keys) show up. Is there a way to see these attributes within the logical model or are they not added until the relational model?
Using SQL Developer 4.2 I get an Error encountered popup. With the text
An error was encountered performing the requested action:
Closed Connection
Vendor code 17008
Doing the same action in 4.1.5 I do not get any errors.
I am using Thick Driver with a client of 11.2. I have only reproduced in Reports. I get the popup if I have a report open something other the sqldeveloper has focus and return to sqldev the popup will be there. Can also reproduce locking screen, Windows 7 Alt-L, and returning to sqldev in a couple of minutes.
I am using Oracle SQL developer 4.2 and found an issue with the copy of large records
select sql_fulltext from v$sql;
In other versions you can copy the entire statement from the Query Result. With 4.2 just the text which you can see. Seems like a bug or a setting?
Other then that great release.
it’s a bug with CLOBs, it’ll get fixed for the official release
Waiting for the official release then.
Keep up the good work
I am using SQL Developer 4.1.5.21 with Mac OS Sierra 10.12 (16A323). It was working fine before Sierra. However, now, when I click on model or constraint or SQL tab in table view, it hangs forever. It shows window stating the it is trying to load module SQL data modeler. Is this a known issue? Also, I see following in logging window:
Sequence 97 — Error in RequestProcessor oracle.ideimpl.usages.UsagesTrackerImpl$3
It finally came back given enough time. Is there a way to pre-load these modules during start up?
export your connections (with passwords), and then rename or delete the system4.1.5 directory in your $HOME/.sqldeveloper directory.
run again, restore your connections, and see if that fixes things up
Thanks Jeff,
Tried that. It worked. I still see “loading data modeler” but it comes back within 45 seconds. That is much better than more than 10 minutes it used before. 🙂
i’ve seen it take awhile to load an extension, but 45 seconds is atrocious.
I use SQLDeveloper on a daily basis – sometimes even more often than that.
I tend to open some files, tables and reports together based on the task I am working on. I suspect that the “Document Groups” feature (Window -> Document Groups) would be great help, but I haven’t been able to figure out how to use it – maybe it’s just the wrong idea.
I searched SQLDeveloper’s help for info regarding this, but haven’t find anything useful there.
Meanwhile, I have to keep closing all files and manually opening and pinning each table, view, report and script file I need when working on a given task. Could you help me?
What I want is to quickly and automatically recreate the environment I had when working on a given task – script files, tables, reports; all reopened together.
Thanks in advance and keep up the great work Jeff!
no way to do what you want today – but it’s something we’ve thought about, building a sort of project that would let you organize db connections, objects, and files.
I’ll take this comment as another vote for “yes, please build this!”
Indeed!
Somehow similar is this other situation: while working I am called to attend a meeting or some other distraction. When I come back, the session has timed out. I must close and reopen the connection to the database, therefore losing all the tables I had open. Big pain.
No, you should be able to right-click on the connection and do ‘reconnect.’
I know! It rarely works. Also, I don’t see any error messages. Just right-click, ‘reconnect’, and still not connected.
Is it OK to talk about this here, or should be filing a bug somewhere else ? I don’t want to bother you…
TIA
It should always work – if not, the connection isn’t available to ‘bring back’ – You can try a THICK connection using an oracle client.
I am using SQL Developer Version 4.1.5.21 on Mac OS Sierra,
java version “1.8.0_102”
Java(TM) SE Runtime Environment (build 1.8.0_102-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.102-b14, mixed mode)
and when trying to configure OCI using instantclient_12_1 I get an error:
Testing the Instant Client located at /Users//instantclient_12_1
Testing client directory … OK
Testing loading Oracle JDBC driver … OK
Testing checking Oracle JDBC driver version … OK
Driver version: 12.1.0.2.0
Testing testing native OCI library load … Failed:
Error loading the native OCI library
The native OCI driver could not be loaded. The system propertyjava.library.path contains the entries from the environment variable DYLD_LIBRARY_PATH. Check it to verify that
the expected native library directory /Users//instantclient_12_1 is present and precedes any other client installations.
java.library.path = /Users//Library/Java/Extensions:/Library/Java/Extensions:/Network/Library/Java/Extensions:/System/Library/Java/Extensions:/usr/lib/java:.
Any idea how to workaround this?
First – do you really need an instant client? If it’s just to use a TNSNames file – that’s not required.
Otherwise, the error message tells you where to start.
“Check it to verify that
the expected native library directory /Users//instantclient_12_1 is present and precedes any other client installations.
java.library.path = /Users//Library/Java/Extensions:/Library/Java/Extensions:/Network/Library/Java/Extensions:/System/Library/Java/Extensions:/usr/lib/java:.”
Also, configuring Instant Client on a Mac is not fun now b/c of the OS changes Apple made
See this…
Hi Jeff,
Thank you for your reply, it was helpful, I was able to waorkaround this by adding:
export DYLD_LIBRARY_PATH=/Users/username/instantclient_12_1
to:
/Applications/SQLDeveloper.app/Contents/MacOS/sqldeveloper.sh
Now java.library.path is set correctly to:
/Users/username/instantclient_12_1:/Users/username/Library/Java/Extensions:/Library/Java/Extensions:/Network/Library/Java/Extensions:/System/Library/Java/Extensions:/usr/lib/java:.
and it works:
Testing the Instant Client located at /Users/username/instantclient_12_1
Testing client directory … OK
Testing loading Oracle JDBC driver … OK
Testing checking Oracle JDBC driver version … OK
Driver version: 12.1.0.2.0
Testing testing native OCI library load … OK
Success!
SQLDeveloper and instantclient are the only tools available on Mac OS to connect to an Oracle database.
Thank You,
Codrut
I must add however that I have disabled SIP:
csrutil disable
There are lots of java based solutions out there that support Oracle Database. From Oracle, you have SQL Developer & SQLcl, plus of course the Instant Client like you said. But there are more than a few 3rd party ones.
I’m glad you’re using SQL Developer though.
When I compare a model to my database, my model only cvers say 5 tables, but my database is huge – maybe 900 tables, and it takes forever to come back with a diff, even though I’m only comparing one bject. Does SDDM get metadata on the entire database, or filter it to thos in my model?
i’m confused your model has 5 tables but you asked about comparing 1?
we look at everything so you can see what db objects your design is missing…i think after you do a RE into a model, we assume you don’t care about the other objects going forward that you did not import and only look at the objects in the database that have been added since you did that RE
Hi Jeff. I’ve just started using SDDM, I used to use ERWin. So does the compare only compare my model with the DB objects I have REd into my model, or to all objects in the database?
It depends on how you built the model I THINK. If you begin your model by doing a RE, and you don’t grab a table, that table isn’t considered for the compare going forward. I THINK.
Ah, I think that’s what it should do, but I believe it compares it to the entire database.
what version are you running?
I’ll give it a look-see.
Jeff,
I believe my Oracle ID/Schema is missing nevcessary privs to use SQLdev effectively. I own no objects, but have been granted a custom role giving me access to the objects in another schema.
I can see the tables (via synonyms) and data from the SQL Worksheet, but when trying to run pl/sql, I get ‘table or view not found’ errors.
What do I need to tell/ask my DBA?
I should add, anonymous blocks work fine, but create procedure or function fails to compile with the above error.
depends if the plsql is set to definer or invoker rights
I get the same compile error with either DEFINER or CURRENT_USER
oh compile…i thought you meant execute
on comppile, your user needs select privs on those objects or it won’t work
From an SQL Worksheet (the same one, actually, where I am executing the CREATE proc or func statements), I can see, describe, and select data from all the objects referenced in the PL/SQL, but I am still getting the compile error “table or view does not exist”.
Hi, Can SQL Developer help configure , execute, schedule RMAN?
Yes.
Hi Jeff,
Is there any way of sorting User Snippets besides physically reorganizing the UserSnippets.xml?
Thanks,
the custom category? from what i can tell, we do alpha sort them in the display already
Hello Jeff
Atm. when i want to know the data type from an Column i have to go through all tables manually.
Is it possible to get the data type info of the column also , if i execute an query in sql developer ?
regards john
do a shift+f4 desc on the table/view in your query
Hi Jeff,
is it possible to connect SQLDeveloper and DataModeler to GitLab (not GitHub!)? I tried, but when I enter my credentials and click Next, get Validation Failed message: invalid advertisement of !
What I’m doing wrong?
Hi Jeff
is it possible to access in sql developer nosql avro schemas ?
When connecting to a nosql db i can only see in the browser a empty folder “Tables”.
Regards Günter
You’re the first to ask.
Scripts containing packages.
sqldeveloper doesn’t contain any type specific file endings (e.g. xxx.fnc for a function or xxx.prc for procedure), right? (Would be helpfull to distinguish file content by file extension)
But my main “problem” is when loading a package from a script. I don’t have a navigation tree related to that script. The navigation tree is always related to the database!
Compiling the script from file successfully results in marking it as having “no change” (italic label is gone). Sometimes I forget to press “save” reflecting the changes in the file system!
When I accidentially click on same package in navigation tree I now have 2 tabs open with the same name only with different window title. Sometimes having lots of tabs open you don’t register the new tab. ctrl+s doesn’t throw an error. This results in having different package versions in db and in script file!!
So it would be very very helpfull to
– e.g. having different background colors in tabs created from files and from database source
– having an own tree for packages within and only related to the tab
– get a warning when opening a database object when same object is already open in an existing tab
– mark tab from file as changed (e.g. by *) until it’s really saved to file system
when you have the file open, right click, Outline – there’s your nav tree for your script.
Jeff, I left a post about trouble building 1:1 identifying relationships in SQL developer a couple of weeks ago and then ended up traveling for business (sorry for the delay). I did some more troubleshooting to narrow down the problem and also have a model you can recreate the problem with. Also I want to make sure it is not a misunderstanding between definitions. So here goes:
I am trying to build a 1:1 identifying relationship between two entities (lets call them parent and child). An example could the parent = a problem log, the child = resolution data about the problem. It is an identifying relationship, i.e. I want the parent key to become the child’s PK as well.
The problem seems to be centered around SQL developer indicators on the relationship (identifying, source optional, target optional). There will always be a parent (i.e. source is mandatory). There may not be a child, the problem has not been ‘worked’ yet (i.e. target is optional).
In SQL developer, if the relationship has identifying checked, then I cannot select target optional (target optional is greyed out)
If I check identifying and then uncheck source optional, the model freezes.
I suspect that I may be misinterpreting what SQL Dev means by those indicators, so I look forward to your help.
I also have a model with two entities (named parent and child). To produce the freeze all you need to do is go into the relationship and uncheck source optional. Let me know where you want me to email it.
This 4.1.3.20 on a Linux 64-bit CentOS (6.8) install.
Roland, I think you’ll get a better response if you can post this here.
4.0.3.16 When using SQL Worksheet and using the drop down list of connections, the list does not remain in the order as they are on the Connections panel. This behavior also occurs in the small connections box to the far right. We have well over 50 connections and it’s not fun having to hunt down the one we want. This must be a preference because my old SQL Developer version on my old computer maintained the order to be as on the Connection panel to the left.
we changed the order to be – current/active connections first, then alphabetical everything else
so if Z is connected, it will show up first, and then connections a, b, & c
Is there a way to override that?
nope
what order do you want it – alphabetical always? we had that forever and folks were always asking for an easier way to select connections that were already open when running a report or doing a db export…hence the change.
Sorry, only now saw your response – stupid spam filter on my email. Mine does not seem to operate as you describe. It seems to list recently opened connections on top, even if they’re no longer active. So on any given day I might have over a dozen connections listed on top with the rest in alpha order.
should be active/open connections up top, and then the rest in Alpha
How Do I turn the following table:
CASE TYPE AMOUNT
1 civ 30
1 tr 20
1 crim 50
2 civ 30
2 crim 40
3 civ 50
4 tr 60
INTO the following result:
CASE CIV CRIM TR SUBTOTAL
1 30 50 20 100
2 30 40 70
3 50 50
4 60 60
TOTAL 110 90 80 280
Please let me know, my DBA gave me this question and I have been stumped!!!
I’m not so good with trivia/homework. Try the SQL OTN Space/Forum.
Hi, Since upgrading to 4.2.0, none of my user defined extensions are showing. One example is my own custom view errors tab. Has anything changed with regards to the setup of these? It was setup as described in your post http://www.thatjeffsmith.com/archive/2012/07/how-to-view-errors-for-views-in-oracle-sql-developer/ but with a different tab title.
Thanks.
Hi Jeff, I’m working on a Java stored procedure. Every example I see uses System.err.println to display exception information. Is there a way to configure SQL Developer to display the output of these calls?
Jeff, I am using SQL Developer and 12C for Intro to DB class I am teaching. I am bumping into a bug I can’t seem to solve or find a solution to. When I setup a 1: 1 identifying relationship between two entities, the application hangs. Some times prior to hanging, I see that a large set of FKs have been established on one of the entities. It appears to be some type of FK loop that is resulting. I am not sure which parameter on the relationship or the entities may be driving this behavior. I am using the Data Modeler from within SQL developer (vs the standalone version). Any insight would be greatly appreciated.
Roland DePratti
Eastern Ct State University
Can you get me the DDL for the two tables causing the problem? Then I can re-create the issue here and see what’s what.
I’m assuming you’ve got a schema you’ve engineered into a Logical Design? Or if you’ve created it from scratch need to get the design itself from you.
Unable to connect using 4.1.3.
I am trying connecting using Basic connection – that is using host, port and SERVICE_NAME.
I get Network Error ( vendor code 17002 ).
When I try the same using version 3.0.2, I have no problem.
The symptom is closest to the thread below.
https://community.oracle.com/thread/3881596
Any ideas what could be wrong here ?
Also – where do I find “TUNNELS” settings ?
abhay
Are you using an SSH Tunnel? If not, that doesn’t apply.
How do I verify that ?
Also – the connection is to a database in AWS. But I am successfully able to access it with 3.0.2
If you don’t know, then the answer is ‘no’ you don’t have one.
Are you using an ip address or a hostname to connect? Try both.
Hi
I have captured the Schema in different versions of a product using the ‘Generate DB Doc’feature in Oracle SQL Developer. However, I now want to be able to get difference of these schemas with one another. Is it possible to use the output of ‘Generate DB Doc’ as an input to Diff function. Right now, I am only able to find an existing connection as allowed input to Diff function.
I would suggest using the modeler to do your diff. It can use offline metadata to do compares.
Hey, Jeff
I have Excel (2016) files to import. I am having SQLDev create a script for the import. as one column has over 4000 characters in it, so I need to change things like VARCHAR2(7342) to CLOB.
(Its odd,though. In the generated INSERT statements, SQLDEV seems to recognize that the data is too long, as it splits the column into multiple concatenated TO_CLOB calls of 500 characters each. If it knows to do this, why does it still create column definitions like VARCHAR2(7342)??)
The problem, though, is that TO_CLOB(‘yada yada yada;) || TO_CLOB(‘bada bing bada boom’) appears to be done in VARCHAR2 mode. Once this goes over 4000 characters, it generates “ORA-12899: value too large for column” errors. I don’t see how this made it out of testing before being released, so I am wondering (hoping) that I simply have a setting or two misconfigured.
So, is this a bug or what?
No help for this one???
Sorry, just haven’t gotten to this one yet. If it happens like you say, yes, it’s a bug.
FWIW, 12c supports 32k VARCHAR2 column definitions now. Are you on 12c by any chance?
Not at work, but this is a private project, so I move to 12c.
I will try that in the interim.l