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!
I have created a REST Data Source of Type "Oracle Cloud Applications (SaaS) REST Service" and let Apex create the Data Profile/schema automatically. How can I create a derived field in the Data Profile that extracts a substring of a value that's contained in an array? The value I need is '1001': (full JSON available here: https://pastebin.com/gYkn43Vm) I can extract the full link by creating a derived field of data type "JSON Document" and the path links[0].href, but where do I go from there? If I try to create a second derived field to extract the desired substring from the from the first derived field, I always get met with Invalid SQL Expression: ORA-00902: invalid datatype. The SQL expression I'd like to use is replace(regexp_substr(links2, '/\d+/'), '/', ''), but even dump(first_derived_field) does not work, so I can't even diagnose anything (trying cast(first_derived_field as varchar2), json_serialize(first_derived_field) etc don't work either, same error). My derived field: When trying to create a derived field based on the previous one: Nothing seems to work: I'm using Apex 21.1.7.