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

Question

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;

Can you please edit the question and highlight what exactly it is you need help with? — simbabque
Hi. In short, I need a solution in the last query that gives me all Values in the P1_NAME and SET_STATE column. Will edit question with that now. — user2082599
What do you use to run the Oracle stuff, and how do you pass it to your Perl program? Can you influence what the program expects? Better yet, can you show an minimal reproducible example of that program as well as how it's invoked? — simbabque
@simbabque the last Process in the question shows the current code. The perl script is purely expecting: "Name New_state" for each entry that has changed. So if I have 300 names and change the Set_state for 200 of them, it should send a list or array of 200 to the perl script — user2082599
also posted the actual Procedure that calls the perls script. this should only be called once. This means that when I edited 200 entries, it will send a list to the job and in turn will be passed to perl in array or list form. — user2082599

Tom Tom · Accepted Answer · 2016-09-26T19:43:38

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!)

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

1 Answers