Hello

This is the SQL code I'm working with:

SQL Query
CREATE TABLE TEST_TABLE
(
  COL_1 NUMBER(9),
  COL_2 NUMBER(3)
);

CREATE OR REPLACE PACKAGE TEST_PKG AS
  TYPE r_testrecord_type IS RECORD (ID_1 NUMBER(9), ID_2 NUMBER(3));
  TYPE t_testrecords_type IS TABLE OF r_testrecord_type INDEX BY PLS_INTEGER;
  PROCEDURE BULK_INSERT(in_t_testrecords in t_testrecords_type);
END TEST_PKG;
/

CREATE OR REPLACE PACKAGE BODY TEST_PKG AS
  PROCEDURE BULK_INSERT(in_t_testrecords in t_testrecords_type) IS
  BEGIN
    FORALL i IN in_t_testrecords.first .. in_t_testrecords.last
      INSERT INTO TEST_TABLE VALUES (in_t_testrecords(i).ID_1, in_t_testrecords(i).ID_2);
  END BULK_INSERT;
END TEST_PKG;
/

How do you call the stored procedure BULK_INSERT using TOracleQuery, and pass a PL/SQL table to it? I don't know what type to select in DeclareVariable, because my type is a PL/SQL record. I also tried the Package Wizard but it crashes on this package.
A PL/SQL Table of records cannot be passed from the client to the server. You can only pass PL/SQL Tables of scalar data types. There are 2 options:

  • Modify the package procedure so that it only uses parameters of a PL/SQL Table of a scalar data type, or
  • Pass PL/SQL Tables of scalar data types to a PL/SQL Block, which then converts it to a PL/SQL Table of records and then calls the package procedure.
© Allround Automations forums