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,802 Comments
When I call the procedure through PL/SQL , it works fine but when i call the same through ORDS with same input , it throws the below error. Please correct me.
SQL Error Code: 6550, Error Message: ORA-06550: line 2, column 8: PLS-00306: wrong number or types of arguments in call to ‘ESB_PAYC_DETAILS’ ORA-06550: line 2, column 8: PL/SQL: Statement ignored.
p_source =>
‘begin
TCT.PKG_bala.pay_details(finp_type => nptype, finp_value => :npvalue, fIscard => :card, p_verrcode => :code,
p_verrdesc => :description, v_refcur => :v_refcur);
end;’
);
Parameter definition
——————————
IN Parameter
—————–
nptype ,npvalue and card —> ‘STRING’
OUT Parameter
——————-
code – INT
description -STRING
v_refcur – RESULTSET
Post man Input
——————–
{
“nptype”:”C”,
“npvalue”:”4123123412341234″,
“card”:”Y”
}
How is nptype getting set?
Sorry, It’s typing mistake here……
It is like the below in code
finp_type => :nptype
I would need your code to do more detective work, sorry.
BEGIN
ORDS.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => ‘TestAPI’,
p_url_mapping_type => ‘BASE_PATH’,
p_url_mapping_pattern => ‘TestAPI’,
p_auto_rest_auth => FALSE);
ORDS.DEFINE_MODULE(
p_module_name => ‘pay’,
p_base_path => ‘/pay/’,
p_items_per_page => 0,
p_status => ‘PUBLISHED’,
p_comments => NULL);
ORDS.DEFINE_TEMPLATE(
p_module_name => ‘pay’,
p_pattern => ‘/inquiry’,
p_priority => 0,
p_etag_type => ‘HASH’,
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => ‘pay’,
p_pattern => ‘/inquiry’,
p_method => ‘POST’,
p_source_type => ‘plsql/block’,
p_items_per_page => 5,
p_mimes_allowed => ”,
p_comments => NULL,
p_source =>
‘begin
TCTD.PKG_BALA.BALA_payc_details(
finp_type => :nptype,
finp_value => :npvalue,
fIsPaycard => :card,
p_verrcode => :code,
p_verrdesc => :description,
v_refcur => :v_refcur);
end;’
);
ORDS.DEFINE_PARAMETER(
p_module_name => ‘pay’,
p_pattern => ‘/inquiry’,
p_method => ‘POST’,
p_name => ‘nptype’,
p_bind_variable_name => ‘nptype’,
p_source_type => ‘HEADER’,
p_param_type => ‘STRING’,
p_access_method => ‘IN’,
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => ‘pay’,
p_pattern => ‘/inquiry’,
p_method => ‘POST’,
p_name => ‘npvalue’,
p_bind_variable_name => ‘npvalue’,
p_source_type => ‘HEADER’,
p_param_type => ‘STRING’,
p_access_method => ‘IN’,
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => ‘pay’,
p_pattern => ‘/inquiry’,
p_method => ‘POST’,
p_name => ‘card’,
p_bind_variable_name => ‘card’,
p_source_type => ‘HEADER’,
p_param_type => ‘STRING’,
p_access_method => ‘IN’,
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => ‘pay’,
p_pattern => ‘/inquiry’,
p_method => ‘POST’,
p_name => ‘code’,
p_bind_variable_name => ‘code’,
p_source_type => ‘RESPONSE’,
p_param_type => ‘INT’,
p_access_method => ‘OUT’,
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => ‘pay’,
p_pattern => ‘/inquiry’,
p_method => ‘POST’,
p_name => ‘description’,
p_bind_variable_name => ‘description’,
p_source_type => ‘RESPONSE’,
p_param_type => ‘STRING’,
p_access_method => ‘OUT’,
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => ‘pay’,
p_pattern => ‘/inquiry’,
p_method => ‘POST’,
p_name => ‘v_refcur’,
p_bind_variable_name => ‘v_refcur’,
p_source_type => ‘RESPONSE’,
p_param_type => ‘RESULTSET’,
p_access_method => ‘OUT’,
p_comments => NULL);
COMMIT;
END;
Postman JSON Input…
{
“nptype” : “S”,
“nptypevalue” : “23456”,
“card” : “Y”
}
When I call the same procedure with below input , it is working fine.
DECLARE
verrcode Number(10);
verrdesc varchar2(100);
v_refcur SYS_REFCURSOR;
BEGIN
TCT.PKG_bala.pay_details( (‘S’,’23456′,’Y’,verrcode,verrdesc,v_refcur);
END;
I’m going on vacation for the week, so I recommend you move this to our public forums over at community.oracle.com
How can we make a call to SQL function in ORDS….
I am trying with this option ….Is this correct? Please correct me .
p_source =>
‘begin
returnvalue := pkg_bala.bala_function(
serno => : serialnumber,
fEmail => : email,
ferrcode => : code,
fStatus => : status);
end;’
);
i am capturing function return value in out parameter like the below
ORDS.DEFINE_PARAMETER(
p_module_name => ‘real’,
p_pattern => ‘/registration’,
p_method => ‘POST’,
p_name => ‘returnvalue’,
p_bind_variable_name => ‘returnvalue’,
p_source_type => ‘RESPONSE’,
p_param_type => ‘STRING’,
p_access_method => ‘OUT’,
p_comments => NULL);
You’re close
p_bind_variable_name => ‘returnvalue’And then in your begin block do this
:returnvalue := pkg_bala.bala_function(...returnvalue is a :bind variable you’ve declared via the DEFINE_PARAMETER() call.
When I try to call function in ORDS, I am getting the below error. Please give your suggestions.
ORDS code for function call
————————————-
Defined ORDS.ENABLE_SCHEMA, DEFINE_MODULE,template,handler etc
——————————————–
‘begin
ReturnValue := (TCT.pkg_mgr.realreg(
Serno => : serialnumber,
fEmail => : email,
ferrcode => : code,
ferrdesc => : desc,
fAction => : action,
fStatus => : status
));
end;’
Defined return value also like this apart from other IN and OUR parameters….
ORDS.DEFINE_PARAMETER(
p_module_name => ‘time’,
p_pattern => ‘/registration’,
p_method => ‘POST’,
p_name => ‘ReturnValue’,
p_bind_variable_name => ‘ReturnValue’,
p_source_type => ‘RESPONSE’,
p_param_type => ‘STRING’,
p_access_method => ‘OUT’,
p_comments => NULL);
Error details:
—————-
403 Forbidden
2022-07-26T15:31:19.940356Z | DsFMi8TKh3tml1blMmFE5g |
The request could not be processed because a function or procedure referenced by the PL/SQL statement being evaluated is not accessible or does not exis
your rest enabled schema can’t execute TCT.pkg_mgr.realreg or something in that program is being referenced which the rest enabled schema can’t access.
thanks Jeff…
It works..
Thanks for your quick response.
Awesome, enjoy building your REST APIs!
Jeff,
Here are two vendors that connect to Fusion SaaS Databses. It is time Oracle adds that feature to SQL Developer.
https://ritesoftware.com/products/cloudminer/
https://sqlconnect.com/
I already have at least one Fusion SaaS making their databases available to customers via SQL Developer Web.
This decision is up to the Fusion team, not us.
In SQL Developer Web, the displayed output appears to be automatically rounded to 3 decimal places. This can be rather confusing when your key value is actually held to 5 decimal places.
Is there a setting to disable this feature or is this a bug that would need an SR to resolve?
I’m going to file a bug for you. If you execute as a script, you’ll see the full value, so you have a workaround.
Thanks Jeff. That’s much appreciated. I’ll keep an eye out for any patch updates.
Hi, I’m recovering from a little surgery and decided to spend some time looking at ORDS. I watched this video https://youtu.be/prkcgJLsfw4 and when I got at the SQL statement backing the REST service (39:52) I simply got stuck. I have never seen a cursor nested into a select statement in 15 years of PL/SQL (that’s Oracle baby, you’ll never, never know enough).
But why such an exotic (to me) statement in a simple REST example? I decided to dive deeper and use the same construct on my own data and use it in my REST homework. And, surprise surprise, I find out the resulting JSON was a beautiful structured one, the answer to an old question about how to get a master-detail-subdetail response instead of a flat repetitive resultset. Anyway, this leads me to some considerations:
the way we write the backing code affects the JASON format returned
the way we write the backing code also affects the performance, which is vital in a REST service
so we must be very careful when deciding what we want and how we get it. The question is: what are the relations between the JSON structure and the underlying query, and, Is there any guideline or pattern to drive us to the right solutions?
The ‘cursor’ trick is interesting, it does come in handy when you want a hierarchical response in your JSON without having to write any PL/SQL code.
There are OTHER ways, see this post – it’s quite fun!
The question is: what are the relations between the JSON structure and the underlying query, and, Is there any guideline or pattern to drive us to the right solutions?
What a great question. I’m going to do my best to answer it, but feel free to reply with follow-ups if you want to go deeper.
Hi Jeff,
I red Stew’s post and yes, that’s really interesting, I have to work on it. By the way, I’m using Database Actions on my always-free OCI account. To make the REST handler work with “cursor” I have to set the page size to zero and that’s fine, but I noticed that every time I change and save the handler SQL code, the REST call ends up with “ORA-22902: CURSOR expression not allowed” because the page size property has changed to null(?) in the handler. Setting it back to zero fixes the problem, until the next code change.
Yup, I found that myself and logged a bug to get that sorted going forward.
Hi Jeff
Currently using SQL Developer 22.2.0.173 Build 173.2018
I can connect to any of our Oracle 19c databases however when I attempt to modify profiles the dialog box fails to appear for certain patch versions. Not all DBs are patched to the same patch level.
Does not work (i.e. presents dialog box for profile to edit): “Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.14.0.0.0”
Works (Profile Edit Dialog box appears) : “Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.11.0.0.0”
Any thoughts or have I missed an update and this is a possible bug?
Kind regards
Steve
Do you see any messages in the Log panel?
Hi Jeff,
What version of sql developer is compatible or supported for Windows Server 2019? The Platform Support for 21.4 shows this:
Oracle SQL Developer 21.4 is available for Windows 7,8,10 and Windows Server 2008/2012
Thank you,
David
depends on the Java support.
if windows server 2019 is supported with oracle jdk 8 and 11, you can run pretty much any version of SQL Developer
Jeff,
Thank you!
SQLDeveloper 22.2
Is it possible to disable the “Query Builder”? Accidently clicking on it in a worksheet with a complex query causes SQLdeveloper to lock up.
That’s not a feature you can disable today.
in SQL developer, we have more than a hundred of database that we are supporting. i entered all of these connections and group it by folders according to it’s applications. there are times that i do not know which folder that the database belongs to and i had to go through each of them to find it.
is there a better way like find/search box in the SQL developer? please advise.
thank you.
No…not today at least, but sounds like a good idea.
Hi,
We implemented a service and for some parameters it takes longer that statementTimeout setting.
When the timeout occurs the client receives code 500 (not very helpfull).
Internally in logs we see ORA 1013 – The user cancelled the request.
How can we catch this error and return a code like 217 for example so the user know it must change the parameters to split the request in smaller chunks?
Thank you
Hi Jeff,
I’m trying to use the journal table method but can not find documentation for it
This no longer works:
https://www.thatjeffsmith.com/archive/2016/05/sql-developer-data-modeler-adding-journaling-tables/
I’m sure it is out there but please can you point me in the direction of where it now is?
Also, is it an idea to use a hash to see if an update actually updates a row and not audit it if not? Eg an update of setting all values of a table col to 1 without a where clause looking for where it it null or not 1? Extra overhead in the update but fewer rows and data in the journal.
TIA
Phil
How do you mean it no longer works?
Hi Jeff,
I “suddenly” get an additional symbol added to my browser symbols: a yellow dot in a green cirkel,
The design itself now got a suffix [develop] to the name.
I browsed through manuals but failed to find a clue.
Can you help me out here?
Thanks in advance.
Cheers
Wim
Browser symbols? What are those?
Hi Jeff,
When I want to generate links in the response (for request with GET Method) it is pretty easy if I use the handler like this (it is enough to use the proper alias i.e. “$.id”):
begin
ords.define_handler(
p_module_name => ‘links.example’,
p_pattern => ‘:id’,
p_source_type => ords.source_type_collection_item,
p_source => ‘select emp.empno “$.id”, emp.*, decode(emp.mgr, null, null, ‘^/managers/’ || emp.mgr) “$related” from emp where empno = :id’);
commit;
end;
But I want to use PL/SQL procedure (not SQL SELECT statement ) for the handler for GET Method (not POST method)!!!
In this case while defining the handler:
p_method is ‘GET’
p_source_type is ORDS.source_type_plsql
p_source is ‘BEGIN get_emp_json2; END;’
And the procedure itself is like this:
create or replace PROCEDURE get_emp_json2(p_empno IN emp.empno%TYPE DEFAULT NULL) AS
l_clob CLOB;
BEGIN
select json_object( ’employees’ value json_arrayagg(json_object (
’empno’ value e.empno,
‘ename’ value e.ename,
‘job’ value e.job,
‘mgr’ value e.mgr,
‘hiredate’ value to_char(e.hiredate,’YYYY-MM-DD’),
‘sal’ value e.sal,
‘comm’ value e.comm,
‘deptno’ value e.deptno ) ) returning clob ) as json_doc
INTO l_clob
FROM emp e
WHERE e.empno = DECODE(p_empno, NULL, e.empno, p_empno);
OWA_UTIL.mime_header(‘application/json’);
HTP.print(l_clob);
END;
/
How can I generate the hyperlinks in this case ?
Can I generate them only manually ? If YES, so how to generate server name:server port and schema name for such a link not hardcoding the names but rather generating them automatically?
http://localhost:8080/ords/ordstest
Best regards
Krzysztof
There is a way to generate links in plsql handlers automatically.
But, your code is generating the JSON and printing it vs letting ords handle that.
So now you’ll have to generate the links yourself.
You can probably grab bits off the request headers to reconstruct the URL for your link.
My advice would be to let ords generate as much of the response for you. That’s the beauty, just write SQL or call plsql, we do the rest.
In the browser top before Design[1] there is e.g. symbol for maps/folders
I referred to these as “symbols”.
Maybe should have called it “icons”
Anyhow, I do not see where I can upload a printscreen so I hope this clarifies it; -)
Cheers
Wim
Oh, you mean Data Modeler? Also there are plenty of sites out there you can upload pictures to… including our product forums.
Interactive report CSV downloads do not always work on the first attempt with APEX application. Instead of returning a file for download, APEX sometimes returns the normal page.
Steps to reproduce the issue:
Log in to APEX QA.
Go to application Reports
Click Actions > Download > CSV > Download.
The page will redirect to http://qa.casms.am.edwards.lcl/ords/f?p=104:64:SESSION_ID:CSV_N::::. The “CSV_N”
I’m not on the APEX team, and while I might be able to help, I don’t have the bandwidth. The APEX community is amazing, I suggest you go there for help with APEX.
Hi, is there a way to get a fresh timestamp in the prompt each time you hit enter in SqlCl? In SQLPlus I was able to use this as my prompt:
SET SQLPROMPT “_USER’@’_CONNECT_IDENTIFIER _PRIVILEGE _DATE> ”
but in SqlCl, it seems like the timestamp is set once when you connect, but doesn’t change after that.
Hello Jeff,
how to permamently disable (to not show this box) Save dialog for Worksheet when i close worksheet or close application?
“… has been modified. Save changes?”
If there isn’t a preference for it, I don’t know of a way.
Using SQLDeveloper, is there a way to write a hierarchical report that can expand and collapse nodes in a tree structure in a similar manner as the connections expands to shemas and its objects.
If not directly in SQLDeveloper, any suggestions on the minimalistic approach could be.
Hi Jeff,
I am starting to use ORDS to REST enable my PL/SQL APIs, but I have a couple of questions.
1) Eventually, I will need to enable authentication in order for my users to use the web services. What would be the best OAUTH method to implement authentication for internal users?
2) Is there any way that the database can automatically “know” who the user is using the rest service when using OAUTH? This information would be very valuable for database auditing etc. along with any additional home-grown application security that may be used during the rest call. I know the owning schema and proxy user information is available, but this is still generic.
Many thanks in advance,
Mark.
Hi Jeff,
Sorry to nudge, but did you get chance to look at this question?
Many thanks,
Mark.
I looked at it, and I thought about it.
Here’s what I would do.
Build a REST API to return a nice little JSON doc. Then use that with a REACT/Python/Whatever app you like to build your report.
Hi Jeff,
Thanks so much taking the time out of your day to reply!
Unfortunately I am not after a report in this case and I apologise if my question wasn’t clear.
My question was more about what ORDS does automatically after successful OAUTH authentication with respect to setting any database environment variables (e.g. SYS_CONTEXT) in the connection from the pool before executing the REST call. Using standard Oracle database auditing, I would like to see the user/client who initiated the REST API through ORDS.
Many thanks in advance,
Mark.
Sorry, you had asked multiple questions, my answer was for your other question on hierarchical…
I think you want to look at the pre-hook feature. It allows you to implement finer-detailed authentication, and lots of customers use this for example to enable things like Virtual Private Databases (VPD) – where a user only gets access to certain rows in a table. Here’s a great external write-up on that.
The authenticated user for an oauth2 client is the CLIENT ID associated with that client, so whoever you gave that client to…unless you could pull additional info off the request header.
Hi Jeff,
Just what I was looking/hoping for!!
Thanks
Please can you tell me how to preserve blank lines in 20.4 as this used to be possible but I’ve looked and used the search feature but it seems to have been dropped?
Try this
Hi Jeff, Can you please delete this post as it contains some URLs
A Data Modeler question – when importing a model from an Oracle db data dictionary, is it possible to filter on a list of tables from a text file? There are several thousands of tables in a schema and I want to only import few hundred based on a list I have in Excel.
Thank you advance for any help!
Mmmmm, no. Do you have a user that only has select privs on these tables? That might help.
Or generate ddl on just those tables, and import that.
Total newbie question here, I’m afraid. Apologies for that. I’m a sysadmin, not a DBA, and my DBA asked me to install ORDS (22.1) against an Oracle 19c DB (or at least she did the configuration once I’d installed it). The install went fine, and I even got it running in Tomcat without any issues, but every time we try going to http://:8080/ords/ all we get back is
404 Not Found
2022-06-24T10:54:13.286453205Z | 9oJxaAU6iB0lWtiUOCPb5A | ORDS-22001
The procedure named apex could not be accessed, it may not be declared, or the user executing this request may not have been granted execute privilege on the procedure, or a function specified by security.requestValidationFunction configuration property has prevented access. Check the spelling of the procedure, check that the execute privilege has been granted to the caller and check the configured security.requestValidationFunction function.
Now, we don’t have APEX installed in this DB at all, and the docs say it doesn’t need to be. So what might I/we be missing, please? We’ve tried a simple DB connection with the ORDS_PUBLIC_USER and the password extracted from
ords –config /etc/ords/config config get –secret db.password
and it worked, so it’s not a password thing. Any thoughts, please?
With thanks,
Ben
Welcome newbie! Don’t apologize or feel bad.
/ords is the base URI for Tomcat to know you’re talking to the ORDS web app.
Now, if you’re not using APEX, there’s nothing for ORDS to serve at that URI.
Once you have a schema REST enabled, you can use that account to login to SQL Developer Web if you enabled that on the install.
Go to localhost:8080/ords/sql-developer
Then supply the schema name, and then again with the password.
Then go to the REST panel. from there you can enable a table or hand-code a REST API.
Before you can use ORDS for anything, you’ll need to enable a schema and publish a REST API on that database. Then you can call that.
I already provided feedback for this on the ORDS forums.
How can I get SQLDeveloper to set roles after login so the connection and schema browser show the objects accessible via those roles? I.e., apply the role to EACH session that SQLDeveloper establishes with the database?
I’ve looked and cannot find where I can tell SQLDeveloper which roles to apply upon login. The user id does NOT have the roles default in the database; they have to be turned enabled after login to the database (by database manager policy because we don’t want every session to have the roles; only enable when needed and intentionally turned on).
Or is that the requirement… that the roles are default so the database enables them.
You could code them via a login.sql script. Or use a logon trigger.
I think the login.sql will work.
Thank you!