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,937 Comments
Hi there,
I’m a Toad user. I have just started at a new job and they only use SQL Developer. I am trying to find out if you can export data from a table into an excel instance. I want to be able to set my cursor in my own pre-existing spreadsheet and have my oracle data export to that specific cell. I am only finding an export tool via SQL Developer that creates a new workbook and spreadsheet for every export. As an analyst this is a pain in the butt when I’m working in 20+ tables, as I have to pull all my data back into one workbook.
I tried googling the posts here but the only one relevant to what I am looking for was an explanation of how Toad works 🙂
Please help. My freeware Toad is only good for 3 months, after that I am totally on my own. As an analyst I have always used Toad as I find it has a lot of functionality for manipulating / analyzing data that I haven’t found as easy to do / find via SQL Developer.
that excel instance feature makes native windows calls, which we don’t do since we’re java
you’re best bet is to copy/select/paste the data over where you want it if you’re not writing out a new sheet/file
Thanks so much for the suggestion. I didn’t even think about the Java vs Windows situation.
how to import a test case from one schema to another schema ?
I want to import test case from 1 schema to another schema through command line interface. but in ‘sdcli unitest -imp’ there is no option to remap schema. so i am getting error while running the test cases in new schema that object of old schema must be declared.
how to remap the schema while importing test case ?
hi jeff,
Can you tell me how to share test cases across the schemas.
i am not able to share a test case from one schema to another.
or this is not possible as of now ?
Hi Jeff,
I have local version of subversion and I was able to upgrade all previous versions of SQL developer without any problem until 4.1. It uses SVNKit 1.8.5 and it is not compatible with previous version 1,7.11. I can see files and revisions but I cannot see history “Version History”. New version of SVNKit use different format to store history. Is there any way to convert data?
what version of svn are you running for your repository?
Subversion Client: SVNKit/1.7.11 Client (SQL Developer Version 4.0.3.16)
Java(TM) Platform 1.7.0_55
Oracle IDE 4.0.3.16.84
Versioning Support 4.0.3.16.84
no, the version of your local SVN, not your client
you’ll probably need to upgrade it
I’m using local version.
I did first time
Team->Create local Repository -> repository path
C:\Users\XXXXX\repos
during each upgrade I was exported /imported connection.
my repository URL
file:///C:/Users/XXXXX/repos
you prob need to create a new local version with 4.1 and move the repository from the 4.0 version over
Hi Jeff,
in our company we have to work with special company security_role, the original DBA-role is granted to this security_role, so I have dba privileges but starting the Data Pump Export/Import Wizzard brings up this error:
“DBA privilege required.”
Do you have a workaround expect granting dba-role directly?
many thanks for a great product and a very nice site
Stefan
http://www.thatjeffsmith.com/ask-a-question/
Paustrofred
[email protected]
Hi Jeff,
I am happily converting from Toad to SQL Developer. Smooth ride, so far.
What I find annoying is this:
whenever a SQL error occurs with a statement in the worksheet, the application tells me what went wrong where, but does not place the text cursor at the position indicated.
ORA-00924: missing BY keyword
00924. 00000 – “missing BY keyword”
*Cause:
*Action:
Error at Line: 12 Column: 7
So I am forced to manually find the position where the error occured (line 12, column 7 in this example).
Do you happen to have an idea how to solve that (apart from stuff like showing line numbers and having the current position in the status bar)?
Many thanks for this site,
Fred
if it’s a big statement, run it as a script…the error message will be hyperlinked, clicking it will take you to the line of the error
Thanks this works like a charm!
Version 4.1.0.19
Build MAIN-19.07
Jeff,
Found comments from 2011 on doing this with Toad, but not SQLDev. I want to filter tables/views based on a common column name? For cases where referential integrity may be application based. :/
Dave
View -> Find DB Object
Toggle on ‘column’, do your search across one or more schemas. The search results turn into your new object browser.
Very cool. Thanks.
Jeff,
Originally my DBMS output text from procedures / packages would appear in my “Running: IdleConnections%…” tab down below. This is the tab generally near by “Messages”, “Statements” & “Output Variables”. For some reason unbeknownst to me, it decided to stop.
Now I am forced to use the separate DBMS Output window, and declare “DBMS_OUTPUT.ENABLE;” in each package / procedure I use.
What happened? Is this an SQL developer thing, or is this something that I need to set on Oracle’s side?
Thank you for your help, I appreciate it.
SQL Developer: Version 4.1.0.19 Build – 19.07. Running Windows 7 Enterprise 64 bit.
Set serveroutput on, put that in a script. Tell sqldev to run that script on new connections. I have a post or two on it here.
SQL Developer 4.1.0.18
I have a variety of sql scripts stored on a windows box “winserv” in directory “shared\myoraclestuff”.
I am having issues trying to open the scripts to a worksheet because I cannot browse to “\\winserv\shared\myoraclestuff”.
Can you help?
Map it as a network drive.
We have gone away from using mapped drives because of the virus vulnerability. Because I am a team of 1, I will just copy the scripts to my computer and/or start storing them in the database. Please consider the UNC option as an enhancement for the future. Thanks.
Jeff,
We just switched from oracle client 11.2.0.1_32 bit to oracle client 11.2.0.4_64 bit and I get the following error when running SQL Developer 3.0:
c:\app\ted\product\11.2.0\bin\ocijdbc.dll : Can’t load AMD 64-bit .dll on a IA 32 bit platform.
Not sure how to proceed
Thanks
You’re asking a 32 bit application (SQL Developer running under a 32 bit Java) to load a 64 bit DLL – and it’s like, huh?
Your homework:
I’ve noticed that in the new version of sqldeveloper, after launching the debugger, a line of text is shown: ‘The debuggee process is running. To interact with the Stack window, pause the debuggee process.’ Perhaps debuggee should be debugger?
The exact version:
About
—–
Oracle SQL Developer 4.1.0.19
Version 4.1.0.19
Build MAIN-19.07
IDE Version: 12.2.1.0.42.150416.1320
Product ID: oracle.sqldeveloper
Product Version: 12.2.0.19.07
Version
——-
Component Version
========= =======
Oracle IDE 4.1.0.19.07
Java(TM) Platform 1.8.0_11
Versioning Support 4.1.0.19.07
Where are you seeing that?
Here’s what I see in the debugger log panel:
Connecting to the database HR.
Executing PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( ‘192.168.0.11’, ‘4000’ )
Debugger accepted connection from database on port 4000.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.DISCONNECT()
Process exited.
Disconnecting from the database HR.
Debugger disconnected from database.
We need the ability to select several packages in the schema and use “Save Package Spec and Body” . But this is not possible when selecting several packages. and Quick DDL only creates DDL for the spec.
I know there is the export wizard but the programmers coplain it is too long. Is there a “right-click” option for generating DDL for spec and bpdy for several packages at once?
The wizard is too long? Too many steps you mean? But to answer your question, no.
Yes I meant Too many steps . It’s a pity – why doesn’t the Quick DDL generate the ddl both for spec and body? We always generate DDL for both at the same time!
Maybe in the next version?
i repeatedly export a whole project, consisting of more than 20 types 2 scripts and one package (header and body), in one go by using a cart. maybe this http://www.thatjeffsmith.com/archive/2014/09/30-sql-developer-tips-in-30-days-day-8-use-the-cart-to-build-deployment-scripts/ helps
Using sqldeveloper 4.1.0.19.
When connected to a user with select ay dictionary+select catalog privs+select any table privs, why does Shift-f4 (popup describe) access ALL_* objects?
This is very slow in 12c (1 min almost) on an Oracle EBS 12.2.4 database. Instead a query on the DBA_ views will be very quick.
Here is the query being used by “Popup describe”. Can we use DBA_ views instead somehow?
select object_type,owner,object_name,rank
from ( select object_type,owner,object_name,0 rank
from all_objects
where object_name = :NAME and UPPER(owner) = nvl(:OWNER,:SCHEMA_CONTEXT)
and object_type not in ( ‘SYNONYM’ )
union all
select ao.object_type,ao.owner,ao.object_name,decode(UPPER(syn.owner), nvl(:OWNER,:SCHEMA_CONTEXT),10,20)
from all_objects ao,all_synonyms syn
where ao.owner = syn.table_owner
and ao.object_name = syn.table_name
and syn.synonym_name = :NAME
and ( UPPER(syn.owner) = nvl(:OWNER,:SCHEMA_CONTEXT)
OR
UPPER(syn.owner) = nvl(:OWNER,’PUBLIC’)
)
)
where object_type not in (‘PACKAGE BODY’) and rownum < 100
order by rank, CASE object_type WHEN 'TABLE' THEN 0 WHEN 'VIEW' THEN 1 WHEN 'INDEX' THEN 2 WHEN 'PACKAGE' THEN 3 WHEN 'PACKAGE BODY' THEN 4 ELSE 5 END
I’m using the black background with colored text in the worksheet editor running on Windows 7 and printing to some huge B&W laser printer – but you got me thinking. I explored the printer properties boxes and found a setting “Print with Black” (instead of Fifty Shades of Grey) and that fixed the problem.
Thanks!
cool, glad you found it!
Jeff,
I am using SQL Developer 4.1.0.19. I rarely print code out but I had to print out some code for a co-worker to look over. I found that SQL Developer took all the colors of my code and tried to shade them on a black and white printer, rendering some words unreadable.
I tried again setting the color appearance to “Monochrome” rather than “Color” and I had the same result. Is there some way to make SQL Developer just print without trying to approximate color on a black and white printer?
i’ve never printed code from SQL Dev – so I was curious to try
i’m just getting black text on a white background by default
I’m on 4.1, OS X, on a Canon color printer
I just hit File > Print, took all the defaults
Don’t mind the yellow scribbling, I’m out of paper as my kids have stolen all of it
Hi Jeff,
currently i have to do a lot of work regarding datapump im- and exports.
therefore i use the dba view and the datapump reports and wizzard.
whenever i click on one of the folders “Exportjobs” or “importjobs” a list of existing datapump jobs is shown. and below a small tab titled “log files”. This tab loggs the following error:
CHECKING MASTER TABLE
PL/SQL-Prozedur erfolgreich abgeschlossen.
Commit
Fehler beim Start in Zeile : 35 in Befehl –
BEGIN
IF (:UTL_ERROR IS NOT NULL) THEN
Raise_application_error(-20001,:UTL_ERROR);
END IF;
END;
Fehlerbericht –
ORA-20001: STATE IS NOT ‘NOT RUNNING’
ORA-06512: in Zeile 3
I’m using sqldev version 4.1.0.19 with db version 11gR2
Greetings from abroad
Peter
Hello ,
I have installed sql developer 4.1 and jdk1.8.0_45 on linux machine. I am in /opt/sqldeveloper/sqldeveloper/bin directory and firing ‘sdcli ‘ command to run unit test. but getting error that command not found. I ran test cases on windows machine with same ‘sdcli’ command from same folder and it worked. Why it is not working on linux ? Is there any extra settings which i missed ?
Hi Jeff,
exporting or saving my PL/SQL package code leads to unsatifactory results.
The EOL on my windows system remains the LF character. How to change to CR LF ? I allready modified presets for environment and export. Doesn’t work. can you help me out ?
Mats
What version are you using?
Hi Tom,
I’m using SQL Developer Version 4.0.0.13. I was asked to take a numeric field that display numbers like 830, 1015, 1250, and so on, to display like a time in hh:mm format (8:30, 10:15, 12:50). This is the table and the data field: ZZTMEDTTRN.IN_PUNCH
There is no need to determine if it’s AM or PM here. Just hh:mm.
I’m stumped. Any suggestion on how to force a numeric data field to display like time would be appreciated. Thank you!
I assume that ORA_DEBUG_JDWP=host=mypc;port= 1234 is set on my desktop and I substitute the database server for ‘mypc’?
Jeff,
You did a nice job on ‘How to Start the PL/SQL Debugger’ back in Feb. 2014. And the 4.1 User guide has 1.7.2 (Remote Debugging). But very basic. Would you have an example of setting up remote debugging?
Dan
Hope to do this for a web application that is running, Don’t think the Oracle client running the application can issue the debugger command.
Please go into more detail on defining the ORA_DEBUG_JDWP operating system environment variable.
I don’t, but a few others do.
Start with Barry’s.
I had read Barry’s posting. He too uses the execute DBMS_DEBUG_JDWP.CONNECT_TCP(‘127.0.0.1’,4000); in his example. Looking for one that uses the ‘other option’ (i.e.: Operating System Variable).
Hi Jeff
I am using Data Modeler Version 4.1.0.866 for a SQL Server DB.
When I sepcify a Datetime column it produces Datetime (8). How can I stop it from adding the (8) as its not required and it causes my downstream process to fail?
Thanx
Asif
Hi Jeff,
I’m trying to work with a package on a 9.2 Database, but i can’t see the body and specification. I got this error sometimes ORA-01460: unimplemented or unreasonable conversion requested tips. It works on 4.0.3 but 4.1 didn´t. Please Help!!
4.1 uses a 12c JDBC driver, it sounds like it doesn’t like a 9iR2 database, which is no longer supported. I would keep 4.0.3 on your desktop for the older databases and 4.1 for ones that are still under support.
HI Jeff–
Thanks for the last reply. It helped me a lot.
I created a Package as below
create or replace package pkg_Types
as
cursor c is select AREAID ,
AREANAME ,
SEQNO ,
AREASC ,
COMPID, CAST(NULL as varchar2(1 char)) AddEdit from mArea;
Type Area_typ is table of c%rowtype;
end pkg_Types;
Here I have a table mArea
Name Null Type
——– ——– —————–
AREAID NOT NULL NUMBER(10)
AREANAME VARCHAR2(50 CHAR)
SEQNO NUMBER(10)
AREASC VARCHAR2(10 CHAR)
COMPID NUMBER(10)
I Executed this block
declare
V_Bulkcopy pkg_Types.Area_Typ;
rc sys_refcursor;
Begin
SELECT AREAID ,
AREANAME ,
SEQNO ,
AREASC ,
COMPID , ‘A’
BULK COLLECT INTO v_BulkCopy
FROM mArea;
open rc for select * from table(V_Bulkcopy);
sys.DBMS_SQL.Return_Result(rc);
END;
This Block is Giving me below Result
ATTR_1 ATTR_2 ATTR_3 ATTR_4 ATTR_5 ATTR_6
———– ————————————————– ———– ———- ———– ——
-153 asdasdasd 139 ASDASD 1 A
But I need Result as below:
AREAID AREANAME SEQNO AREASC COMPID ADDEDIT
———– ————————————————– ———– ———- ———– ——-
-153 asdasdasd 139 ASDASD 1 A
So I changed the Code like below:
declare
V_Bulkcopy pkg_Types.Area_Typ;
rc sys_refcursor;
Begin
SELECT AREAID ,
AREANAME ,
SEQNO ,
AREASC ,
COMPID , ‘A’
BULK COLLECT INTO v_BulkCopy
FROM mArea;
open rc for select ATTR_1 AREAID ,
ATTR_2 AREANAME ,
ATTR_3 SEQNO ,
ATTR_4 AREASC ,
ATTR_5 COMPID ,
ATTR_6 AddEdit from table(V_Bulkcopy);
sys.DBMS_SQL.Return_Result(rc);
END;
Is there any other way so that I can get the column names. Please let me know.
Thanks
Sayam
I am trying to integrate unit test SQL developer with team city. i want to run test cases from command prompt on build server. In the command there is a parameter called ‘-db’ where we pass connection name of owner. Now there will be a new user for each build so i can not graphically login every time to create a connection name. Can you give me better option by which that connection name will be automatically created or instead of connection name, schema name will work ?
Is there any way by which for every new schema i can automatically create connection name ?
It might be easier to attack it from the other end
>>Now there will be a new user for each build
Don’t do that. Or use a proxy user. Or create public synonyms. Or…
This is not possible. Even if it is possible to create connection for each schema is tricky then also it is fine, but the option which you have given is not possible. We cant change build settings. It will create a new schema for each build. So we will have to follow that process only.
I want to create new connection only.
As mentioned in help document, there is an option of ‘CREATE LOCAL CONNECTIONS’ which will create a connections for all unblocked users. But that is not working.
We are integrating SQL developer with team city (our build system) where there will be new schema for each build. To run the test cases from command line we need to specify connection name for that new user without login to the database through (which is the only way to create connection name).
in Help document i found that there is a way which will create connection for all unblocked users. But for this we need to change configurations.
In Tool-> preferences -> Database -> Advance
Configure oracle client where i browe the folder where my oracle client is installed (path of ORACLE_HOME) but it failed.
Error :
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 E:\app\product\11.2.0\client_1\bin is present and precedes any other client installations.
java.library.path = C:\Users\pasalkar\Desktop\sqldeveloper\jdk\jre\bin;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;C:\ProgramData\Oracle\Java\javapath;E:\app\product\11.2.0\client_1;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\Intel\OpenCL SDK\3.0\bin\x86;C:\Program Files (x86)\Intel\OpenCL SDK\3.0\bin\x64;C:\Program Files\TortoiseGit\bin;E:\HashiCorp\Vagrant\bin;.
Can i get the solution for this or is there any other way by which i can create a connection name for each build schema automatically ?
i THINK you need to take the ‘BIN’ off your path
i tried without ‘BIN’ as well. but it did not work.