I'm really struggling with something and when I've searched for solutions, they all seem to point to issues with date fields, but my request doesn't have a date field. I also tried to replicate what I'm doing in a workspace on apex.oracle.com and of course the issue didn't occur.
I have a page with a form and in the sub region, I added an interactive grid selecting data from the database that is related to the parent item in the form region. In good troubleshoot fashion, I stripped this back as much as possible and edited my database table so that it allows nulls in every field except the PK. The SQL for the Interactive Grid is:
select rlr.folder_name, rlr.ROWID
from recording_location_r rlr where rlr.recording_id = to_number(:P102_RECORDING_ID_IN) and rlr.active_ind = 1
I load the page and it looks great. I select Add Row to the Interactive Grid and type a new Folder Name, when I then click Save within the Interactive Grid, I get the ORA-01858 error. In the debug, I see this:
My recording_location_r table is set with folder_name to be varchar2(255):
If I take that exact select/ insert, drop it into SQL Developer and change :APEX$VAL2 to ‘test’,and :P102_RECORDING_ID_IN to something real, the insert works just fine (my recording_location_r_id field is setup with a trigger and sequence).
I even hard coded a RECORDING_ID in my SQL to test, but I get the same error (the debug shows this):
How can I see the actual SQL that was executed? I thought it would be in this debug window, but I can't see what happens after the bind steps. I'm using APEX 22.2.4, and an Autonomous Database - both in OCI. When I look on cloud.oracle.com at the database performance hub > SQL Monitoring, it says 'No monitored SQLs found'… How can I set an APEX SQL to be monitored?
I've been racking my brain with this for a few hours and am hoping for a lifeline here. Thanks!