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
Jeff, how do I make colored output with SQLcl.
I tried
set sqlformat ansiconsole
followed by
select ‘@|red red |@’ red from dual;
The result set returns a red ‘red’, yet It also returns ‘@| red red |@’.
( See also http://dba.stackexchange.com/questions/150029 )
PRETTY sure that’s a bug, when the developer wakes up this morning, I’ll show it to him to make sure.
Thanks for looking into it!
Hi, Jeff!
Same problem here.
The ANSI expected output is ok but, after that the command appears either.
I capture the “bug” to explain better (see on the above link):
https://s17.postimg.io/g5q1wirr3/Cropper_Capture_4.png
My sqlcl version is the latest ( sqlcl-4.2.0.16.260.1205 ).
I’m running it inside ConEmu on an Window 7 machine.
Update: running in DOS prompt results in the same problem.
Sorry about my English!
No problem, and that’s a bug.
Hi Jeff,
How do we go about housekeeping the unit test repository tables?
ie. tables likes ut_suite_results, ut_suite_item_results etc.
Is there any built it process for this?
Thanks.
define ‘housekeeping’
Are the any automated tools to maintain the sizes the UT repository tables?
No…I think you’d have to have tens of thousands of unit tests before this was ever a concern. And maybe not even then.
Hi Jeff!
How can i see xmltype value in debugger? I recompiled sys.xmltype for debug and type in watches changed to xmltype from opaque but value still empty.
Also if i right click variable in watches there is “Object Display Preferences” line and inside are options “Invoke toString Method” and “Evaluate Expression”. If i could invoke getClobVal method then i get value of xmltype, is it possible? Also how this “Evaluate Expression” works? May be documentation exists?
Thanks in advance.
I’m not sure I can get to this until next week, but I’ll try.
Hi Jeff!
Looks more like next year now 🙂
Did you get any information about xmltype during debug?
Guilty as charged. Open World is upon us and I’m slammed for the next 10 days. I’ll try to remember to try again then.
Hi Jeff!
Do you have some time for xmltype debugging?
I’m not a XMLDB guy, so not sure I could help you. I’m happy to take your question to a guy that could though.
Hi Jeff
In the SDDM Reporting Repository, is it possible to get information about the Remote design status of a table (same as in the Summary property of the table)? At least as a flag telling that this table is a Remote design table.
Thanks
Jo
Hi Jeff,
Let me add some details.
We’ve got a pretty large data Warehouse datamodell – about 3500 tables. After migration from single user ERwin we now have about 30 different Designs in SDDM (v412) and are trying to consolidate/get overview by using the Reporting repository/schema. In our environment, large Designs run too slow against Subversion and in addition we run many Projects in parallell. To help out both these issues our plan is to have something between 10 and 20 designs in the future (logically separated to avoid Projects influencing each other to much).
But some tables are common to multiple designs and we would like to use the Remote design functionality in combination with the Reporting repository to ensure that every table definition is maintained in one and only one Design. But so far, we have not been able to locate “Remote design” Properties in the Reporting repository (and the “Used as remote object” in the Reports module of SDDM only gives false).
Have you got any hint of how/where to get the Remote design status of a table in Reporting repository?
Another thing – with multiple designs – it would be extremly useful to have some kind of batch update of the Reporting repository (check out + export: for a given list of designs). The same applies to generating reports. Can this be solved now or are there any future plans?
Greetings (and thanks in advance)!
Jo
Hi Jeff,
I’ve been testing SQLCl and I issued a DESC dba_tables command, there weren’t answer from the client, I mean, my ssh client was locked 😮 .
I had to close the client and re-enter again.
Of course, with SQL*Plus I hadn’t any problem.
My host is Linux x86-64.
Greetings!!
appears to be fixed in the version we’re about to ship
Hi,
i connected to my sybase database in Oracle Sql Developer 4.1. Connection successful. but when i select File->Data Modeler-> Import -> Data Dictionary am getting the following error in compiler logs.
Error during handleEvent action Data Dictionary(id=176).None of the attached controllers handle….
i want to create a ER Diagram from the connected database schema.
Please help on this as i am blocked on accessing the Data Dictionary.
data modeler officially supports SQL Server but not Sybase. try making a generic JDBC connection, don’t use the sql server connection type to connect…it might work
Hi Jeff,
I’m having a small issue with using SPOOL to the delimited format. I specified my delimiter in Preferences as pipe (“|”), however when I spool to a csv file using the /*delimited*/ hint, commas are forced every time. I also tried using the SET colsep “|” command, but that also does not change anything. The only workaround that I’ve found so far is to run a SQL statement, than right click the output window and save it with pipe as delimiter – after doing that, running scripts using SPOOL results in pipe delimited files.
Is there any better way to achieve this?
I’m using SQL Developer Version 4.0.2.15
Hi Jeff,
Do you have any ideas? Checked on the current version and the problem still exists.
Cheers,
Piotr
We installed ORDS3.0 and now trying to connect to the tomcat from SQL Developer. We get the error: Error occurred retrieving Privilege. The target server failed to respond.
Do you know what this could be?
Hi Jeff,
How do I escape a special character in the password?
FANT @ //sa-dba-3-24.losrios.edu:1526/tstclone >bridge table_same as “jdbc:oracle:thin:fant/””myp#ssword””
2* @hrdevdb3-24.losrios.edu:1526/hrtst.losrios.edu”(select * from table_same)
ORA-01017: invalid username/password; logon denied
ORA-01017: invalid username/password; logon denied
Sep 08, 2016 5:46:50 PM oracle.dbtools.db.DBUtil handleException
SEVERE: Warning, unhandled exception: ORA-00942: table or view does not exist
Sep 08, 2016 5:46:50 PM oracle.dbtools.raptor.newscriptrunner.commands.BridgeTableDef execute
SEVERE: Issue running BRIDGE command
I tried putting the quotes around the \ before and after the password as well and it still didn’t work. see above.
Thanks
Tao.
Hi!
Have 2 quetions if someone can help out with them.
1., Can I set the double quote to highligh string as single quote ones? As ‘test string’ will be marked as string, but not “another test string”, which is used for column namings.
2., After a Ctrl+R Replace all, can I set in config to close both 2 rows with ESC or other hotkey rather than with mouse? Can I dismiss somehwo (would be better with shorcut key) the Highlighted replaced strings after checked them and looked ok? Right now I can only end the highlight with another CTRL+R replace invoke and close in with mouse. This is quite a long precedure with a replace all. I know I can disable highlight the result, but it is a good feature, just would like to dehighlight after it.
Thank you,
Tibor
Sorry Tibor, I’ll try to get a second to answer these for you tomorrow.
Hi Jeff, you gave my company a webex demo of SQL Developer Data Modeler and the SVN integration several months back. Lots of good information. I believe you said there was a configuration setting to make SQL Developer save models in LOTS of little files or FEWER bigger files? But I cannot locate that setting in build 4.1.1.888
Thank you,
–Tyler
Hey Tyler, I think the problem is the version you’re using.
Try getting v4.1.3 and then, look at your design properties.
I hope things are going well there!
How do you add multiple constraints on one table column? I have an existing constraint defined in my logical model that uppercases. I also need a unique constraint. How would I accomplish that in SQL Data Modeler?
two different questions, there’s a unique property you can set on any column, that will handle that
you can define one check constraint per column in the modeler if you’re looking at the column preferences, but if you look at the table level constraints, you can add as many column CHECK constraints as you want there
Many thanks Jeff.
no problem! sometime give me a call and let me know how you’re using the modeler with your EPM stuff and maybe I can do more outreach there…
A quick question regarding viewing the database package body in sql developer. We’ve a procedure and within it a list of special characters that need to be removed. After we compile the package the db dictionary shows a different special character than I coded. Example. I’ve compiled the package that had a trade mark symbol and it stored as a diamond symbol. Not sure why it stores differently.
Appreciate all the help.
it’s possible that the character is stored just fine and your editor font just doesn’t support the display of the trade mark symbol. Check your editor display font in the preferences.
it’s also possible you sent up a character to the database to be stored that’s not supported by the database’s character set.
I have somehow caused my SQL Developer to open previous queries in tabs next to my connections. They used to open right of the start page. Any ideas on how to return them to their former place? Thank you.
Window > Reset Windows to Factory Settings
whats is the performance Tuning and what is it’s steps ?
I review the Database Status from DBA Panel (very nice feature) but working with RAC view that SESSIONS graph doesn’t work with GV$…
Any ideas about how to view GV$… information ?
Do you know if there is any option about working with alarms from Database Status ?
Regards
I upgraded from SQL Dev 3.x to 4.1.3. When I run some existing queries with lines that have been commented out but still have an ampersand substitution variable (e.g. — a.org = ‘&p_org_code), the run of the query is now prompting to fill in the substitution. In 3.x (and someone here has 4.0), it did not do that. In those older versions, a commented-out line is commented out and no prompting occurred. I looked for a preference, but could not find anything related (at least that I could recognize). Any thoughts on how to get 4.1.3 to stop prompting for a commented-out line’s substitution variable?
Add set scan off to your script
Thx for the reply! Although “set scan off” does indeed stop the prompting for “&” variables in conditions that are commented out, it also stops the prompting for “&” variables in conditions that are NOT commented out, but exist in the same query. Sometimes I have queries that I write for support purposes and depending on the information I have been given, I may uncomment/re-commment a condition line before running the query. Wondering what changed from 3.x/4.0 to 4.1, to cause it to look at a commented-out line differently.
what changed is we got pretty much 100% to what and how SQL*Plus behaves. SQL*plus has that behavior, so now does SQL Developer.
Jeff – while logging into sqlcl, I get the below error. How to suppress it?
os: SUSE Linux Enterprise Server 11 (x86_64)
sql user/pwd@oradb
[INFO] Unable to bind key for unsupported operation: backward-delete-word
[INFO] Unable to bind key for unsupported operation: backward-delete-word
[INFO] Unable to bind key for unsupported operation: down-history
[INFO] Unable to bind key for unsupported operation: up-history
[INFO] Unable to bind key for unsupported operation: up-history
[INFO] Unable to bind key for unsupported operation: down-history
[INFO] Unable to bind key for unsupported operation: up-history
[INFO] Unable to bind key for unsupported operation: down-history
[INFO] Unable to bind key for unsupported operation: up-history
[INFO] Unable to bind key for unsupported operation: down-history
[INFO] Unable to bind key for unsupported operation: up-history
[INFO] Unable to bind key for unsupported operation: down-history
SQLcl: Release 4.2.0.16.175.1027 RC on Tue Aug 30 10:25:07 2016
Copyright (c) 1982, 2016, Oracle. All rights reserved.
You shouldn’t see those once the product is released.
Hi,
I used sqlcl-4.2.0.16.308.0750-no-jre.zip download file.
SQLcl: Release 4.2.0 Production on Mon Nov 07 14:20:33 2016
The problem is still present !
Hi Jeff,
I have create a sequence and put it in the cart, but moving it to schema cloud fails with
Error starting at line 5 in command:
CREATE SEQUENCE “FUX_SEQ” MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 3 NOCACHE NOORDER NOCYCLE NOPARTITION
Error at Command Line:5 Column:132
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 – “SQL command not properly ended”
*Cause:
*Action:
created it where, 11g or 12c?
I am using SQLDev V4.1.3.20.78 Windows 64-bit with JDK 8 included, is SQLdev independent of the java installations on my machine. The reason I am asking is keep getting Java errors Example
Export connections give java.lang.NullPointerException
Export data set java.lang.reflect.InvocationTargetException
And I would like to know if this a SQLDev “bug” and I need to raise this with My Oracle Support, or something my desktop support team have done to my machine.
Thanks
Hey Jeff, I am using Version 4.1.3.20.
I would like to do a diff between two tables, located in the same database. EG…
Source:
Database: Production10
Schema: PointOfSale
Table: OrdersBKUP
Target:
Database: Production10
Schema: PointOfSale
Table: Orders
Is this possible in SQL developer?
They need to have the same name and be in different schemas.
Hi Jeff –
It will be better , if SQL developer handle the ref cursor output from package similar to PL/SQL developer. ( ie , I can scroll thru the result set , export into CSV , it is much more friendly) .
My 2 cents ( feedback).
Thanks
The path you entered, is too long. Enter a shorter path
File Name could not be found. Check the spelling of the filename,
and verify that the file location is correct.
In my SQL Developer (4.0.2.15 build 15.21), when I tried to import a PIPE separated text file (.txt) through the import utility, getting error message as “There are no readers registered for the txt type.”. With this error message, the operation is aborted and could not proceed further.
What does this mean? I could open the “.txt” file with default editor Notepad.
could you help me on this!
Save the file with a csv or tsv extension. Then use the wizard and set the delimiter character to a pipe.
Ok – I get it and understand – you are not support – but where can I log a potential bug with SQL Developer 4.1.3.20 Build MAIN-20.78?
Just upgraded the Oracle DB to 12C. Now when modifying Materialized view – get an error about a wrong clause and it blows away the MV. If I take the DML and paste to a worksheet – and remove the no in memory clause – all works good. If I know where to log the bug – I will.
thx in advance.
show me the incorrectly generated MV code please
you would log a bug with My Oracle Support via a service request
CREATE MATERIALIZED VIEW TEST1 AS
SELECT
SYSDATE
FROM
DUAL;
Modify the SQL Query to:
SELECT
SYSDATE AS DT
FROM
DUAL
go to DDL tab – get the following:
DROP MATERIALIZED VIEW TEST1;
CREATE MATERIALIZED VIEW TEST1
LOGGING
TABLESPACE BAR
PCTFREE 10
INITRANS 1
STORAGE
(
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
NOCOMPRESS
NO INMEMORY
NOCACHE
NOPARALLEL
USING INDEX
REFRESH ON DEMAND
FORCE
USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE AS
SELECT
SYSDATE AS DT
FROM
DUAL;
COMMENT ON MATERIALIZED VIEW TEST1 IS ‘snapshot table for snapshot FOO.TEST1’;
an ORA-00922: missing or invalid option.
If I copy the dml to a worksheet, and then remove the NO INMEMORY clause – it works. If you just click the OK button – one gets an ORA-00922: missing or invalid option message and the MV is dropped.
You then have to grab the dml from the Edit MV window (which is now a simple create statement) and paste into a worksheet – and remove the NO INMEMORY clause – and it all works.
i’m on 12.1.0.2 and this works – are you on 12.1.0.2 or 12.1.0.1?
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
Working with our DBA team and we now have an SR open as well….