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
Hopefully just a quick question.
Using git with
Oracle SQL Developer 4.2.0.16.260
Version 4.2.0.16.260
Build 16.260.1303
Whenever I am pushing to our master repository the user name gets an appended to it. This is flagging the commit as being from an unknown user. Is there something I missed in the setup – or someplace I can correct this?
I’d ask the JDev team – we get this code from them.
OK – the string got swallowed by the HTML – what gets appended is ‹›
Hopefully that will show up.
The link you had in your reply goes to a facebook picture of a sandwhich – do you have an alternate contact link for the JDev folks?
Oops. Musta pasted the wrong clipboard item. Try this.
Hi Jeff, I’m using SQL Dev DM 4.1.5. When I foward-engineer a logical subview to a relational model, the names are not being converted from logical to physical. I exported the names from the Glossary Editor to a CSV and imported the CSV into the tool via Name Abbreviations. All of the options are checked.
What am I missing ???
Thank you so much for your help !!
Sincerely, Ed.
Was trying to get results from a select on dbms_sqltune.report_sql_monitor via SqlDeveloper (4.2 in this case), but only get the very beginning of the results (~73 chars). Can’t do any set commands, so what can I do to get the full sql monitor report? Am I missing something really simple?
Example:SQL Monitoring Report
SQL Text
——————————
select * fro
Your site has been VERY helpful over the years.
Thanks!
>>Can’t do any set commands
Why not?
You’re gonna need to, you’ll need to do SET LONG at least I think…
Have you seen this?
I looked at your reference, was doing something similar (queries running longer than x secs as the parent, then child reports based on the sql id for futher info). But when I tried adding a “set long 200000” at the beginning of the child query, I keep getting ORA 922. Same with any set option. What am I missing here?
set long 200000
select DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST(
sql_id => :SQL_ID,
report_level=>’ALL’) as report
from dual
using SET commands – you need to make your report type, SQL Script
Yep, that did it. I missed that option.
Thank you sir!
Still having an issue with setting linesize. I can run the child statement via sqlplus and it is formatted fine. But when running via SqlDeveloper, it still chops it off at 82. Even tried setting a login.sql with linesize at 200. Is it something to do with the call to dbms_sqltune maybe and sqldeveloper isn’t able to reformat it?
Sample code from SQL Worksheet:
set LONG 1000000
SET LONGCHUNKSIZE 100000
SET LINESIZE 200
set pages 49999
set trimspool on
SET ECHO OFF
SET FEEDBACK OFF
select DBMS_SQLTUNE.REPORT_SQL_MONITOR(
sql_id => ‘&SQL_ID’, type=>’TEXT’,
report_level=>’ALL’)
from dual;
test it in the sqldev worksheet with F5
you could also try selecting it into a CLOB and printing it via dbms_output like i did in my example
Ok, I got it now that I see how the CLOB output get formatted. This opens up a lot of possibilities for t/s issues with this tool.
Thanks!
Hi Sql dev version 4.2 ; this error seems to just sit there
Your database connection has been reset. Any pending transactions or session state has been lost.
i repeatedly cancelled it or clicked ok on the message but it stuck takes couple of minutes to go away. This issues started with new version only.
We have a new jdbc driver for the next EA – those issues appear to be resolved.
I’m normally an Access/Excel user and the ease of switching between them, such as copy and paste, is highly useful. Is there a quick way to paste rows of data from Excel into a select statement “Where In” portion into Oracle SQL developer?
if you can figure out how to copy out a comma separated value list, then yes
otherwise you’d have to use a macro or regex search/replace to format a list of values to be comma separated and/or quoted if not numbers
I recently switched to sql dev 4.2 version. Since then all of my connections are dropping very frequently. i have to reconnect every minute or so. Earlier my connections used to persist for at least 30 minutes. I checked with DBA and no change was done at their end. Any help will be highly appreciated.
It’s an issue on our side – we’re working on it.
Thanks. Can i expect a fix with next version ?
I just restarted my sql developer and machine.connections seems to persists now. Hopefully issue does not reappear.
A simple one, where slqcl stores the alias?
Depends – Windows or NIX?
If *NIX, in your $HOME/.sqlcl directory.
If Windows, it’s in your AppData, Roaming Profiles, SQLcl folder.
Thanks! Found it is aliases.xml on .sqlcl on the ~.
I guess I can put all my scripts on sqlcl and have this aliases.xml as a backup?.
you mean, convert your scripts as aliases, and use that as backup for your scripts?
Yes convert some or most of my scripts as aliases, they are pretty handy. The only concern I had is how do I backup or transport them to another machine.
I am HR on orcl > help alias
ALIAS
——
alias [=;| LOAD []|SAVE [ ] | LIST [] | | DESC ]
DROP
HI Jeff,
I m using oracle sql developer 4.0.3.
While debugging procedure or package smart data,watches window doesnt popup.
What could be the reason for it ?
I know there was a bug at some point where the smart data/data tabs wouldn’t populate values correctly.
Can you go get v4.1.3 or v4.1.5 and try again?
Jeff,
In SQL Developer, I can Right-Mouse-Button on a connections Folder and have the option “Disconnect Connections”. It would be nice to also “Connect All”. What the best way to suggest that as a feature for a future release?
My Oracle Support or sqldeveloper.oracle.com and submit your idea.
Connecting 300 connections at once, prob not a good idea, esp when you think about what could be in a login.sql script.
Please provide an example for “Stock – Open-Hi-Lo-Close” in SQL Developer User Reports or Link to documentations to more than the very basic charts.
Additional information…
I keep getting the message “The data has insufficient number of columns. This graph type requires at least 3 columns of data…” Eventhough the SQL I used works for other Chart types.
I don’t have one on file. But I’ll add it to my list of things to play around with.
Hi Jeff,
Thanks for your replay.
Can you give me an idea of the format of the SQL the “Stock – Open-Hi-Lo-Close” Chart is expecting?
For example, how many columns should the SQL returns and how will the chart determine what are the “Lo” and “Hi”?
Thanks in advance.
I upgraded from sqldeveloper64-3.2.10.09.57 to sqldeveloper-4.1.5.21.78-x64 but had to switch back. In ver 4 I found that a “select * from table_abc;” only shows a portion of the full column name whereas every other version prior to this has always shown the full column name regardless of the width of the data value.
Is there an option in ver 4 to revert to the full column name?
do you have auto-fit columns on data?
if so, set it back to best fit or on header
Hi Jeff,
I successfully created a PostGreSQL connection with SQL Developer 4.1.3 but i can’t see the public schema, so i dont have any tree and can’t see my tables showing when I start my connection.
How can I get my tree of the public schema ?
Best regards
Thibaut
I’m having the same issue with 4.1.5 and 4.2.
The connection setup works fine but I can’t see the schema tree and when I try “Schema Browser” I get an error.
What error?
I got this error the first time I attempted “Schema Browser” on that connection:
java.lang.ClassCastException: oracle.dbtools.raptor.schemabrowser.SBController cannot be cast to oracle.ide.controller.TriggerController
Now it’s grayed-out and I can’t select it. I read in another post that the “Schema Browser” tab only works for Oracle… anyway, my main issue is not having the “tree” on the left pane.
Do you know of a trick to bring it up? I can run a query to pg_catalog.pg_tables and get the table listing but that’s a little inconvenient.
what postgresql dist and version are you running? and are you trying to migrate said tables over to Oracle?
We’re running version 9.2.18 on Red Hat Enterprise 7.3.
we’re using 9.6 internally
hello Jeff,
i came across your post about Oracle SQL Developer: Code on Demand, and i have made a few on demand strings. The weird thing is when i type the ID sometimes i get the autofill and other times it doesnt show up?
what am i missing here? are there certain circumstances where the ability to have the templates show up?
thank you
Could be a bug? But I would need to know more about when it doesn’t work – is it always the same template that doesn’t work, does it always work when the SQL Worksheet is blank?
that has been the frustrating part. there has not been any specific instances when it does or does not work. i have tried different examples. blank worksheet, not blank worksheet, highlight the id, cursor after the id, ctrl+spacebar at both of them. i have been trying templates that i have made and one’s that were pre-loaded. i should have said, but i am using version 4.1.3.20
Hi Jeff,
I wonder is there’s a way in the SQL Developer CLI to export/import tests. We would like to use SQL Developer unit tests in a CI mode with Bamboo. We’d like to have the tests stored in a Git repository and have Bamboo import them into the unit test repository and then run them. Is it possible to export/import tests without the GUI and without human interaction?
If not, we’ll need to write our own scripts which will put the test definitions directly into the DB tables. Thanks for your help. Jan
JAn,
awhile back Jeff posted this http://www.thatjeffsmith.com/archive/2016/06/shopping-for-csv-with-sql-developers-cart/
I use carts for version control and some CI. Check out my post http://wp.me/pUer6-1Z
YES 🙂
Plus they’re also in db tables, you could probably move them around on your own using just SQL
Hi,
I am looking at table properties. I have columns defined as NUMBER(15,2). When I switch to the Data tab, these numbers display as integers, i.e. no decimal portion. Is there a way to format numbers for the Data tab?
Thanks!
you want to force the decimal display even if it’s 1.00 vs 1?
I see what is happening. Exactly what you wrote, i.e. zeros after the decimal are not getting displayed. Is there a way to force the decimal display for all numbers?
Not that I know of.
More control with sqlplus scripting I imagine.
Hi,
I am using sql developer version 4.0.2.15
After running a query i get results in “Query Results” tab when i can scroll and check all the columns or select and copy any row.
But, Suddenly it got locked, as in i am unable to scroll or click anywhere in the Query result tab.
I tried with fresh installation but i am facing “SAME” issue. Can you please help
Is this with a particular query or table – or for any or all queries and tables?
Can you pull down a copy of SQL Developer v4.1.5 with the embedded JDK?
It’s going to have a much newer version of java and many many bug fixes since 4.0.2 was released.
hi,
Using Version 4.1.3.30
Is there any way of changing the colour of the scroll bar in the Query Result pane. When you click on it the colour turns to a dark grey but otherwise it’s very difficult to see.
Thanks
N.
Not that I know of. You can do a PgDwn on your kb instead as a workaround.
It might look different if you change the Look and Feel from Oracle to Windows in Preferences.
hi Jeff,
I’m using SQL developer version 4.1.5.21
I’m trying to run a script which does ACCEPT to take user input with some format validations and default values. This scripts works as documented when I run in from SQLPLUS.
Document Ref Url: https://docs.oracle.com/database/121/SQPUG/ch_twelve005.htm#SQPUG026
I’m unable to get this to work in SQL developer however. Is this a limitation in SQL developer? If so, when can we expect this SQLPLUS like behaviour in SQL developer?
It works for me in v4.2 – you didn’t give me a code sample, so I made my own.
put in script, run with F5 – which invokes the SQL*Plus script engine we use
get string, then prints string
Hi Jeff,
Thank you for the quick reply.
Apologies for not being very clear in initial post.
And I skipped the code sample because the reference URL had the examples in it that could be used.
Here is the code sample that highlights the point I’m trying to make.
ACCEPT date_time DATE FORMAT 'YYMMDD HH24MI' -
PROMPT 'Enter date and time [YYMMDD HH24MI]'
select '&date_time' from dual;
DATE
Makes reply a valid DATE format. If the reply is not a valid DATE format, ACCEPT gives an error message and prompts again.
Now as per the documentation, the ACCEPT is expected to give an error message and prompt again for any input that does not conform to the mentioned format (ex. 162300 0000 or 161132 2500 or anything else that is not a date time of that format) .
SQLPLUS honors this behavior as documented. But SQL Developer does not.
I have not tried the version 4.2 yet, but with the version I have this has been the case so far. This is also the case with validation for other variable types
PS: I could not figure out how I could share a screen shot like you did.
Regards,
Uday
Forgot to mention that I use F5 to execute the scripts in SQL Developer just like you have indicated.
Regards,
go get v4.2, you’ll be happy
SQL> accept date_time date format ‘YYMMDD HH24MI’ prompt ‘Enter date and time [YYMMDD HH24MI]’
Enter date and time [YYMMDD HH24MI] abcd
Error starting at line : 1 in command –
accept date_time date format ‘YYMMDD HH24MI’ prompt ‘Enter date and time [YYMMDD HH24MI]’
Error report –
SP2-0685: The date “abcd” is invalid or format mismatched “YYMMDD HH24MI”
Enter date and time [YYMMDD HH24MI] 161130 1230
SQL> select ‘&date_time’ from dual;
old:select ‘&date_time’ from dual
new:select ‘161130 1230’ from dual
‘1611301230
———–
161130 1230
SQL>
Cool!.. so v4.2 is the version that does the trick!
I just hope I can reuse all my others scripts just the way they are with new version.
Thanks a lot Jeff!
Cheers!
Hi Jeff,
I am using Orace 12c (Release 12.1.0.1.0) database and Oracle Sql Developer version 4.1.5.21.
All table’s primary key columns are generated with GENERATED ALWAYS AS IDENTITY property. I understand that oracle internally created sequence (seq name assigned with random value) and assign it as default value.
Now because of this every time i compare to database, all IDENTITY columns appears as difference between two databases (because of sequence name difference assign by Oracle randomly). I would be exclude all these columns here (tried with excluding sequence but it didn’t work).
Please advise if there is any solution for this issue or any other alternative.
Thanks,
Anand
In Data Modeler when “Previewing” a DDL is it possible to change the termination default semi colon to another character (i.e. slash ‘/’)?
hello !
i would like to ask you guys about the best and the most efficient method to upload million of records to one if the created tables of the database in sql developer and oracle 11g express edition
Thank you in advance
Hi Jeff,
I am new in SQL Developer. I want to store bangla text in oracle 11g database. I have created a table with column type NVARCHAR2(50). My Database character set is AL32UTF8; Now I want to insert ঢাকা in my table and while I query on the table it should show me ঢাকা on result set.
Could you please suggest me what to do?
You shouldn’t need to do anything special, just pick a display font for your editors that supports the characters you want to display.
Java is unicode by default.
Hi Jeff
Thanks a lot. it just worked for me and many many thanks for prompt reply.
But I can not set font for Unit Test Implementation window.
For example, I want to run a test with expected result set to ‘РФ’, but after my test is executed I receive the message: Expected: [‘????’] Received: [‘РФ’]…
Would you please help with that?
I can attach screenshot of this issue.
I saw you are using conemu for sqlcl.
Can you tell the font type and the sqlprompt string you are using?
Hi, when I ctrl+click on a function in another package that the one i’m currently in, it redirects me to the top of the package and not the function itself like other IDEs do.
It’s the same case for all my team, is this a bug or a feature ?
Thanks
________________________________
Oracle SQL Developer 4.1.3.20
Version 4.1.3.20
Build MAIN-20.78
IDE Version: 12.2.1.0.42.151001.0541
Product Version: 12.2.0.20.78
The same behaviour here!
Ctrl-Click leads to the top of the package instead of going to the specific code in the body.
SQLDev Version 4.1.5.21 build MAIN-21.78
it’s doing that correctly in 4.2…
what’s your exact scneario, so I can test?
what you are describing WOULD BE a bug
scenario:
1. from navigator tree dbl click to open pck_body_a
2. scroll trough code to find a reference to say: pck_body_b.proc_func
3. hold down and hoover over the pck_body_b.proc_func name (it turns into a link)
4. click on the link with down
5. pck_b opens (not the body!) and places the cursor first position first line
Hi Jeff,
Last week i created a “create view…” script and executed it against the database from within SQLDeveloper. All went fine.
The same script executed in SQL*Plus crashed while installing.
My bad, i left some whitelines into the script.
I love to work with SQLDev but it seems to execute code different from SQL*Plus.
Is there some property/-ties i can set in SQLDev so SQLDev ran into the same errors like SQL*Plus?
Greetz,
Mark
>>I love to work with SQLDev but it seems to execute code different from SQL*Plus.
exactly, they’re completely different programs – if you’re going to write your script in one tool and deploy in another, you need to test-test-test.
we emulate SQL*Plus behavior in SQL Developer when running your scripts with the F5/Execute as script button.
What version of SQL Developer are you running?
and please share a code example so I don’t have to guess what problem you’re running into.
Wow Jeff! That(JeffSmith)’s fast! Great! 🙂
Running all on windows with SQLDev Version 4.1.5.21 build MAIN-21.78
The sample:
CREATE OR REPLACE FORCE VIEW V_test
AS
select kol1
, kol2
, kol3
, datum
from test
where rownum<10
;
Between kol2 and kol3 there is a whiteline and this “beautifully” 🙂 compiles in SQLPlus:
SP2-0042: unknown command “, kol3” – rest of line ignored.
SP2-0042: unknown command “, datum” – rest of line ignored.
SP2-0734: unknown command beginning “from test_…” – rest of line ignored.
SP2-0734: unknown command beginning “where rown…” – rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
1 CREATE OR REPLACE FORCE VIEW V_test
2 AS
3 select kol1
4* , kol2
No errors.
SQL>
With whiteline it only compiles in SQLDev, without the whiteline it compiles in both well.
Thanks in advance.
Greetz,
Mark