CODEWITHSUNDEEP
oracle-databaseplsql
bobothewise
bobothewise

Reputation: 45

Invalid Datatype when passing collection into function

CREATE OR REPLACE PACKAGE test_package
IS
  TYPE IDTable IS TABLE OF test_table.id%TYPE;

  TYPE RowIDTable IS TABLE OF VARCHAR2(500);

  FUNCTION delete_rows (row_ids IN RowIDTable) RETURN IDTable;
END test_package;
/

CREATE OR REPLACE PACKAGE BODY test_package
IS
  FUNCTION delete_rows (row_ids IN RowIDTable) RETURN IDTable
  IS
    ids IDTable;
  BEGIN
    DELETE FROM test_table
    WHERE ROWIDTONCHAR(rowid) IN (SELECT * FROM TABLE(row_ids))
    RETURNING id BULK COLLECT INTO ids;

    COMMIT;

    RETURN ids;
  END;
END test_package;
/

I keep getting ORA-00902: invalid datatype when using the above function. What am I doing wrong?

The error is pointing to the DELETE statement.

Upvotes: 2

Views: 1477

Answers (2)

Alex Poole
Alex Poole

Reputation: 191360

It looks like the problem is the type of collection you’re using. in that it’s a package-level PL/SQL collection type.

The ruby-plsql documentation says it supports PL/SQL records, but the table and varray types don’t refer to PL/SQL - suggesting they have to be SQL (i.e. schema-level) user-defined types.

Even without Ruby in the picture, the invalid-datatype error is thrown at runtime, when the same package-defined types are used consistently. Prior to 12c you couldn’t use a PL/SQL collection in a SQL statement at all, and it still doesn’t seem to work in this scenario.

Your code works if you change from the the PL/SQL type to a schema-level type, in this example a built-in varray type:

CREATE OR REPLACE PACKAGE test_package
IS
  TYPE IDTable IS TABLE OF test_table.id%TYPE;

  --TYPE RowIDTable IS TABLE OF VARCHAR2(500); —- not used now

  FUNCTION delete_rows (row_ids IN sys.odcivarchar2list) RETURN IDTable;
END test_package;
/

CREATE OR REPLACE PACKAGE BODY test_package
IS
  FUNCTION delete_rows (row_ids IN sys.odcivarchar2list) RETURN IDTable
  IS
    ids IDTable;
  BEGIN
    DELETE FROM test_table
    WHERE ROWIDTONCHAR(rowid) IN (SELECT * FROM TABLE(row_ids))
    RETURNING id BULK COLLECT INTO ids;

    COMMIT;

    RETURN ids;
  END;
END test_package;
/

which you can test with anonymous block:

declare
  ids test_package.idtable;
begin
  ids := test_package.delete_rows(sys.odcivarchar2list('ROWID1', 'ROWID2'));
end;
/

It would be more efficient to not convert every rowid in the table, so you can do this instead:

WHERE rowid IN (SELECT CHARTOROWID(column_value) FROM TABLE(row_ids))

but bear in mind that rowids are not always immutable - hopefully the references you are passing in are recent enough to always still be valid. Otherwise, work with primary keys rather than rowids.

I don’t know Ruby so not entirely sure how that translates; again from the docs it looks like:

plsql.test_package.delete_rows(['ROWID1','ROWID2'])

... but not sure how it’ll handle the returned (PL/SQL) table type. That may need to be a schema-level table type too. (And you can probably create types as either varrays or nested tables if you want to make your own, which is probably a good idea.)

Upvotes: 2

Alexander Gusev
Alexander Gusev

Reputation: 295

Why don't you use TABLE OF ROWID instead of TABLE OF VARCHAR2(500) on RowIDTable?

Like so:

TYPE RowIDTable IS TABLE OF ROWID;

Then you wouldn't have to cast ROWID:

DELETE FROM test_table tbl
WHERE tbl.rowid IN (SELECT column_value FROM TABLE(row_ids))
RETURNING id BULK COLLECT INTO ids;

Hope this helps.

Upvotes: 0

Related Questions