Problem with APEX 22.1 and REST API PUT/POST

Stephane Baribeau

hello everyone,

I need your advice / help with using APEX and REST API PUT / POST on an AutoREST table.

Currenty this is what i've done

1. In Shared Component -> REST Data Source i've created a DATA source based on the AutoREST Table. I've 3 REST source operation (GET, POST and PUT)
2. In the PUT, i have :id in the URL pattern, in the request body template i've clicked autogenerate
3. in the Database Operation i've set as Update Row

In the Operation parameter, i have set like this

and inside the save process i have this

I've see that the apex_exec.execute_web_source is deprecated, What else i can use?

So, after all that, when i click on save button and the process do his job, in the APEX Debug i've see this error

If i test with Postman, it's working really good!

But inside APEX, i can't POST or PUT without error, but GET is working great!

and if i look on the data mapping every fields are empty

Any clues about whats going on?

Thanks for your help

ilmarsk

Hi Stephane!

Few ideas.

1. Instead of apex_exec.execute_web_source use apex_exec.execute_rest_source;
2. The error might indicate that your body request is missing or malformed. Inspect the debug line that shows URL request submitted.
3. As a test1 - hard code request body for post operation with valid json string.
4. As a test2 - hardcode parameter values and make sure the date source resolves them into proper json string in the body.
5. Make sure that you reference operational request body parameters in Request Body Template. ( i just posted a related question, but it has scr print of var reference.

https://community.oracle.com/tech/developers/discussion/4503796/can-apex-exec-execute-rest-source-read-use-clob-parameter

Stephane Baribeau

Hi, first of all, thanks for your answer,

After i've switch to EXECUTE_REST_SOURCE, i've got a kind new error, or it's an error i've not seen before

- user=APEX_220100, type=OPEN-RECURSIVE, sql_id=g0w799s31xq86, sql_text=begin :1 := dbms_network_acl_admin.get_wallet_aclid(:2); end

And for your question #2, here is what i see in the DEBUG

making PUT request to https://u2.economie.gouv.qc.ca/u/otn/otnprj_proj_colab_trnfo_num/33, using request body: {
  "dt_deb":#DT_DEB#, "dt_fin":#DT_FIN#, "dth_maj":#DTH_MAJ#, "nom_proj":#NOM_PROJ#, "dth_creat":#DTH_CREAT#, "id_util_maj":#ID_UTIL_MAJ#, "id_doss_clien":#ID_DOSS_CLIEN#, "id_util_creat":#ID_UTIL_CREAT#
}

I will now test with default value to see if i get another error.

Thanks again!

Stephane Baribeau

https://community.oracle.com/tech/developers/discussion/comment/16848068#Comment_16848068

After some tests with default value, i get the same error. Does it possible it's because we use ORDS 21.1.4? or is it possible because we use default submit when button pressed?

Thanks for your help.

Stephane Baribeau

After some tests, i've found the problem, but can't understand why.

this is the JSON that the execute_rest_source

making PUT request to https://myhost/test/project/webservice/33, using request body: {"dt_deb":2022-09-22T19:25:24,"dt_fin":2022-09-22T19:25:24,"dth_maj":2022-01-01,"nom_proj":test st552,"dth_creat":2022-01-01,"id_util_maj":2211,"id_doss_clien":CLI20222,"id_util_creat":2211}

But i get error 500 now with a null value

i've done some change in the JSON with " " and it's working

{

"id_proj_colab_trnfo_num":31,

"dt_deb":"2022-09-22T19:25:24Z",

"dt_fin":null,

"dth_maj":null,

"nom_proj":"test st552",

"dth_creat":null,

"id_util_maj":"2211",

"id_doss_clien":"CLI2022",

"id_util_creat":"2211"

}

Is it possible to add " " with value? I don't find the information inside the APEX Doc.

Thanks for your precious help.

ilmarsk

Hi Stephane,

Glad to see you are making progress! If I understand you correctly the problem you have/had is with the request body. Mainly, you are trying to figure out how to add quotes.

1) I would play around with the template and see what can be done - adding quotes, single double etc. Unfortunately, I am still figuring out myself this functionality, so no much help.

2) If your request is guaranteed to be under 4000chars you can use sql and JSON_OBJECT functionality to create the body and pass it via one variable.

Thank you.

Stephane Baribeau

https://community.oracle.com/tech/developers/discussion/comment/16848188#Comment_16848188

Hi, I was able now to use " ".. when we define the REST Data source and create the json, i've forget to use the " " for each field, now it's working.

But, i've facing another problem! lol Sorry! :) When we have a null value, he add " " on each side of the null value! this field is an optional form entry and if user don't pu content on it, i must return null to the database.

here is the request body template

{"dt_deb":"#DT_DEB#","dt_fin":"#DT_FIN#","nom_proj":"#NOM_PROJ#","id_doss_clien":"#ID_DOSS_CLIEN#"}

the DT_FIN can be null.

If you have any advice, i'm in! :)

Thanks for the precious time you take!