Oracle pl/sql Array-CodePudding

let's see if somebody can help me, I need to delete rows from different tables and I did think to do it using an array so i wrote this :

DECLARE
  TYPE mytype_a IS TABLE OF VARCHAR2(32) INDEX BY BINARY_INTEGER;
  mytype mytype_a;
BEGIN
  mytype(mytype.count   1) := 'MYTABLE';

  FOR i IN 1 .. mytype.count 
    LOOP
      DELETE mytype(i) WHERE valid = 'N';
    END LOOP;
END;

Trying to run this piece of code using sqldeveloper I get the ORA-00933 command not properly ended, if I put directly the table name it works, what am I doing wrong?

Thank you.

Thank you very much guys, it works perfectly.

CodePudding user response：

This is not the correct approach. You have to use Dynamic SQL for this -

DECLARE
type mytype_a is table of varchar2(32) index by binary_integer;
mytype mytype_a;
stmt varchar(500) := NULL;

BEGIN

mytype (mytype.count   1) := 'MYTABLE';

for i in 1..mytype.count loop
    stmt := 'DELETE  FROM ' || mytype(i) || ' where valid =''N''';
    EXECUTE IMMEDIATE stmt;
    end loop;
END;

CodePudding user response：

You would need to use dynamic SQL, concatenating the table name from the collection into the statement, inside your loop:

execute immediate 'DELETE FROM ' || mytype(i) || ' where valid = ''N''';

Or you can put the statement into a variable so you can display it for debugging purposes, and then execute that, optionally with a bind variable for the valid value:

stmt := 'DELETE FROM ' || mytype(i) || ' where valid = :valid';
dbms_output.put_line(stmt);
execute immediate stmt using 'N';
dbms_output.put_line('Deleted ' || sql%rowcount || ' row(s)');

... which I've made also display how many rows were deleted from each table. Note though that you shoudln't rely on the caller being able to see anything printed with dbms_output - it's up to the client whether it shows it.

The whole anonymous block would then be:

DECLARE
  type mytype_a is table of varchar2(32) index by binary_integer;
  mytype mytype_a;
  stmt varchar2(4000);
BEGIN
  mytype (mytype.count   1) := 'MYTABLE';

  for i in 1..mytype.count loop
    stmt := 'DELETE FROM ' || mytype(i) || ' where valid = :valid';
    dbms_output.put_line(stmt);
    execute immediate stmt using 'N';
    dbms_output.put_line('Deleted ' || sql%rowcount || ' row(s)');
  end loop;
END;
/

You could use a built-in collection type to simplify it even further.

db<>fiddle showing some options.

Hopefully this doesn't apply, but if you might have any tables with quoted identifiers then you would need to add quotes in the dynamic statement, e.g.:

stmt := 'DELETE FROM "' || mytype(i) || '" where valid = :valid';

... and make sure the table name values in your collection exactly match the names as they appear in the data dictionary (user_tables.table_name).