Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

UTL_HTTP and using client certificates

Olafur TOct 30 2020 — edited Oct 30 2020

Hi,
On Oracle 19.3 EE, and trying to call a REST service that requires a client certificate.
I got issued a .pfx/.p12 file with the client certificate to use. Could not use that just straight up as a wallet (bad magic number).
So I created a new Wallet using orapki as described in: Converting 3rd Party pkcs12 or .pfx Wallets for use with Oracle 12 (Doc ID 2147608.1)
Added the end servers intermediary certificate (for the https access) to the newly created walllet. So now the wallet has the client certificate plus 2 server certificates that came with it originally and intermediate certificate of the end server.
ACL created to give my schema user privilege to use client certificates:

exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('all_access.xml','SCHEMA', true, 'connect');
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('all_access.xml','SCHEMA', true, 'use-client-certificates');
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL('all_access.xml','file:/etc/ORACLE/WALLETS/oracle/custom/certwallet);

When I try to call a resource that requires the client certificate, I either get an immediate ORA-29276: transfer timeout (I have timeout set to 30 seconds, this comes immediately) or I get a 403 indicating that UTL_HTTP is not sending the client certificate credentials.
How do I tell UTL_HTTP to use a specific certificate with a certain host?
The wallet I created will only work with this specific host.
Using this certificate in Postman was a piece of cake. Just added the certificate and attached it to a host and after that all REST requests worked without any issue, how can I achieve that in PL/SQL? :)
image.pngRegards
Oli

Comments

Hoek
Please remove this part from your code, since it is a bug:
EXCEPTION
WHEN OTHERS THEN
pErrMsg := SQLERRM;
ROLLBACK;
RETURN 0;
"do not be a sheep in wolf's clothing." ;)
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5080528400346352393#tom5211388400346093295

And do not commit in a loop.
Commit only once, when your complete transaction is done.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4951966319022

Edited by: Hoek on Aug 13, 2012 11:47 AM
jurgenk
And where do you assign a value to mTEMP?
Keith Jamieson
Can you not find all the tasks which are not done in one sql statement?
Do you have a common column between task_done and task_list, eg task_id.
If not, you should have.

Assuming you have a column task_id in task_list and task_done:
for cur in
(
SELECT tl.* FROM TASK_LIST  tl
   WHERE ISACTIVE = 1 
   and not exists(select 1 from task_done td where td.task_id = tl.task_id)
)
loop
  -- only deal with tasks  from the select here
  null; 
end loop;
 
710208
Thanks for reply, but my question is regarding the possibilities of getting 1 task executed twice.
the listed code is not the entire code, but the structure of function only.
710208
yes there is a common column.
but my problem is ,some of the tasks in the loop get executed more than once.
note :this happens mostly when the execution time is too long

thanks for the reply.
710208
Entire code is not posted.

Just the flow of code is given. please assume that all those values are assigned and working fine.
Suppose 5 rows are to be processed in the loop. some task say TASK3 get executed twice even though its occurrence
in the list for loop is only 1.
This happens when execution takes a long duration.

thanks .
Purvesh K
user10442658 wrote:
Dear concerned,

I have one function in a package (databse 11g) with following code.
As listed, looping a table TASK_LIST to execute each task after confirming that it is not
already executed (by querying TASK_DONE table).

Problem I have encountered is , at times same task get executed more than once even if I am counter checking
with variable "mSUCCEED". Please guide me in this regard.
I think this happens because of Duplicate Data in TASK_LIST table. Did you check that? If there are Duplicates, either remove them or use a Distinct keyword. However, you would not get Distinct Data with ' SELECT *', you will have to fetch particular columns to get a Distinct. Moreover, you are just using the TASKNAME in the Loop, so why do a "Select *"? Its a Bad practice.
Using this, you will not have to query the Task_Done table, to ensure whether the Job is processed.

Extract from your Other Reply
but my problem is ,some of the tasks in the loop get executed more than once.
note :this happens mostly when the execution time is too long
Plus, the execution time of the code would not be related to Duplicate Tasks being processed. AFAIK, When the "SELECT * FROM TASK_LIST WHERE ISACTIVE = 1" cursor is processed, any change in the data would not be reflected in the current execution. (I stand corrected, if I am wrong.)

Edited by: Purvesh K on Aug 30, 2012 6:07 PM
unknown-7404
>
And do not commit in a loop.
Commit only once, when your complete transaction is done.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4951966319022
>
Well - make up your mind. Should OP commit only once? Or when complete transaction is done? ;)

This code appears to implement a batch process where each step is independent of the other steps. As such each loop iteration does, in fact, represent a different transaction.

Each iteration of the loop is execting a (potentially different) function. Each function could very well be a 'complete transaction' that uses entirely different schemas, objects, tables, queries, etc.

If so then a commit after every function call IS a commit when the transaction is done. If this procedure is nothing more that a batch job that executes a series of independent steps then it is appropriate to issue a COMMIT after each step.

The AskTom link you cited applies to the traditional 'commit within a transaction' case. That, and similar cases, represent users doing frequent commits because they think they are saving resources or protecting the work already done even though there is still more work to do on those same objects.
Sven W.
user10442658 wrote:
Dear concerned,

I have one function in a package (databse 11g) with following code.
omg. this code is so full of bugs, I don't even know how to comment this garbage.
I assume it is not your code and you just have to maintain it. Poor guy. Is the other one already fired? I do hope so.

Well lets start with the most obvious problems.

1) Remove all COMMIT and ROLLBACK commands from it.
2) Remove the when others exception handler from it.

Tell us the result when running it again.
unknown-7404
>
This happens when execution takes a long duration.
>
As Purvesh already state a single execution of this one procedure cannot execute the same task twice unless that task is present twice in the initial SELECT that defines the cursor.

So another possibility is that the procedure is being called a second time while it is still executing the first time.

This can happen when a DBMS_JOB is scheduled to execute and calls the function or if the function is called a second time by another process or user.

There is nothing in your code that locks the task records being processed so a second function call might very well try to execute the same tasks.
Your cursor is
FOR L IN (SELECT * FROM TASK_LIST WHERE ISACTIVE = 1 )
But it doesn't have a FOR UPDATE clause to lock those records. So another query for tasks "WHERE ISACTIVE = 1" will get the same records.

In fact, I don't see anything in the code you posted that sets ISACTIVE to a value other than '1' but you likely have that somewhere in the procedure.
1 - 10

Post Details

Added on Oct 30 2020
13 comments
8,474 views