CODEWITHSUNDEEP
oracle-apex
muhammad shoaib
muhammad shoaib

Reputation: 25

I have created a interactive report. I would like to execute a PL/SQL based on a column link available per record

I have created a interactive report. I would like to execute a PL/SQL based on a column link available per record. For example, the report has flight_ID and departure_date columns. When user clicks the link present on that record, using PL/SQL I would like to update/insert into another table with the details available in present record under above 2 mentioned columns. How can I achieve this? Please help. i have a plsql block.

BEGIN
   INSERT INTO flight_seats
      WITH seats AS
       (SELECT rownum r FROM dual CONNECT BY LEVEL <= 350)
      SELECT flight_shedule.flight_id,
             flight_shedule.departure_date,
             r seat,
             CASE
                WHEN r BETWEEN start_a AND end_a THEN
                 'A'
                WHEN r BETWEEN start_b AND end_b THEN
                 'B'
                WHEN r BETWEEN start_c AND end_c THEN
                 'C'
             END CLASS,
             900 fare,
             'o' status
        FROM seats, airplane, flight_shedule
       WHERE flight_shedule.airplane_id = airplane.airplane_id;
END;

how can i do that in oracle apex.

Upvotes: 2

Views: 2718

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132580

You can define the link using a URL like this:

apex.submit('INSERTSEATS_#FLIGHT_ID#_#DEPARTURE_DATE_STR#');

#FLIGHT_ID# and #DEPARTURE_DATE_STR# reference column aliases from the report. Column DEPARTURE_DATE_STR is the departure date formatted in some specific way e.g. YYYYMMDD.

This will submit the page with a request value something like "INSERTSEATS_123_20151231".

You can then create an on-submit page process that fires when :request like 'INSERTSEATS%' (PL/SQL condition).

The first thing this process needs to do is parse the request value to find out the flight ID and departure date. You can use functionapex_util.string_to_table(:request,'_') to split the request string into an array of 3 elements, i.e. the string 'INSERTSEATS', the flight ID e.g. '123' and the departure date e.g. '20151231'.

FInally you can use the flight ID and departure date values obtained in your insert statement (converting the date string back into a date value first).

Update

@Drumbeg has pointed out in a comment that you can also set page items via apex.submit. So your link could be defined like:

apex.submit
  ({request:"INSERTSEATS", 
    set:{"P1_FLIGHT_ID":#FLIGHT_ID#, 
         "P1_DEPARTURE_DATE_STR":#DEPARTURE_DATE_STR#}
  });

Your on-submit process would then have the condition Request=INSERTSEATS, and would use the values of the page items in the insert statement.

Upvotes: 2

Drumbeg
Drumbeg

Reputation: 1934

Here is an asynchronous approach that I have used in the past.

Firstly, you will need some supporting page items for variables.

  • PXXX_DEPARTURE_DATE
  • PXXX_FLIGHT_ID

Now change your column link so that it triggers a custom event and passes required report row data.

javascript:$('body').trigger('INSERT_SEATS', {flightId:"#FLIGHT_ID#", departureDate:"#DEPARTURE_DATE_STR#"});

Now create a dynamic action based on a custom event.

  • Event : Custom
  • Custom Event : INSERT_SEATS
  • Selection Type : jQuery Selector
  • jQuery Selector : body

This dynamic action should have two TRUE actions:

  • JavaScript Action (used to set temporary page items with data from trigger, note that this refers to the event itself. The data is contained within the event).

    $s('PXXX_FLIGHT_ID', this.data.flightId);
$s('PXXX_DEPARTURE_DATE', this.data.departureDate);

  • PL/SQL action (reference page items and perform your DML).

  • Alert (optional, but I usually alert the user that the action has completed).

You don't have to attach the custom event to the body. It can be attached to any DOM node on the page. So, for example, if your report region had a static ID of flightReport, you could do $('#flightReport').trigger('INSERT_SEATS', .....)

Upvotes: 0

Related Questions