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,803 Comments
When running SQL*Plus and typing “host”, I am placed into an environment that appears to mimic the one in which I called SQL*Plus from…in this case, ksh with its associated environment definitions (PATH, etc.) I am then able to move around, run scripts with embedded environment variables, etc. as I normally would.
When running SQLcl and typing “host”, it appears that I am placed into a bash environment (without access to expected environment variables, aliases, etc.). If I then type “ksh”, the environment is configured as I expect.
Is there a way to automatically have SQLcl start in ksh vs. bash?
Hi,
We want to use OpenAPI (swagger) with ORDS, the basics are working. Currently all our handlers are defined like this:
‘begin a_package_name.perform(:body_text); end;’ — JSON text as CLOB
This results in a not very useful OpenAPI, since we do not have the detail-specs of the ‘JSON text as CLOB’.
We want to explorer the possibilities of defining our package-procedures with object-types (CREATE TYPE… AS OBJECT), or similar constructs that allow nesting (eg. an address in person type). This could/would allow the ORDS-OpenAPI generator to get a detailed description. (our logic is working it is all about the OpenAPI description)
Is this possible with ORDS? Examples? Or other links?
regards
Christophe
ORDS definitely supports user defined types, here’s one example.
Is ORDS apply check on file upload size before it writes to :blob internally ? need to restrict user to upload big file. is there any setting that can be used in ORDS web.xml?
No…why wouldn’t you have your application sanitize the inputs before passing it over? You could also have a INSERT or UPDATE trigger enforce this. Or build it into your APIs.
Thanks for confirmation. i was looking if we could perform file size check at ORDS level (ords.war) instead ask every app or third party app to implement file size check before passing to ORDS.
Jetty, Tomcat, WLS all have settings for restricting size of post requests… Something else to look at vs having API or database control this.
thanks for suggestion. looks good.
Hello,
I’m trying to find a way using SQL Developer v20.4 to open a pks file and split out it into two tabs in the same way as Toad for Orale does.
That’s because I’m saving package pks file and pkb file as one single file, in my case pks.
Toad for Oracle has an interesting feature, if you save the speck and the body in a single file, Toad is able to recognize this situation and open the speck in one tab and the body in another tab. Save action persist any change in the file and when you close one of these tabs, both are closed.
Regards!
We’re not Toad.
You should be saving those objects to separate files. Why? Because they are separate objects in the database.
Toad helps feed your bad habit – treating a SQL script containing multiple objects as multiple files.
Hi Jeff,
I am facing an strange problem with SQL Developer 17.2. I am able to write and run the query but in query result set I am not able to scroll through the data. Do you know what could be the cause of it, and how I can solve this?
Thanks,
Nitin
Your software is 4+ years old. Step one, go get version 21.2.1 and try again.
The Generate DB Doc is a super tool. Is there a way to convert the output to PDF that would allow it to be included in program documentation?
Hi Jeff,
I’m using SQL developer to manage REST Data Services between different environments, each with its own instance of the database.
Using what you wrote on the subject (https://www.thatjeffsmith.com/archive/2018/12/how-to-export-your-restful-services/), we’ve been using the export module function of SQL developer to commit the services to our source control.
Recently though, we’ve noticed that exporting the modules cause a huge number of conflicts during merges, as the generated modules.sql file lists ords handlers for templates differently. It can list every GET before every PUT for all templates, or vice versa, resulting in tons of modified lines and messy version control.
I can’t figure out where this comes from. Exporting twice in a row without making any changes will produce the exact same files, it’s only when making actual changes that this happens.
Any ideas?
Regards,
Martin.
Version of ORDS?
And just curious, what sort of APIs have you built? Always great to hear how our stuff is working in the real world!
Both environments from which we export the modules are running 19.2.0.r1991647.
As for the API, we previously had an old application entirely hosted on our database, that would generate the web pages with heavy use of htp.p and the like. A few years ago, we’ve made the decision to develop an Angular client for everything web related, and only keep the actual business related code in the database.
We’ve then developed our API to handle the client’s requests, from auth to business data handling to form submission, etc. It definitely has been extremely useful in helping clean our code and make it maintainable!
Hi Jeff,
Should the Encoding in the Preferences/Environment dialog to be set according to the database NLS_CHARACTER_SET ? Is there a relation?
Thank you
Hi Jeff,
is it possible to add a new RDBMSSite e.g. PostgreSQL in DataModeler and configure a type mapping ?
Regards Günter
You can’t add a new site, but you can hijack an existing one as shown in this video.
Hi
I’m trying to get Kerberos authentication to work from SQL Developer 21.2 on to of Instant Client 21.3.
I’m already able to use SQLPLUS from the Instant Client so the configuration (listener, sqlnet, realm, SPN, etc) has been covered.
In SQL Developer I’ve selected “Use Oracle Client” and set the path to the location of the Instance Client and also select the OCI/Thick driver.
I can see from the listener log that it gets connection request from but on the SQL Developer end it returns ORA-01017: invalid username/password.
So it seems to me that a Kerberos ticket isn’t being construckted and send along with the connection request.
Any ideas what might me missing or wrong?
Regars, Torfi
When you click the ‘test’ button in the OCI/Client config section in Advanced Preferences, do you get all ‘Ok’ messages in the test output panel? That will confirm you’re connections are available to be be OCI/Thick for JDBC Thin.
Yes, I get OK on all tests:
————————————————————————————————————————-
Testing the Instant Client located at C:\oracle\product\instantclient_21_3\64bit
Testing client directory … OK
Testing loading Oracle JDBC driver … OK
Testing checking Oracle JDBC driver version … OK
Driver version: 21.3.0.0.0
Testing testing native OCI library load … OK
Success!
————————————————————————————————————————-
But as the Instant Client is using JDBC one can not use the Network Alias in the TNS Connection type, when I switched over to Connection Identifier the Kerberos connection worked.
Regards, Torfi
Hi again
Just one this, remember to check OCI/Thick driver under Preferences/Database/Advanced or check OCI in the connection under Advanced.
Better to do it under Preferences as then it applies for all new connections.
-Torfi
Hello Jeff!
I’m using Oracle SQLDeveloper Command-Line (SQLcl) version: 21.3.2.0 build: 21.3.2.287.1503 for Oracle EE 12.2.0.1.211019. I’ve tried to split big partition and got:
ORA-03148: OCI_ATTR_ROW_COUNT cannot return a row count exceeding UB4MAXVAL. The actual row count is [9868887344]03148. 00000 – “OCI_ATTR_ROW_COUNT cannot return a row count exceeding UB4MAXVAL. The actual row count is [%s]”
*Cause: The row count was larger than UB4MAXVAL. OCI clients using
OCI_ATTR_ROW_COUNT cannot see row counts larger than UB4MAXVAL.
*Action: Upgrade Oracle client to version 12 client libraries or higher and
use OCI_ATTR_UB8_ROW_COUNT instead.
Do you know is this error caused by database or by sqlcl? Oracle support haven’t provided me answer on this question yet.
Regards, Dmytro
Hi. I have multiple apex applications on my instance in an Autonomous database environment.
Using the vanity url method i can only map one application to the vanity url. How can i achieve mapping my multiple applications using various vanity urls without using a webserver like nginx.
I saw your post on the forums and LinkedIn and I’ve sent it over to our APEX/Vanity URL product manager.
Is there a way to pass parameters to the WHERE clause of a object in a CART in sql developer?
eg.
BUSINESSNR = %GIVEVALUEFORBUSINESS% — does not work, fails without any output in the outputfile
The only way i have now is to use a Parameter table and use that one in the view.
oops… double… ok.. where is static..
You can add a where clause in the cart entry.
Hi Jeff, i’m new using ORDS (latest version) and i’m trying to send this JSON to a POST endpoint:
{
“id”:”123456″,
“last_name”: “Test/Test”,
“mi_name”: “Test”,
“first_name”: “Test”,
“address”: {
“street_name_1”: “Test 123”,
“city”: “NY”,
“commune”: “NY”
},
“user”: “Test”,
“origin”: “API”,
“data_origin”: “Test_origin”
}
I define an package.procedure that obtain all the parameter so i can easily invoke it from the definition_handler and then use the open-api-catalog and show all the parameter that this procedure accept.
My problem is the “address” parameter, i define that input parameter as CLOB, but i cannot make it work. if i just accept one parameter like p_json IN CLOB, and then map the :body_text and use all the function inside PL/SQL is easy to do it, but when export the open-api-catalog it just show me that only accept 1 parameter instead all of the above in the json example.
is there something that i can do ? because i need to use more complex json as IN parameter.
I already read the Oracle documentation and a lot of post but i cannot get any answer.
Add a comment to the handler and that should appear in the OpenAPI so you can be more specific about the inputs.
Is there a way to pass parameters to the WHERE clause of a object in a CART in sql developer?
eg.
BUSINESSNR = %GIVEVALUEFORBUSINESS% — does not work, fails without any output in the outputfile
The only way i have now is to use a Parameter table and use that one in the view.
No, the WHERE clauses are static.
Hi Jeff:
I’m using Datamodeler Version 21.2.0.183
I create a relational model with two tables, an later in the fisical model, i added two roles a select privileges on the two tables.
Althougth when i try synchronize the model with the database, it detects the diferencies but it doesn’t generate the create role statemens or the grant statements
Is there a way to generate grant/revoke statements when i change the privileges in Oracle Data Modeler?
In SQL Developer, is there a setting to save more than 6 entries in the File/Repopen menu?
No, I don’t believe so.
Hi Jeff
Trying to use SQLcl here and it hangs on doing a
lb genschema -split
Upon CTRL+C it shows “illegal group reference.”
It seems it doesn’t like our packages or triggers with the dollar sign ‘$’, as if I try to do a simple describe :
SQL> DESCRIBE AG$SOMETHING_SOMETHING
It outputs:
“Illegal group reference.”
This works fine for other objects with simpler names, without the dollar sign.
So this is related to regex it seems.
Is there any way around this, or it simply is impossible to work with dollar sings in db objects with SQLcl?
Thanks for your time and attention!
I’ll need to file a bug for you.
Do you need anything on my part ?
I can both generate a changelog AND desc my table…
SQL> lb genobject -type table -name AG$SOMETHING_SOMETHINGAction successfully completed please review created file ag$something_something_table.xml
SQL> desc AG$SOMETHING_SOMETHING
Name Null? Type
________ ________ ________________
ID NUMBER(38)
NAMES VARCHAR2(100)
SQL>
Normally – I have seen $ / # etc work as word splitter / end of word by oracle i.e
if I do
desc myschema.my$table#
this gives error BUT
desc myschema.”my$$table/” this works.
try enclosing the name with double quotes at SQL prompt and see if that works.
Hi – I have a question re. placeholders (the ones with &name or &&name)
We were developing a complex query which contains a couple of placeholders. First we had them defined using &name only but since it was tedious having to reenter their values again and again we replaced them with &&name. After that SQLDeveloper didn’t ask again (since the value was already defined). This was good for development.
But now – after having finalized the query – we actually want to use that query using *different* values for the placeholders. So we changed all the && back to & but odd enough, SQLDeveloper still doesn’t ask for the value but recycles the old ones, i.e. we get no prompts. 🙁
How do we teach SQLDeveloper to forget those old values and prompt us again?
try adding this to the top of your script
undefine name
Sorry, there was no “reply” for your append, thus as new question:
@Jeff Smith
> try adding this to the top of your script
> undefine name
Thanks – that worked!
I assume there is no command or menu-option to remove ALL values defined in that way in one go, is there? Just as hint for a little improvement. 😉
Hi Jeff, is there a “sql only” script to install all the required ords objects/schemas in the database? Equal to the installer at the point “Enter 1 if you want to verify/install Oracle REST Data Services schema”. Background: I want to (re)create my pdb completely by sql script from scratch for automated tests.
No…but the schemas are in the db for your to say…data pump export or extract.
How to pass date and time parameters in ords query .
current datetime format: “2021-08-30T04:26:39.472Z
See today’s post.
Hi Jeff,
I have multiple connections defined in SQL Dev and i would like to have a script to call out via connect commands to multiple of those connections running specific scripts against each connection/schema.
I have tried the usual conn/connect user/pwd@ but that doesn’t work – I’m sure i saw a piece on it a while ago but cant recall the correct way to get it to work.
Thanks in advance
Paul
Using either ezconnect or tns on your CONNECT commands should work just fine.
Those connections will be closed as soon as your script session is concluded.
Hi, Jeff.
How do I open a second (or third) tab for a table in SQL Developer 20.2+?
I can right-click and split the table, but can’t find that “clone” option that existed in old versions?.
You cannot.
Hi,
we want to use ORDS services as a paid service for the clients of our customer. To be able to know how many times a services has been called, we log in each service the request of the customer.
Now we want to block the request when the customer has no more credits to call this service.
For the put, post, delete operations, we are able to inject a plsql call to verify this and react as necessary.
But how do we do this for a get-call? Is there a way to inject plsql logic, which writes data to the db, into a get call?
Thank you in advance.
Filip Huysmans
Use the prehook feature. That will get called for EVERY request. When your user exhausts their limit, kick in your logic. They’ll never even get to the actual ORDS code path if you return a FALSE from your prehook function.
Apart from the Docs, some nice examples here from a customer.
SQL Dev 21.2.1 and also 21.2.0
When trying to get the SQL for a Partitioned Table, there is nothing specifying the partition definition. This did not happen in 19.4.0
Regards
Did you turn it off?
Yeah, so SQL Developer wasfreezing up with a message about “Comparing body to … something or the other”.
So I put it into a Search Engine and I get this:
https://community.oracle.com/tech/developers/discussion/4283208/sql-developer-hangs-on-comparing-subprograms
So I disable “Supported Sync Spec and Body” in Preferences as you suggested there. Restart required.
Guess what? It deleted ALL my passwords. I know, I know, I SHOULD have backups somehwere but .. UGH, wow, man. What the heck? I did NOT expect that.
I have passwords that I can’t exactly get back very easily. The ones I really need. Of course.
Wow. Is it supposed to work this way? Shouldn’t there be some kind of notice?
Thanks again.
– Mike Kemp
So I JUST tried this in 21.2, and after restart, all of my connections are still here.
No, that’s obviously not supposed to happen. This was in version 21.2? It’s where I just tried your scenario as described.
OK, thank you. I’ll update my version. Thanks again!!
Hi Jeff,
Now that I know what you actually read this and respond, well. Huh … I can really have some fun!! Moohaha .
Just kidding.
Maybe not.
On a serious note, though, I do have another bug to report.
I am on 20.4.1.407, Build 407.0006.
When I navigate from tab to tab using the keyboard (Ctrl + Tab), I have to fire a “Mouse Over” event on the tabs themselves to get the screen to update to show that a new tab is active.
When the active tab is not showing as active, it is a bit confusing to know what you’re looking at on the screen.
This may be an issue only when several tabs are open, and when the application has been running for some time. Not sure about those two points.
I’ll stop there. If you need further clarification just let me know.
Thanks,
Mike Kemp
reproduce it on 21.2 and let me know if it’s still a thing…you do that, I’ll file the bug for you 🙂
Hello,
I’m trying to use sqlcl instead of SQL*Plus and having relative success on this attempt.
Except for the fact that when I paste code that has CRLF line endings, sqlcl adds an extra new line.
I know that if I use a text editor to convert text to LF it works.
Is there any way to make it work without this extra step? i.e. to paste text that has CRLF as line endings directly to sqlcl and have it not adding extra lines between them?
Regards!
we had an issue with extra line breaks in a recent release, but i’m not able to reproduce this in 21.3 which is due to go our SHORTLY. What version are you using?