Array of all values from Table where changes occured (Oracle Apex)

I have a table with values which is being collected from an external procedure.

  Name | State
  Dan  | Active
  Stan | Active
  Maggy| Inactive

On Apex I then have a Tabular form with select list from another Table which allows the user to update the State field. this table is as follows:

  STATE_ID  | STATE_NAME
  Activate  | Active
  Deactivate| Inactive

with a PL/SQL query in the LOV to call this field. It will then toggle the options and is displayed as:

  Name | State    |Set_state
  Dan  | Active   | Activate / Deactivate (in dropdown LOV)
  Stan | Active   | Activate / Deactivate (in dropdown LOV)
  Maggy| Inactive | Activate / Deactivate (in dropdown LOV)

This means the user can decide that Dan should be inactive and then select Deactivate from the dropdown list etc. for all the relevant names.

Now the problem I am having is the procedure from there is sent externally to a Perl script to go and do various things outside of Oracle. I therefore need to create a process that will take all the names and 'Set_State' and send it in array or list form to external script as array or to a file.

This is what I initially done, but obviously not working as :P1_NAME and :SET_STATE are the names of the columns and not the actual values. Hope I am making sense here :)

  DECLARE
  set_state VARCHAR2(20);
  name VARCHAR2(20);

  BEGIN

  name := :P1_NAME;
  set_state := :SET_STATE;

  mytask := 'Change_state';
  PROC(name, set_state);                < This sends Name, Set_state to below procdure
  END;

Please can someone help me to get the last query to contain all the values from P1_NAME and SET_STATE column when the Submit button is clicked and not just the name of the Columns. Either in array or list form.

So once I changed STAN to Inactive and Maggy to Active and button is clicked, it should send: Stan Inactive Maggy Active

to External Procedure.

This is the procedure that calls the Perl script. Not really relevant to the quesiotn, but pasting anyway.

  create or replace PROCEDURE "PROC" (name IN VARCHAR2, set_state IN VARCHAR2) IS

  BEGIN

  DECLARE

  BEGIN
  mytask := 'PROC';
  myscriptname := 'Script';
  dbms_scheduler.create_job(
  job_name      => myscriptname
  , job_type      => 'EXECUTABLE'
  , job_action    => '/app/proc.pl'
  , number_of_arguments => 2
  , enabled => FALSE);
  dbms_scheduler.set_job_argument_value(myscriptname,1,name);
  dbms_scheduler.set_job_argument_value(myscriptname,2,set_state);
  dbms_scheduler.enable(myscriptname);
  end;
  END;

Solution

With a standard tabular form (updateable SQL Query) - NOT a classic report with apex.item API calls.

I'm making some assumptions about your setup because it is not 100% clear. If it doesn't match all the way it shouldn't matter too much - treat it as an example.

Query used:

SELECT name, state_id 
FROM states

Whereby:

column NAME: text field, or display only with save state enabled
column STATE_ID: select list, and the LOV you've used

Create an on-submit process, after validations. Make sure to associate the process with the tabular form. Set the scope to 'all created and modified rows'.
Doing this will allow you to write PLSQL which will execute in the scope of each eligible row in the tabular form. You can use bind variable syntax to refer to columns which submit their state (eg text fields, hidden items, select lists, checkboxes).
Note that "all rows" effectively means "those rows in the current pagination which has been submitted".

In this case, I can use below PLSQL to execute the process for each row which has been changed.

BEGIN
  PROC(name => :NAME, set_state => :STATE_ID);
END;

You can refer to the actual column names (headers) in bind variable syntax. Key point being that the process has to be associated with the tabular form!

(if you don't have an actual "real" tabular form, you should point this out, and this is why I asked for the source SQL. This info matters a lot more than a lot of the other info to be found in the post!)