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,827 Comments
I am using sql developer in mac OS X 10.9.5, but I cannot create procedure.
I had done that successfully before, but now it not working or executing. When I right click either I cannot get the feature to create procedure.
I need more info. What version of SQL Developer are you running?
Can you execute an existing procedure? Is the execute button on the toolbar disabled?
Are you logging in as the code owner or someone else – is it possible it’s a privilege issue?
Sorry for the late response. Thanks for the quick reply.
I cannot create procedure and cannot execute procedure. Even the execute is disabled too.
I am using Version 4.0.3.16 with JAVA platform 1.7.0_71.
Thank you once again for the reply.
Hi,
I’d like to use the sdcli Interface to script a Database Copy. Is this possible with the dba batch command ?
You can, via the Cart.
So you’ll need to define the database objects you want copied in the cart. Then run sdcli cart copy…
CART Usage: -config|cfg [-target|tgt ] [-logfile ] [-deffile ] -config|cfg [-target|tgt ] [-logfile ] [] -config|cfg [-logfile|log ] [-deffile ]
cart
cart
Supported commands:
export -cart
cloud -cart
copy -cart
Examples:
cart export -cart /home/carts/cart.xml -cfg /home/carts/exporttools.xml
Export the objects included in cart.xml using the options saved in exporttools.xml
cart cloud -cart /home/carts/cart.xml -cfg /home/carts/cloudtools.xml
Deploy the objects included in cart.xml using the options saved in cloudtools.xml.
cart copy -cart /home/carts/cart.xml -cfg /home/carts/copytools.xml
Copy the objects included in cart.xml using the options saved in copytools.xml
Jeff —
I using SQL Developer 4.0.3.16 with a MySQL connection. Generally speaking things seem to be working, but I can neither sort nor filter the Query Result. There are no sorting icons and if I click on the header I see a greyed out filter icon, but I am unable to activate this. All rows have been fetched.
Is this a limitation of the MySQL implementation or have I missed an option some where?
Thanks,
Thanks,
Hi Jeff,
About
—–
Oracle SQL Developer 1.5.5
Version 1.5.5
Build MAIN-5967
IDE Version: 11.1.1.0.22.49.48
Product ID: oracle.sqldeveloper
Product Version: 11.1.1.59.67
Issue:
When I am executing 2 queries separated with semicolon in worksheet I am only seeing the result of first query but if I am executing the same queries using F5 i can see both result in the script output section.
I have tried the below fix like “Automatic freeze the result tab” but its not working.
Could you please help me on this
Your best move now would be to upgrade – your software is about 6 or 7 years old at this point. The current version is 4.0.3.
Hi, Jeff.
I’ve got a problem connecting SQL Developer 4.0.3 with an Oracle RAC. I have to use various schemas in the schema. I can open one schema, open the second too, but when I try with the third it shows the progress bar “Executing…” and does nothing. It depends on the order, not the schema. I can’t use jdbc (port filtered), so my tnsnames is something like
TNS_NAME =
(DESCRIPTION_LIST=
(LOAD_BALANCE=ON)
(FAILOVER=ON)
(DESCRIPTION=
(SOURCE_ROUTE = ON)
(ADDRESS=(PROTOCOL=TCP)(HOST=server1)(PORT=1630))
(ADDRESS=(PROTOCOL=TCP)(server2)(PORT=1630))
(CONNECT_DATA =
(SERVER=DEDICATED)
(SERVICE_NAME=myServiceName)
)
)
(DESCRIPTION=
(SOURCE_ROUTE = ON)
(ADDRESS=(PROTOCOL=TCP)(HOST=server1)(PORT=1630))
(ADDRESS=(PROTOCOL=TCP)(HOST=server3)(PORT=1630))
(CONNECT_DATA =
(SERVER=DEDICATED)
(SERVICE_NAME=myServiceName)
)
)
(DESCRIPTION=
(SOURCE_ROUTE = ON)
(ADDRESS=(PROTOCOL=TCP)(HOST=server4)(PORT=1630))
(ADDRESS=(PROTOCOL=TCP)(server2)(PORT=1630))
(CONNECT_DATA =
(SERVER=DEDICATED)
(SERVICE_NAME=myServiceName)
)
)
(DESCRIPTION=
(SOURCE_ROUTE = ON)
(ADDRESS=(PROTOCOL=TCP)(HOST=server4)(PORT=1630))
(ADDRESS=(PROTOCOL=TCP)(HOST=server3)(PORT=1630))
(CONNECT_DATA =
(SERVER=DEDICATED)
(SERVICE_NAME=myServiceName)
)
)
)
Manuel, I think you need to open a Service Request with My Oracle Support.
Also, I don’t know what this means
I can’t use jdbc (port filtered)
Sorry. I wnated to say “I have to use various schemas in the server”, And thank you
Thank you anyway
Oups, i forgot the salutations !
Anyway, cheers to you Jeff, and thank you for you amazing blog, that keep me out of the mud in my work everyday!
Best regards!
Hi jeff,
Sql Developer keep telling me that it can’t open a table with a column named COMMENT. (graphicaly i mean!)(ORA-00904: “comment” : identificateur non valide)
I think this word is protected, and everything work fine when i make a query like select “COMMENT” from…
Is there any way to overcome this?
So you THINK the table or column is called COMMENT or TABLE, but it’s actually “TABLE” or “COMMENT”.
Both are reserved words. You can however force this name by quoting the object name.
Definitely not recommended. Definitely guaranteed to cause problems down the road. But you CAN do it.
Thank you!
I’m not going to touch the structure, because it’s not mine, but the advice is much appreciated.
Thank you again for your disponibility, and the help you provide.
Have a nice day!
Hi jefff I am getting the error “Missing unique/primary key” while using generate table API form SQL developer 4.0.3.15
And do you have a primary/unique? If not, generating an API for UPDATE and DELETE will be hard if we don’t know how to uniquely ID the row to be updated or deleted.
Hi Jeff,
is there a chance to supress polulating the schema name whenever i save pl/sql code to a file. for example save stored proc code as new file leads to >CREATE OR REPLACE PROCEDURE “KUR_ZL_MAN030”.”BVE9XX_GEN_030″CREATE OR REPLACE PROCEDURE BVE9XX_GEN_030″< bevore rolling out the scripts on customer systems. guess the result 😉
greetings
peter
strange. my text was somehow “crippled”. what i wanted to ask was:
whenever i save the code of a stored procedure/view etc. the schema name is inserted for example:
CREATE OR REPLACE PROCEDURE “KUR_ZL_MAN030″.”BVE9XX_GEN_030″.
what i need is the pure source without the schema name and the quotation marks. just like that:
CREATE OR REPLACE PROCEDURE BVE9XX_GEN_030
otherwise sometimes i forget to remove the clutter and roll out the script on a customers database. guess what happens when i try to execute them 😉
greetings
peter
How exactly are you saving it?
I’m in the procedure editor, I hit the save button – said file is generated/saved with contents exactly as shown in the editor. I’m not seeing the schema added or the name quoted.
It sounds like you’re using the Export feature?
If so, uncheck ‘include schema’ in the wizard. As for the quotes, there’s no way to avoid those as that’s how DBMS_METADATA spits it out.
Hi Jeff,
in the db-explorer right click on the precedure “quick-dll” (hope the menu entry is called something like that in english, i use german version) choose “save to file”
maybe you want to have a look here https://dl.dropboxusercontent.com/u/25663382/2014-10-21%2008_08_36.jpg
Hi Jeff,
Im new to SQL developer. First time I have entered the db details and clicked on save and connect and it connected successfully to the DB. But when i do the same for the second time im getting below error. Im not able to save connection or connect also but the test show Success
” failed to create naming Context for db connections”
“DatabaseConnections has no JNDI context so cannot list connections”
I have searched in many sites but in all sites they have mentioned to clear the folders under ./sqldeveloper directory everytime and restarting sqdeveloper will work but when i do so sql developer is not opening at all.
Whats the fix for this as i dont find a permanent fix in any sites.
Awaiting your reply.
Regards
Raj
Hi Jeff,
Adding to the above the version im using is 3.0.X.X but the above error doesn’t occur in 4.0.X.X . Is there anyway to avoid the above error in 3.0.X.X version since we are using that version……
Thanks
Raj
I have no idea. You need to upgrade to version 4 for multiple reasons though. Use this as an excuse to push that through.
What version of SQL Developer are you running, and on what OS?
Hi Jeff,
Im using 3.2.10.09 version. When i use 4.0.1.14 version its working fine. OS –>Windows 7 (64 bit).
Is that due to the jdk version…not sure though just an assumption…..
The jdk version thats in the 3.2.10.09 version is 1.6 and the jdk version of 4.0.1.14 is 1.7……
You could try running 3.2 with the 1.7 JDK and see what happens.
Hello Jeff,
These problems occur when testing my Instant Client. The Instant Client directory was prepended to my path and is in java.library.path below. FYI, I’ve looked in my “C:\Windows\system32” directory and there are several odbc*.* files, possibly due to installation of PL/SQL Developer.
Windows 7 Pro, 64-bit OS
Java(TM) Platform 1.7.0_25
Oracle IDE 4.0.1.14.48
Testing the Instant Client located at D:\java\instantclient-basic-windows.x64-12.1.0.2.0\instantclient_12_1
Testing client directory … OK
Testing loading Oracle JDBC driver … OK
Testing checking Oracle JDBC driver version … OK
Driver version: 12.1.0.2.0
Testing testing native OCI library load … Failed:
Error loading the native OCI library
The native OCI driver could not be loaded. The system propertyjava.library.path contains the entries from the environment variable PATH. Check it to verify that
the expected native library directory D:\java\instantclient-basic-windows.x64-12.1.0.2.0\instantclient_12_1 is present and precedes any other client installations.
java.library.path = D:\java\jdk1.7.0_25\jre\bin;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;D:\java\instantclient-basic-windows.x64-12.1.0.2.0\instantclient_12_1;D:\commands\cygwin;D:\commands\cygwin\bin;D:\java\jdk1.7.0_25\bin;D:\java\jdk1.7.0_25\jre\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Program Files\SourceGear\Common\DiffMerge\;D:\java\eclipse-standard-luna-R-win32-x86_64\eclipse\plugins\org.apache.ant_1.9.2.v201404171502\bin;D:\Program Files\nodejs\;C:\Program Files\TortoiseSVN\bin;C:\Program Files\SlikSvn\bin;D:\java\apache-maven-3.2.3\bin;.
So ‘D:\java\instantclient-basic-windows.x64-12.1.0.2.0\instantclient_12’is listed first, or close to it, in your OS PATH variable?
I’d make it the very first thing and try again.
Thx for the quick reply!
The instant client dir is the VERY first thing in my OS path. I don’t know how/where java.library.path prepends the other directories.
C:\>set path
Path=D:\java\instantclient-basic-windows.x64-12.1.0.2.0\instantclient_12_1;D:\commands\cygwin;D:\commands\cygwin\bin;D:\java\jdk1.7.0_25\bin;D:\java\jdk1.7.0_25\jre\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Program Files\SourceGear\Common\
DiffMerge\;D:\java\eclipse-standard-luna-R-win32-x86_64\eclipse\plugins\org.apache.ant_1.9.2.v201404171502\bin;D:\Program Files\nodejs\;C:\Program Files\TortoiseSVN\bin;C:\Program Files\SlikSvn\bin;D:\java\apache-maven-3.2.3\bin;
Hi,
My problem has been solved.
Turns out I was using a 32-bit JDK 1.7. After I downloaded the 64-bit JDK version, and changed %APPDATA%\Roaming\sqldeveloper\1.0.0.0.0\product.conf for this new version, the test worked.
Testing the Instant Client located at D:\java\instantclient-basic-windows.x64-12.1.0.2.0\instantclient_12_1
Testing client directory … OK
Testing loading Oracle JDBC driver … OK
Testing checking Oracle JDBC driver version … OK
Driver version: 12.1.0.2.0
Testing testing native OCI library load … OK
Success!
If you grab a table from Schema browser and you want to use query builder to create your select, if your table is large and you only want a small number of columns, is there a way to do an un-select all rather than having to uncheck each one individually? I can’t find it and I work with PeopleSoft which has really wide tables. Help!
We lost that – had it in v3.2 – I’m calling it a bug. Stay tuned.
Jeff,
What do you think is the likelihood of SQL Developer providing functionality similar to Toad’s Code xPert in future? I expect you’re familiar with this feature but if not, Toad converts PLSQL into an XML representation of the source code and then uses rules coded as XPATH statements to flag up coding issues. This would be the final feature to wean my current client away from Toad entirely.
Not very likely. My experience has been that that sort of thing is more of a sell feature than a use one. It’s popular in very large shoppes with management. So looks good in a sales presentation. But practically, not extremely useful for the end user dev.
But then again, we have the expertise to build that stuff, and if became a popular request, it wouldn’t be impossible.
For what it’s worth, I think you’re the 2nd person to ask me about it in 3 years or so.
Hi Jeff, great website. I got a problem. I love the new features in SQL Developer 4 but I cannot configure it to Copy to HTML.
How can I add Copy to HTML extensions to SQL Developer 4??
It can work on earlier versions.
Appreciate for ur help. Thanks
The developer of that extension has to update it for version 4.
Is there a way to configure a default directory for Script Runner? I’d like to type @myscript and have SQL Developer look for the script in the default directory.
It’s the same behavior as SQLPATH variable in SQL*Plus.
Thanks.
Fernando.
Tools > Preferences > Worksheet > Select default path to look for scripts.
Great. Thanks!
I’m “that guy” that uses sqlplus for everything. SQL Developer has come a long way and I’m going to take it for a spin.
Thanks for all the tips.
Fernando.
Enjoy the test drive, and let me know if you have any questions!
Hi Jeff,
I am using SQL Developer 4.0.3.16
I am trying to call or execute a function from the package on worksheet. when i run that code it prompts me to enter binds for V_return i selected Null and applied. It throws me an error saying expression is of wrong type. I am trying to execute the following anonymous block
DECLARE
MODEM_ID_LP NUMBER;
IMSI_LP NUMBER;
START_DATE_LP VARCHAR2(200);
END_DATE_LP VARCHAR2(200);
v_Return SYS_REFCURSOR;
BEGIN
MODEM_ID_LP := NULL;
IMSI_LP := NULL;
START_DATE_LP := NULL;
END_DATE_LP := NULL;
v_Return := TAP2_MOC_DETAIL_DBP.GET_BY_IMSI_DATE(
MODEM_ID_LP => MODEM_ID_LP,
IMSI_LP => IMSI_LP,
START_DATE_LP => START_DATE_LP,
END_DATE_LP => END_DATE_LP
);
/* Legacy output:
DBMS_OUTPUT.PUT_LINE(‘v_Return = ‘ || v_Return);
*/
:v_Return := v_Return; –<– Cursor
/*–rollback; */
END;
The Error Report
ORA-06550: line 23, column 18:
PLS-00382: expression is of wrong type
ORA-06550: line 23, column 1:
PL/SQL: Statement ignored
06550. 00000 – "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
Before i step through your code, which won’t work for me b/c I don’t have your code/data – does this work when running in the pl/sql editor? B/c this is our code you’re running, not yours – that is, this is what the PL/SQL editor generates when executing pl/sql objects.
Hi Jeff,
It throws the same error message when i execute it from pl/sql editor.
Connecting to the database XXX
ORA-00936: missing expression
ORA-06512: at line 13
Process exited
Disconnecting from the database XXX
Hi Jeff,
do you know if integrated GIT support in Data Modeler will be coming any time soon?
Kind regards
Axel
Soon – no.
Ever – the market will dictate that.
Hi Jeff,
im running SQL Developer 4.0.1 and 4.0.3.
i know that i can set my look and fell at
Tools –> Preferences –> Environment –> Look and Feel
but I’m searching the possibility to change the colour.(fusion blue and so on)
what should i do?
You could try hacking up some jars – which would be against our terms of use, or you could be happy/OK with the blue, or you could use SQL Developer 3.2 which supports this, or you could enter a bug/enhancement with the JDev team in MOS.
I’m having character code issues with sdcli.exe when the report is using the “script” style.
The same report having the “table” style is displayed correctly.
I tried changing the encoding settings without any luck.
Is it a bug?
Database NLS_LANG settings AMERICAN_AMERICA.EL8ISO8859P7
Sqldeveloper Version 4.0.3.16 -Encoding (Windows-1253, UTF-8…)
Windows 32bit
Probably, but what exactly does this mean?
I’m having character code issues
If you think it’s a bug, then please report to My Oracle Support.
Thank you for the quick response.
It actually means international characters are not display correctly with the “script” style.
Anyway, i thought asking you if it is a known bug before reporting to Support.
Not a known issue. Check the file encoding and the font you’re using in the text editor/shell to view said output.
Hi Jeff,
Great site! I was wanting to build an extension where I can multi-select many tables, and drop them all at once (after being prompted to confirm the list). I looked at the SQL + XML extension example in a link you provided and I see how to do a single table drop. Can you point me to a resource that could show me a multi-object example?
Thanks!
Kyle
Hi Jeff,
Just following up here to see if you had any thoughts on the above question. I would really like to do some extending of SQL Developer, but I need more than examples – is there something more in-depth that describes this extension interface and all of the options that can be used. The examples I have found online do not go deep enough into detail.
Thanks!
Hi Jeff,
Just checking in once more. Any thoughts on this? I would really appreciate your help!
Sorry, no. But we want to publish a how-to SDK when 4.1 is officially released. This type of extension will probably require a Java extension, not an XML one.
Ok, thanks for the response, Jeff! Know there is no easy way currently is a good enough for now. I’ll look forward to the how-to SDK!
Hi Jeff! I’m looking for the query that is resolved when you click the table in the tree, ¿where in SQLDeveloper can I enable it to be shown?
Thanks in advance and sorry for the poor english.
Regards!
Hello Jeff,
I have Oracle SQL Developer 3.1.07 installed on my Windows desktop. I am connecting to Oracle database on Unix Server. During the development / testing phase, Application Developer needs to take the backup of tables. Is there any easy way to do the backup and restore from SQL Developer ?
Thanks in advance.
Sandip
We have interfaces to Data Pump and RMAN – but that’s often locked down for a developer-type user.
You could also back the data out to flat files/scripts and load your tables back up afterwards, but that gets messy as the objects get bigger, in size and in number.
Thanks Jeff for your reply.
Yes, RMAN is locked for developers at our site.
I will have to execute sql queries to get the result and then manually stored them as ‘INSERT’, ‘CSV’ file etc.
Is there any way to write the script for this ? The script should be able to get the data for all the tables and offload it to CSV/ INSERT format.
Thanks again.
Sandip
If your DBAs are taking backups, you should be good. If they’re not, make sure someone is.
As for your question – yes. You can use the CART to create an export. And that can be scriptable – is that a word? – via the command line interface (SDCLI).
SQL developer window showing some extra characters, like for each whitespace it is showing a ‘.’ and for each enter it is showing “<<P" reverese of p. can you please help me out in removing these characters
Hi Jeff,
First off, thank you for your efforts on this great site. It is proving very useful for my colleagues and I, who are currently migrating, as long term users of TOAD, to SQL Developer (v4.0) .
One of the really handy features in TOAD is being able to navigate to the child table showing the foreign keys values held in a parent table whose structure I am examining.
In TOAD, the Schema Browser allows me to view the Constraints tab of my table of interest, as does SQL Developer, and then, having selected a foreign key, I can click on the name of the table (shown as a hyperlink) hosting the foreign key values and their look-up values, and a new window pops up showing me the Data, Constraints, Indexes etc. of that look-up table.
Can I do the same thing in SQL Developer? If so, kindly describe what I need to do. This will make a lot of reluctant newbies very happy.
Cheers,
Downunder Dave
Wellington
Jeff,
I am using sql developer 3.0.04. I am running a large query having 980,000 rows. I want to just copy all the data without having to requery (Crtl End or Ctrl A requeries again and then I can paste into excel. The user wants to just do copy and paste into excel without using export. Is there a fast way to do in sql developer.