CODEWITHSUNDEEP
restoracle-apex-20.1oracle-rest-data-services
Gerry
Gerry

Reputation: 207

Oracle Apex 20 - REST source action on column in interactive report

I am trying to setup a form with table report and use REST sources only.

Currently I have a Form and Interactive report setup. It uses a rest source with dynamic items. i.e:

http://source/asm/instances/:group

where :group is then searchable from the form.

Now the columns returned are all user related, but one item I want to be able to set via the REST API. So assuming I entered a group and get the following results:

group | name | surname | status
ABC   | Dan  | Murray  | inactive
ABC   | Mary | Swanson | active

The REST Source has a function to activate/deactivate the status remotely on the tool. i.e

http://source/asm/instances/ABC/dan/activate

and

http://source/asm/instances/ABC/dan/deactivate

Now I need to modify the status column to be in a list form with two options, activate/deactivate. This is where the issue comes in. When either of the two items are selected, it needs to use the relevant REST source as posted above and post to the tool, meaning it will activate/deactivate on the fly. I have spent the entire weekend searching and trying and I just cannot find a way to do this.

The closest I got so far was to create a link, but that just routes me to the URL which is not what I need.

I really hope someone is able to help me here as I am struggling big time.

Upvotes: 0

Views: 1672

Answers (1)

davidm
davidm

Reputation: 1760

Maybe I solved your problem, give it a try. :)

Version

Oracle Application Express 20.2.0.00.20

Solution

I suppose you know how to set the REST Data Source (DS) and create an Interactive report with the data fetched from de DS, so I will skip those steps.

  1. Go to your Interactive report definition and under settings find Local Post Processing and set the values:
  • Type: SQL Query
  • SQL Query:
select 
    /*leave all the columns fetched from the DS*/
    --
    -- Add this column 
    '<a class="rest-button-status" data-id="'||<PRIMARY-KEY>||'">Link</a>' BUTTON
  from #APEX$SOURCE_DATA#

  1. Go to the BUTTON column definition, under Security disable Escape special characters.

  2. Add a new hidden item and call it P1_NEW.

  3. Run the application and you should see your Interactive Report with a link in the last column.

  4. Now add an Dynamic action defined as (name it however you want):

enter image description here

  1. To this DA add two True actions:
  • First - Set Value

enter image description here

// copy paste code
$(this.triggeringElement).attr('data-id')
  • Second - Execute Server-side Code (PL/SQL)

EDIT Page Items to submit - P1_NEW


-- code
DECLARE
    l_clob  CLOB;
    -- I added a parameter PK
    v_url VARCHAR2(2000) := 'http://xxxxx:8080/ords/xxxx/xxxxxx/test-apex/deactivate?PK=';
BEGIN
    L_CLOB := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
        -- I added a parameter PK
        p_url                  => v_url||:P1_NEW,
        p_http_method          => 'POST' -- or GET or other
    );
END;

The toggling system (activate/deactivate) is up to you, maybe the best solution is to have one endpoint and then the DS controls the status.

EDIT - based on comments Here are some screenshot of the second DA:

  1. Add another action to the DA.

enter image description here

  1. Define an Execute Server-side Code or Execute PL/SQL Code (the name of the action depends on the Oracle APEX version you have)

enter image description here

Upvotes: 0

Related Questions