process on a dynamic based query to delete selected results

Question

I am having trouble deleting results generated from a dynamic query based report.

I have a static region where I have defined all of my page items that are used to filter/search. After submitting the values, a classic report of type "PL/SQL Function body returning SQL query" is generated matching the page item values.

Now I need to come up with a process that would delete all those results that got generated via the classic report. Note that all of the page items are optional, that's what makes it a little complicated, at least for me.

Here's what I have used to generate the classic report :

declare
q varchar2(4000):=NULL;
begin
q:='
SELECT * FROM test WHERE col1 IS NOT NULL AND ';

IF :P001_name IS NOT NULL THEN
q:=q||' name= :P001_name AND ';
END IF;

IF :P001_order_date IS NOT NULL THEN
q:=q||'order_date = :P001_order_date AND ';
END IF;
return q;
END;

I tried doing a delete using same where clause that I have used in the above query, that didn't help.

Any alternative methods/suggestions are welcomed. The idea is to drill down to some selected records and delete them after reviewing.

I'm using oracle apex 5.0.4. and Oracle 12C db

Answer 1

Your process will need to bind all the bind variables for the page items. As written, the number of bind variables in your query varies, which means you would need to use the DBMS_SQL package. However you could adjust it so that the number of bind variables is fixed and then you can use execute immediate:

declare
  q varchar2(4000):=NULL;
begin
  q:='DELETE FROM test WHERE col1 IS NOT NULL ';

  IF :P001_name IS NOT NULL THEN
    q:=q||' AND name= :P001_name ';
  ELSE 
    q:=q||' AND :P001_name is null ';
  END IF;

  IF :P001_order_date IS NOT NULL THEN
    q:=q||' AND order_date = :P001_order_date';
  ELSE 
    q:=q||' AND :P001_order_date is null';
  END IF;

  execute immediate q 
    using :P001_name, :P001_order_date;
END;