Nearly 7,000,000 Oracle professionals use SQL Developer on a regular basis. Have a question about Oracle SQL Developer? Searched this blog and couldn’t find the answer? Ask away!
If your question is about Oracle Database, SQL, PL/SQL, etc – go Ask Tom!
Feel free to ask anything you want, but I’ll feel free to send you to Support or our Forums if it goes sideways.
Note: This page has turned out way more successful(?) than I would have ever imagined. Please keep these things in mind when asking questions.
- I am NOT support. Don’t expect me to log bugs for you, or give you official timelines on bug fixes, enhancements, or product releases.
- I AM NOT SUPPORT. Don’t open an SR with My Oracle Support AND leave a question here. Pick one and go with it, and when in doubt, go to My Oracle Support.
- I try to answer questions as quickly as possible. If you don’t get an answer, ask me for an update. I may have just forgotten or overlooked your request.
Go!
7,788 Comments
Hey Jeff – I left a reply to your reply under a post you made about 6 months ago regarding MS Access and SQL Developer you advised just using an older version of SQL Developer…), so please disregard one or the other of these posts…
The question that I posed is whether there is a decision about discontinuing support for ODBC/MS Access connections is on the level of policy or is on the level of actions with inadvertent results that will be addressed in future releases? I have written a number of PL/SQL scripts that use this connection for migrations (from MS Access to our Oracle Enterprise DB) and was hoping you had some general suggestions for work arounds…..
My sincerest apology for double posting, but regardless – much Aloha and Mahalo from Honolulu….
…and yes I am THAT guy who cornered you at Oracle Open World 2014…
We need to find a straight up Access JDBC driver. It’s not our policy, it’s that Java removed the ODBC bridge tech in Java 8, which meant we lost the Access support we were using.
Best workaround is to use SQL Developer 3.2 probably – there’s nothing in 4.1 that would make the Access migration easier, it basically just let you copy tables and data over.
Thanks Jeff – and fyi, the 4.0.3 version works as well.
Okay, I take it back – the Access tab is there in SQL Developer, but the ODBC driver is not working (as you knew…) Looks like it’s 3.2 or nothing…..
Hi Jeff,
While copying and pasting tables in the relational model, I see object names are copied adding “v1” as suffix (applies for tables, primary keys, unique constraints, etc.)
Is there anyway to specify a given PREFIX (or at least sufix) while copying, like if I have MY_TABLE I would like “FOO_MY_TABLE” instead of “MY_TABLEv1” ?
Thanks,
i don’t think that’s configurable, but you can always change it afterward
Hello Jeff,
In SQL Developer version 4.0.3.16 with Built main 60-84, when there is an error, it clearly point to the line number as shown below:
“Error at Command Line : 193 Column : 8
Error report –
….
”
Do you any idea if that feature is still available in the newest version 4.1.3.20 – Built MAIN-20-78?
I try to re-create the error in the new version but it does not show the Error at Command Line: 193 Column: 8 any more.
Thanks,
HD
“
yes?
Well, I meant that the “Error at Command Line : 193 Column : 8” which helps for debugging no longer show in the newest version 4.1.3.20.
Thanks,
HD
the screenshot I posted is from version 4.1.3
Is these something in the Features that I need to enable? Please advice. Thanks.
What did you turn off?
What code are you running, give me an example.
I did not turn off any thing. All I did was download that new version then import the configure file to the new version and start using by create a new temp table with a query from different tables. Then I got error ” SQL Error: ORA-00942: table or view does not exist”.
In the previous version, it shows the line number where that error occurs “Error at Command Line : 146 Column : 3
Error report – SQL Error: ORA-00942: table or view does not exist” so you can go there and look for that specific line rather then go thru the whole script but for this new version it no longer show like that.
ok
so
send me a create temp table statement/script I can test with.
if you want my help, i’m happy to try. if you want to post to the forums, awesome – that’s great. but doing both is a bit insulting.
Thanks Jeff! I fixed the error. It was a typo of the table_name in the “from”. I was just curious why the message of “Error at line …” was not show in the newest version. I am sorry about the two postings and have deleted the other one. Thanks again!
Hi Jeff,
I have a data model and I would like to replicate some tables as temporary tables. (Application will first use temporary tables and then move data to permanent tables once user press SAVE).
Using sql developer data modeler:
1) How can you specify that a given table is a temporary one?
2) is there any way to replicate a subset of the model (say three tables with their relationships) indicating that the replicated subset will be temporary tables?
Any tip on how to model this using SQL Data Modeler would be great.
So far I have seen that this is usually done via CREATE TABLE using SELECT, but I guess that misses also sequences and relationships?
Thanks a lot,
1. in the physical model
2. replicate..yes, you can COPY tables to new objects in the diagram. Select, right-click, copy, paste.
I want to export two columns, doc_id and photo(blob) together so that a person can refer-to or look-up a photo by its doc_id. How to do this (keeping them together) ? Tried SQLDev export and download, maybe I’m not setting the right options?
The workaround we did was to provide hyperlink to open the image file and send the zip file (report + images). Make sure to save in jpg format – .dat will not work on all computers. Is there an easier way?
why not create an APEX app to have your users refer to the BLOBs? exporting them means you’re taking the files off the db server…the best you could do in SQLDev is to try this I think
Great! thank-you very much Jeff.
Hi Jeff, Thank you for your blog and helping us.
I have on question. I have seen below info many times in results.
Never got a chance the see the query which displays this into.
Do you know how to display this?
——————————————————–
Running as: | @
——————————————————–
Query run date-time | Fri 03-28-2014 @ 18:11:56 CDT |
——————————————————–
Thank You.
where exactly have you seen this? those messages aren’t familiar to me as anything automated we do around running scripts
Thank you Jeff for reply!! I found the code which was used in procedure.
prompt ————————————————————-
prompt Running as: | &_user@&_CONNECT_IDENTIFIER
prompt ————————————————————-
prompt Query run date-time | &_date; |
prompt ————————————————————-
Hi, I appreciate the blog, and I greatly appreciate SQL Developer. One frustration – I spent a lot of time when writing packages to include the tags and comments that would be included in the “Generate DB Doc” feature. It worked great in version 4.0, but all my “methods” documentation disappeared in 4.1 (currently running 4.1.1.19 19-59). Did the syntax change or will the feature be back?
OH…that would be a bug, and it’s not one I’m aware of. Can you send me a sample stub so I can test something exactly like you’re doing?
How-to:
1. Create a mini-package header and body with doc tags; in my case I use the package body for the comments, as it is easier to keep in sync with changing logic:
CREATE OR REPLACE PACKAGE aTEST_FOR_ORACLE
/**
* Project: Enrollment Project
* Description: Data return for reporting and email
* DB impact: YES
* Commit inside: YES
* Rollback inside: YES
* @headcom
*/
AS
END aTEST_FOR_ORACLE;
CREATE OR REPLACE PACKAGE BODY aTEST_FOR_ORACLE
AS
/**
* Project: Enrollment Project
* Description: ENRL_REPORTING_PKG – Data return for reporting and email
* DB impact: YES
* Commit inside: YES
* Rollback inside: YES
* @headcom
*/
/**
* Prototype for internal use only procedure – the procedure performs a consistency check between coverage types selected and
dependents found.
*
* @param pI subscript of plan table element to check
* @param pCvrg_Lvl coverage level to check
* @param pFound set unconditionally to ‘y’
*/
PROCEDURE Confirm_Error_Lvl_Chk(
pI IN INTEGER
,pCvrg_Lvl IN VARCHAR2
,pFound IN OUT VARCHAR2
);
—
/**
* The procedure performs a consistency check between coverage types selected and
dependents found.
*
* @param pI subscript of plan table element to check
* @param pCvrg_Lvl coverage level to check
* @param pFound set unconditionally to ‘y’
*/
PROCEDURE Confirm_Error_Lvl_Chk(
pI IN INTEGER
,pCvrg_Lvl IN VARCHAR2
,pFound IN OUT VARCHAR2
)
AS
BEGIN
NULL;
END Confirm_Error_Lvl_Chk
;
END aTEST_FOR_ORACLE;
2. In a 4.1 version of SqlDeveloper, right-click the connection, select “Generate DB Doc”, deselect all object types, select package bodies, “OK”, wait for completion, when the results display in the browser there is no method documentation.
3. Repeat in a 4.0 version, the method documentation displays on the first display after clicking the package name.
i can’t get your code to compile, it’s not valid PL/SQL
so all of your procedures are private, no specs? that might be the issue
if you add your notes to the spec, or even declare your procedures in the spec, I’m guessing it might work
Thanks. It’s not worth further effort.
Agree, it would not have compiled if pasted into SqlDeveloper as one element – I did not include the slashes following header and body because I typically work with header vs body separately and paste into S.D. Sorry. My original point was that you lost a feature between 4.0 and 4.1, and this is still true – there are reasons why some methods are package-local, and because of that, I document all my methods in the body rather than in the header. I am able to get the method documentation from the header, but not the body. Apparently I am a member of a very small population.
hello, please am new and am just starting ro learn oracle sql. Am having difficulties in connecting a sample table HR..please what sample table can i use for my training and where can i get it
all of the sample schemas are available on GitHub actually
Hi Jeff,
I’ve enabled the option to display worksheet output in a zebra pattern (Database->Worksheet->’Grid in checker board or Zebra pattern’), but was wondering if it’s possible to change the color theme of the result set. I did some investigation and found information on setting up a system-wide theme, but nothing on the result set theme specifically.
Thanks!
Paul
That’s not configurable, yet. It’s on our to-do list.
Hi Jeff. I’m new to Oracle and am trying to write a summary report using data from my database. I have a database with about 8 tables in it. I’m trying to write a query that will give me things like total counts and sums of the data that is in the tables. I can write individual queries to get me each report item by itself but is there a way I can get the results with one query or view that shows all of my report items together? Joins allow me to combine fields from multiple tables and show it in one result, but since I am creating new column names or fields with my queries, it doesn’t look like joins are the solution. Unions seem to combine the result of 2 queries but i want to show the result of each individual query inside of my main query as it’s own column. For example, I have 3 tables: A, B &C. Table A has fields w,x,y,z Table B has fields x,v,t,u and Table C has fields t,q,r,s. I want to write a script that will give me the total unique counts of x, total counts of t and the sum of r. The result I’m looking for would look like a table or list with the headers of: total unique count of x, total # of t, and sum of r. How do I do this?
Are tables A, B, and C related?
yes, tables A, B, and C are related.
Its a bit hard to glean *exactly* what you’re after, but perhaps something like this:
select count(distinct a.x), count(distinct b.t), sum(c.r)
from A, B, C
where a.x = b.x
and b.t = c.t
or perhaps you meant something like this:
select a.x, count(distinct b.t), sum(c.r)
from A, B, C
where a.x = b.x
and b.t = c.t
group by a.x
If you want to elaborate more, give us some sample data and expected output, head on over to asktom.oracle.com and ask a question there, and we’ll help out
Cheers,
Connor McDonald
Hi Jeff
Could you please suggest how to get results using something like this (pipeline + CLOB):
SELECT * FROM table(dbms_stats.diff_table_stats_in_history(‘ME’,’MY_TABLE’,SYSDATE-1,null,0));
To obtain results in SQLPLUS I use
set long 30000
show long
and it works.
But in SQL Developer I get:
“ORA-20010: DBMS_STATS INTERNAL ERROR in report_line : Length of line “SOURCE A : Statistics as of 11-APR-16 08.48.40.000000 AM AMERICA/LOS_ANGELES
” exceeds 80, line length = 82″
Any ideas?
Thank you.
Roman
what’s linesize set to? 80 sounds a LOT like linesize, esp since it’s talking about ‘length of line’
Hi Jeff,
Thank you for reply.
Are you talking about this “linesize”?
show linesize
linesize 1000
SELECT * FROM table(dbms_stats.diff_table_stats_in_history(…));
“ORA-20010: …” exceeds 80, line length = 82″
I think It should be somewhere inside SQL Developer. Some limit. But I cannot find out where it is 🙁
Thank you.
Roman
Hi,
I have another question for you today Jeff. I used in the past the ability to add extension in sqldeveloper in regards the context menu. it served me well for Oracle connections.
I’m right now trying to do the same for mssql and mysql connection unfortunally those do not show up. The need is easy is to let the developer create query as :
select top 10 * from #OBJECT_NAME# order by id desc
or
select * from #OBJECT_NAME# order by id desc limit 1,10
Thank you for your help.
Ricardo
Not supported for non-Oracle connections, sorry
thanks, I will provide the use of “snippets” as alternative.
Hi Jeff,
A little question.
In the editor when I open the sql of a table I see the schema owner. I would love to see a schema owner when I open a package or package body, but I can’t find the property. We’re on Version 4.1.2.20.
On schema objects, non pl/sql, we use dbms_metadata to generate the DDL. Including the SCHEMA is an option. When we use the PL/SQL editor to pull up PL/SQL, we get the source directly from the _SOURCE views. We know what schema it belongs to, so we don’t need to add the SCHEMA. to the CREATE OR REPLACE.
Now, if you open a pl/sql file that has it in it, we’ll honor that of course.
You can also generate the PL/SQL with the SCHEMA. in the CREATE or REPLACE if you use Tools > Database Export. In that way, you could generate all the PL/SQL source files as you wanted, assuming you checked the ‘schema’ option.
Hi Jeff,
How to migrate TOAD connection to SQL Developer?
Thank you,
Adnane
One at a time.
Yes
Hi Jeff,
I have about 3 years using Oracle SQL Developer and when I want to turn some code into comments, I had to use the Toogle Line Comment function to do it. However it “comments” a full line or selected lines using the characters ‘–‘.
Is there any way to “comment” selected text without commenting a full line? using the characters “/* */”? Hope you can help me.
Thanks.
No, but you can wrap — commented lines with /* */ pairs using the Formatter. Not sure that will help you..
Not really, I always need to comment a specific part of a line, never the full line. Is there any workaround?
Not today, sorry
hi Jeff, I am going to be upgrading my pc shortly and will need to obtain a new copy of Oracle SQL Developer. I am currently using 11g 4.1.2.20.
It is a x32 pc. My new system will be x64. I see a lot more options for downloads available since the last time I downloaded the software. Is there somewhere you can direct me, or just describe briefly the different verisons. I see a standard, enterprise, express editions. I am wondering if I should get the 12c version instead of 11g. Are you able to provide any insight?
thank you…
Download SQL Developer from here:
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
Those versions (12c, 11g) and editions (standard, etc.) you mentioned are for the database; SQL Developer will connect to them all.
Hi Jeff,
How displaying column names when a query returns no result ?
Thanks a lot.
Sylvain
Hi Jeff ,
happy that you did some of the cosmetic change regarding the search text vs background colors :).
I m writing you to suggest you a feature in sql developer. I myself keep all the connections for all my database systems ( mainly oracle, mysql, mssql) into the product and try to find the best way to organise those.
In doing so I noted two little things that could be a good asset to add into the product.
1rst multiple level of folder as of today it seemed that we can use only one ?
2nd I notice that sometime I want to put additional context of the name of the connection however it seems to me that it s not a good place to do so. It will be good I think to have a free field as “comment” into the connection editor for us to put some additional information regarding the connection ( i.e what is it , what is for, who is using it etc … )
Here you go , did not read those suggestion in the blog sorry if it s repetition to you….
Thanks you.
Ricardo
I ALSO want to have nested connection folders. It’s a standing enhancement request.
cool … and what do you think about the idea to have an additional free text field in the connection editor ?
Thank you !
what would you do with it? Just read it when seeing the connection properties? not sure i see the value there or the problem you’re trying to solve
The goal will be to add some context information about the usage of one connection vs another .
for instance a have a convention in the naming of the connection using [server name] + [dbname/schema] + [user]
some time I create multiple instance with same serve name and db schema but with different users that I have some specifications for instance here you have a list of hypotetic comments that could be added to connections:
* connection used by cron automatic job to process this and that
* connection for that “kind” of users that have limited access to this portion of the db
* this connection / server is used to managed that kind of process
* this connection is used only 2 times a month to process the expenses report of sales representative
hope that helps clarifying my initial post.
thanks for reading !
Hi jeff did you see my answer to your comment ? ok if you don t think it s worth but wanted to ear you about it.
I support this idea. The comment/description field for connections would be very nice.
In your email newsletter I see that it always puts an emoji at the @orcl> between the L and the greater than symbol. Example:
HR@orcl < img src=”http://s.w.org/images/core/emoji/72×72/1f37b.png” alt=”?” class=”wp-smiley” style=”height: 1em; max-height: 1em;” %gt; < img src=”http://s.w.org/images/core/emoji/72×72/1f37a.png” alt=”?” class=”wp-smiley” style=”height: 1em; max-height: 1em;” > >
Is there a deeper meaning here or is it a Microsoft (Outlook) vs Oracle thing? Cheers 🙂
No deeper meaning, I just like showing that SQLcl supports a SQLPrompt that uses emojis, and it reminds me that work can be fun 🙂 ?
Hi,please tell me any tool that formatted my oracle code.
yes, sql developer has an oracle code formatter
Hi Jeff, I just moved an Oracle 10g schema to Oracle 12c using the DataPump export and import wizards. Very painless. Much easier than other methods I’ve used over the years. One question, though….Is there a way to see the SQL generated by the wizard?
Thanks,
Tom
Yes, go to the PL/SQL tab on the last page of the wizard
Having trouble connecting SQL*Developer to a SQL Server database. Downloaded the Third Party JDBC Driver and unzipped in C:\apps\TDS, so the entry in Third Party JDBC Driver is C:\apps\TDS\jtds-1.3.1.jar. Open a new connection, give it a name, enter my username and password, click the box for Save Password and on SQLServer. Put in the Hostname and use the default port (1433). But when I click on Retrieve database (as seen in a few blog posts on how to set this up) I get….nothing. Blank. Nada. If I click on Test I get: Status: Failure – Test failed: Login failed for user ‘dl23’. What did I miss?
Check the sql server logs to see if there’s a security rule preventing your login from working.
Don’t believe there are any rules preventing my login. With the Microsoft SQL Server Management Studio installed on my desktop I can access the remote database no problem. I’m using SQL*Developer 4.1.3.20 with jtds-1.3.1.
ok, but check the logs anyway
Checked logs. Nothing that wasn’t expected found.
Hi Jeff,
Thanks for providing a great site and lots of SQL Developer tips! I’ve been using Oracle SQL Developer at my work for about 6 years (mostly running queries & reports, but have done a few inserts & update scripts as well). I work as an IT Business Analyst. My only training is taking Oracle Database 11g Intro to SQL and Program with PL/SQL in-classroom courses through Oracle University in 2010, bugging my DBA with my SQL questions and learning that way and reading your blogs on your site, which have been awesome! I want to take my Oracle knowledge further by going the SQL Developer route (Database Application Development – Learning Path and get certified).
I don’t see too many Oracle training classes in my area (Sacramento, CA), what would you recommend? Self-Study? Practicing in Oracle Live?
Thanks,
Catherine
The Northern Ca Oracle Users Group meets quarterly – big 1 day conferences, in San Jose. I would try to get to one of those, and get some advice from your peers. You can of course always do self-study. There’s so many free resources out there, I wouldn’t limit yourself to formal/$$$ programs.
Thanks Jeff, I will definitely check out the Northern California Oracle User Group! What other free resources would you recommend.
Does SQLcl properly read and “use” NLS_LANG environment variable?
My test case is as follows:
1. export NLS_LANG=POLISH_POLAND.EE8MSWIN1250
2. run sqlcl and connect to DB (11.2)
3. compile package from the script (Polish characters in CP1250 encoding inside)
4. open the package from DB in SQL Developer
Polish characters are lost and substituted with ‘?’. Moreover NLS_LANGUAGE and NLS_TERRITORY (from NLS_SESSION_PARAMETERS) are also different then ones set in NLS_LANG.
Using SQLcl 4.2.0.16.049.0842 on linux host.
The same package compiles correctly in SQL Developer (POLISH_POLAND.EE8MSWIN1250 set explicitly in preferences).
Hi, Jeff.
Could you tell me if I had done anything wrong in my “SQLcl vs. NLS_LANG” case or SQLcl just ignores NLS_LANG? Or any other solution?
I’ve checked out that compiling (with SQLcl) the script saved in UTF-8 encoding (which is the default one on my computer) leads to proper character conversion and preserves all characters in the database. It suggests that SQLcl ignores NLS_LANG and uses OS’ character set. Am I right?
Thanks,
Tomek
we don’t set those, we don’t read the registry like Windows SQL*Plus does – you’ll need to set them in your login script
Jeff, thanks for your replay.
I use SQLcl on the Linux machine, not Windows. I’ve tried with setting NLS_LANG *environmental variable* before starting SQLcl. But it doesn’t work. So what does?
What do you mean by “set them in your login script”?
Thanks,
Tomek
in your login.sql script, if you have one, add
ALTER SESSION SET NLS_LANGUAGE=AMERICAN…
We’re not picking up the NLS OS env variables with SQLcl
Hi Jeff,
Thanks for a great site! I always catch something new about SQL Developer.
I have a question about vertical selection in SQL Developer (like in Notepad++).
Thanks,
We kind of have that today, see this post.
Yeeees
Thank you
hi Jeff, I am using 11g version 4.1 so I cannot explode the * but the ctrl+shift functionality works like you described in your post.
this is very helpful. i saw in your post from several months ago about adding the ability to drag the cursor down instead of clicking each time. You had said that it was something being considered. I just wanted to see if there was any update on that feature….
thanks!
There IS an update. The cursor down mode widget is showing up in our code editor for early v4.2 builds. So keep your fingers crossed.