Forum Stats

  • 3,826,047 Users
  • 2,260,590 Discussions
  • 7,896,779 Comments

Discussions

Need some help on a weird intermittent error with Rest Service calls

Scott H.
Scott H. Member Posts: 323 Bronze Badge
edited May 26, 2022 1:46PM in APEX Discussions

Hi everyone.. I really need some help here as I'm stuck between a rock and a hard place.

Running on-prem 12cR2 MS Windows 2016 64-bt

Apex Version 21.2.5.

We have integrated with Oracle Content and Experience Cloud for data management.

So, here's the issue.. due to some control issues with the embedded UI, I'm using their Rest API to get the contents of the folders. My records are associated to a folder, and I run a script to get all the data. If I detect a folder in my list, i recursively run the same script to get that folders details and so on until I get all the folder and subfolder contents.

I have an interactive grid that calls my procedure, and then returns the sql using lazy loading to make the page speed up (sometimes these docs can take a bit to process with lots of subfolders)

Due to their authentication, I do not believe I'm able to use the REST enabled SQL in Apex to get this data... but here's my issue

It works fine for hours, days, weeks, and then this will start happening. All of a sudden my users will get this error



I am able to reproduce it when it happens, but if I run my procedure from SQL Developer, I cannot recreate it.. i have logging procedures in my code to detect ORA-errors and they do not, so this seems to be an error with Apex calling it

here is my debug for the page

Exception in "Home Folder":
Error Stack: ORA-29262: bad URL
ORA-06512: at "APEX_210200.WWV_FLOW_EXEC", line 3150
ORA-06512: at "APEX_210200.WWV_FLOW_DYNAMIC_EXEC", line 1280
ORA-06512: at "SYS.UTL_URL", line 28
ORA-06512: at "SYS.UTL_URL", line 170
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQL", line 1726
ORA-06512: at "APEX_210200.WWV_FLOW_DYNAMIC_EXEC", line 1274
ORA-06512: at "APEX_210200.WWV_FLOW_EXEC_LOCAL", line 449
ORA-06512: at "APEX_210200.WWV_FLOW_EXEC_LOCAL", line 694
ORA-06512: at "APEX_210200.WWV_FLOW_EXEC_LOCAL", line 836
ORA-06512: at "APEX_210200.WWV_FLOW_EXEC_LOCAL", line 882
ORA-06512: at "APEX_210200.WWV_FLOW_EXEC_LOCAL", line 1459
ORA-06512: at "APEX_210200.WWV_FLOW_EXEC", line 3141
ORA-06512: at "APEX_210200.WWV_FLOW_EXEC", line 3249
Backtrace: ORA-06512: at "APEX_210200.WWV_FLOW_EXEC", line 3150
ORA-06512: at "APEX_210200.WWV_FLOW_DYNAMIC_EXEC", line 1280
ORA-06512: at "SYS.UTL_URL", line 28
ORA-06512: at "SYS.UTL_URL", line 170
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQL", line 1726
ORA-06512: at "APEX_210200.WWV_FLOW_DYNAMIC_EXEC", line 1274
ORA-06512: at "APEX_210200.WWV_FLOW_EXEC_LOCAL", line 449
ORA-06512: at "APEX_210200.WWV_FLOW_EXEC_LOCAL", line 694
ORA-06512: at "APEX_210200.WWV_FLOW_EXEC_LOCAL", line 836
ORA-06512: at "APEX_210200.WWV_FLOW_EXEC_LOCAL", line 882
ORA-06512: at "APEX_210200.WWV_FLOW_EXEC_LOCAL", line 1459
ORA-06512: at "APEX_210200.WWV_FLOW_EXEC", line 3141
ORA-06512: at "APEX_210200.WWV_FLOW_EXEC", line 3249
ORA-06512: at "APEX_210200.WWV_FLOW_INTERACTIVE_GRID", line 4037

I have tried moving the procedure to a pre-load proc and running the grid normally and when this starts happening, it happens there too.

the only way I seem to be able to clear it is to drop my "temp" holding table or delete the records from the table,.. or sometimes, it just starts working on it's own.

The URL doesn't change. Really could use some guidance here.


If need be, I can put up a demo at apex.oracle.com.. but not sure it'll matter there.


Would upgrading to 22.1 be a viable solution?


Thanks,

Scott

Tagged:

Answers

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Consulting Member of technical Staff Munich, GermanyMember Posts: 1,329 Employee

    Hi Scott,

    hmmm… the ORA-29262 error only says that there was a bad URL passed to one of the built-in packaged.

    ORA-29262: bad URL

    Cause: An improperly formed URL was passed to the PL/SQL API.

    Action: Check the URL and retry the call

    Also …

    I have an interactive grid that calls my procedure

    can you provide more information about what that means? Are you calling the UTL_URL package directly from your code? Which concrete statement of your code receives that error? Can you share your code, so that we get a better picture about what your code does?

    regards

    Carsten

  • Scott H.
    Scott H. Member Posts: 323 Bronze Badge

    HI Carsten... after a few days of looking at this, I think the rest call was red herring...


    in my sql, i realized a call to utl_url.unescape which we removed, and are hoping that will solve the problem.

    The bad url made me think immediately to the API call which caused my confusion.

    Thanks for your help though,

    Scott