Skip to Main Content
Forums

ORDS, SODA & JSON in the Database

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!

UTL_HTTP - ORA-29024 Certificate validation failure

Shra1Jan 12 2015 — edited Jun 10 2015

I'm having issues with Oracle Certificate validation failure. while using UTL_HTTP calls. I've set the Oracle wallet path correctly and downloaded and registered the server certificates in Oracle Wallet. Tried similar https calls to other services. they're good. Can anyone throw ideas as to potential errors in the code/calls below?

CREATE OR REPLACE PROCEDURE show_html_from_url (p_url  IN  VARCHAR2 DEFAULT NULL) AS

  l_http_request   UTL_HTTP.req;

  l_http_response  UTL_HTTP.resp;

  l_text           VARCHAR2(32767);

  l_default_url   VARCHAR2(300) := 'https://gb.redhat.com/';

  l_url       VARCHAR2(600) := 'https://global.blyeplod.net/blyapi/Contract/ProjectSetup';

  l_un VARCHAR2(256) := 'username'; --sending correct username

  l_pwd VARCHAR2(32) := 'password'; --sending correct password

  l_api_key VARCHAR2(256) := '***';

  lv_wallet_path  VARCHAR2(300) := '/u01/app/oracle/product/11.1.0/db_1/wallet';

  lv_request_body VARCHAR2(4000) :=

  '{"Projects":{"Project":{"ProjectNumber":"110229","ProjectFriendlyName":"110229 - Test Project","ProjectRegion":"NAM"}}}';

BEGIN

  if p_url is not null then

     l_url := p_url;

  end if;

  -- Make a HTTP request and get the response.

  UTL_HTTP.set_wallet('file:'||lv_wallet_path);

--//This step raises the ORA-29024: Certificate validation failure

  l_http_request  := UTL_HTTP.begin_request(l_url,'POST' --//Method

  ,'HTTP/1.1');

  UTL_HTTP.set_authentication(l_http_request,l_un,l_pwd);

  UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml charset=UTF-8');

  UTL_HTTP.set_header(l_http_request, 'Content-Length', length(lv_request_body));

  UTL_HTTP.write_text(l_http_request, lv_request_body);

  --//ping and check response

  l_http_response := UTL_HTTP.get_response(l_http_request);

  -- 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);

   UTL_TCP.close_all_connections; 

  END;

EXCEPTION

  WHEN OTHERS THEN

dbms_output.put_line('***HTTP Test Raised error: '||sqlerrm);

    UTL_HTTP.end_response(l_http_response);

  UTL_TCP.close_all_connections;

    RAISE;

END show_html_from_url;

/

SQL> exec show_html_from_url

***HTTP Test Raised error: ORA-29273: HTTP request failed

ORA-06512: at

"SYS.UTL_HTTP", line 1029

ORA-29024: Certificate validation failure

BEGIN show_html_from_url; END;

ERROR at line 1:

ORA-29273: HTTP request failed

ORA-06512: at "SYS.UTL_HTTP", line 1389

ORA-29261: bad argument

ORA-06512: at "PWB.SHOW_HTML_FROM_URL", line 41

ORA-29273: HTTP request failed

ORA-06512: at "SYS.UTL_HTTP", line 1029

ORA-29024: Certificate validation failure

ORA-06512: at line 1

Comments

fac586
Answer 
eaolson1 wrote:
I've just finished debugging a strange problem in a page process that was giving incorrect results because a page item had the wrong value. We discovered this page item value was getting set as soon as the user's session was created and even before he loaded that page. The V() function would return a value for this item even when there was no value in WWV_FLOW_DATA.
Eventually, I turned on session tracing and found this when V('P1_ITEM') was being called:
SELECT ATTRIBUTE_VALUE FROM WWV_FLOW_PREFERENCES$ WHERE USER_ID=:B3 AND SECURITY_GROUP_ID = :B2 AND PREFERENCE_NAME = 'PERSISTENT_ITEM_'||:B1
There was indeed a value in WWV_FLOW_PREFERENCES$ for PERSISTENT_ITEM_P1_ITEM. We don't really use preferences except for the usual built in report sorting and this "persistent_item" type of preference is new to me. Even when I deliberately set a preference (apex_util.set_preference), it does not add this prefix.
I can't find anything in the documentation on this. Does anyone know what this might be?
This is on Apex 5.1.2.

This is how values are persistently stored for page items where the Maintain Session State property is set to Per User.

This may have been set in error as it is an uncommon but occasionally useful feature.

Marked as Answer by Eric Olson 1 · Sep 27 2020
Eric Olson 1

That must have been what happened. It appears in 5.2 if the item is changed back to Per Session, the preferences remain, and the V() function can retrieve the preference rather than the page item value. As far as I can tell, this has been fixed in 18.2, at least in testing on apex.oracle.com.

1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 8 2015
Added on Jan 12 2015
4 comments
22,174 views