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,793 Comments
Hey Jeff, Need help on OBIEE sampleApp in Vmbox. Tried following your guidance in other pages but going insane with multiple issues while setting up network in Vmbox.
I am not able to setup port forwarding.
Will be very very thankful if you can help.
I’m trying to debug an issue that i’m getting with the latest ojdbc8 and xdb6 jars. I have a table with an xmltype column in it and when i try to retrieve it, for some values I seem to get back null, even though there is data there.
I’ve tried both v17.3 and v17.4 to reproduce the issue to try to diagnose where it might be. If i just do a select my_xmltype_col from the table, the data looks nice. However, the moment i do a my_xmltype_col.getclobval() (which is what i’m doing in my java application to retrieve the value as a string) I get something that looks like “oracle.sql.clob@23231d92”. If i try right clicking on it to get the single record view, then i get my connection was reset (when i try the right click).
My env is like this: — Database Info —
Database Product Name: Oracle
Database Product Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Database Major Version: 11
Database Minor Version: 2
— Driver Info —
Driver Name: Oracle JDBC driver
Driver Version: 12.2.0.1.0
Driver Major Version: 12
Driver Minor Version: 2
Driver URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXXXXXXX)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=YYYYYYYY)))
Driver Location: Unable to parse URL: bundleresource://236.fwk79605832/oracle/jdbc/OracleDriver.class
As an addition to this, if I pull back the XMLType into java and then do .getstringval, it works like a charm. I’m not sure why it doesn’t work db side then…
managing xml and jars and oracle drivers – not something I can do here. Open an SR with MOS or post a thread on the forums, and i’ll make sure our XML PM can take a look at it
hi
so whenever i am trying to open any table after making the connection and i am clicking on a table its giving me an error saying ora: 01460
unimplemented or unreasonable conversion requested
please help with an action on what to do
o whenever i am trying to open any table after making the connection and i am clicking on a table its giving me an error saying ora: 01460
unimplemented or unreasonable conversion requested
please help with an action on what to do
What version of Oracle database are you connected to?
Hi Jeff,
how can I define the date format in “File panel”, e.g. when I open a saved file for my sql files in SQL-Developer? It is by standard in US notation (e.g. “11/26/17 10:59 PM”) and so it’s hard for me to interpret the date for my location area.
I would like to see them e.g. in format ‘yyyy.mm.dd HH24:MI’ to see which file is most recent. It’s much easier for me to read.
Thanks for a good hint
Dieter
We should be getting that from the OS via the regional settings.
Thanks, but unfortunately it doesn’t.
I’m running Windows 7 Enterprise and with language and region setting to German.
Date-format: TT.MM.JJJJ is definitely different to the US-region used by SQLdev.
Any java config I could change? I’ve a separate JDK installed to get it work, because of no admin permission on my PC.
Hi Jeff,
I’m currently working to enable SQL*Developer for Radius Authentication to the database (enabled Active Directory authentication and authorization). In order to do so, I need to figure out how to cast the setting to every client PC to change from JDBC use to using OCI calls / Thick client.
Can you point me to where that configuration is housed? It would be incredibly helpful.
Everything is under the AppData, roaming profiles folder for the Windows OS user.
Hi,
I’m using SQL Developer version 17.4.0.355.2349 with a high resolution/dpi monitor.
SQL worksheet works fine but when I go to the “Real Time SQL Monitor” the SQL Developer window shrinks to about 1/4 the size and becomes unusable.
I have tried using JDK 9 but this didn’t help.
Any ideas on how this can be fixed?
Hi Jeff,
I’m using version 17.3 of SQL Developer and have noticed that the query screen doesn’t paint well when the query contains a string that exists over several lines. So the opening quote for one string is on (say) line four and the matching closing quotes lives on line seven. Sometimes these are strings with the new ” q’ ” syntax.
Have you experienced that before, or is it just my platform that’s the problem?
Thanks
Mungo
We have two query output screens, which one? Grid or Script Output?
Who said “output screen”? I’m not at work, so I cannot remember the name you give to the “query screen” – the rectangular text area that I type my SQL into.
Mungo
Ok. I guess I don’t know what you mean by ‘paint well.’
Hi Jeff,
Matter of your website here: are you aware that your “Unsubscribe” page looks darn ugly with the labels and the radio buttons askew? It’s not the easiest of sites to navigate, but I guess you don’t have the time to polish it.
When I saw the ugliness on my iPad, I thought that it would be fine on a Windows PC. Nope.
Don’t shoot the messenger…
Regards
Mungo Henning
Why are you trying to unsubscribe?
Send me the page you think is ugly, I’ll fix it.
My Question is: How do I easily find what is at column “30” ? Something to turn on in preferences ?
I know I could arrow over counting a column for each arrow click.
ORA-00936: missing expression
00936. 00000 – “missing expression”
*Cause:
*Action:
Error at Line: 51 Column: 30
I talk about it here.
Sorry if asked befor but I only find answers for export on data.
How do I export objects (ex. packages) with standard no double qoutes enclosing schema and objectname? (adding the schema is fine)
so now I alway get
create or replace package body “schema.”package” as
but I want
create or replace package body schema.package as
That’s how the database, via DBMS_METADATA, generates the DDL.
Sorry if this question has been answered somewhere on your forum, I can’t find the answer. (On the bright side, I found about 50 things that make life better in SQL Developer while searching for this answer.)
If I’m typing a statement in a SQL worksheet and use the auto complete on a table that is for the login user, the fully qualified schema disappears when I hit enter. Is there a setting to retain the fully qualified schema while keeping the auto complete/suggestion feature?
Example if I logged in as user ‘HR’:
SELECT * FROM HR.EMPLOYEES
SELECT * FROM EMPLOYEES
How do we keep the fully qualified schema (HR) in the statement when working in the SQL Worksheet?
Looks like it removed the markup in my example…
Example if I logged in as user ‘HR’:
SELECT * FROM HR.EMPLOYEES
–type in fully qualified table name using auto complete
SELECT * FROM EMPLOYEES
–auto corrects to this after hitting enter with auto complete
I answered your question here.
Spoiler: it’s a bug.
(Formatter Question)
Hey Jeff,
I am using SQL Developer 17.3.1, which has the new formatter tool. I read through the ‘Advanced Format: Custom Format’ template that comes default, and I am having a hard time learning how to edit the document to format queries to my preference. I went the the WordPress site mentioned in the document, and I am still lost. Is there a page / document out there that can help me understand how to tweak the format to my liking?
I am trying to format my SQL as follows:
Default:
SELECT
*
FROM
dba_audit_trail
WHERE
username = :Username
AND timestamp > trunc(SYSDATE)
ORDER BY
timestamp DESC;
Desired:
SELECT
*
FROM
dba_audit_trail
WHERE
username = ‘lkup’
AND timestamp > trunc(SYSDATE)
ORDER BY
timestamp DESC;
As you can see all I want to do is double indent anything that follows the initial select statement. This helps me visually group queries in a worksheet.
Your help and consideration is appreciated.
Well, looks like the page stripped out the extra spaces. I hope my description was enough. Basically the default formatter lines SELECT, FROM, WHERE etc… along the same edge. And then indents the variables. What I want is to double tab everything after the select.
you were almost there…
Leave a comment on Vadim’s blog post. He’ll reply with an answer.
18.1 does offer this solution, but i don’t think that’s what you’re looking for.
Actually that formatting looks really nice. Where do we get 18.1 😉
between now and the end of March, theoretically.
Just downloaded sqldeveloper 17.4.0.355.2349. When launching, it appears to launch then dies. I was attempting to upgrade from 17.2.0.188.1159. The 17.2 version still launches and runs just fine, but 17.4 will not. Is there an easy way to kill any remnants of the prefs for 17.4 to rule out any corruption or the like?
yes, delete your system17.4 directory – not sure what OS you’re on to tell you where it’s at, but assuming windows, it’ll be under your OS User AppData folder.
If it gets to the launch/splash screen, then it’s not a Java issue. It’s probably something like you’re guessing, the system prefs directory is FUBAR.
yep, deleting AppData\…\SQL Developer\system17.4.0.355.2349 did the trick.
I now have a different issue… app is dying when using the REST Service Developer module… but I’ll see if I can isolate before posting more unless you are aware of known issues with the module in 17.4?
What does ‘app is dying’ mean exactly?
And are you using the REST feature build into the database tree or the view > REST Services Dev panel?
Using the view > REST Services Dev panel. Here are my steps:
1- Connect
2- Expand Modules Node, Expand Privileges Node
3- Right click on “REST Data Services” node, and do nothing while viewing the right click context menu
4- …. after a few seconds, Oracle SQL Developer crashes and creates windows crash dump.
Please show me the crash dump. And no, that shouldn’t be happening.
But also – it’s SO much easier to do your REST Dev in the tree. We’re considering deprecating the method you’re attempting to use.
So, related to why I’m using the REST Services Dev panel is the need to migrate Rest services defined in the APEX repo to the ORDS repo. I’ve worked out a methodology that works using that tool as I have been unable to find an automated method during ORDS upgrade.
Here’s the Windows Event data or the crash when using that REST Services Dev panel in 17.4:
Log Name: Application
Source: Application Error
Date: 2/15/2018 12:14:41 PM
Event ID: 1000
Task Category: (100)
Level: Error
Keywords: Classic
User: N/A
Computer: Win7-64-Stacy
Description:
Faulting application name: sqldeveloper.exe, version: 17.4.0.355, time stamp: 0x58ac981a
Faulting module name: ntdll.dll, version: 6.1.7601.24024, time stamp: 0x5a58e1b4
Exception code: 0xc0000374
Fault offset: 0x000ce9fb
Faulting process id: 0xc9c
Faulting application start time: 0x01d3a6804823e2dc
Faulting application path: C:\Users\Siena\Downloads\sqldeveloper-17.4.0.355.2349-no-jre\sqldeveloper\sqldeveloper.exe
Faulting module path: C:\Windows\SysWOW64\ntdll.dll
Report Id: b545308c-1273-11e8-9cd0-00505600d102
Event Xml:
1000
2
100
0x80000000000000
67379
Application
Win7-64-Stacy
sqldeveloper.exe
17.4.0.355
58ac981a
ntdll.dll
6.1.7601.24024
5a58e1b4
c0000374
000ce9fb
c9c
01d3a6804823e2dc
C:\Users\Siena\Downloads\sqldeveloper-17.4.0.355.2349-no-jre\sqldeveloper\sqldeveloper.exe
C:\Windows\SysWOW64\ntdll.dll
b545308c-1273-11e8-9cd0-00505600d102
I care not about the retrieval of the data; I care about the displaying of the data. Can you point me in the direction regarding displaying of NULLs please?
Ta again
Mungo
Sure thing.
Hi Jeff,
Showing my ignorance of the SQL Developer tool here, and hoping you can educate me.
I’m using version 17 of sql developer on a legacy oracle database. I’m only in my job by six months, hence I am doing a lot of “select *” from tables of interest, just to get the hang of the application and its design.
Some tables have 200 columns or so. Often these have one or two crucial columns, plus a lot of optional attributes that don’t interest me.
What I’d like in SQL Developer is some button that implements “suppress null columns”. So whilst all the data shown for a column is null, hide it from view. If I do decide to scroll down to the next fifty rows and a column has data in that field, then suddenly show that column.
Can you do this otherwise in SQL Developer? If not, and if you sympathise with my desire, add this to the wish list please.
Secondly, some queries that I write take a few minutes to process. So I might kick off a query and then wander off for a coffee. When I return it would be useful to see the date and time of when the first row was displayed. Just so I can visually “date” the query results.
There’s plenty of room on the top bar that shows the query elapsed time, so I would suggest adding this there please.
Thirdly, better handling of query cancellation would be nice: if I press the “cancel query” I want to be able to move on immediately, not after two minutes of slow deliberation.
Great website Jeff; answer my questions and the level of praise goes up!
Regards
Mungo Henning
Sorry, no way to do the NULL thing. And imagine how expensive that would be – reading all the values just to make sure there were no NULLs, before returning the resultset.
I would go for, never using SELECT * FROM. Always just ask for the columns you’re really interested in seeing.
On the query cancellation, we’re at the mercy of the database and the jdbc driver. While that query is running, the connection is busy. If you want to do something else, like run another query, fire up an unshared worksheet. Or esp, if you know it’s going to be bad in advance, run the long running queries in an unshared worksheet. That way, you can do all your normal clicking around and running queries while it’s busy.
Ta Jeff,
Surely the NULL column suppression is purely in the domain of the java program? I was not expecting a full resultset and then suppress null columns.
Obliged again
Mungo
Anything we do with data, we do with SQL.
We can decorate the data after the fact, and we do have options for how to display NULLs.
I have installed ORDS 17.4.1.352 into an existing APEX 5.1 environment. At the time of the ORDS install, I did not migrate the existing APEX REST definitions. How can I migrate the APEX REST defs after the fact?
I’m on vacation today, but try
java -jar ords.war help
Pretty sure you can find an option in there for what you want.
The @file.sql trick can only be used in certain places, but not, say, in a DECLARE section. I’m looking for a way to merge files together when I compile, so a second file will be inserted into the first file, as if the two files are, in fact, one single file. Similar to the pre-processor directive #include in C/C++, that can appear anywhere in code, as long as the content of the #included file is valid at that point.
Is there a way to merge a .SQL file into another .SQL file?
Declare block is invoking the PLSQL engine… @ is a sqlplus command, the DB has no idea what that is.
You can definitely have @script calls inside/nested in your scripts, but not as part of a SQL or PLSQL block.
I’m getting a bit lost in trying to determine where to create new REST services. In the past, we have using APEX to do this… but now I’m reading that it may be preferred to do outside of APEX? Can you point me in the right direction here? APEX or straight up ORDS for REST service creation now? Is there a document that talks about this migration in general?
Pick one or the other – do not mix.
There was a time when there was only APEX available.
Then APEX Listener was rebranded to Oracle REST Data Services and included the REST code path. So you could use either.
You can migrate your RESTful Services from APEX to ORDS.
At some point you will HAVE to migrate your APEX REST Services to ORDS.
You’ll get the best REST support from ORDS.
APEX 5.2 does offer ability to work with your ORDS defined services though, and it should be a very nice feature. They just won’t be stored in the APEX repository.
SQL Dev v17.4
Database v11.2.0.3
Monitor Sessions
Missing IN or OUT parameter at index::2
Vendor Code 17041
Hi Amin Adatia,
Igot the same problem even with the 19.4 SQL against 12.2 Oracle Database. To Solve this problem you need to check carefully the environment, especiually the NLS_LANG setting. After setting this correctly, according to the database I could get rid of this Problem.
In my case I had to use NLS_LANG=GERMAN_GERMANY.UTF8, this wil differ in your environment.
Hope this helps
Jeff, I love “SQLcl” and I greatly appreciate the work you’ve done to share what it can do. Because of that promotion I’ve been trying to use it more for supporting hundreds of dbs. My question is how best can I share various issues/bugs I run into? They’re mostly simple items (like “def ” does not display the variable’s value and definition) but I’m starting to get a list and not sure where to start posting them. I’m using 17.4, btw. Thx.
the forums would be a good place to start
if you feel you have found a legitimate bug, then open a service request with my oracle support
Question regarding SQL-Developer 17.4 and explain Plan.
In the versions <= 17.3 you could create an execution plan just by hitting F10. If you do so in 17.4 the Execution Plan window appears empty. In order to get a plan, i have to run the cursor once and then select the appropriate child cursor from the drop-down menu. Is it intentional behavior that you have to run the cursor once to get a plan displayed?
Thanks
It’s a bug.
However, the F10 explain plan…is not so good. The plan you get using the drop-down – is always going to be more accurate.
But, 18.1 should have F10 working again. There are a few workarounds in the meantime.
Thanks for the quick reply
Hi Jeff,
I am working on unit testing using SQL Developer. The procedure has one input variable of type VARCHAR2 – PV_PNUM and output variable is of type REF CURSOR – C_OUT_CUR. It returns a set of records based on the input.
To test if the procedure is working fine for the input passed and is expected number of returning rows for given input. I am writing a pl/sql block in Process Validation to access the output refcursor variable and loop through it / fetch data and count the no of records. Have tried accessing the variable as – {C_OUT_CUR}, {$C_OUT_CUR} and {C_OUT_CUR$}. I am able to access the input variable using {PV_PNUM}
However, it doesn’t work and i’m getting error – Validation User PL/Sql Code failed: Unable to convert to REF CURSOR.
Request you to suggest a way to validate the output of the procedure with ref cursors, as i do not want to write the whole procedure logic again in the dynamic value query in the test implementation block. It would be difficult to maintain the code at different places.
Getting this error – Validation User PL/Sql Code failed: Unable to convert to REF CURSOR.
Hi Jeff
Please can you help.
How can I remove query from spool output in sql developer.
I am using Oracle SQL Developer 4.1.0.19 to spool the results of a query to a csv file.
I want to hide the statements and query from the output file.
Thank you.
Fayyaz
JANUARY 29, 2018 AT 1:37 AM
While loading BLOB to one of the column in SQL DEV 17.4 and at the time of saving I get below error. This works fine in SQL Dev 4.1 though – is this due to JDBC version or something else please?
UPDATE “ECHNWLT”.”IMS_IMAGEDETAILS” SET WHERE ROWID = ‘AAAhYbAARAAAkVJAAA’ AND ORA_ROWSCN = ‘179639352771’
One error saving changes to table “ECHNWLT”.”IMS_IMAGEDETAILS”:
One error saving changes to table “ECHNWLT”.”IMS_IMAGEDETAILS”:…. is that all you see? What’s in the log panel?
Does this happen for any/all tables/blobs? If you create a new table to test, does that work?
Hi Jeff,
I’m using 17.4.0.355 and the SQL tab for materialized views results in:
— Unable to render MATERIALIZED VIEW DDL for object DWPRES.MV_DIM_LM_QUIZ with DBMS_METADATA attempting internal generator.
The SQL tab works fine for views, tables etc. including the tables underlying the MVs and I can use DBMS_METADATA to extract the sql at the command line.
This didn’t happen with 4.2, and started when I upgraded to 17.
Any thoughts?
We run a TON of queries to grab everything for the MV DDL, one of those is probably not working for your environment. If you go look at the Log panel, there’s a ‘Statements’ page. You should see the queries we run when you hit the ‘SQL’ page.
As a workaround, if you use the Tools > DB Export wizard, and can you get the DDL for your MV that way?
Thanks Jeff, here’s the logged error:
SEVERE 1428 234469 oracle.javatools.db.AbstractBuildableObject$BuildablePropertySupport Error : “java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected
” building MV_DIM_DATE, executing sql:
SELECT /*OracleDictionaryQueries.ALL_TABLE_TYPE_QUERY*/
A.TEMPORARY, A.DURATION, A.IOT_TYPE, A.PARTITIONED, A.NESTED,
( SELECT 1 FROM SYS.DBA_EXTERNAL_TABLES E WHERE E.OWNER = A.OWNER AND E.TABLE_NAME = A.TABLE_NAME ) EXTERNAL_TAB
‘N’ SHARDED, ‘N’ DUPLICATED
FROM SYS.DBA_TABLES A
WHERE A.OWNER = ? AND A.TABLE_NAME = ?
I got the same when I included the owner and MV name and ran the sql at the command line. Adding a comma after the EXTERNAL_TAB fixed that.
So why is the comma missing in the constructed code?
I thought that it may be a database version issue, but it’s the same against 11.2.0.4 and 12.1.0.2.
Regards,
Stuart.