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,793 Comments
Hopefullly another quick thing… looks like the docs for ORDS 19.2 haven’t quite caught up to the installation process. I want to install ords directly to a (19c) pdb, but it looks like I still have to create the ORDS_PUBLIC_USER in the cdb? Is there a script or instructions on how to do this, grants needed, etc.?
No?
Just provide the connection details to your pdb in the installer, nothing will go in the CDB, nor will any users be created there.
Hello Jeff
I’m trying to call EBS Order Create API – I want to create multiple order lines for a given order header. I created record type and table type variables as input parameters for my package. I exposed the Package as ORDS but I’m unable to pass JSON – I get an error stating invalid column type. Would you please help and suggest. Thanks!
Below is the specification from my package.
TYPE order_hdr_in_rec IS RECORD
(
cust_account_number NUMBER,
party_site_number NUMBER,
org_id NUMBER
);
TYPE order_line_in_rec IS RECORD
(
orderitemid NUMBER,
quantity NUMBER,
price NUMBER
);
TYPE order_line_in_tbl IS TABLE OF order_line_in_rec
INDEX BY BINARY_INTEGER;
PROCEDURE create_order (p_orderhdr_in IN order_hdr_in_rec,
p_orderline_in IN order_line_in_tbl,
p_out_order_number OUT NUMBER);
REST SQL WorkSheet :-
begin
XXONT_CREATE_UPD_ORDER_PKG.create_order(
p_orderhdr_in => :order_hdr_in_rec,
p_orderline_in => :order_line_in_tbl,
p_out_order_number => :p_out_order_number,
p_return_message => :p_return_message
);
end;
Hi Jeff,
We are trying to install and configure ORDS in standalone setup.
Per DBA, they have installed per instructions, however ORDS is still not enabled.
I get following messages, when I run
SELECT * FROM TABLE(ORDS_METADATA.ORDS.check_object_support(p_schema=>’ORDS_METADATA’, p_object=>’EMP’, p_object_type=>’TABLE’));
ORA-20013: REST enablement disallowed for blacklisted schema : ORDS_METADATA
ORA-06512: at “ORDS_METADATA.ORDS”, line 1236
ORA-06512: at “ORDS_METADATA.ORDS_SECURITY”, line 188
ORA-06512: at “ORDS_METADATA.ORDS_SECURITY”, line 108
ORA-06512: at “ORDS_METADATA.ORDS_SECURITY”, line 168
ORA-06512: at “ORDS_METADATA.ORDS”, line 50
ORA-06512: at “ORDS_METADATA.ORDS”, line 1220
ORA-06512: at line 1
Any Clue?
Any direction would be appreciated.
Thanks and Regards
Aqeel..
You can’t rest enable the ORDS_METADATA schema. Your ‘EMP’ table should be somewhere else.
I have got a strange problem:
on Windows:
I have an Oracle 18c instance and Oracle Rest service deployed to Tomcat8.5
Because I have to load a table in one GET, I configured
defaults.xml with 150000
Everything runs fine. query runs with …….&offset=0&limit=150000
Now, the table will increase (by now it’s still the same size), so I modified pagination to 200000 and my query accordingly
and the GET fails with 404.
So I changed it back and found, that, no matter what I do, the GET query only runs with limit=150000
e.g. limit= 140000 fails, too.
For me it looks like, there is another configuration location, where 150000 is set.
But I cannot remember, that I did something like that.
Actually I’m only wondering, whats going on.
(of course I restarted everything, including the server)
Is there another configuration location, that influences the max limit of items, I can GET wird ords ?
If no, does someone have an idea, what can be the problem ? (caching?)
If you want to retrieve all records in one GET, set the pagination size to ‘0’ – that’s code for unlimited. Make sure it’s not set for your module or pool – that will override the default pagination size.
Doing what you’re doing won’t ever be fast…or efficient on the mid-tier, but I’m assuming you’re OK with that.
Hello Jeff,
that doesn’t work for me.
I tried with &limit=0, then I get a result with 0 records.
limit=-1 delivers 25 records
limit=100 delivers 100 records
limit=150000 response 404
I don’t have any explanation
I was talking in general, not using the Limit parameter.
Hi Jeff
We had ORDS services on https server. We used an OAUTH (CLIENT_CRED) with RESPONSE_TYPE=TOKEN.
After setting up WebLogic on this server we can’t use neither our old auth_client, nor new created auth_client. It returnes 401 error anyway.
Maybe it’s impossible to use token-based authorization with WebLogic? Or we should smth repair?
Thanks you very much
I do not have the rights to install views on the Oracle.
Nevertheless to simplify my life I would like to write a number of CTEs, store them to a file and import them into the SQL Developer before each SQL I write. Just.like an #include in C
How do I do that with the SQL Developer?
Take a look at code templates
SQL Developer Data Modeler Reporting Repository needs DBA privilege… why ?
I was getting an error …
schemafactory create : Error=ORA-01031
I found a tutorial that implies I need DBA priv…
https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldevdm/r30/datamodel4reprpt/datamodel4reprpt.htm
the error goes away if I grant DBA. I was unable to find the script that creates the schema so I don’t know why I need DBA for this.
Thanks
https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldevdm/r30/datamodel4reprpt/datamodel4reprpt.htm
Sorry, I don’t know.
I can’t get the Real Time SQL Monitor to work with my 12c database running on Amazon AWS .
Is there something I need to do or is it just a feature that does not exist on AWS
A good question for AWS.
Hi Jeff,
When I make an error in a SQL script I do not get line and column number returned in the error message anymore. It just says
ORA-00936: Ontbrekende uitdrukking.
00936. 00000 – “missing expression”
*Cause:
*Action:
Any idea how I can reenable the “Error at Line: 12 Column: 7” message after Action:
I’ve tried several SQL Developer versions (also 19.2).
I’ve enabled the OCI instant client.
Hi Jeff,
What is your preferred method in connecting Excel via VBA to Oracle Autonomous Database (Transaction Processing)?
Thanks.
I don’t have a preferred method for that – because I would never do that.
I would not use Access or Excel as a front-end to an Oracle Database. The main reasons being they generate bad SQL and it’s a slow/awkward interface.
What are you trying to achieve at the end of the day?
Thanks Jeff,
I currently have a large Excel Workbook with many forms and charts where the user enters their individual data and also a small amount of common data. What I eventually l would like to achieve is having the common data reside on a Oracle Autonomous Database where each individual user’s Workbook (via VBA) imports that common data into their Spreadsheets from the Database as well as being able to import/export data for each individual’s Workbook particulars.
I’m new to using Database’s so sorry if I’m on the wrong track with what I’m trying to achieve here.
Appreciate your advice.
Thanks.
Hi Jeff,
When I make an error in a SQL script I do not get line and column number returned in the error message anymore. It just says
ORA-00936: Ontbrekende uitdrukking.
00936. 00000 – “missing expression”
*Cause:
*Action:
Any idea how I can reenable the “Error at Line: 12 Column: 7” message after Action:
I’ve tried several SQL Developer versions (also 19.2).
I’ve enabled the OCI instant client.
v19.2
Oracle 12.1
I have been creating tables from Excel spreadsheets using xlsx files and now it load just hangs after displaying the sample set of rows and skipping the specified number of rows. The logs shows the following message
SEVERE 374 296 oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$1 Invalid ValueEval type passed for conversion: (class org.apache.poi.ss.formula.eval.MissingArgEval) at org.apache.poi.ss.formula.functions.MultiOperandNumericFunction.collectValue(MultiOperandNumericFunction.java:221)
I would need to see one of your xlsx files…and is it to a new or existing table?
I want to pass a RECORD type variable as input to the ORDS enabled package. Can I get some example or steps on how I can pass RECORD type or TABLE type input parameters
You have some code for me to build a demo/example around? That way I don’t have to guess what you’re wanting to do.
Hello Jeff
I’m trying to call EBS Order Create API – I want to create multiple order lines for a given order header. I created record type and table type variables as input parameters for my package. I exposed the Package as ORDS but I’m unable to pass JSON – I get an error stating invalid column type. Would you please help and suggest. Thanks!
Below is the specification from my package.
TYPE order_hdr_in_rec IS RECORD
(
cust_account_number NUMBER,
party_site_number NUMBER,
org_id NUMBER
);
TYPE order_line_in_rec IS RECORD
(
orderitemid NUMBER,
quantity NUMBER,
price NUMBER
);
TYPE order_line_in_tbl IS TABLE OF order_line_in_rec
INDEX BY BINARY_INTEGER;
PROCEDURE create_order (p_orderhdr_in IN order_hdr_in_rec,
p_orderline_in IN order_line_in_tbl,
p_out_order_number OUT NUMBER);
It is a new Table
How do I send you the file?
You’ve never used something like DropBox?
Hello Jeff
Have you had a chance for my table type requirement ? Any pointers would be great.
Many Thanks, Sourav
Hello,
I am using sqlcl for some linux batch processing as it seems to handle several things a bit better than SQL Plus. However, it seems that if I write a script and run it in the background, it just hangs when attempting to spool the output. Is there a setting I am missing?
Thank you, Pete
Can you provide an example which demos how the spooling & background is accomplished and o/s platform?
Hi, I stuck in installation of APEX 19.1 at XE database.
when I want to start localhost:8080/apex/apex_admin I got the message that user and password for XDB is required.
What is missing?
Hi Jeff,
If you would recommend I ask this question a forum, please let me know.
I am working on some initial REST functionality — exploratory work.
I am using 2-legged authentication — client credentials.
I am trying to find out how the “client_secret” can be changed, for a client created using the oauth.create_client() procedure.
I have an SR open — SR 3-21558053111 — regarding this, but am not able to make much progress, at least in part because the answers I am provided are being provided by someone in development, and passed along to me via the Support analyst. I am not complaining about this — the analyst is very helpful and I understand the dependency on development for some things such as this.
What I have been told is that there is no pl/sql API for doing this, but that it _should_ be possible using the oauth UI, at /ords//oauth/clients
If that is true, I assume it will require basic authentication to access that “oauth UI”, and the question is: which username/password should be used.
The latest update — today — to the SR says to use the user created with the ords.war package, e.g.
java -jar ords.war user test_developer “SQL Developer”
However, this was not done during the install (we have ORDS deployed to a WLS server).
I also saw in an earlier forum question (https://community.oracle.com/thread/4291229) “Not able to connect as ords developer from SQL developer to Ords” you responded “That user you are creating is a jetty user you can do basic authentication with …we recommend you use oath2 instead”.
So I guess my question is 2- fold:
a) is there really an OAUTH UI, accessible via web browser, at /ords//oauth/clients where the client_secret for a previously created client can be changed, and if so,
b) what username/password would one need to supply to access this UI?
Thanks for any thoughts regarding this,
-paul
a)yes – that’s why we advised you use that
b)ANY user that has the “SQL Developer” role – could be a WLS user that you create and assign that role to.
Thanks Jeff,
Regarding
>>> b)ANY user that has the “SQL Developer” role – could be a WLS user that you create and assign that role to.
Could this also be a “client” created with oauth.create_client?
o/w I will check with our FMW admin about the possibility of creating a WLS user.
This is not normally done in our environment, but I will follow up via the SR about this.
Turns out the answer to the following:
>>> a) is there really an OAUTH UI, accessible via web browser, at /ords//oauth/clients where the client_secret for a previously created client can be changed
is NO.
After more than 1 month, I finally got this answer (via SR 3-21845653871, spun off from SR 3-21558053111) from Oracle Support:
<<<>>>
Sad news is it took more than a month to get a correct answer.
Good news is that the duty manager told me the need for this has been recognized and it has been agreed internally that this (the ability to change a client_secret) should be provided.
The answer I quoted from the SR seems to have been stripped from my reply.
I am adding it below:
It has been confirmed by development that there is no way to change the client_credential at this time (the oauth ui imply to have cross schemas, which is not possible and outh ui is old and being gradually replaced, sorry for the initial misunderstanding)
There is NO way at this time to change the client_secret created by API.
Hi Jeff,
If I have a chart report which has a table child report, is there a way for the child report’s query to have access to a column that is returned by the main report’s query, but is not used in the graph?
In other words, the main report’s query returns columns for the group, series and value, and one additional column that is not used by the graph. Is there a bind that is available to the child report’s query for the previously mentioned additional column? or is there some other way or method to pass an additional parameter to the child report?
Replied to the wrong comment 🙂
Just bumping it up, in case you missed my question.
Every column from the main query is available via the :BIND notation in the child query.
I have 2 instances of 19.2 on different machines, and in none of them that works.
When a column returned by the main query is not used in the chart, the value is not available to the child report. It is like it is not the query what makes them available to the children, but the report itself.
In those cases, the bind variable is assigned a value of “12” (I have no idea where that value comes from, but it is always the same).
Just tested on 18.4, and the same thing happens.
I have a proof-of-concept report based on the HR schema, in which in the main query I return both the department id and department name.
The child report queries the employee table conditioning the department_id (comparing it to :DEPARTMENT_ID). As soon as I change the mappings in the main report, so that the chart uses the department name instead of the department id as the group, the child report stops working, because it always gets 12 as the value for :DEPARTMENT_ID. That is also the value that is assigned to any bind variable in uppercase which is not really a column from the main report.
Instead of me guessing what your report is, how about sharing it? Then I can give you a better answer.
I don’t think I can upload files here, so here is the XML (I hope that’s what you meant).
You can run it on a connection to the HR schema.
Thanks,
null
null
That didn’t work.
Here is a link to the XML file: https://send.firefox.com/download/b4c85cec407bf24f/#dxpPbcwNpEeuuNpkNfj1kw
Arggg! I keep replying to the wrong thread, and I can’t delete my comments…
Sorry.
Were you able to test the report?
I noticed this:
When there are more than one database_id’s (caused by e.g cloning) the AWR report viewer in the DBA tab is not working anymore, it’s possible to choose a database id, but there will never show up any snapshot id.
When there’s only one known database id it’s working OK. It is not a AWR issu, on sqlplus we can create the reports we want.
I tried sqldeveloper 4.x(…), 18.4 , 19.1 and 19.2 on windows 7/10 with Oracle database 12 and 18.
Could you confirm this behavior ?
Kind Regards,
Tom Breed
yes it’s a known bug – and it’s been fixed, but unfortunately too late for version 19.3
the fix will be available in 19.4
Hi Jeff 😉
Hi Jeff,
Were you able to look into it?
Since I upgraded SQL Developer 19.2 saved connections are lost forcing me to create connections all over again and again for subsequent sessions.
Make sure your java home is at least oracle java 8 update 152 or higher
Thanks Jeff,downloaded SQL Developer with 64 bit JDK Included.Deleting SQL Developer folders in C:\User\***\AppData\Roaming\…. fixed the issue.
Hi Jeff, going crazy as I try to edit a BLOB via SQL Developer. Following your instructions (among many!), but when I get to the “pencil” icon, it only opens a VIEW window, not an EDIT window. what am i missing?!?!?
you’re browsing a table, and NOT in query results, correct?
Correct. Following:
Find your table
The BLOB cell will say (BLOB)
Double-right-mouse-click
Hit the ‘pencil’ button
Opens VIEW VALUE window
are you SURE it’s a table, and not a view?
version of oracle db, sql developer, and java?
try creating a new table with a blob in it, and see if you can add a file to a new record
yep, its a table! created a new table, same issue. happening on all fields, not just BLOB’s. database is Oracle 11.2.0.4. For SQL Developer, i normally use V3.1.07 with Java 1.6. Thought that was old, so I installed SQL Dev V18.4 with Java 1.8 – issue happens in both. running on a Windows 7 Enterprise (SP1)64-bit PC
Java6 and 3.1 are ancient.
Java 8 and 18.4 are..old.
But it should be working, even on 11204.
Windows 7 is getting killed by MSFT soon – you should probably upgrade off of that as well.
You see any messages/errors written to the Log panel?
found this:
SEVERE 107 0 oracle.ide.extension.HashStructureHook Unexpected runtime exception while delivering HashStructureHookEvent
yeah i see that error too on my 11204 box, but the feature is working for me
I’m stumped.
Thanks for checking.
Hi Jeff,
I’m trying to build on the custom card.js sqlformat.
How can I include the column name before displaying the value?
e.g.
instead of:
ROW 1
PRIMARY
I want:
ROW 1
ROLE: PRIMARY
Thanks in advance.
Here you go
Hello Jeff
My question is about SQLDeveloper Data Modeller (abbr SDDM).
I have recently started using SDDM 19.2 0.182. I am a long time ERwin user.
I am developing a “Logical Model”.
I need to define a “Node” entity to represent a hierarchy.
The attributes are node_id (pk), node_name, sw_apex.
I added a non-identifying 1-to-M relationship, which resulted in the entity getting an extra attribute named node_id1. This is as one expect. However, for better readability, I wish to rename the node_id1 as parent_node_id.
How do I achieve this?
The Release 18.2 User Guide did not help.
Thanks for your help. – Madani
Hi!
We’re trying out the Oracle JDBC REST driver. Is there a way to establish SSL connection without certificate validation? Let’s say I don’t want to change ORDS settings and don’t have access to Java environment configuration. Is there some way to achieve that?
Quick one, hopefully. Looks like the 19.2 ords.war isn’t included in the 19.2 sqldeveloper build. Is that correct, and should I expect that to be the case in the future?
yes, we quit shipping ords with SQL Developer with v19.2, there should be a release note for that
you can still download ords separately and use SQL Dev’s installer wizard to get it going though
Yes, just have to change *my* release notes too. thanks for the quick reply.
Hi Jeff,
v19.1
We are exporting table data to CSV via a GET service. Some of the text fields contain special, carriage returns and line feed characters.
This results in scewed csv file with carriage return fields appearing on a new line which messes up the processing of the file.
We have tried to put quotes “” around the text but this results in the consumer having quotes displayed on their system.
Any suggestion or any way to configure anything in ORDS config for this?
Regards
Satinderpal
Your problem is you’re using the wrong format for your data – having carriage returns in your strings means you essentially can’t use CSV.
I would look at using xml, json, or even a CLOB to transfer that data to your application.
v19.2
I export the Connections File and Encrypt the Passwords with a Key. Try to import in v19.1 or 18.4 and I get an “error” that the Key is not the same — I have verified by doing this twice in exporting the connections. Importing into v19.2 works with the Key. The file format is json.
we changed the password encryption kit, so you can’t downgrade 19.2 exported connections
Correct. Following:
Find your table
The BLOB cell will say (BLOB)
Double-right-mouse-click
Hit the ‘pencil’ button
Opens VIEW VALUE window