This content has been marked as final. Show 8 replies
Please read the FAQ and format your listing so it can be easily read and understood.
Is the procedure with the issue running with AUTHID DEFINER or CURRENT_USER?
Is the user executing the procedure gaining access via a role or an explicit grant of execute?
Have you commented out specific lines of your code to identify the line that is creating the error? If not do so before proceeding.
Perhaps then we can help you.
Thanks for the reply
The procedure runs with the CURRENT_USER
The error is at the line here
L_RESP := UTL_HTTP.GET_RESPONSE(L_REQ);
Up to this line the procedure runs fine when its called from cursor.
Try it as DEFINER and see if that addresses the issue.1 person found this helpful
I'm not suggesting you change it permanently ... just for debugging purposes.
I figured out what is causing the issue, but I am not sure how I can resolve it.
I am sending the Oracle Error message concatenated to a user defined error message. A sample error message which I sent is
Duplicate Record found in data. ORA-00001: unique constraint (ABCD.AK_ABCD_MANDS) violated
If I remove the 'ORA-00001: unique constraint (ABCD.AK_ABCD_MANDS) violated' part it works fine. I checked if its the length, but I can give big user defined errors but when I concatenate Oracle error message it creates issues. I am not sure if its the colon, or dot or braces which is causing an issue when its sent.
You can trap expected exceptions and use NULL to force your code to ignore them ... or test conditions prior to performing actions to make user those actions are appropriate ... or use DBMS_ERRLOG to catch constraint generated exceptions without causing a failure.1 person found this helpful
Georgey wrote:Don't do that. You are not covering all chars that need to be encoded (escaped). Use UTL_URL.Escape().
--Making sure than the spaces in URL are replaced with %20
L_REQ_STRING := REPLACE(L_REQ_STRING,' ','%20');
--Making sure that the URL string is less than 2000 characters
L_REQ_STRING := SUBSTR(L_REQ_STRING,1,1990);
-- Trim the end of the string if it has %20
L_REQ_STRING := RTRIM(L_REQ_STRING,'%20');
Thanks a lot for the answer.
There are demos of UTL_URL functionality in Morgan's Library: www.morganslibrary.org/library.html