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,790 Comments
Do you know a way to rename a set of columns with a regular expression in Data Modeler?
I try to explein better with an example I have a set of columns start with XX* and I want to replace XX with YY.
Do you have any suggestion ?
Best Regards
Luca
yes, see my post on using search and replace in the modeler, and there’s support there for REGEX
Hi Mr. Smith
In SQL Developer 4.1.3, can we have a chart title whose content is based on bind variables or something similar? Let me explain,
my SQL statement is the following:
select nom_reseau,
pct_cpu_used
from cumulative_by_year_month
where year = :YEAR
and month = :MONTH
order by 2 desc
the chart report works just fine but i would like the chart title be something like “Cumulative CPU usage – May 2016” where YEAR = 2016 and MONTH = 5
Regards,
Hi,
Can we know whether SQL Developer Data Modeler supports stored procedures. What kind of analysis or diagrams it can generate?
Thanks
Our ACE Director Heli just talked about that today…
Hi,
I am running unit test in sql developer and my test is failing just because there are additional dashed in the ref-cursor result set in proc out parameter, which is not the case with dynamic value query.
Can you please help me with this! Below is the test execution output in debug mode.
The following procedure was run.
Execution Call
BEGIN
“SCHEMA”.”PKG”.”PROC”(I_PAR1=>:1,
I_PAR2=>:2,
I_PAR3=>:3,
I_PAR4=>:4,
O_PAR5=>:5);
END;
Bind variables used
:1 VARCHAR2 IN
:2 VARCHAR2 IN
:3 VARCHAR2 IN (null)
:4 VARCHAR2 IN (null)
:5 REF CURSOR OUT (null)
Execution Results
ERROR
O_PAR5 : Expected: [COL1 COL2 COL3 COL4 COL5
———- ————————————————————————————————————————————————————————————————————————————————————— —————————— ——————– —————————————————————————
VALUE1 VALUE12 VALUE13 VALUE14 VALUE15
VALUE2 VALUE22 VALUE23 VALUE24 VALUE25
], Received: [COL1 COL2 COL3 COL4 COL5
———- ————————————————————————————————————————————————————————————————————————————————————— —————————— ——————– ——————-
VALUE1 VALUE12 VALUE13 VALUE14 VALUE15
VALUE2 VALUE22 VALUE23 VALUE24 VALUE25
]
Hi Jeff,
I am very thankful for all that I have learned in just a few months from your site. I have one more step in my project which I am having trouble with. I have seen many posts with this same question. None have a good answer. Most have some not nice responses. I have an oracle table. Every day I run a SQL script to update the table and then I export that table using SQL developer into an excel file on a shared drive. I use the wizard for that. I have an SSIS package that takes the excel file and completes the project. Using the SSIS to aid in the excel export is not an option. How can I automate the daily process of running the script, but more importantly exporting the table in excel format to the shared drive.
Hey Josh, thanks for the feedback. It’s cool to know that there are real people out there than are getting something from the blog posts.
I’m going to ignore the SSIS bits – b/c I work for Oracle AND b/c I don’t know much about it. But.
I would do this.
Don’t use SQL Developer.
Use SQLcl – it’s a command-line interface FOR SQL Developer.
Write a script – it will be VERY simple.
Something like…
set sqlformat csv
set echo off
set feedback off
spool C:\your_drive\your_file.csv
@your_script.sql
spool off
Inside of your_script.sql – you’ll have your query. the output of running this via SQLcl will be a CSV file with your data in it.
Pretty darn sure SSIS can handle CSV just as well as it can handle Excel.
Otherwise, there’s no way to schedule SQLDev to start, connect, run query, export results to Excel.
Hello Jeff,
In sql developer 4.1.3, the db connection box on the upper right corner of a sql worksheet lists the db connections in alpha order before any connections are made. However, once a connection is made on any open worksheet, that connection is then reordered to the top of the list. Is there an option to preserve the alpha order regardless of whether or not a db connection is connected or not?
Thanks,
David
nope, we sorted active connections up top as they were most likely to be used for something like a db compare or running a report, or anything else that uses a connection picker
Thank you for the response. Now I’ll quit looking for a way to change it back.
Why is my SQL History no longer displaying (when I click F8 nor View->SQL History)?
I don’t know
try Windows > Reset Windows to Factory
Won’t I lose all my SQL History if I do that, reset windows to Factory, and I’d have a lot to lose? Is there somewhere on my hard drive where Oracle SQL developer is storing all my SQL, where I can find my previously run queries that way?
Thanks so much!
no you won’t lose anything – that resets the desktop layout controlling where the different panels are located and their docking status
Hello Jeff,
I am trying to export data in the delimited format using sqlcl. I see an option to set the Line Terminator in SQL Developer, and wanted to do the same using SQLcl. Can you please let me know how I can achieve this using SQLcl?
Thanks in advance!
that’s not configurable in SQLcl, sorry
Thanks for the prompt response, Jeff!
I am seeing different behavior in how SQL Developer exports data vs using SQLcl to export.
In the table that I am trying to export, for some of the records in the COMMENTS column, we have line feeds that have been inserted by the end users, and when I export this data from SQL Developer, I see the special character LF in the comments, whereas if I export using SQLcl, I see the special characters CRLF in the comments. Is this a bug or configuration issue? Can you suggest a workaround?
Thanks!
I think if SQLcl is running on *NIX it’ll use LF and if it’s running on Windows it’ll use CR/LF pairs.
We ask the OS what it wants to use for a line terminator via
System.getProperty(“line.terminator”);
So if you want CRLFs, run SQLcl on Windows. If you don’t want CRLFs, run SQLcl on Linux/OSX/Unix.
And since that’s a property, you can actually set that yourself with the SCRIPT command.
script
2 var FormatRegistry = Java.type(“oracle.dbtools.raptor.format.FormatRegistry”);
3 FormatRegistry.setLineTerminator(“?? “);
4 /
makes for lots of beer
Now you have Beer Emoji line terminators.
Thanks again for that response!
However, i am afraid I’m not familiar with the solution you provided. Also, I am running this from windows, so, if you could provide the solution to setting the Line Terminator from within windows, I would really appreciate that.
yeah, open a cmd window
run that script command inside of SQLcl – it will change property we use to determine what the line terminator is
Hi,
Another ERD question. I’ve used the “Responsible Parties” to show ownership (dead good). Is there a way to show that info on the symbol for an entity in a diagram the same way I can choose to see attributes?
regards
jsa
Thanks for all of your work on SQLDeveloper and educating us!
When formatting source code (F7), is there a way to have the parameters line up below each other? i.e.:
bompexpl.exploder_userexit (verify_flag => 0,
org_id => in_organization_id,
order_by => 1,
grp_id => in_group_id,
session_id => in_session_id,
levels_to_explode => 9,
bom_or_eng => 1,
impl_flag => 1,
plan_factor_flag => 2,
explode_option => 1,
module => 1,
cst_type_id => 0,
std_comp_flag => 2,
expl_qty => 1,
item_id => htrs.inventory_item_id,
alt_desg =>”,
comp_code => ”,
rev_date => SYSDATE,
err_msg => l_err_msg,
error_code => l_error_code);
I use the SQLDeveloper format function extensively, and every time I use F7 to reformat code with procedure or package calls, it strings out the parameters on one line.
Hi Jeff,
I have an application that uses the thick database paradigm and all application users are database users. For the middle-tier connection pool, a separate user called “web” has been created. All application users must connect via proxy to gain access. In SQL*Plus this would be achieved by:
$sqlplus web[mark]@testpdb
Ideally however, as well as providing the password for the “web” user, I would also like to provide the password for “mark” as this provides an extra level of security. I know this is possible using JDBC and OCI but this is not possible in the SQL*Plus application. Is it in SQLcl?
After posting this question on Ask Tom, they suggested raising an enhancement request for SQL*Plus and perhaps SQLcl. By the way, is SQL*Plus now deprecated in favour of SQLcl?
Many thanks
Mark
you can use the thin jdbc driver url in your connect string, have you tried it?
I have several User Defined Reports, each associated with a specific database. Is there a way of having a report associated with a database so I don’t have to choose the database each time I run it?
reports aren’t tied to databases, sorry
Hi Jeff,
In Data Modeler, would it be possible to have the entity names in Logical Model and the table names in Relational Model un-prefixed, whereas in the generated DDLs, all the table names only would get a PREFIX_?
Thanks,
V
One way to do it would be to use the name substitution feature of the generate ddl dialog – but you’d have to add an entry for every TABLE in your model.
Hello Jeff,
I am doing a project where each day the same comma-delimited text file will need to be imported to my Oracle database from a shared drive (same but with new data). A one-off import works fine using the wizard, but I was wondering if it is possible to somehow automate the import process (e.g. by setting up some sort of scheduled job or something like that).
The idea is to have it so the database imports/updates without my daily intervention — such that it knows to import at 10:00AM, for instance.
Do you know if this is possible?
I am very new to Oracle, and I am currently using SQL Developer. Couldn’t figure this out for the life of me!
Thanks!
See my post on using SQLDev to Setup SQL*Loader runs.
Once you have your script/ctl file built, you just need a OS job to run on a regular basis to run the SQL*Loader scenarios. This assumes the filename/data format doesn’t change.
Great. Thanks, Jeff. I’ll try it out.
Hey Jejj,
How can I load/unload MS excel datasheet directly into a remote oracle server without any client installation at my laptop/PC ?
Thanks
You can connect to Oracle from Excel using an ODBC driver, try that.
Thanks for the reply Jeff.
But that would require client installation which i want to avoid. Right ?
Define ‘installation.’
The Instant Client is just a zip file you can put down, no installer to run. It’ll have the ODBC driver for you. Or, get a 3rd party Oracle ODBC driver and use that.
I’d not do this to begin with, but you’re not asking me for the ‘best way’ to copy data from Excel to Oracle…
Can the SQLDeveloper Report/Chart feature create a line chart where x contains date/time and y the values to be plotted?
Yes. See today’s post for more info.
I rebooted my Win7 machine today, and when I started up SQLDeveloper (4.0.2.15) it asked if I wanted to import user preferences. I shrugged and clicked no. It started up and all my connections are gone!!! Where did they go, and where can I find them?
you should have a system4.0.2.15… folder in your window user appdata roaming profiles sqldeveloper folder – of course now it has all brand new settings. you could delete the folder and let it import from an older existing directory there if you have one
I want to do a difference between a single schema in the source database and a single schema in the target database. Can I use the database diff tool to do this? I cannot find a place to specify which schema in the target database I want to use for the compare.
Are the 2 schemas the same name? If so, then in the compare wizard, set the ‘maintain’ option – that tells us to resolve the objects in the 2nd source using the schema attached to the objects in the first source.
If they are not the same name, you’ll have to login AS the user you’re trying to compare.
Just saw that there is a new version available for download from a couple of days back. Any information what was changed?
For the record, “insert” stmts still don’t make it into history even with this version.
just lots and lots of bug fixes
SQL*Plus supports optional script parameters, via default values. It’s as simple as:
define a1=&1
select ‘&a1’ from dual;
If the script is called with a parameter, there is no prompt. SQLCL always prompts?
See: https://blogs.oracle.com/opal/entry/sqlplus_101_substitution_varia#9_14
By the way, I love sqlcl, and I love your blog. 🙂
I found a workaround – to define 1=” 2=”… in login.sql, and clear them after use. Not elegant, but gets the job done.
I’ve got sqlcl working fine on windows and like it quite a bit.
I’m also trying to get it running directly in our Solaris (sparc) environment and am having difficulties.
I’ve tried using both Java 7 and Java 8 JDKs, and I’m getting the same error when starting sqlcl
May 18, 2016 8:57:30 AM oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli log
SEVERE: null
java.lang.NullPointerException
Any ideas how I might fix this?
Did you ever figure this out?
No, I never did.
Jeff,
Great articles!! Very low tech question for today.
Oracle SQL Developer 2.1.1.64
File > open (or Ctrl+O, or Folder Icon)
Left side shows your favorite folders. Long ago I managed to add my own folder. Now I cant recall how to add/delete these. Been googling it. See a fw people asking the same, but no answers. Can you shed some light on this please.
thanks
Cam
Not sure how we would have done that in v2.1 – maybe a page in the preferences?
In version 4.1, each editor’s file/open/save dialog will remember the last 5 or 6 directories you’ve used.
Hi
Is it possible to connect to SQL Server 2008 with sdcli? I am using SQL developer 4.1.3.20 and jtds1.3.1 and can successfully connect using windows authentication using the GUI. However, the sdcli guide seems to be missing the syntax for SQl Server connections.
No, but we’re considering opening up 3rd party jdbc drivers for SQLcl.
thx
Hi Jeff,
thanks for your great blog with so many helpful tips and tricks.
Currently we are evaluating a new PL/SQL Development tool as a replacement for a commercial product.
As i’m working with SQL Developer for some years now, i proposed to use it instead of purchasing another (cheaper) commercial tool.
During evaluation we came across code completion feature (which i personally do not like too much, no matter in which tool) and we found out, that he behaviour is not really correct when a package procedure or function uses a parameter of %ROWTYPE or Type declared in package header.
As soon as the round opening bracket for the procedure parameters is written, completion insight pops up and lists the available parameters for the procedure.
BUT: to my surprise a %ROWTYPE parameter, lets say it’s of type employees%rowtype and p_emp_row is it’s name, will be resolved into single parameters, so instead of
employees_tapi.upd(p_emp_row employees%rowtype)
as it is declared in the package spec, the parameters listed are:
employees_tapi.upd(p_employee_id number, p_first_name varchar2, ….
IMHO this behaviour is not correct, as it does not reflect the signature of the package procedure and cannot be used in the manner the code would generated.
I could imagine that internally the rowtype procedure parameter is splitted into it’s fragment because SQL Developer does not pick up the complex argument from all_arguments which is at data_level 0, instead it picks up the single arguments with data_level 1 which all have the complex type parameter as their parent and presents those components as the parameter list for the procedure.
Is there any way to tell SQL Developer not to resolve type parameters into it’s fragments?
Best regards and thanks in advance.
Thomas
No, I think that’s a bug.
>>(which i personally do not like too much, no matter in which tool)
I actually recommend disabling the AUTOMATIC component of the insight, so you only see it when you want it.
If you drag and drop your package procedure to the Worksheet, does it generate a correct ANON block?
Hi Jeff,
thanks for the fast response. I tried dragging the procedure from object navigator (from packages node) to anon pl/sql block – but that doesn’t work either.
This time the procedure has both variants: first the rowtype parameter is shown correctly, but in addition the rowtype parameter gets resolved, so that in fact the parameters are duplicate, the correct one and resolved as well …
Btw, i’m using the most current version 4.1.3 according to Oracle Technology Network download page.
Regards
Thomas
Thanks Thomas, I’ll take a look. Remember, for the best support, go to My Oracle Support.
I have installed sqlcl on my Windows 7 machine and can connect to a database but do not gat any output from commands I type in e.g.
Picked up _JAVA_OPTIONS: -XX:MaxPermSize=512m
Java HotSpot(TM) Client VM warning: ignoring option MaxPermSize=512m; support was removed in 8.0
SQLcl: Release 4.2.0.16.131.1023 RC on Thu May 12 14:15:55 2016
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Thu May 12 2016 14:14:40 +01:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> tables
tables
SQL> help edit
help edit
SQL> select * from user_tables;
select * from user_tables;
SQL> tables
tables
SQL> show jdbc
show jdbc
any ideas ?
Thanks,
Jim