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,727 Comments
Hi Jeff, have a question about sql_macro and ORDS.
Created this macro:
CREATE OR REPLACE EDITIONABLE FUNCTION FNC_GET_GEODATA_GOB
(i_gobid number, i_get_anno number)
RETURN VARCHAR2 SQL_MACRO(table) AS
— v_rdfg_table varchar2(200):= ‘RDFUSER.NETWORK1#rdfg_STRM_G_ABOX’;
— v_asset_view varchar2(200):= ‘RDFUSER.VW_STRM_ASS_MSP_KABEL’;
— v_subtype varchar2(200):= ‘rf:OWLClass_000590’;
v_rdfg_table varchar2(200);
v_asset_view varchar2(200);
v_subtype varchar2(200);
v_err_msg varchar2(4000);
BEGIN
SELECT
nw.network_owner
|| ‘.’
|| nw.network_name
|| ‘#rdfg_’
|| replace(sem.sem_model, ‘TBOX’, ‘ABOX’) semtab,
nw.network_owner
|| ‘.’
|| frm.form_viewname,
gob.gob_subtype
into v_rdfg_table, v_asset_view, v_subtype
FROM
lidia.lidia_sem_nodes sem
INNER JOIN lidia.lidia_graph_network nw ON sem.fk_graph_network = nw.id
INNER JOIN lidia.lidia_geo_object gob ON gob.fk_sem_model = sem.id
INNER JOIN lidia.lidia_objects_forms gob_form ON gob_form.fk_geo_object = gob.id
INNER JOIN lidia.lidia_domain_form frm ON gob_form.fk_form = frm.id
WHERE
gob.id = i_gobid;
RETURN
q’!
select json_object(‘type’ value ‘FeatureCollection’, ‘features’ VALUE json_arrayagg(
JSON_OBJECT(‘properties’
value (JSON_OBJECT(
vw.*) )
— ,’geometry’ value SDO_UTIL.TO_GEOJSON(rdfg.geom) FORMAT JSON,’id’ value rdfg.gid)
,’geometry’ value rdfg.geom.get_geojson() FORMAT JSON,’id’ value rdfg.gid)
returning clob) returning clob) as “{}geojson”
FROM
!’ || DBMS_ASSERT.NOOP(v_asset_view) || q’! vw
INNER JOIN !’ || DBMS_ASSERT.NOOP(v_rdfg_table) || q’! rdfg ON rdfg.onto_id = vw.ldkey
WHERE
rdfg.is_anno = i_get_anno
AND rdfg.gobsubclass = !’ || DBMS_ASSERT.ENQUOTE_LITERAL(v_subtype)
;
END;
/
If i execute it like this it works fine and get the geojson data:
select * from FNC_GET_GEODATA_GOB(i_gobid => 79, i_get_anno => 0);
If a create a REST service for it it doesn’t work , error no data found
62565%3a%20The%20SQL%20Macro%20method%20failed%20with%20error%28s%29.%20%20ORA-01403%3a%20no%20data%20found%20ORA-06512%3a%20at%20%22LIDIA.FNC_GET_GEODATA_GOB%22%2c%20line%2013%20ORA-06512%3a%20at%20line%205%20
If i replace i_gobid with harcoded value 79
WHERE
gob.id = 79;
the rest service works. Any idea or can i do this in another way?
Greetings,
Martien
This isn’t a simple question. For me to help, i’d need the table, some data, the actual REST API you coded…and I’m not a full time support engineer, so if you want me to take a look, give me a complete test case, and I’ll try.
Have you dumbed this down to a hello world version of SQL Macro to see if that’s the culprit (bug!) or if that’s just a red herring?
Hi Jeff,
I said to myself that I would use the new VS Code extension for a month instead of the existing SQL developer application I was using and I ran into another feature that I used a lot in the old SQL Developer that isn’t in the new VS Code version and that is the ability to see the definition of a table when you CTRL + Left Click on a table in your SQL.
I think VS Code has a default action to peek definition of a variable/object which could be hooked into to produce the same output that we usually see in SQL Developer when i CTRL + Click on a table.
Let me know if you need any more info and I’d be happy to provide.
Also, I think it would be a great idea if there was some way of seeing all the feature requests of users of the VS Code extension and having an option for people to vote on them and then the most popular one would be added to the developer pipeline. This will save me writing all my findings to you here which I’m sure is probably not ideal for you haha.
Thanks,
Jack
I think VS Code has a default action to peek definition of a variable/object which could be hooked into to produce the same output that we usually see in SQL Developer when i CTRL + Click on a table.
Yes, that was our thought, too – but it’s reserved for the info used to represent the database object in the virtual file system, that’s why you get that json/meta view you see. I think we’re still kicking around ideas to make this better.
All of the feature requests are known, basically, – all the things we built for folks in the old sql developer basically represent 80% of the roadmap for the VS Code extension. The rest will be made up of integration with other popular extensions, say for example the python universe imagine dragging and dropping a table into a python file and we generate code for you.
Public facing we have the community forum, where folks can basically upvote posts there, and officially we have My Oracle Support.
Hi Jeff,
We are trying to open apex url over internet. For that we have created one proxy server and mapped the apex url into a different one. Now when we are trying to open the url the webpage is loading however we are unable to login and its showing forbidden error. Do we need to make any changes in ords configuration ? We are using ords 23.2/apex 23.2 + ohs. you help would be really helpful for us. Thank you!!
Sorry not enough details, please open a Service Request with My Oracle Support, and be sure to include all of your ORDS configuration parameters.
I’m assuming you’ve been able to load apex pages successfully from your internal network on the existing ORDS.
If so, then it must be a proxy/load balancer issue somewhere…
Finally, what do you mean by ORDS + ohs? ORDS 23.2 would be running as a standalone program, or as a servlet in tomcat or weblogic.
Hey Jeff!
I had a question regarding importing json database connections from SQL Developer to Sqlcl. I am using the following command but it’s giving me the below error:
SQL> cm import database_connections_sql_developer.json
Import file database_connections_sql_developer.json not found in current directory or SQLPATH.
I have checked the current path that sqlcl is in and the file I am looking to import is in that folder so I don’t understand why it’s saying that the file is not found. Please can you help?
Many thanks!
Jack
Just avoid the problem and supply the full path to the file.
Hi Jeff. I managed to figure out the issue. I think it’s a bug with sqlcl related to having spaces in your path to the file. I put the file in a folder where there were no spaces to the path of the file and it worked with no problems. Should I report this at My Oracle Support?
No worries, I’ll sort it Monday – thanks!
Hi Jeff,
Is the SQL Developer Java version dead? No new updates for 9 months. I tried the VSCODE version but it has still a long way to go before being comparable to the classic one. Meanwhile the java version is not being improved anymore.
What are the development plans for SQLDeveloper?
Thank you
To end of life it as soon as the VS Code extension has what you need.
What’s on your must-have list?
We’re doing a maintenance update this month for SQLDev, it’ll get a new java, new SQLcl/commons library, and a ton of bug fixes. But no net-new features are going into SQL Developer. And modeler as well will be coming over as an extension for VS Code.
I haven’t tested it thoroughly and I have to get accustomed to the VSCODE interface. What I am definitely missing is the scheduler section (only the obsolete DBMS jobs are supported), the code outline function, the reports section, no pinnable result sets, no XLSX exports and maybe many others I haven’t noticed yet. The formatter is better (finally!!).
IIn general the VSCODE version is more a plugin than a standalone application, so there aren’t specific menus in the menubar, only context ones. Maybe it is only a matter of getting used to the new interface but for now I still prefer the older.
Thank you
Once you get the hang of it, i’ll think you’ll like it. Especially when you see the extensions available for python, javascript, yaml, json, csv, etc.
Reports are being worked on right now.
DBMS_SCHEDULER will be coming, but later.
Excel exports is super high on my list.
Code Outline, did you see this:
Also, the formatter is the same, we just changed the defaults.
Hey Jeff,
One thing I found missing that is holding me back from switching to the VS Code version at the moment is having SQL Formatting options available in the extension settings like you can do in SQL Developer. This would be really useful for me and hopefully something not too hard to implement.
Many thanks,
Jack
The developer is building that now, should be ready end of August.
Thanks Jeff! Is there anywhere I can see a list of features in the pipeline and also a place where I can suggest any other useful features that I find are missing while using the new VS code version? Or is here the best place?
best formal places are My Oracle Support and the forum
https://forums.oracle.com/ords/apexds/domain/dev-community/category/sqldev-for-vscode
Features actively being developed include…
Hey Jeff,
I would like to use Oracle SQL Developer Data Modeler. I have few questions, about something I was not able to find out how to do.
1) My DB naming convention for primary key constraint is: {table_name}_PK
I would like to every PK index have the same name as PK constraint, and still be explicitly defined (not created implicitly in the DB when constraint is created).
Other indexes have different naming convention.
Am I able to achieve automatic generation of the names for PK indexes?
2) I would like to have some free domains just to attach check constraints to string columns. Can I specify VARCHAR2 domains with no size, and to choose column size for each column? When I tried it (I created domain with no size or zero size), I was not able to define the size somewhere else. The reason is I don’t want to create dozens of domains with the same check constraint for every text column of different size.
Thank you.
Robert
Hey Jeff,
if it will take some days it is ok. Can you just please confirm it, because I feel the possibility my question was overlooked.
Thank you.
Robert
I answer questions as I can.
You’ll get more attention on our modeler forums
So ORDS allows curl POST request. I have successfully pull plenty of data using POST.
Mostly curl post are simple select statements return in JSON format. Works very nicely and simple.
Question is why does this command not return in inserts mode
SELECT /*insert*/ * FROM ACCT_TABLE;
When running same SQL in Web SQL Developer Script does work well.
This command using CURL only return rows as normal * and not /*insert*/.
So, yes command does return with all rows, but no error is return, curl post just like ignores the insert hint.
You would need to use the rest enabled sql endpoint, I’ll do up a demo for you.
On the ORDS curl not returning Insert statements question.
I think the curl POST SQL is treated as single sql and not running sql as a sql script.
In sql developer you have option of Run Statement or Run Script.
In web sql developer if i run statement sql button, it doesnt show the insert on output, just all the lines without insert part.
but in same web sql developer if i run sql as sql script it returns with inserts like so.
Insert into cisadm.C0_INSTALLATION (INSTALL_OPT_ID,QTE_RTE_TYPE_CD,CAMPAIGN_CD,ID_TYPE_CD_BUS,ID_TYPE_CD_PER,CUST_CL_CD_BUS,CUST_CL_CD_PER,ACCT_REL_TYPE_CD,ROLLOVR_THRSH_FACT,BILL_RTE_TYPE_CD,OVERRIDE_BIL_DT_SW,MIN_BILL_PRT_AMT,START_CASH_ONL_PTS,START_CR_RAT_PTS,CASH_ONLY_PTS_THRS,CR_RAT_THRS,MAX_DAY_AGE,HILO_FAILS_SW,GL_BATCH_CD,AP_BATCH_CD,VERSION,QUK_ADD_TNDR_TYP,STRT_BAL_TNDR_TYP,CRE_FA_SS_SW,IB_BASE_TM,IB_BASE_TM_DAY_FLG,USE_ALT_BILL_ID_SW,APAY_CRE_OPT_FLG,BS_FRZ_OPT_FLG,ALT_PER_ID_REQ_FLG,ACTGDT_FRZ_OPT_FLG,PREM_GEOTY_REQ_FLG,ALT_REP_FLG,CTI_INTGR_FLG,START_STOP_DTL_MAX,FUND_ACTG_FLG,BILL_CORR_OPT_FLG,ALT_BILL_ID_OPT_FLG,ALT_CURRENCY_FLG,USR_DIV_CTRL_FLG,USR_DIV_RESTRICT_FLG,USE_NAME_SEPARATOR_SW,NAME_SEPARATOR) values (‘11111′,’ ‘,’ ‘,’KVK ‘,’CEDULA ‘,’COM ‘,’RES ‘,’MAIN ‘,0.7,’POSTAL ‘,’Y’,0.1,0,1000,1000,800,9999,’Y’,’C1-GLFCX’,’C1-APFCX’,107,’CASH’,’CASH’,’Y’,to_date(‘1/1/1900, 12:00:00 AM’,’YYYY-MM-DD”T”HH24:MI:SS”Z”‘),’P ‘,’N’,’AXDT’,’ATWL’,’OPTN’,’CHPC’,’OPTL’,’NONE’,’N ‘,30,’NTPR’,’ ‘,’ ‘,’C1AL’,’C1NO’,’C1NR’,’N’,null);
1 rows selected.
Now i need to figure out how to get the curl post sql to be treated as a run script to get script type output above.
Did you check the docs? See section 9.1.5.3
Thanks for Reply.
Yes, that is the docs i reference for many months to build out other curl sql request, gen small reports, stats counts, etc.
For the most part it was fairly simple to configure, setup certs, and simple select and pull data down.
We only have few key tables that we like to store as insert statements in txt file “just in case”.
But, if using /* insert */ the command will not create insert statements like the web sql developer does.
I have tried the 9.1.5.3 with multiple line query on other commands such as “DBMS_METADATA.SET_TRANSFORM_PARAM” and it executes all statements and works well, but not for insert.
The insert hint does not, the set sqlformat insert says error, when i try to use keyword RUN it states it is not allowed.
I think i will just create a java module to pull select data as json format, and the piece together an insert statement to a file. Thanks for your time.
Hey Jeff,
Does plsql have a package or utility that can create a function/package/procedure dependency graph? We always use lucidchart for diagrams. Before we develop a feature we create a package diagram, but with development time it becomes obsolete and we never go back to it. We would like to see the relationship between functions in a simple text/diagram way.
We have all the parts/pieces to do it…it just hasn’t gotten off the drawing board into a product, yet.
Hello Jeff,
We use SQLDEVELOPER through Citrix. Users encounter issues when relaunching the application after exiting using the cross button. The progress bar freezes at around 10%. Is there a way to disable/hide this cross in order to force users to use ALT+F4 to close SQLDeveloper?
Thank you very much for your support.
Sincerely,
Nabil
Mmmmm, I can’t think of a way, when you’re building a java swing app and it’s Windows, it just paints the windows buttons like minimize/maximize/exit.
I would figure out why launching the app is hanging, and fix that.
One possibility is the dtchache.xml file is being corrupted – this happens frequently when one user launches multiple instances of the app. If you delete that file at startup, i’m betting your problem goes away.
Hi Jeff,
SSH tunneling on the current version of SQL Developer doesnt work and a search on support site presents this note.
I believe this is a pretty handy feature and appreciate if you can throw some light on this issue, whether this get fixed in future releases.
Bug 36130399 : SSH CONNECTION FAILS JAVA.LANG.NOCLASSDEFFOUNDERROR: ORG/APACHE/SSHD/COMMON/FUTURE/CANCELOPTION
Regards,
Rajesh
Fall back a version or open a tunnel using putty, or we’ll have version 24.2 ready in a few weeks with this fixed.
Hi Jeff
I’m looking at ORDS 24.2 to replace ORDS 21.4 with multiple databases with ORDS. I really like the “new” ords syntax, separating out the config, etc.
Sadly, I still have some old databases 11.2.0.4 and 12.1.0.2 remaining as well as supported 19c.
All the ORDS documentation says that you need a supported database (as it likely always has) and I get that. But, if I look on the Certification tab in My Oracie Support and look at the certifications for “Oracle REST Data Services” (version 24.1 is the latest they have), it shows as “Certified” with Oracle Database 11.2.0.4 (for example).
I appreciate that you are NOT support, and I have logged with support…. who say they can’t even look at whether the information being provided to me on the certifications tab is wrong because I’m asking about a database product that is out of support (i.e. 11.2.0.4).
If it can work, I’d like it to, as the ORDS upgrade cycle is shorter and simpler while we also need to tackle database upgrade. if it cannot work, I’d like that to be clear from My Oracle Support.
It would be even better if the upgrade attempt bombed out with a message saying that it isn’t going to work.
If I install or upgrade ORDS in an 11.2.0.4 database (ords install)… no errors and it appears to have worked.
After ords serve… I get errors in the log
WARNING Unable to start the Universal Connection Pool: oracle.ucp.UniversalConnectionPoolException: Failed to create initialPoolSize connections while starting a pool: oracle.ucp.UniversalConnectionPoolException: Failed to create initialPoolSize connections while starting a pool: oracle.ucp.UniversalConnectionPoolException: Cannot get Connection from Datasource: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12504, TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
(CONNECTION_ID=Xj+pNKFjSdmaCzakgpsYZg==)
The tnslsnr log on the “target” database of the database pooi shows some ok connections then some malformed ones (I’ve maybe garbled it worse by chaning the service name to and ip address to
15-JUL-2024 12:31:42 * (CONNECT_DATA=(CID=(PROGRAM=Oracle REST Data Services)(HOST=__jdbc__)(USER=oradev))(SERVICE_NAME=)(CONNECTION_ID=qmMT5fvdSd6yDLlM8ks/uQ==)) * (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=30254)) * establish * * 0
15-JUL-2024 12:31:42 * (CONNECT_DATA=(CID=(PROGRAM=Oracle REST Data Services)(HOST=__jdbc__)(USER=oradev))(SERVICE_NAME=)(CONNECTION_ID=VihQCks8SuytpQfy+NUofg==)) * establish * 12504
TNS-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
ORDS 24.2 seems to work with database 12.1.0.2.
I’d appreciate if you do read this if you can confirm that there is a technical reason why this cannot work with 11.2.0.4 and what that is, and also if there is any way to get Oracle Support to have correct certification information.
Thanks
Tim
We still support 11gR2, unfortunately. It should work. My internal 11gR2 testing environment is working on latest ORDS.
Hi Jeff,
Quick Dev question. Spent the day trying to get to grips with Java Stored Procedures on an ATP instance in OCI. Worked my way through the official docs and got a number of examples working successfully which was good.
1. What is the recommended (and easiest setup) IDE for developing Java on an ATP DB. Connecting to OCI seems tricky with a cloud configuration file. Is JDeveloper the way to go or is that old now? Had a look at the database actions in the OCI ATP dash, but couldn’t see anything. I have been loading java files from sql dev, but that assumes they are bug free before uploading. I have found various folks on forums all giving conflicting advice: what do you think would be my most painless option?
2. Do you know of any good walk through tutorials? I had a look through your archives (and Tm on Oracle Base) but couldn’t spot any.
Thanks as always!
I don’t know of anyone actually using java stored procedures, in or out of Autonomous DB, but I will try to get you some advice.
Hi Jeff, searching for ORDS development material, I’ve finded this pretty example https://diveintoapex.com/2021/11/07/create-an-ords-rest-service-with-custom-json-in-and-out/.
When defining the handler he uses a kind of global variable, :body, “generic_service(p_request => json_object_t(:body),p_response => l_response);” Do you know any documentation about this variables representing de http framework ??
Thanks in advance
Yes, :body reads in the POST or PUT request body into a BLOB
:body_text would do same, but into a CLOB
This is documented here
https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/24.2/orddg/implicit-parameters.html
Thank you very much, tha was exactly what I’m looking for, in the mean time I’ve finded this
https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-http-headers-and-ords-specific-bind-variables
kind regards
Mauricio Fernández
you’re quite welcome, and yes Tim (oracle-base) has a ton of great content for ords!
Hi Jeff,
I have searched the internet after how to debug in vscode with the SQL Developer extension without any result.
Is it possible to debug PL/SQL code in vscode with the SQL Developer extension?
Wonders,
Jan
No, not yet.
We’re working on the debugger now for an update later this Summer.
love sql dev over toad, but with the introduction of windows clipboard history i find i cannot use the key board (arrow up/down, nor enter) after i activate the history (win-v brings up the windows clipboard history display, but the up/down arrows and enter keys don’t work, i have to use the mouse)
any idea why this is, or if aware is there any work around or fix coming
thank you
Tell me more about this
but with the introduction of windows clipboard history
What do you mean by this statement.
gonna have to be quick, the page refreshed last time and i lost what i had written
since win 10 there is a clipboard history that you can turn on, when on windows saves your last 20 or so ctl-c or ctl-x entries, you use win-v to see the list, arrow keys to scroll through the list, enter to select one to paste
when running sql dev, when the history is displayed the arrow keys and enter don’t work (so i have to use the mouse)
sql dev isn’t the only program that shows this action, but it is the one i use the most
Best guess you need to remap some keyboard shortcuts.
I’ll be back Monday to have a look.
Hi Jeff, I was wondering if you’ve thought of anything – and i have a question about ‘remappping’
the Windows Clipboard History comes up as an on top window, i’m not a Microsoft Windows programmer, so i don’t know the guts of the opsys, but when i press Win + V that on top window should have focus
OK, after writing and posting the above i went looking through the key mappings in SQL Dev (thank you for being able to sort on key assignments) and on a whim i unmapped the ALT + Down setting – after that Windows Clipboard History works as expected (remember, I unmapped ALT + Down, but the Down arrow by itself started working
thank you for the time and SQL Developer
So you’re good now, after the remapping?
Regarding the article concerned with pagination of a cursor:
https://www.thatjeffsmith.com/archive/2023/11/rest-api-paging-results-of-a-pl-sql-refcursor/
When using the method shown in the above article:
Can the resulting Rest-handler be used to query for querying?
That is, is it possible to add a query string as commonly used in ORDS:
q={query as JSON}
to get a more focused filtering on the result set of the handler?
No, those built-in features of a SQL based REST API all go away when you move into the ‘code it yourself with plsql’
But, you COULD add them back in, as in, you parse the query parameters in your plsql and update your dynamic plsql to accommodate those requests.
So it’s possible, but it’s not automatic.
Also, try asking your question on the page related to the topic if possible, it’s helps others follow along.
Greetings!
I started using SQLcl recently thanks for the videos, really helpful.
Instinctively I tried the following sequence of commands, the “help edit” seems to infer that I could set _EDITOR=”inline” to be able to edit the sqlbuffer with the inline editor. however it doesn’t work how I thought i would.
It there a reason why I can’t edit the buffer using he inline editing when I bring back a SQL query with the ‘history 10′ command?
It does bring the query back into the buffer but I can’ edit it using inline editor.
VI and notepad can be used yes. but then I have to use ‘/’ to execute the query, which doesn’t put the sqlbuffer in the history, only the ‘/’…
I would love to see this in upcoming versions of SQLcl
I think that’s just a deficiency in the History command.
I think we put it in the buffer so you could quickly run it via ‘/’ vs using ctrl+R…but if you DO need to edit something it becomes tedious as you’ve explained.
I’ll file an ER for you, thanks for the feedback!
Hi Jeff,
Do you know when 23ai or any of 23 versions will be available in OCI? 19c is the only version available for paid instances. I really need to use javascript in the backend… I think!
Autonomous database instances. Forgot to add that part. Thanks in advance!
I know exactly when, but I’m not allowed to tell you. You won’t be waiting much longer.
I watched a few videos on YouTube about Github Copilot for database and I’m very interested to know whether anyone has tried using Copilot and SQL Developer for VS Code and can you write a blog post on that about the experience with PLSQL syntax?
No because talking about AI and someone else’s AI is not easy right now.
But you can expect more in this area, stay tuned.
Is there a target timeline / release version YY.1 when SQLDEV for VS Code – > will be feature complete?
It took us 18 years to build SQL Developer. We’re hoping to have an Extension that’s suitable to replace the old SQLDev in 1.5 years, and we’re 5 months in.
Probably gonna be close to 2-2.5 years, esp when you factor in Data Modeler.
Is there a particular feature or set of features you’re keen to see sooner vs later?
I plan to stick with old SQLDEV until the debugger is available.
Cool.
Won’t be much longer then.
Trying to get to grips with ORDS: confused over something after reading every Post I can find over the last couple of days. I want to use ORDS to build a RESTful api layer consumed by multiple clients: mobile clients, SaaS clients etc
I have a read all of your posts I can find on ORDS and got most of them working in my environment (ATP OCI). I was reading some of the posts by Tim Hall on Oracle Base. Has has examples of procedures using APEX_JSON.write to return content. If I use this: I see the returning content in the ORDS Admin panel when i log into OCI, but nothing in the browser when I run the end point. If i put an explicit htp.p in the procedure, I see the content in the browser, but errors in the admin panel. I see examples from Steve Muench using in out parameters and printing the response in the handler like this:
p_source => q'[
declare
l_response json_object_t;
begin
generic_service(p_request => json_object_t(:body),
p_response => l_response);
owa_util.mime_header (‘application/json’, true);
— Show example of modifying the response from the invoked
— json-in-json-out method before returning.
l_response.put(‘method-used-was’,’post’);
htp.p(l_response.stringify);
END;
]’,
(not sure what the q is for at the beginning p_source parameter)
My current preference is to use PL/SQL JSON datatypes to create and move the json around with, a bit like the example above.
Any tips on the best way of doing this. Procedures/Functions : APEX_JSON.write/ htp.p : in-out parameters : simple/complex handlers. Are any methods more optimised than others at scale?
Why do some methods show in the browser when running the endpoints and some do not? Is there an implicit conversion for APEX which does not work when called from the browser using the APEX apis?
Final point, in an enterprise setting, would you still use the api gateway in OCI, or just expose the ORDS end points straight to mobile and SaaS clients.
Many Thanks
So much here.
How about you send me an email at [email protected] ?
Hey Jeff!
Bear with me as I’m inexperienced with Java… I have a connection to an Oracle DB in SQL Developer which uses a custom JDBC driver. The driver is a JAR file I imported using the “Third Party JDBC drivers” screen. Is it possible to use this driver with the VS Code extension? (I see that “Custom JDBC” is an option for Connection Type, but it wants a URL and despite searching I have no idea if I can use this field to somehow refer to a file on my local machine…)
Why do you need a 3rd party jdbc driver to connect to Oracle?
Whoops, that’s a typo… it’s a NON-oracle DB
More specifically… it’s an IBM Db2 DB, and so my JDBC drivers are the standard db2jcc4.jar & db2jcc_license_cisuz.jar
Alright, I’m learning… I found IBM documentation for DB2, which says the URL should be of the format jdbc:db2://server:port/dbname. However when I try that in Oracle Developer extension (using “Custom JDPC URL”) I get the error “No connection provider found”
I AM able to connect to this DB programmatically (in Python, not Java) using the above syntax for connection URL–when I do that, I also provide “com.ibm.db2.jcc.DB2Driver” as the value of “Java class name” and don’t need to provide the JDBC driver JAR files.
Maybe I need to specify some detail(s) under the Advanced tab of Create Connection?
Sorry if this is a simple question; I am new to all of this and have been searching the web for as much info as I can find.
What you want is an IBM DB2 extensions for VS Code.
Well that just makes too much sense. Guess I got tunnel vision on migrating all DB connections I had in OSD to the VS Code extension. Thank you Jeff!
Hello Jeff,
I hope you’re doing well. For the last few months, I have been trying to authenticate database connection via SQL Developer to Azure AD using latest SQL Developer 23.1.1 and many combinations of JDBCs version. However, I haven’t been lucky enough when the similar approach works on SQLPLUS. Do you have any pointers or suggestions to help resolve this problem?
Thank you.
Jaruwan N.
Did you try this?
https://blogs.oracle.com/ateam/post/centralize-database-authentication-with-azure-ad-tokens-and-sqldeveloper23
Hello Smith,
I hope you’re doing well.
Lately, my APEX_PUBLIC_USER account has been getting locked every morning when I arrive at work. Initially, I thought this behavior was due to the FAILED_LOGIN_ATTEMPTS setting in the profile. To address this, I created a new profile for APEX_PUBLIC_USER and ORDS_PUBLIC_USER with unlimited attempts and an unlimited PASSWORD_LOCK_TIME.
However, the issue persists.
Do you have any pointers or suggestions to help resolve this problem?
Thank you.
Nate
That’s a really bad idea, you’ve just made it easier to compromise your database, especially if someone has a weak password.
Go change your profiles back.
Hello Jeff,
I have changed the profile. Now, back to my main issue.
Do you have any suggestion for me?
Thank you.
There are several things that could cause an account to go inactive/locked. You’ll need to investigate a few scenarios. Passwords expired, account locked due to bad login attempts, someone manually editing the user…
Hi Jeff,
If I recall correctly, you mentioned something about 2FA for SQLDev Web (Database Actions) in the past. Is that something which is available already?
Thanks!
Not yet.
Hey Jeff,
Can I pass a custom sorting/ordering function to the APEX Interactive Grid’s control break ? The default ASC and DESC options are not what I need. I have a list of items. Each item belongs to a category. The control break works great to group them and show the header as the first row of each group. However, it orders the categories/groups/control breaks in desc or asc order only. But i need it to be a custom order. Each item has an index. I dont know how to get the control break to follow the order that I want.
Thanks in advance,
That’s a great question, but it’s not in my wheelhouse. You can find the answer here, or someone that will know the answer if you ask a new question assuming Search comes up empty.