How to work with PL/SQL table of record type? - 10/19/21 03:00 PM
Hello
This is the SQL code I'm working with:
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.
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.