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
Hi,
is is possible in SQL Developer to auto add db link after table name ? for example:
SELECT * FROM apps.ap_invoices_all@DB_LINK ?
Hi,
Currently I’m using SQL Developer Version 4.2.0.16.260 Build 16.260.1303.
I’ve got problem show CLOB data, not show full data.
Its get cut.
I’ve tried :
set long 1000000000;
set longchunksize 1000000000;
Still, data get cut.
Where is it getting cut?
SET Feedback OFF behaves differently than SQLplus
SQLPlus:
SQL> SET FEEDBACK OFF
SQL> select * from ssss;
select * from ssss
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQLcl
SQL> SET FEEDBACK OFF
SQL> select * from ssss;
SQL> SET FEEDBACK ON
SQL> select * from ssss;
Error starting at line : 1 in command –
select * from ssss
Error at Command Line : 1 Column : 15
Error report –
SQL Error: ORA-00942: table or view does not exist
00942. 00000 – “table or view does not exist”
*Cause:
*Action:
Btw I am not sure if there is any official way to send feature suggestions. So I’ll just put them here.
1. Enable the use of environment variables inside an script. This is one of the most common use case for any cmd utility. If anyone in 2017 wants to have interactive sessions they can just use SQLDeveloper or Toad.
Example:
#!/bin/bash
db_schema=$1
db_table=$2
file_name=$3
sql @select_all.sql
——
spool ${file_name}
select * from ${db_schema}.${db_table}
spool off
—–
2. Separate the streams of information. We should be able to redirect the errors to &2 and normal debug output to the screen. I don’t see any benefit of having those in a spool file.
We should have the option to get the spool results in &1 or a file.
SET TERMOUT OFF doesn’t work when you want to use env variables in your script 🙁
Example: This is considered and interactive session.
sql user/pass@SID <<EOF
select * from ${db_schema}.${db_table};
EOF
3. Now spool on the other hand side should contain the data that a command returns. (header+data)
Yeah, that doesn’t seem right. Checking with DEV to make sure.
Is there an alternative way to change the theme in the latest versions since the theme feature has been removed? The whiteness is quite disturbing to the eyes.
Whiteness of the code editors? Yes.
Or Whiteness of the app overall?
Hello,
I used to use the Multi-Cursor Editing ( i think that is what it was called ) , used to call it column editing, anyways in version 17.3.1.279 it no longer works. I verified I have the Preferences-mouse actions set to control-shift.
I must be missing something.
Thank you this Blog, it is very helpful.
Sam
You’re going to need to be more specific.
In 17.3, Windows, I just tried
Ctrl+Shift+Click to add multiple cursors and edit => Works.
Edit Menu, Block Selection…block select text => Works.
What are you doing/trying?
v17.x
Open Monitor Sessions
Top portion show all the sessions with Instance, SIDD and Serial
The detail screen at the bottom run a query which seems to only join on SID so that the Active SQL shows multltiple sql one for each INSTANCE+SID+SERIAL. Is this a feature or a bug? Currently, I get to see an SQL that has nothing to do with my session (based on the OSUser and machine name)
Regards
Same issues on v17.2 and v17.3 and v17.3.1
Previous versions of SQL Developer had a feature that autogenerated GROUP BY clauses. I don’t see that available in v17. Do you know if this is still available and has just been moved from the Completion Insight tab in preferences?
Thanks
It’s still there. It’s just more now. ‘Enable Semantic Analysis Info Tip’ – and the feature in play
before
after
I am using Sql Developer Data Modeler, V. 17.2.0.188. I imported a schema from Erwin 7 and have successfully synced my model, which includes 40 Subviews with a “production like” schema. When trying to produce a report, via the Files –> Reports menu, I receive an error when for all options except XLSX. The error is:
2017-11-29 09:35:26,981 [Thread-511] ERROR ReportsGenerator – TransformerException during report generationjavax.xml.transform.TransformerException: XML-22004: (Fatal Error) Error while parsing input XML document (Invalid char in text.).
2017-11-30 13:30:34,980 [Thread-529] INFO RenderPDFReport – Using XSLT 1.0
2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: Nov 30, 2017 1:30:36 PM org.apache.fop.cli.Main startFOP
2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: SEVERE: Exception
2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: org.apache.fop.apps.FOPException: An invalid XML character (Unicode: 0x12) was found in the element content of the document.
2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: javax.xml.transform.TransformerException: An invalid XML character (Unicode: 0x12) was found in the element content of the document.
2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: at org.apache.fop.cli.InputHandler.transformTo(InputHandler.java:217)
2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: at org.apache.fop.cli.InputHandler.renderTo(InputHandler.java:125)
2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: at org.apache.fop.cli.Main.startFOP(Main.java:166)
2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: at org.apache.fop.cli.Main.main(Main.java:197)
2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: Caused by: javax.xml.transform.TransformerException: An invalid XML character (Unicode: 0x12) was found in the element content of the document.
2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: at org.apache.xalan.transformer.TransformerImpl.fatalError(TransformerImpl.java:780)
2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: at org.apache.xalan.transformer.TransformerImpl.transform(TransformerImpl.java:756)
2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: at org.apache.xalan.transformer.TransformerImpl.transform(TransformerImpl.java:1284)
2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: at org.apache.xalan.transformer.TransformerImpl.transform(TransformerImpl.java:1262)
2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: at org.apache.fop.cli.InputHandler.transformTo(InputHandler.java:214)
2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: … 3 more
2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message:
2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: ———
2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message:
2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: ; SystemID: file:/C:/Devon/DataModeler/Output/report_data.xml; Line#: 1527; Column#: 150
2017-11-30 13:30:36,163 [Thread-529] ERROR RenderPDFReport – PDF report generation FOP message: javax.xml.transform.TransformerException: An invalid XML character (Unicode: 0x12) was found in the element content of the document.
Can you point me in the right direction please? I don’t know how to identify the character it doesn’t like. If I found the character, which is in the database schema, I don’t know what I would be able to do about it. Please advise. Thanks you.
Is there a way to increase the size of the columns in the .csv file created from spooling?
It appears that the columns are all being limited to 9 characters and the user has to expand the column in order to view the contents which is all there in Excel but partially being hidden.
I have these settings and have played with them with no success:
SET FEEDBACK OFF;
set PAGESIZE 10000;
SET COLSEP “,”
SET HEADING OFF;
SET NEWPAGE NONE;
SET TRIMSPOOL OFF;
SET LINESIZE 500;
SET WRAP OFF;
CSVs don’t have column sizes. There just CSVs. I think you’re asking an Excel question.
Jeff-
Is there a way, in SQL Developer, to change the currently viewed database edition in the object navigation tree?
I can see the editions in that tree, but I’m trying to understand how to “switch” between editions in the GUI and compare, for example, contents of a table across 2 editions?
Jeff,
Where, in the SQLDeveloper IDE, is the “Export” feature for SQLDeveloper connections?
I’m using version 4.1.3.20, main build 20-78. I highlight the connection/connections that I’d like to export, and left-click, right-click, double-click, search the menu bar, but I don’t see the option to export the connection to xml.
I admit, it might be right in front of me and I’m simply being dumb, but I’d very much like to save my team from having to re-invent flat wheels.
Here.
Right click on ‘Connections’ item in the connection panel.
Thanks
Hello Jeff,
I have a question on sdcli.exe using the cart function. I am planning to use it export the lookup data for a periodic backup.
sdcli.exe cart export -cart C:\Users\u0051292\Cart_1.xml -cfg C:\Users\u0051292\export_tool.xml
With above command i am able to make it work and i see data getting exported. But I what to schedule it on the server via a batch job.
The problem i face is above statement asks for Password for the Connection at the time of execution. Is there a way to hard code the password so that it runs silent on a batch?
I know there will be security concerns but i don’t care as this will be scheduled in a server which i only have access.
I have an example of an automated cart export at http://plsql.me/carts-and-sql-developer-command-line-interface/
The key you are missing is the cart needs to include the connection information referencing a SQL Developer connection. See the line with
‘
The connection UTILS exist with SQL Developer GUI connection list with the password saved. The login user has elevated read privileges to extract the DDL but with little CREATE privileges,
Hello Jeff,
I am currently doing a project in a company using Oracle.
They want to implement their forecasts into Oracle.
I have found that this is done by setting up forecast sets, which works just fine and it also shows the consumption when new sales orders come in.
But i have seen that you can make the forecasts appear as “Forecasts MDS” when looking up the part in e.g. Supply/demand. And this could then generate a planned order for the forecast?
Is it possible that you have any guidance or mby know a post explaining about how this could be done?
Sorry, I don’t know what a forecast is. I’m just a lowly database guy.
Hi Jeff,
I was trying to modify the package body which was left open from yesterday. I believe the session might have got disconnected and so i did a reconnect. However when i try to compile the package body, the sql developer just hangs. This has happened to me the second time. I copied the code, just in case and i am saved! as expected the sql developer hung. I’ve had to kill the sql developer session. Is this a known issue with a resolution?
it’s hanging probably because of something locked/blocked in the database, go check session waits
I’m using SQL Developer to query DB2. I have “auto-commit” turned off in the Tools->Preferences->Databases settings, but it doesn’t seem to have any affect. It does automatically commit. I ran a single UPDATE statement and then saw it had an error. I typed ROLLBACK but it was too late.
Is there a way that I can actually make sure AUTO-COMMIT is off?
Pretty sure that setting only applies to an oracle connection.
But if your update had an error, then there was nothing to rollback, because the statement failed. Yes?
Thanks for the reply. Unfortunately, this UPDATE did not have an error. It was syntactically correct. It was just missing some of the WHERE clause details to narrow down the affected data. When I ran it, it said I successfully updated X rows (and I was expecting 1). So, oops, I tried to do a ROLLBACK but it was already committed to the dB
Jeff,
I could create a process model without creating any object for Logical Model or Relational Model.
But I am not able to generate documentation from that process model in SQL developer Data Modeler.
-Kriti
Hi Jeff,
I am very new to SQL developer Data Modeler, My objective is to generate documentation for my existing database.
Issue I am facing is that existing database doesnt have any key constraints defined in database.
What do you suggest should I follow Top Down Approach for creating Design or Bottom Up Approach.
Thanks
Kriti
Do you at least have primary keys?
Yes but not in all the tables and relations in other tables are usually not based on primary Key.
Eg: Table 1: PK (Column1) , Column2 , Column3
Table 2: Column2 , Column3
So relation between the tables is based on Column2 and Column3 which dont have any constraint defined.
This is the best we can do outside of you going in and building the FKs yourself.
I am trying to connect to github enterprise from the Team..git.. menu and if I want to connect to public github with username / password no problem. Internally with github enterprise this doesn’t work as it is expecting sso authentication. Looking at another tool that is working it is using OAuth for authentication, does SQL Developer support this and if so what are the details for doing so. Can’t find anything on this topic for simply connecting the client to an internal repository.
Jeff – any update on this? May have gotten lost in the holiday break.
We get the GIT support from JDeveloper – so if it supports it, then we would as well.
Thanks, I’ll Google / search MOS for OAUTH on JDeveloper and see if I get any hits.
Do you have any plans to include git as versioning tool for oracle data modeler just like you did with oracle sql developer?
We’ve looked into it. It’s a ‘nice to have’ at the moment.
Is there any way to share a connection.xml file? I work off my laptop sometimes and sometimes off my desktop, they are both on the LAN. I’d like to be able to put my connections file on my user drive so that when I don’t have to maintain 2 copies of them. Any ideas?
Yes, sir!
In the “Connections” pane, right click on label, “Connections”. Observe the resulting drop down menu. Select “Export” and follow the prompts to export the connection(s) that you select. When the export is complete, ftp the resulting XML file to your target system. Use the same technique to “Import” the XML file. It works a treat!
Hi Jeff,
We are using SDDM (v17.3) and the Reports functionality to generate Excel worksheets to maintain/update table and column comments. That works fine but we also like to do the same with views, but for “Table Views” there is no “Available Collections” on column level (as for “Tables” having an available collection named “Columns”).
Is there a reason for this or can this functionality be added to a later version of SDDM?
Thanks
Jo
Sure, there’s always room for enhancements!
Hi Jeff,
Ok, I have registered an SR for this, resulting in Bug 27143851. Hopefully the development team will look into this some time in the future – there is another enhancement request (Bug 26383429) that relates to handling large data models (4000+ tables in multiple designs) that I rather give a priority… (sorry to bring this up here but I am a bit frustrated as I am given no information – the bug says nothing as far as I can see..). My natural reaction to this is to ask if it is useful to the development team to have enhancement requests registered?
Regards
Jo
All I can say is neither enhancement request is scheduled for implementation.
Thanks Jeff for your investigation!
I guess all I can hope for is that the Bug 26383429 will be considered some time in the future. Today I have a lot of manual work updating the Reporting repository and running out reports. Together with the lack of functionality for mass-updates, using SDDM for large data models (enterprise models – multiple designs) may not be the best option althought I really, really want it to be.
Best regards
Jo
>> Together with the lack of functionality for mass-updates
I’m not sure exactly what you’re doing, but both the transformation scripts and the Search feature both allow for updates to multiple properties on multiple design objects.
Hi Jeff,
There is a story behind our situation as we run multiple parallell projects (this is a data warehouse mirroring about 40 source systems – each with projects of their own) so to be able to handle this volume of project concurrency we have split our model into multiple designs covering different domains (I am sorry to say that we have about 50 designs with use of remote tables). So “mass-updates” is accross designs in our case. The multiuser functionality based on use of Subversion seemed to unstable to put this model into one or a few designs (our Citrix clients also ran out of memory – SDDM getting slow/unstable). So what I really want is a scalable repository to run api/sql against or as a second best option – scripting functionality – command line based – outside SDDM.
Best regards
Jo
Ok. I hope you will understand that you are probably a 1%’er. This is a good and bad thing. Good that you are pushing the limits, and doing awesome things for clients. But bad because your needs exceed what most of the other users require.
If you see something we can do better with the reporting repositories, that would probably be an easier change to make.
Yes – I know our situation is beyond most use. One of our source systems use SDDM for their 1200 tables model in one design and that works fine.
We also use the RR a lot for consistency checks accross designs. That works very nice! But it takes about one hour to export all designs manually. To have a scripting option would really help. Then I could schedule the export to ensure that the documentation we expose from RR is updated soon after the designs are checked in Subversion. Same issue relates to the Report functionality. Manual operations are bad even with only one design.
Thanks
Jo
Is there a UI preference that controls window handling such that everything is not tab based? Interested in having new windows open up in more a MDI style instead of the everything in tabs concept.
No, we use the JDev FCP, and docs is a core component.
In 17.3.1.279 any “window close” operation invokes a “close all documents” operation on all open window types.
I right clicked on a document in sqldev, a SQL Worksheet. Chose ‘close.’ It only closed that worksheet, and no others. I’m going to need more information from you to see if there’s a problem.
(reposted changing ” text to ‘[…]’)
You are correct. A false positive working with only two windows. Working with a colleague it appears there is a different close window interaction when [Close all worksheets on disconnect] is checked versus unchecked.
How we tested:
Database:Worksheet:Open a worksheet on connect = checked
Database:Worksheet:New worksheet to use unshared connection = checked
Database:Worksheet:Close all worksheets on disconnect = checked
Database:Worksheet:Prompt for Save file on close = checked
Create/save an Oracle connection using jdbc, do not specify password, [Save Password] = unchecked
When opening a SQL window the method is right click connection in and click [Open SQL Worksheet]
When SQL Worksheet opens type something to change window status to modified; ie. select * from dual
When closing a window the ‘x’ on right side of tab is used.
After 1st connect an initial SQL Worksheet opens, open 2 additional SQL Worksheets; remember to modify each SQL Worksheet window.
=====
Step 1: open connection, initial prompt for password.
Step 2: open 2 additional SQL worksheets, no additional prompt for password (reminder to modify each window).
Step 3: close the 3rd window. This will prompt for [Save changes], select [No], SQL worksheet closes, [Save changes] re-prompts, select [No], done
(Main connection seems to be closed at this point.)
Step 4: open new SQL Worksheet (3rd), will be prompted for password.
Step 5: open new SQL Worksheet (4th), will not be prompted for password.
Change:
Database:Worksheet:Close all worksheets on disconnect = unchecked
(restart SQL Developer)
Retest from Step 1, now [Save changes] will behave as expected, main connection still appears to close on window close.
Unexpected behavior:
Closing window seems to close main connection or clears password from initial connect.
When [Database:Worksheet:Close all worksheets on disconnect = checked] is in effect, [Save changes] reprompts using [No]
For us this reproduced on Windows and Mac using SQL Developer 17.3.1.279.
Tim…
Database:Worksheet:New worksheet to use unshared connection = checked
Database:Worksheet:Close all worksheets on disconnect = checked
So if you toggle either of those above, it works as expected?
Results following your thinking…
—–
Database:Worksheet:New worksheet to use unshared connection = unchecked
Database:Worksheet:Close all worksheets on disconnect = checked
or
Database:Worksheet:New worksheet to use unshared connection = unchecked
Database:Worksheet:Close all worksheets on disconnect = unchecked
No double prompt for [Save Changes]+[No]
No prompt for password opening new SQL Worksheet after closing a SQL Worksheet.
This behaves has expected.
—–
Database:Worksheet:New worksheet to use unshared connection = checked
Database:Worksheet:Close all worksheets on disconnect = unchecked
No double prompt for [Save Changes]+[No]
***Prompts for password opening new SQL Worksheet after closing a SQL Worksheet.
—–
Database:Worksheet:New worksheet to use unshared connection = checked
Database:Worksheet:Close all worksheets on disconnect = checked
***Double prompts for [Save Changes]+[No]
***Prompts for password opening new SQL Worksheet after closing a SQL Worksheet.
—–
Tim…
How can you change the connection that is assigned to a “UNIT TEST” or a Suite of unit tests?
When I first create a unit test in SQL Developer, it assigns a database connection to it, which is displayed in the upper right corner in a drop-down menu. However, when you try to change that connection, there does not appear to be anyway to save that change. I have tried committing my change to the unit test repository, but every time you reopen the same unit test, it will always revert back to the original connection.
Thanks for any help you can provide.
Hello. We’re getting Java exceptions in the log from trying to edit a pl/sql package that contains multi-line text.
Version 17.3.0.271
Build 271.2323
Any suggestions? Thanks so much.
SEVERE 968 5888 oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1 null at oracle.javatools.editor.BasicView.getOffsetForXCoordinate(BasicView.java:2990)
SEVERE 961 167 oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1 null at oracle.javatools.editor.BasicView.getOffsetForXCoordinate(BasicView.java:2990)
SEVERE 960 109 oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1 null at oracle.javatools.editor.BasicView.getOffsetForXCoordinate(BasicView.java:2990)
SEVERE 959 1712 oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1 null at oracle.javatools.editor.BasicView.getOffsetForXCoordinate(BasicView.java:2990)
SEVERE 956 7306 oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1 null at oracle.javatools.editor.BasicView.getOffsetForXCoordinate(BasicView.java:2990)
E.g. This is sufficient to raise the exception.
CREATE OR REPLACE PACKAGE BODY demo_pkg AS
PROCEDURE do_stuff AS
v_text VARCHAR2(800 CHAR);
BEGIN
v_text := ‘a
b
c
d
e
f’;
END do_stuff;
END demo_pkg;
Thanks for the test case, will look at it ASAP.
And it’s fixed in 17.3.1
https://community.oracle.com/thread/4085700
Cheers
Hi Jeff. I’m using the new version of SQL Developer (17.3.1), but still facing connection resets since version 4.2 (it also happened to me on v 17.2). The number of connection resets have dropped, but they still occur even when working on SQL Developer, for example, I have 2 worksheets opened pointing to 2 different connections, I’m working for a while in one of them, When I want to use the other one, I get “Your database connection has been reset…” error message. I’m connecting to DBs from 11.2.0.3 to 12.1.0.1. It looks like there’s a timeout somewhere that drops them (just a hunch). Any idea on how to debug this? Thanks in advance for your help.
It they’re timeouts, most like JDBC driver timeouts or the database administrator has an inactivity rule setup that is dropping the connections intentionally.
Hi Jeff, thanks for the reply. I know there’s no timeout at the DB level (I’ve checked the profile the users have configured). How can I check if there’s a JDBC driver timeout?
So if no resource consumer groups, you have the default timeout settings for the driver itself, and then you have network timeouts.