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,803 Comments
Jeff, couple things. Been using the “sqlcl” tool and the potential is excellent. SQLCL has been “early adopter” for quite a while and seems to be suffering from a lack of documention (inline and online).
I assume it is under staffed since it’s a free tool. I view this product as an important “generational leap” from sqlplus – and I would like to start using it in all my environments. To do that in my mind, it’s got to be bundled with the Oracle DB installs (like sqlplus is), and or it’s got to be a “production” product at very least.
When will Oracle take steps to make this happen! Hopefully in my lifetime.
Thanks, Mike
We’re taking steps today, it’ll happen in your lifetime. Maybe even this year.
SQL Developer is already bundled with Oracle DB Installs, and SQLcl will be in that.
The docs will happen. For now, the best resources are the built-in Help topics.
I have to write a short college paper on the comparison and contrast of Oracle and Microsft Access parallel processing. I am not asking you to provide a ton of info, but would apprciate it if you could provide a brief overview of each. I have zero knowledge of Oracle and very limited knowledge of Access. Thank you!
Access or SQL Server? If it’s Access, it’s going to be a really short paper.
Jeff,
I really appreciate such a quick response. It’s only a 1 -2 page paper to “compare and contrast the parallel processing capabilities of Oracle and Microsoft Access”. However, I may put a tad of info about SQL server as well, but will mainly focus on the info in the quotes above. Also, would it be okay to reference you and your site with the information? Thanks again.
Steve
i would have bet a dollar that Access offers zero parallel processing. is it even a multi-threaded program?
anyways, on the Oracle side, apart from our official database docs, Oracle-Base is an excellent resource.
How do I import data from an excel file that has 10 tabs into 10 Oracle tables?
You would run the wizard 10x, one for each worksheet/tab.
Thanks for quick reply to my question. I’ve been doing that but it takes many hours. I was looking for a process that reads each tab and load the data from each tab to a corresponding table in Oracle.
If you’re doing this on a regular basis vs ad hoc, then I agree…you need a better solution.
Setup a command line process that automates this for you, totally possible if the xls file and the tables are always the same.
I was just wondering if there is a reason why SQL Devoloper won’t let you use bind variables in an update statement? Is there a way to make this work?
what exactly are you trying?
update ps_y_slr_stu_award set Y_AWARD_STATUS = ‘ACOP’ where emplid = :1 and aid_year = :2;
whenever I try to do something like this it tells me I have an invalid number
it works for me, but i don’t know how ps_y_slr_stu_award is defined and I don’t know what values you passed
Hmmm alright well thanks
Hi Jeff,
every time my collegue connects with sql developer with thick client to our 11.2.0.3 Oracle Database Standard Edition One he receives this error message: “ORA-00942: Tabelle oder View nicht vorhanden
Statement………: SELECT count(REVISION) FROM USER_ORDS_REPOVERSIONS WHERE REVISION >= :”SYS_B_0″ ”
Do you have an idea what can be the issue here?
Thanks,
Best regards,
Stefan
no, but does USER_ORDS_REPOVERSIONS in fact, exist?
no, it does not exist. we were wondering why it is selected…
we do query for it, to look to see if ORDS is installed on your database, but we shouldn’t be popping up any error messages in the UI
Hi, I upgraded today to latest version (v 4.1.3.20) and am noticing that when you search for a package from “Find Database Object” panel and click on your package or package body, it no more displays the package contents, it just displays “create or replace ” on the first line and nothing else, is this a known bug ?
I can confirm that these packages that I am trying to open does have 1000s of line of code as I can still go back to my v 4.0.0.13 and do the same actions and get to see package contents. Any help/suggestions? Thank you !!
When I click on Tables to view columns, SQL Developer freezes and says, “Waiting for Editor to initialize”. I have to close it and re-open to use SQL Developer. What broke?
not sure. does this happen every single time you go to click on a table? just once? only certain tables? after a restart, does it work as expected?
If I click a table in my own schema, it returns after 5 seconds. If I close SQL Developer, and re-open it, and click directly on the target table from a different schema, it never returns.
Question: In SQL Developer why is it still necessary to commit after a query that uses a parallel hint (otherwise the parallel sessions are not closed or de-allocated) ? For example, if I select /*+ parallel(5) */ from table; and forget to issue a commit,
I’ve still got 5 open parallel sessions…using resources both on the DB server and on my local workstation.
I realize commit is necessary for DML or to let others see new DDL objects but why for a SELECT ?
probably b/c you haven’t read all the data back…if your query brings back 1000 records but you’ve only fetched the first 50, the other 950 are left waiting on the server to be queued up by your 5 proceses
New tables have been added to a schema for which I’ve been maintaining a Relational Model (with many subviews). I tried to add the tables to the model using the ‘Synchronize Model with Data Dictionary’ option, but the new tables aren’t added to the Tables list of the Rel. model.
If I start from scratch and re-Import the whole DB from the Data Dictionary, the new tables are listed.
How can I add the new tables to my existing Relational model?
Hello,
is there a way to perform a full remote database copy using the command line tools of the sqldeveloper?
I know there is this database copy wizard, which perfectly suits my needs from the functional point of view.
I just want to make:
– a full copy of the database (no selection steps needed)
– of a remote database,
– without having admin permissions on the source database,
– without full oracle client installation (sqlplus, import, export etc.)
– using the command line.
Thanks!
Dear Jeff,
I have started using Oracle SQL Data Modeler recently and I am trying to fully understand how relations are defined in the logical model:
I have a business entity “Customer” with attributes ID (Primary Key) and NAME and DEFAULT_CURRENCY.
I have a business entity “Currency” with attributes ID (Primary Key) and CURRENCY_CODE and NAME.
I would like to make a relationship in Oracle Data Modeler which links DEFAULT_CURRENCY in “Customer” to a given ID in business entity “Currency”, and I am struggling to discover how to do it.
So far I can only define relations if I use CUSTOMER_ID and CURRENCY_ID then an attribute CURRENCY_ID is created in “Customer” entity, but I would like to be able to choose name of the attribute linking both entities and maybe (not sure if it is a good idea or not) be able to use just ID as primary key in both entities. In this particular example you see that CURRENCY_ID in “Customer” loses the important I formation that we are pointing to the “Default currency for this customer”. Same for other scenarios where you could even want “DEFAULT_CURRENCY” and “BALANCE_CURRENCY”.
Thanks,
Jeff,
Thanks for an earlier post(s) on Cart. I have instructed my team to use it a the preferred form for doing deployments. I have one wish for it, when using the Separate Directory or Type Files options it builds the driving script at Generated-20160328080522.sql.
It would be nice to have it be something more related to the what is being exported. Like the dominant schema, or connection information.
Thanks again for Carts. It has been a game changer, with sdcli I can do continuous integration with test without much intervention!
I saw somewhere that SQLDeveloper used to include macro recording/playback capability that disappeared around version 4.0. Are there any plans to bring it back?
it’s still there, you just need to assign a keyboard shortcut to the operation – just search on ‘macro’ in preferences > shortcut keys
Thanks for the answer. I’m using version 4.0.0.13, and “macro” doesn’t return any matches. Is there some add-in I need to make it available?
I checked in version 4.1.3
Hi Jeff,
SQL Developer Version 4.1.3.20
is it possible to have the SQL Developer data grid show MS Word Formatting characters? I have a XML process that’s failing as two customer addresses have word formatting characters at the end of address – possibly caused by operators copy/pasting from MS Word.
TOAD displays these in the data grid, but I can’t seem to put my finger on an SQL Developer option to do the same.
Thanks
two thoughts come to mind, use the replace() function to pull out the data with a character that WILL display, and/or 2, play around with the fonts to see if there’s one that has something to support the formatting chars…or query out the data in hex. i guess that’s 3
@OP
select substr( replace( dump( text, 17 ) ,’,’), 15) txt from TAB
will show non ascii values as control characters or numbers. DUMP displays the bytes stored in the database, the 17 say show the byte as its ascii value
maybe a better resolution is
in environment
NLS_NCHAR_CHARACTERSET = ???? –< whatever the windows client is
then
cast( txt as NVARCHAR2(64) ) txt from tab
should show as pasted.
Thanks Jeff for your support to sql dev user: I am a new user and I reluctantly closed my sql results output where usually I see the results in the table from the queries that I run. Somehow I can’t see it anymore. Please advise.
So if you run a new query, you don’t see the results?
Yes Sir, meanwhile the script output displayed the number or rows with no errors.
Thanks
try Windows > Reset Windows to Factory Settings
Thanks Jeff, however the result tab is not still showing.
something, really really bad has happened then. find your system4.1.3… directory in your OS user appdata/roaming folder, should be under SQL Developer, and NUKE it. then restart sqldev and see if that’s fixed it.
Thanks Jeff that worked out !
We have an Oracle 11g database and have a hung freeze or slow when we try to use data masking, i think maybe is ‘cos a great amount of foreign keys and related tables? Thank u very much in advanced.
Maybe you’re right, but do a session trace and figure out for sure.
I am using sql developer 4.1.3.20.78. I make can make a simple query and save it as .sql.
When I open it and view the worksheet vie the sql statement is there.
When I then go to the Query Builder view I can see the header for the table, but the columns do not display. Also if I drag another table to the query builder, it will not display.
What am I doing wrong?
Tnx
Just a note to say I’m really enjoying working with SQL Developer, we moved away from a “commercial” IDE and once we worked our way through the myriad of available options and got comfortable it’s a really great tool great to work with – cheers!
Hello Jeff I have a question to you , are you familiar with problem regarding sqlcl (4.2.0.15.349.0706) when you try to display plan of your last query using this one : SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR) ? I get wrong results using this one in sqlcl and proper results using sqlplus ? Do you know why ? When I test it I used the same sql queries in sqlcl and sqlplus.
Adam
I am now, stay tuned. And thanks.
No problem please let me know if discover why , I have my own
suspicion.
Dear Jeff,
Thanks for running such a cool site. You helped me out a lot.
I’m on Version 4.1.2.20 and I found a little problem or is it a setting I can’t find? When you hava a JAVA file and you look at the source, the part “CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED scheme.name as ” is missing. To me it’s like the “create or replace PACKAGE BODY scheme.name ” is missing.
Perhaps it’s intentional, but I doubt that?
Can you help me out on this pesky one?
I don’t work with stored java procedures much, but… ‘When you have a JAVA file and you look at the source,’ => where are you getting this file from? Are we generating it from an existing java class/resource/something?
I made the file myself, to find files on the server in a particular directory and put them in a list .
this is the complete code, so whit the first part you don’t see in SQLDeveloper. (You can see it in some other tool).
You have to make a
CREATE OR REPLACE TYPE T_VARCHAR2_4000_TAB AS TABLE OF VARCHAR2(4000)
/
first.
and then:
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED “Files” as import java.io.File;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.OracleDriver;
public class Files {
public static String dirList( String inBronDir
, String inDelimiter) {
StringBuffer dirlist = new StringBuffer();
File path = new File(inBronDir);
File[] f = path.listFiles();
String file_name;
for(int i = 0; i 0 ) {
dirlist.delete(dirlist.length()-1, dirlist.length());
}
return dirlist.toString();
}
public static ARRAY dirList(String inBronDir) throws SQLException {
// ora:
Connection conn = new OracleDriver().defaultConnection();
ArrayDescriptor ad =
ArrayDescriptor.createDescriptor(“T_VARCHAR2_4000_TAB”, conn); —
// make dir-listing and save in File[] (for testing isFile)
File myDir = new File(inBronDir);
File[] files = myDir.listFiles();
String file_name;
String[] fileList = new String[files.length];
// files with isFile() FALSE
// go with value NULL in the array.
for(int i = 0; i < files.length; i++) {
if (files[i].isFile()) {
fileList[i] = files[i].getName();
}
}
return new ARRAY(ad, conn, fileList);
}
}
Hi Jeff,
Help! I’ve imported an existing Oracle database into DataModeler, and I’m having trouble explaining what the Relational diagram relationship lines ‘mean’. Here’s the example:
In Barker-speak,
a PRODUCT may have 1 or more ALTERNATE_NAMEs
an ALTERNATE_NAME must describe a PRODUCT
The Logical diagram shows what I expected (sorry about the rough approximation of the diagram ). Optional at the Product end, mandatory at the Alternate_Name end.
Product ——– __________< Alternate_Name
In the actual database, ALTERNATE_NAME.PRODUCT_ID is a Not Null field with an FK on it, referring to PRODUCT.ID (ie. the PK in PRODUCT)
So far, all good.
But the Relational diagram's relationship is shown as a solid line connecting the 2 tables. I've visually lost the optionality at the PRODUCT end.
Product_______________< Alternate_Name
What am I missing? Is there some way to 'show' the optionality at the Product end? If not, how do I (and the Clients) tell from the diagram that there IS that optionality?
Tearing hair out,
Ellen
you should see the ——— lines in the relational diagram as well. do a RE of HR and check the line drawn to show the manager FK that has EMPLOYEES join to itself
Hi Jeff,
Thanks for getting back to me on a Sunday!
I looked at the Employee – Manager example you mentioned. The difference is – the Manager_id field on which the FK is built is nullable. (ie. Managers can exist without any Employees)
In my situation, the Product_id on which the FK is built ISN’t nullable (ie. an Alternate_Name cannot exist without being associated with a Product. We don’t want a bunch of orphan Alternate_Name rows in the table.)
It seems that you get a solid line if the FK field is NOT NULL, and a dashed line if the FK field is nullable.
Is that right, or am I missing something?
Thanks,
Ellen
Ellen,
Given the notation you describe, you must be working with a logical model. Look in the “Relationship Properties” box. (Double click on any relationship in the model -OR- Right click on the relationship in the Browser and select Properties.) There will be 2 checkboxes called “Source Optional” and “Target Optional”. Use these to control the notation as you described.
So, why is this not already setup?
The database import (reverse engineering) is limited by the constraint enforcement in the database. The database can only enforce the mandatory constraint at the foreign key in the ALTERNATE_NAME table. There is no mandatory constraint enforcement at the foreign key table. In other words, the database can’t enforce that at least one ALTERNATE_NAME record must exist for every PRODUCT record. Since this constraint is not in the database, the import can’t produce it.
If you want to ensure each record in the PRODUCT table has at least one “ALTERNATE_NAME” record holding a foreign key for it, you will need to run a report periodically to confirm all is good. Enforcing this constraint with trigger logic or application logic gets tricky when there are simultaneous DML running against the same records.
Duane
I’m trying to use SQLcl with OraOpenSource’s apex-diff and Node.js, and I am getting an error that appears to be due to SQLcl’s handling of SELECT CURSOR() statements when SQLFORMAT is set to anything other than default.
In my example below I create a table, insert a record and then SELECT from it in various ways to show the errors.
Whenever I set SQLFORMAT to non-default (CSV or JSON) and do a SELECT CURSOR()… I get the following error :-
“Invalid column type: getOracleObject not implemented for class…”
Do you have any idea what’s wrong?
Cheers
Richard
e.g.
C:\sqlcl_test>sql XXX/XXX@XXX:1521/XXX
SQLcl: Release 4.2.0.16.049.0842 RC on Fri Mar 18 16:10:51 2016
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,
Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing option
SQL> create table test1 (col1 varchar2(10), col2 number);
Table TEST1 created.
SQL> insert into test1 values (‘a’,1);
1 row inserted.
SQL> set sqlformat default
SQL Format Cleared
SQL> select * from test1;
COL1 COL2
———- ———-
a 1
SQL> set sqlformat json
SQL> select * from test1;
{“results”:[{“columns”:[{“name”:”COL1″,”type”:”NUMBER”},{“name”:”COL2″,”type”:”N
UMBER”}],”items”:
[
{“col1″:”a”,”col2″:1}]}]}
SQL> set sqlformat default
SQL Format Cleared
SQL> select cursor(select * from test1) from dual;
—–
COL1 COL2
———- —————————————
a 1
SQL> set sqlformat json
SQL> select cursor(select * from test1) from dual;
Invalid column type: getOracleObject not implemented for class oracle.jdbc.drive
r.T4CResultSetAccessor
{“results”:[{“columns”:[{“name”:”CURSOR(SELECT*FROMTEST1)”,”type”:”NUMBER”}],”it
ems”:
[
]}]}no rows selected
SQL> set sqlformat csv
SQL> select cursor(select * from test1) from dual;
Invalid column type: getOracleObject not implemented for class oracle.jdbc.drive
r.T4CResultSetAccessor
“CURSOR(SELECT*FROMTEST1)”
no rows selected
SQL> set sqlformat default
SQL Format Cleared
SQL> select cursor(select * from test1) from dual;
—–
COL1 COL2
———- —————————————
a 1
SQL>
just what it says, the sqlformat doesn’t support the cursor() output
Hi Jeff,
I’m starting to use the Reports feature more and more of late and am really loving it. It certainly adds to my productivity by not having to issue the same SQL statements over and over with different parameters.
A couple of features that (imho) would help and add value:
1) Add a Refresh Report button to the master report that will issue the query with the previously entered bind variables. Currently, to refresh the report one needs to hit the Run Report button which pops up the “Enter Bind Values” window. I know I could set the refresh rate which results in the same query being issued at specified intervals but in many cases this is overkill, I simply want to reissue my previous query without the additional popup.
2) The “Enter Bind Values” popup is a bit cumbersome. I need to click on each on the configured bind variables to check if it is populated or not. If I have 10 bind variables configured then it requires 10 clicks to check if it has a value. I think that an editable grid/listview would work even better. I would be able to, at a glance, see which bind variables have values.
Another slight annoyance is when you enter a value for a bind variable that is more than about 50 characters. When this happens the window containing the bind variable names becomes too small and you have to resize the window in order to see the bind variables names again.
Regards,
Ron
Hi Jeff,
Is there a way to order the columns alphabetically in the Single Record View screen ?
Regards,
Pradeep
Yes, but not directly. I’ll write up a quick post for you to show how.
Thanks Much Jeff !! Let me know once the blog is ready.
You’re good to go – I HOPE this helps, at least a little bit.
Thanks Jeff !! That Works… but it kind of cumbersome. Is this in the feature list for the next releases?
Sorry, no.
Hi Jeff,
Thanks for an awesome site. It definitely helps people like me who are trying to use mac for oracle development. Here is my question:
How do i setup autofill hot key for select * from on sqldev in my mac.
for e.g. If i type sf and tab i want the select * from to be populated in the editor.
Is that even possible?
Regards,
Pradeep
it’s not ‘tab’ but it’s ctrl+space
Wow !! Really appreciate your fast response. I am exploring more on SqlDev everyday.
Honestly haven’t gone back to Toad for a week now after i installed SqlDev.
Cheers to the sqldev team !!! Keep up the good work.