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,555 Comments
Hi Jeff,
I love the concept of creating user-defined reports in a parent-child(ren)-grandchildren way. If I have one parent (A), two children (B and C) and each of them have at least a child themselves (B1, B2 and C1). If I click on the parent row (A) then the child report (B) that is the first/leftmost will execute automatically, then I click on a row in B, and the first grandchild report is executed (B1). If I then click on child C, then if I have visit C before, the grandchild report (C1) is executed BEFORE it’s parent report (C) is executed. This is not needed, wasting time and crashes the Oracle server process if grandchild report C1 references to :BIND-columns that only exists in C. Could you follow that? 🙂
So I get the feeling that this technique is better for a single parent, single child and then one or more grandchildren but it doesn’t work that well for a tree structure (many children that may have many children).
Hi Jeff.
I wonder if it is possible in my user-defined reports to create a field that I can hover on and then get additional info about? The idea is to just present a single character and if the user wants more information about what’s hiding behind it, (s)he could either hover on the output or click on in it (and not as a child report). Like when the mouse hover above a connection in the connection tree, you get additional info about the connection.
I have tried every way that I can think of: hove, hyperlinks, css…
And of course I mean the technique to use ‘…’ in the SELECT-list or as a argument to dbms_output.
No, not that I know of, sorry.
Hi Jeff and others,
We’re using ORDS 20.4
Let’s assume we have a Client ABC defined in DIT environment, which gets me the Client ID and Client Secret Key.
I promoted the Client definition to FIT, IAT and PROD. I can reset the Client definition in each of these environments and get the Client ID and Client Secret Key.
I communicated the Client Credentials for DIT, FIT, IAT and PROD to my Client/Partner.
No issue so far.
Now, I need copy PROD’s database for a load test in IAT.
How can I reset the Client Credentials to the previous IAT’s value, which I’ve communicated to my Client/Partner?
The same need exists when copying PROD for a refresh in DIT or FIT.
I don’t want to re-communicate to the Client/Partner with a new Client Credentials, when each time the refresh from PROD occurs in DIT, FIT or IAT.
I’ve a dozen Clients and the refresh frequency is every quarter.
Any help to regenerate the Client Credentials with a known value, will be appreciated.
Thank you.
Ramesh
There is no way to do this, via the APIs. But go and look at the OAUTH tables in the ORDS_METADATA schema…
I have a bug/ER filed to make it possible for you to generate a client by supplying the desired secrets vs us generating it for you.
Hello,
Please I need to export some data through a query from an oracle Database to an excel spreadsheet worksheets. the export need to be scheduled to run twice a month. I had thought of using Oracle SQL developer, cart, export function. I don’t know how to schedule these. Please I need help and advise to achieve this. what’s the best way to do this.
Before I answer your underlying question, which I will do by pointing you to a blog post where I show you exactly how to do this, does it have to be Excel, or will CSV suffice? Because it’s 90% easier to stick with CSV.
Hello,
In SQLdeveloper I have several Tabs that have hundreds of SQL statements. When I run them the tab is named query result 1 , query result 2 etc…. Is there any way to put some command in the sql to name tab, don’t want to have to use the right click the tab and rename it manually
You can rename the query results tabs with code, but not the sql worksheet tab itself. If you start using files, then this is fixed for you automatically.
ok so I have one file opened test1.sql , it has 30 different SQL statements in it , when I run them the output tab is labeled query_result_xxxx, in the SQL how to I name the result tab ?
Sorry, you can rename your Worksheet tabs…
set worksheetname Hello — run with F5
Hi Jeff,
Really appreciate your feedback and suggesting me to explore the ORDS_METADATA schema for any direct SQL update. Hope my infra guys (who traditionally owns the password for SYS, APPLSYS, ORDS_METADATA or ORDS_PUBLIC_USER etc…) allow me to do that…
If not, I’m in a spaghettis soup.
Nice part of your update is that you logged an SR/bug with Oracle and as you know DevOpps is a key for any great product. Being a Product Manager at Oracle, you can influence a lot in the product with a resolution for a real issue.
Hoping Oracle as a giant provider of this tool will consider a method to reset the Client ID and Secret Key, with a known pair of values – in lower instances for retaining the contracts with the Partners – in a near-term release.
Beats Regards
[email protected]
Hi Jeff,
I have read the post and i was able to export .xlsx file.
I am using the SDCLI call as you described it in your post.
sdcli cart export -cart C:\Users\jdsmith\sf_blog_cart.sdcart -config C:\Users\jdsmith\sf_blog_cart_example.xml -target C:\Users\jdsmith\export_locations_quoted_numbers_linux_line_feeds_cli.txt.
When I run it on CMD. Its asking for the db connection password I used to create the cart. Please is there an SDCLI switch for password. Is there another option to pass the password
Hi Jeff,
Please I want to create a batch file out of this code that I can schedule with Task Scheduler
sdcli cart export -cart C:\Users\jdsmith\sf_blog_cart.sdcart -config C:\Users\jdsmith\sf_blog_cart_example.xml -target C:\Users\jdsmith\export_locations_quoted_numbers_linux_line_feeds_cli.txt.
Dear Jeff, last week I discovered SQLcl and Liquibase, I LOVE IT!!!!
I want to establish it at Bosch and am analyzing its features, especially who it reacts to DB changes. I found out that renaming a column isn’t a good idea since the column is dropped and recreated. Knowing this you can create a backup table as a workaround before a deployment and so on.
Is there an overview of the DB changes and their implementations where I can see the impact?
It’s me again 🙂 Forgot the most important point: Will SQLcl & LB support EBR?
Thanks for helping us at Bosch introduce CI/CD with LB *thumbsup*
Yes, although not directly. Once you establish your session, you would need to alter it to effect the EBR edition you want to be active. Then any changelog applied would go where you wanted it to go.
Please it has to be excel
Then see this post
We’re going to add (in a future release, high priority ER) a –force flag for update, that if you do NOT include, we will then FAIL/ABORT any update which has a destructive impact like a DROP. That way you won’t accidentally clobber any data.
In the meantime, you can use the ‘updatesql’ command to see exactly what DDL will be applied for your changeset on an UPDATE. You can use this to see if what the database is proposing is safe for your scenario, or if you need to step in with some developer-written/manual changelogs.
So no way to rename output tab except manually ?
If this is the case can it be added for a Request to update the tool ?
Can a product enhancement be put in to allow in the sql code to name the result tabs ?
You can do it with a right-click…to be honest if it were easy, we would have done it by now. There is a standing ER for this very thing though, so you’re not alone.
Hi Jeff,
I am using the ORDS auto rest services , my PL SQL block is :
DECLARE
p1 VARCHAR2 (50) := NULL;
p2 VARCHAR2 (50) := NULL;
p3 NUMBER := NULL;
BEGIN
:out_1 := p1;
:out_2 := NVL (p2, ‘ ‘);
:out_3 := p3;
END;
and the REST JSON response is ,
{
“out_2″: ” ”
}
my question is , why the JSON did not return out 1, out 3 as null , like this
{
“out_1”: NULL,
“out_2″: ” “,
“out_3”: NULL
}
Thanx.
that looks like a restful web service module, not the AUTO plsql feature…if you assign out-1 and out-3 to ” vs NULL, does that help?
ok once I have named the tabs any way to save the SQL file with the Tab Result tabs, so that the next time I open the SQL file the Result tabs will already be there
the files will be there if you close the app with them open (files) but the results will be gone….those are being held in memory, if you want to persist a query result, you need to export them to local files.
Jeff,
how can I display and image in html with ords?
example:
AS
procedure test1 is
begin
htp.p(”);
htp.p(”);
htp.p(‘MyWebsite’);
htp.p(‘ ‘);
htp.p(‘Oracle 12c Application Server’);
htp.p(‘OWA_UTIL.get_cgi_env (HTTP_HOST): ‘||OWA_UTIL.get_cgi_env (‘HTTP_HOST’)||”);
htp.p(‘owa_util.get_owa_service_path: ‘||owa_util.get_owa_service_path||”);
htp.p(”);
htp.p(”);
htp.p(”);
end test1;
—
end datest_pkg;
Regards
I don’t see any code there that displays an image. Are you wanting to do that? Or build a REST API that brings back media content like an image that you could include in your web application?
My apologies:
here is the sample code:
htp.p(”);
htp.p(‘My Website’);
htp.p(‘ ‘);
htp.p(‘Oracle 12c Application Server’);
htp.p(‘OWA_UTIL.get_cgi_env (HTTP_HOST): ‘||OWA_UTIL.get_cgi_env (‘HTTP_HOST’)||”);
htp.p(‘owa_util.get_owa_service_path: ‘||owa_util.get_owa_service_path||”);
htp.p(”);
htp.p(”);
htp.p(”);
end test1;
Jeff,
Apparently it does not display:
but it is a html tag : img src folder name of gif
htp.p(”);
ORDS is just calling your PL/SQL code – is your htp.p() call working otherwise (no ORDS)?
Where is this GIF coming from in the first place?
Yes, The htp.p() code is working.
I placed my static .gif files in the /home/oracle/ords/image folder and defined its location in
ords_params.properties
standalone.static.images=/home/oracle/ords/image
Hi Jeff,
What happened to the “clone” option when we right click a table tab? Now it is always faded, so we can’t open the same table in two different tabs.
Sorry. I was talking about SQL Developer 20.2.
I’m going to be out until Tuesday… Not ignoring your question 😀
Dear Jeff,
I have 3 liquibase questions;
1) I have schemas with FKs on views; and as you may know they are included in view definition,
Now when I deploy I need to put them in the “right” sequence, is there a way around that?
e.g. can I do genschema without the fks then with the fks and do a base release then a patch with all fks?
2) When I do genschema on a Exadatamachine, then want to deploy that on non exadata machine I get error related to the target schema not being exadata e.g. hybrid columnar compression not being available, can you elaborate on how to overcome this?
3) on partitioned tables in the overflow partition clause the tablespace is omitted making the ddl fail, any fix for this?
Cheers,
Duke
Jodi.one – your #1 in Java Oracle Data Integrator.
1 – we should be doing that already, if not, BUG!
Also, I know folks CAN do this, but it’s very rare that I find people actually doing it – so interesting to hear.
2 – you would need to manually update the changelog XMLs to remove any metadata around those details
3 – sounds like a bug I would need a test case
I have written a script with SQLcl v20.4.2 using the BRIDGE command to compare two databases, it’s working fine, but the BRIDGE command is always showing these lines “Created table and inserted 1 row(s)”.
Using:
set term off
set sqlformat default
set feedb off ver off lines 200 trims on term on
Example output:
Created table dbOne and inserted 1 row(s)
Created table dbTwo and inserted 1 row(s)
INSTANCE DATABASE_ROLE TIMESTAMP SEQUENCE INSTANCE DATABASE_ROLE TIMESTAMP SEQUENCE
———————— —————- —————– ——– ———————— —————- —————– ——–
DBATST PRIMARY 31/03/21 09:35:37 5836 DDBATST PHYSICAL STANDBY 31/03/21 09:35:39 5836
How can this be suppressed?
If you have feedback off, it should already be suppressed. I would file a bug with My Oracle Support with your test case.
Dear, looking for “portable SQLDeveloper” a build/version I can run external from by default windows C:\ drive, a version which stores user-configurations, passwords, ini and other run-time info locally, outside of normal windows paths, inside of portable-install directory.
Appreciate help.
Add the line from step 1 in Kris’ post to your sqldeveloper.conf file. Make sure also to use the sqldev with an embedded jdk, otherwise you need to make your java portable as well.
Hi Jeff, I often find I’m exploring tables with 100+ columns within table browser. Is there any plan to filter columns using something similar to Select whereby the columns displayed are filtered based on what you type in a search criteria input field?
Would make life so much easier than looking through a long list of columns on the Manage Columns dialog box.
Daz
Sounds like a messy schema… maybe just use sql worksheet to browse your tables?
SQL Developer 20.4 when connecting to a database gives this error
java.lang.NoClassDefFoundError: oracle/sql/json/OracleJsonDatum
Update is this error happens the first time you are connecting , subsequent connections to same database seem to fine and this issue is not reported.
Are you using a thick client connection? And this is 20.4.0 or 20.4.1?
Yes this is 20.4.1 on Windows 10
if you’re using a thick connection, your client/home needs to be 21c if you’re using 20.4.1
extending the usefulness of exporting multiple blobs :
https://www.thatjeffsmith.com/archive/2014/05/exporting-multiple-blobs-with-oracle-sql-developer/
Each record has its own meta data in the database i.e file extension , name
once the blobs are saved as files, this meta data is needed for obvious reason . Can this be incorporated into the tool? Thank you!!
Jeff,
I just spent about an hour trying to import a pretty good sized spreadsheet into Oracle with SQL Developer. It kept giving me errors about strings within strings and columns of the wrong data type, mostly because I only had it look ahead 100 rows I suppose.
I’ve been an Apex user for a long time so I thought I’d go give it a try over in my Apex workspace and voila!, one try and done.
I guess the reason I’m posting is I’m wondering if the tools groups at Oracle use different code bases for these kinds of operations? It would seem as though that’s true but just wondering why Apex handled it so nicely and SQL Developer kept grousing about the details?
Honestly, it’s great to know those details when it comes down to it but sometimes you just need to get things done and move on.
Earl
Yes, both tools use completely different code.
Send me your spreadsheet if you want me to take a look.
That would be great. Would it help if I sent along the saved settings file from SQL Developer as well? It might reveal if I was doing something stupid, and of course I’d love to know that so I can stop being…stupid.
Got your data, will take a look, get back to you – via EMAIL.
Hello Jeff.
I have some queries in Oracle Format, but the SQL DEVELOPER was unable to convert to ANSI format.
Do you know other tool to do this job?
This is one of n queries that I have to convert.
SELECT DISTINCT
A.INCIDENT_NUMBER NR_INCIDENTE
,A.EXTERNAL_ATTRIBUTE_4 NR_INCIDENTE_PARCEIRO
,G.USER_NAME NM_SUBMITTER
,A.CREATION_DATE DT_CRIACAO
,A.CLOSE_DATE DT_FECHAMENTO
,G2.USER_NAME NM_ULTIMA_ATUALIZACAO
,A.LAST_UPDATE_DATE TS_ULTIMA_ATUALIZACAO
,JTFG.GROUP_NAME NM_CAT_GRUPO
,(SELECT
CIEAV.ATTRIBUTE_VALUE
FROM
APPS.CSI_INST_EXTEND_ATTRIB_V CIEAV
WHERE 1 = 1
AND K.INSTANCE_ID = CIEAV.INSTANCE_ID
AND CIEAV.ATTRIBUTE_CODE = ‘CAT_FATURAMENTO’
) CAT
,A.CUSTOMER_TICKET_NUMBER NUMEROOS
,UPPER(NVL(C.DESCRIPTION,C.NAME)) TPA
,H.PARTY_NAME CLIENTE
,SUBSTR(L.PARTY_SITE_NUMBER,0,4) AGENCIA
,SUBSTR(L.PARTY_SITE_NUMBER, LENGTH(L.PARTY_SITE_NUMBER) – 1,2) SAG
,L.PARTY_SITE_NAME DEPENDENCIA
,LOC.CITY CIDADE
,LOC.STATE UF
,SUBSTR(A.EXTERNAL_ATTRIBUTE_3,0,4) AGENCIADESTINO
,SUBSTR(A.EXTERNAL_ATTRIBUTE_3, LENGTH(A.EXTERNAL_ATTRIBUTE_3) – 1,2) SAGDESTINO
,(SELECT
CIEAV.ATTRIBUTE_VALUE
FROM
APPS.CSI_INST_EXTEND_ATTRIB_V CIEAV
WHERE 1 = 1
AND K.INSTANCE_ID = CIEAV.INSTANCE_ID
AND CIEAV.ATTRIBUTE_CODE = ‘SEMAT’
) SEMAT
— ,(SELECT
— CIEAV.ATTRIBUTE_VALUE
— FROM
— APPS.CSI_INST_EXTEND_ATTRIB_V CIEAV
— WHERE 1 = 1
— AND K.INSTANCE_ID = CIEAV.INSTANCE_ID
— AND CIEAV.ATTRIBUTE_CODE = ‘CRITICIDADE’
— ) CRITICIDADE
,IST.NAME CRITICIDADE
,A.CONTRACT_NUMBER NROCONTRATO
,K.INSTANCE_NUMBER NUMEROBEM
,F.DESCRIPTION DESCRICAOBEM
,F.SEGMENT1 || ‘-‘ || F.SEGMENT2 CODIGOORACLE
,ITL.SUMMARY DESCRICAODEFEITO
— ,A.INCIDENT_DATE DTACHAMADO
,A.INCIDENT_OCCURRED_DATE DTACHAMADO
,A.SLA_DATE_1 TS_PRAZO_INICIAL_ANS
,A.EXPECTED_RESOLUTION_DATE TS_PRAZO_ANS
,A.SLA_DATE_2 TS_IDENTIFICACAO_GARANTIA
,(SELECT MAX(ACTUAL_START_DATE)
FROM JTF.JTF_TASKS_B
WHERE SOURCE_OBJECT_ID = A.INCIDENT_ID
GROUP BY SOURCE_OBJECT_ID) TS_INICIO_ATENDIMENTO
,UPPER(B.NAME) STATUS
,(SELECT
CIEAV.ATTRIBUTE_VALUE
FROM
APPS.CSI_INST_EXTEND_ATTRIB_V CIEAV
WHERE 1 = 1
AND K.INSTANCE_ID = CIEAV.INSTANCE_ID
AND CIEAV.ATTRIBUTE_CODE = ‘GRUPO’
) GRUPO
,(SELECT
CIEAV.ATTRIBUTE_VALUE
FROM
APPS.CSI_INST_EXTEND_ATTRIB_V CIEAV
WHERE 1 = 1
AND K.INSTANCE_ID = CIEAV.INSTANCE_ID
AND CIEAV.ATTRIBUTE_CODE = ‘LAT_SERVICO’
) LOCALATENDIMENTO
,(SELECT
CIEAV.ATTRIBUTE_VALUE
FROM
APPS.CSI_INST_EXTEND_ATTRIB_V CIEAV
WHERE 1 = 1
AND K.INSTANCE_ID = CIEAV.INSTANCE_ID
AND CIEAV.ATTRIBUTE_CODE = ‘FORNECEDOR’
) FORNECEDOR
,TRUNC(TO_DATE(K.INSTALL_DATE)) DT_INSTALL_DATE
,A.CONTRACT_SERVICE_ID CD_CONTRACT_SERVICE
,L.PARTY_SITE_ID CD_PARTY_SITE
,L.ATTRIBUTE7 SG_REGIAO
,IUT.NAME TP_URGENCIA
,IUT.DESCRIPTION DS_URGENCIA
FROM
CS.CS_INCIDENTS_ALL_B A
,CS.CS_INCIDENT_STATUSES_TL B
,CS.CS_INCIDENT_TYPES_TL C
,CS.CS_INCIDENT_URGENCIES_TL IUT
,CS.CS_INCIDENT_SEVERITIES_TL IST
,INV.MTL_SYSTEM_ITEMS_B F
,APPS.FND_USER G
,APPS.FND_USER G2
,AR.HZ_PARTIES H
,CS.CS_INCIDENTS_ALL_TL ITL
,APPS.JTF_RS_GROUPS_VL JTFG
,CSI.CSI_ITEM_INSTANCES K
,AR.HZ_PARTY_SITES L
,AR.HZ_LOCATIONS LOC
WHERE
A.INCIDENT_STATUS_ID = B.INCIDENT_STATUS_ID (+)
AND B.LANGUAGE (+)= ‘PTB’
AND A.INCIDENT_TYPE_ID = C.INCIDENT_TYPE_ID (+)
AND C.LANGUAGE (+)= ‘PTB’
AND A.INVENTORY_ITEM_ID = F.INVENTORY_ITEM_ID (+)
AND A.INV_ORGANIZATION_ID = F.ORGANIZATION_ID (+)
AND A.CREATED_BY = G.USER_ID (+)
AND A.LAST_UPDATED_BY = G2.USER_ID(+)
AND A.CUSTOMER_ID = H.PARTY_ID (+)
AND A.INCIDENT_ID = ITL.INCIDENT_ID (+)
AND ITL.LANGUAGE (+)= ‘PTB’
AND A.OWNER_GROUP_ID = JTFG.GROUP_ID (+)
AND A.CUSTOMER_PRODUCT_ID = K.INSTANCE_ID (+)
AND K.LOCATION_ID = L.PARTY_SITE_ID (+)
AND L.LOCATION_ID = LOC.LOCATION_ID (+)
AND A.INCIDENT_URGENCY_ID = IUT.INCIDENT_URGENCY_ID(+)
AND IUT.LANGUAGE(+) = ‘PTB’
AND A.INCIDENT_SEVERITY_ID = IST.INCIDENT_SEVERITY_ID(+)
AND IST.LANGUAGE(+) = ‘PTB’
AND A.INCIDENT_ID = ?
break it down into smaller pieces and give it a try
I have a lot of code snippets in sql developer, it would be nice to have a search or filter feature for me to find my scripts.
I know sql developer has a code template to quickly input the script but i have to remember the short text.
Hi Jeff,
Is it possible to Authenticate ORDS with SAML or External OAUTH(e.g. Using Oracle Access Manager 12c). The idea behind this is we have partner applications already authenticated by Oracle Access Manager(or Federation via OAM) and want to call ORDS API from these without the need to have further authentication on ORDS side.
We do not want ORDS to left open without authentication also, so that from outside of these partner application tries to access has to get authenticated via OAM.
The trick would be to put ords into tomcat (or WLS), then configure tomcat with OAM. Then ORDS would get the authenticated user and their roles from OAM, and you could tie down your endpoints. Or, use some sort of web gateway that would prevent access to the ords endpoints w/o having already gone though your security rules.
Hey ThatJeffSmith. I really like your posts, and they have been very helpful. I’m kinda old, kinda new at Oracle. I used to work with it at my old job with AT&T. I’m not really a DBA, I guess you’d call me a data architect. We have an existing app we developed in DigitalOcean. the data is MySQL. the App is PHP. I’m completely comfortable with SQL although I’m not a complete genius. We recently discovered the APEX free tier, and for the most part, we are all in (except our users, who are still banging away on DigitalOcean.) We are now undertaking to create a Prototype for an API for our data utilizing ORDS, with the hope that we can migrate our stuff over to APEX. I’ve tried several times to Migrate our data from DO to APEX using SQL Developer, but with limited to no success. What I REALLY want to do is to create a DBLINK to our data in DO, but that has also been without success after googling hundreds of pages. Question 1. Can you create DBLINKs in Oracle Free Tier? Question 2: If the answer to Question 1 is Yes, then can you point me to a procedure to do this? I don’t mind digging around some, but these 2 undertakings have been very elusive to this point. Thanks for any pointers!
You want a database link from Oracle Cloud to a MySQL database running in DigitalOcean?
That’s possible in general with Heterogenous services technology, but doing that between clouds won’t be very user friendly in terms of performance. I’d dump all the data out and rebuild as tables in your Oracle DB (where APEX is running).
Jeff, I’ve been banging my head on trying to setup a REST call from Oracle to another in-house platform (for space planning). I’ve got a functioning REST call setup in POSTMAN and the owner of the space planning service gave me the format of the request body. It is as follows:
WS01555
thepassword
In POSTMAN this is being sent as raw text is the request body. I’ve learned that utl_http.write_text is the typical way to send this data along with the request.
I’ve found several posts on StackOverflow and the Oracle community that are a general purpose procedure to get a response from a web service. My test procedure is as follows:
create or replace procedure show_html_from_url (
p_url in varchar2,
p_username in varchar2 default null,
p_password in varchar2 default null
) as
l_http_request utl_http.req;
l_http_response utl_http.resp;
l_text varchar2(32767);
l_body varchar2(32767);
API_TEMPLATE constant varchar2(32767) :=
‘
$USER
$PASSWORD
‘;
begin
–// setup XML for web call
l_body := replace( API_TEMPLATE, ‘$USER’, ‘WS01555’ );
l_body := replace( l_body, ‘$PASSWORD’, ‘thePassword’ );
— set the wallet folder, without the wallet filename but send the password
— for Oracle the wallet filename is always ewallet.p12
utl_http.set_wallet(‘file:/home/oracle/space’,’WalletPasswd123′);
utl_http.set_body_charset(‘UTF-8’);
— begin the HTTP request
l_http_request := utl_http.begin_request(p_url);
— Use basic authentication if required.
if p_username is not null and p_password is not null then
utl_http.set_authentication(l_http_request, p_username, p_password);
end if;
— send the body XML payload
–utl_http.write_text(l_http_request, l_body);
utl_http.write_raw(l_http_request, UTL_RAW.CAST_TO_RAW(l_body));
— get the HTTP response
l_http_response := utl_http.get_response(l_http_request);
if l_http_response.status_code = 200 then
— Loop through the response.
begin
loop
utl_http.read_text(l_http_response, l_text, 32766);
dbms_output.put_line (l_text);
end loop;
exception
when utl_http.end_of_body then
utl_http.end_response(l_http_response);
end;
end if;
exception
when others then
utl_http.end_response(l_http_response);
raise;
end show_html_from_url;
You can see from my procedure that I’ve tried both utl_http.write_text and utl_http.write_raw. The problem that I’m having is the request body isn’t being sent correctly. I know this because I’m getting a 200 response and the message coming back is:
Authentication failed.
Do you have any ideas about how I can make sure that request body is sent in the correct format to the web service?
Sorry, I’m not the best person to help you here, I suggest you post your setup/question to the PL/SQL forums in the Groundbreakers Community.
Funny that you’re getting a 200 with an authentication message…those two things don’t go together.
Yes it is unusual, except that the web service requires an authentication via the request body that I’m bundling up into the API_TEMPLATE value. I just noticed that the code for that portion of the procedure didn’t paste into the question/post properly, so you were flying without a rudder on that. My apologies that I didn’t notice.
In any case, I finally figured this out, after hours of trial and error. The web service request needed to contain both the content type and the content length specifications. I was only sending the content type and as soon as I added the content length of the request body it started working! It felt like the skies had opened up and the data was flowing like sunshine after weeks of storms. 🙂 Thanks for responding. I appreciate that you took the time even though you needed to point me elsewhere.
Sincerely,
Earl
No worries – the api should have seen your request wasn’t’ properly formed and sent you a 400 response with some sort of indicator…
There ought to be a standard, right? 😉
Hi Jeff,
there’s a pretty good “Import data…” feature in SQLDeveloper – for tables.
It w’d be very helpful to have a similar one – for views.
In database application development it’s used to build views with instead-of triggers as down- or upload interfaces to other applications in order to hide the implementation details of core schema (objects).
Such a feature could help testing the upload functionality or just populating the underlying tables.
Is that an idea ?
Regards R.
Use ords and build some rest APIs, then you can really take advantage of your setup.
Hello Jeff,
can we create Scheduler Job using sqcl and start a script. When I do so the script is not executed. I learned the we have do a setting: set encoding utf-8
But where do we have to do this?
reagrds
Hob
Hi Jeff, i wonder if there’s a similar option in Sql Developer to color code your connections as in Toad.
I find it rather useful, eg. connecting to production shows up in red in Toad, as to avoid deploying on the wrong (production) database by some stupid mistake. The red color (or any other color you prefer to configure) immediately makes me aware it’s a production database and as such be extra careful.
Sorry, just found it, i missed it earlier. It’s an option already present and can be found in the connection properties window, in the upper right corner.
Glad you found it!
Hey Jeff, I was trying to connect my flutter desktop application with the SQL developer using rest API, watched your tutorial on how to setup ORDS , but I read on reddit that I need a middleware. If you could give some insight to how to go about with the developer configuration for this setup.
ORDS is your middleware – you can run it as a standalone java app, or as a Java servlet in Tomcat or Weblogic.
Hi Jeff,
I noticed on the below stack overflow link that you mentioned “No, that’s not possible with SQL Developer (the GUI), but our command-line interface, SQLcl does support that… so, we may have some good news to share on this after version 20.2 is released, stay tuned”
https://stackoverflow.com/questions/24952519/open-sql-developer-from-command-line-with-parameters-connectstring-user-passw
Any update, on if this will be possible in future versions?
Thanks,
Jack
Here you go.
I am using Oracle Database 12c, APEX 20.1, ORDS 20.2. I have ORDS deployed on Weblogic Server 12.2.1.3. When I bring down Weblogic, ORDS still works. How is this? I don’t understand how ORDS is still running. I connect to APEX using http://localhost:7003/ords
I also have no idea…apparently ORDS isn’t running in. WLS, or you didn’t really bring down WLS – or you’re getting a browser cached page. Open Developer Tools in Chrome and refresh.