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 jeff,
i using oracle 9i ,i want migration to oracle 12c ,it connection was success.
but i am connected to oracle data base from java with help of ojbdc7.jar
i am facing some issue like i am getting empty string the data type is varchar2 and i am getting value for number . i go the data base i executed the query i am getting the data,if i am using the ojdbc6.jar i am getting all the data . what is issue why ,i am not getting data. if am using the ojdbc7 . plz let me…
Thank you …..
>>but i am connected to oracle data base from java with help of ojbdc7.jar
Say what?
Are you writing a custom java application or SQL Developer?
Unit testing: global variables from startup in input and validation
I need some global variables in my test case which will be used in inputs to the ‘procedure under test’ and in the validation part.
As suggested in 1 of the post of oracle forum i made a test suite and declared same variable in startup part of Test suite and used in input query as follows:
Startup part of test suite :-
declare
A1 number(1) :=2;
begin
null;
end;
Have used variable A1 in input :
dynamic query in input :- select A1 as A1 from dual;
Tried to use declared variable A1 in input and it is giving following error.
“Suite 1 failed: ADDITION failed: !Error retrieving dynamic query values: ORA-00904: “A1″: invalid identifier!”
why this error is coming and how can I declare and use global variable ?
Please post your question to the Forums, a bit much going on to answer here.
i did post it on forum. But didn’t get the reply there yet.
Hi Jeff,
I’m new to Oracle SQL and I’m hawing a strange problem that I’m unable to resolve.
I’m running windows server 2012 R2 with powershell 4.0.
I had an Oracle SQL express database and ODAC121021_x64 installed on this machine for testing with powershell. I was successfully able to “talk” to the database using this Powershel script:
$connectionString = “Data Source=localhost/xe;User Id=user;Password=password;Integrated Security=no”
[System.Reflection.Assembly]::LoadWithPartialName(“System.Data.OracleClient”)
$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)
$queryString = “SELECT COUNT(customer_name) FROM EMPLOYEES”
$command = new-Object System.Data.OracleClient.OracleCommand($queryString, $connection)
$connection.Open()
$employeesNames = $command.ExecuteScalar()
echo “Number of employees: “$employeesNames
$connection.Close()
But when connection to our production DB I get this error – Exception calling “Open” with “0” argument(s): “ORA-28547: connection to server failed, probable Oracle Net admin error
I cannot figure out what is causing this problem.
I have removed the express database, port 1521 is not used on my machine any more. The firewall is off.
When running SQL Developer 4.0.3.16 the connection test times out – Status: Failure – Test failed: IO Error: Connection reset
I have managed to connect to other Oracle DB that we have in our company with powershell and SQL developer.
I’m able to initiate this connection from a windows 7 PC with the same version of SQL developer.
Regards,
JB
A whole lot going on here, I would recommend you open a Service Request with My Oracle Support…but how is your connection defined on your Windows Server box?
Have spent to much time trying to get it working on my machine I decided to run the SQL developer and PS script form a different machine and it works as expected.
I’m sure that the problem is caused some conflict between the applications I have installed on my machine.
Thanks!
Jeff,
I’m attempting to update an existing View by pasting a SQL statement that I edited in a SQL Worksheet. Once I save the View and attempt to Edit the View, I notice that the View has been truncated. My current SQL statement is 1063 lines, and it appears to truncate at 630 lines.
Now, if I attempt to do a Quick DDL to a worksheet, it appears as if the Line Feed characters have all been converted to spaces (my assumption) and thus I have several long lines.
Either way, I have to log into an older version of SQL Developer to retrieve the complete View if I need to make any modifications to it.
I have searched and searched and I cannot find a solution to this. Your assistance would be greatly appreciated.
I’m using SQL Developer 4.0.3.16.
Thank you, Sir.
Jeff,
is it possible to execute sql in a debug session ?
Regards Günter
Not today, but it’s on our wish list. This would require a change on the database and the tooling side.
Jeff,
When I use Data Modeler’s Reports to output physical data dictionary into xlsx file, the column list comes out in alphabetical order. Is there any way to get it in the physical order (the same as the diagrams and DDLs)?
Thank you much,
Leo
File > Reports > Tables
The only way I can get it out where the columns are sorted are if I do a search for all columns in the model, and then export the search results to XLSX – and if it’s that sort of report, I would expect the search results to be sorted.
If I do File > Export > CSV, I get what you are looking for
Running a spool sql script from SQLCL creates one empty row on the top. Running that same script from SQL DEVELOPER worksheet no empty row is created.
Script is run as:
@C:\ORACLE\SPOOL_DEPOT\csv_spool.sql
Spool script:
set termout off
set newpage none
set pages 1000
set echo off
set feedback off
set verify off
spool on
spool C:\ORACLE\SPOOL_DEPOT\asd.csv
select /*csv*/ * from mnem_sect;
spool off
set termout on
set echo on
set feedback on
set verify on
Any advice?
When I try and define a connection using TNS in the Network Alias I see duplicates. Why is that so? I have defined in the preferences where the TNSNAMES directory is located
Probably b/c you have more than one TNSNAMES* file in that directory – we read ALL of them.
Hey Jeff,
I really hope you can help me on this, as it seems no one knows what they are doing here (or at least I can’t find the right person here at work). I had this query that I have been using over and over again for the last 6 months. We just got an upgrade to Version 4.0.2.15 and now it errors out on the declare statement. I was wondering if something changed in the new version for the syntax? Code snippet below (not all code included):
DECLARE
–******SET THESE VALUES******
someID NUMBER(10):= 1234567890;
… more variables …
BEGIN
–*****SETTING VARIABLES
— get the benefit recipient ID for member
SELECT DISTINCT PARTY_ID
FROM PARTY
WHERE SOME_ID = someID
);
I am not sure what happened but it seems that this is the only instance that fails.
Any help would be great.
Thanks,
-Joe
can you tell me what the error is?
Sorry I forgot to include that it is :
Error report –
ORA-01403: no data found
ORA-06512: at line 51
01403. 00000 – “no data found”
*Cause:
*Action:
Also if I run the query:
SELECT DISTINCT PARTY_ID
FROM PARTY
WHERE SOME_ID = 1234567890;
It works fine and I get the results I am looking for.
can you reproduce this issue with SCOTT or HR schema objects and a very simple query? I need something I can test.
hi Jeff,
do you know how to create superclasses and subtypes using SQL developer data modeler?
Hi I want to send the result of my query to another query
SELECT *
FROM [dbo].[DWXP050]
WHERE INTCOV = @MySearchCriteria — need to save this as a variavle @Mysearchcriteria1
–then I want to
select on the first row column 1, 2 and concatenate just the letter part of column 3 and run it in another query.
how can i write it in Msql server?
I work for Oracle.
I’m sure your question has been answered on StackOverflow though.
Hi,
I am using SQL Developer 4.0.0.13. When I show a logical model the modeling tools are missing. The only way to get them back for the session is to reset to factory defaults. Next time I open SQL Developer the same thing happens. Basically I have to do a reset windows to factory settings to see the tools.
Where do I turn on the Data Modeling Tools/toolbar?
Thanks
We patched 4.0 three times. Try 4.0.3.
Pardon this simple question, but back on April 17, 2013 (Questions & Answers from SQL Developer Tips & Tricks ODTUG Webinar) one of the questions was: Is there a way to import “”User Defined Reports””? The answer was ‘You can copy and paste them into the folder.’ Here is the simple question: Which folder? In the base install folder there are 21 other folders…and they may have one or more folders. Is there a specific folder into which the report file (i.e.: my_report.xml) should be dropped?
So it’s actually the user interface ‘folder.’ In the reports tree, you can copy a report and paste it between other sub-folders.
If you want to share it with someone else, save the report to an .XML file, and then have the other person open it – all from the Reports panel.
Hi Jeff. I am getting a weird error- Trying to import an xlsx file.. I have a set of files that will import just fine, except for one. It will give me this error only when I try to exclude a column:
SEVERE 201 655444 oracle.dbtools.raptor.data.writers.ImportGenerator$SchemaTaskListener Import Data into table ACCOUNT from file new_account.xlsx . Task failed and import rolled back.
I’ve tried to copy and paste the data into a new file, still the same thing.. I’ve tried reducing the column count (row count is only 300ish) to no avail.
Funny thing is, there is a column there that I want to exclude, since it’s a virtual column. If I try to include it, everything works fine, and I get the output script, and the usual errors about inserting data into virtual column.
I think this is a bug when you try and get rid of one of the columns… I don’t get the traditional Continue, Ignore, cancel prompt.. Only an ok prompt when the exception is thrown. Then the SQL file is created with only SET DEFINE OFF followed by a newline or so.
Thanks for the site.
What version of SQLDev are you using? You can send me your table DDL and Excel file to test with if you’d like to [email protected]
Can anyone share me the samples for unit testing the objects are having the XML / collection data type as output parameter
how ‘ctrl+r’ work in sql developer
You mean ‘replace’ in the Editor?
How do I set up a single sign on connection in SQL*Developer?
The SQL*Plus command I use is “sqlplus /@dbname”, but I’ve not figured out the
magic incantation in SQL*Developer yet.
Thanks in advance!
I know it works but requires a thick connection…I’m on vacation this week, but put a note out on the forums. Also is you google this topic and ‘Barry’ or ‘Turloch’, they have examples on their blogs.
Hi, Jeff. Thank you very much for providing your blog posts, as they have been very helpful.
I am using the SDCLI cart cloud command to copy data from an on-premise oracle database to our database schema service instance. I have successfully scheduled this on a cron job to populate a staging table on a twice-a-day interval, and it’s working flawlessly. However, I am seeing that deployments are building up on the cloud service, and I’m having to manually delete them. Is there a way to use the sdcli to delete the cloud deployments instead of having to manually delete them through the sqldev gui? Or is there another way that I could setup job to delete those deployments in the schema service instance?
Thank you,
Dennis
I believe you would just write a shell script to ssh/sftp into your deployments area and RM the ‘offending’ deployment files.
I’m using the SQL Developer “Copy to Oracle” feature to migrate the contents of numerous DB2 tables to Oracle. The process seems to be going pretty well, but it’s not moving as fast as I’d hoped. In the Migration: Data Move Options , you have the ability to tune several parms like # of Parallel Data Move Streams and Number of Rows to Commit After.
Does the “Copy to Oracle” feature respect those Migration parms (I’m assuming the answer is no). Is there a good way to “tune up” the “Copy to Oracle” to help speed things up?
Yes – don’t use it except for very basic, ad hoc ‘migrations.’ Otherwise start a migration project.
Thanks for the quick reply. That confirms my suspicions. I’ll change gears and use Migration vs. Copy to Oracle and just disable the non-table data that I don’t want it manipulating.
In SQL Developer, when I view the SQL for almost all objects (tables, views, indexes, etc.), the owner is included in the syntax (CREATE TABLE “JUSTIN”.”MY_TABLE”) but this is not true for packages/procedures. Is there a way to get the owner to show up in SQL syntax for packages/procedures?
pl/sql code is more of a ‘living’ object – it’s composed by entries in all_source
SQL pages are generated by running dbms_metadata – and you can have the schema included or not in the preferences. If you want your pl/sql generated WITH schema prefixes, run Tools > Database Export, and check ‘schema’ in the first page of the wizard
The columns are as follows:
Col1 – NUMBER(38,0) – NULLABLE=NO
Col2 – VARCHAR2(200 BYTE) – NULLABLE=NO
Col3 – VARCHAR2(200 BYTE) – NULLABLE=NO
Col4 – VARCHAR2(50 BYTE) – NULLABLE=NO
Col5 – VARCHAR2(200 BYTE) – NULLABLE=YES
Col6 – VARCHAR2(250 BYTE) – NULLABLE=YES
Col7 – VARCHAR2(20 BYTE) – NULLABLE=NO
Col8 – NUMBER(38,0) – NULLABLE=YES
When I open a session monitor, sometimes I get a “table/view dont exists message” and sometimes I get progress bar that runs for about 30 seconds then times out.
For the record I am somewhat new to the Oracle tools, so please bear with me.
Those are really wide columns, you might be overwhelming the JDBC layer – query fewer columns or rows. You could do a trace to see what’s happening, but I’m guessing it’s client side not server side issues.
You could try running your query as a script (F5) instead to see if that’s better?
I have a table which I can select the contents from SQLPlus just fine, however when I try to do the same from SQLDeveloper, SQl Developer justs hangs there with a progress bar. Any ideas why the same query runs in SQLPlus but hangs in SQL Developer?
what can you tell me about the table? data types for the columns?
when sqldev hangs, what wait events do you see for the session?
The table has 8 columns with 230 rows, it seems if I limit the column selection to 6 sql developer will run, 7+ it hangs. On another table with 7 columns it returns fine, then on a completely different table with 8 columns it also hangs. On a different connection (server) the 8 column tables return just fine.
When the query is executed, its just a progress bar, no tasks are being displayed to know what its doing.
Right, but what about those columns? Are they CLOBs, VARCHAR2(4000)’s? XML?
And if you open a monitor session panel and open your session, you can see the waits there. This tells us if the process is being hung up b/c lack of server resources.
…a quick question. I was working on some significant PL/SQL within SQLDeveloper when my entire system crashed. I did not save this work, unfortunately. Is there anyway of getting this back? I’d be grateful if there were.
PB
were you working out of a file? check the ‘history’ tab and maybe you can recover something
After going through the import data wizard and saving the settings to an import.sdimp file, is there a way to call that from the worksheet screen as part of a script, or must it be loaded through the import wizard?
ie. something like
IMPORT import.sdimp;
Yes, but from the sdcli command line interface (found in the /bin directory). Or like you say, you can reload it in the import wizard, on the very first screen.
Hi Jeff, I am using SQL Developer 4.0. I am copy pasting output for my script to excel. When I do so, I do not get column header appear with the result.
Is there any way to do that? When I copy paste result from Toad it does it but not with SQL Developer. Please suggest. Thanks.
When copying the output, do Ctrl-Shift-C, then pasting is the same Ctrl-V
Viola! Column headings.
Excellent…Thanks you so much 🙂
Yes, copy with SHIFT+CTRL+C, that will grab the column headers. Search my blog for a couple of posts on this topic.
Thanks a lot Jeff 🙂
Hi Jeff,
I have installed the SQL Developer version – Version 4.0.3.16 and when I am trying to connect a server, I’m receiving the error “IO Error : The network adapter could not establish the connection Vendor code 17002”.
Any guidance how this can be fixed ?
Thanks,
Chandu
confirm you can ping the database server (find it on the network) and that you can reach the listener on the port you’re supplying for your connection
running 4.1.0.17. when I desc a specific table (not all tables, just 1) in sqlworksheet, I get table does not exist. If I press shift f4, I see the columns. If I run command line sqlplus, desc table works.
if you think it’s table specific, i’ll need your DDL to test it. or, check out the View > Log > statements panel and look for queries that aren’t working when you run the DESC. And don’t forget to try our new ‘INFO’ command 🙂
Jeff,
Here is the error from the view-log:
select ‘ERROR: object MONEY_ROOM_T does not exist’ “ERROR:” from dual
also, not finding much on the new info command.
Thanks for helping
Randy
what do you see on the ‘statements’ tab in that log area though? you should see us running a query trying to resolve MONEY_ROOM_T
not sure what you mean by not finding much on the new info command, just try ‘info‘ and run it as a script
I found the sql and it showed I had table and trigger with the same name. I renamed the trigger and the desc works! thanks.