Print Thread
How to work with PL/SQL table of record type?
#62724 10/19/21 03:00 PM
Joined: Feb 2018
Posts: 16
M
Member
OP Offline
Member
M
Joined: Feb 2018
Posts: 16
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.

Re: How to work with PL/SQL table of record type?
Matt Ors #62725 10/20/21 08:29 AM
Joined: Aug 1999
Posts: 22,203
Member
Offline
Member
Joined: Aug 1999
Posts: 22,203
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.


Marco Kalter
Allround Automations

Moderated by  support 

Link Copied to Clipboard
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Release build 20200307)
Responsive Width:

PHP: 7.1.33 Page Time: 0.037s Queries: 15 (0.011s) Memory: 2.4991 MB (Peak: 3.0380 MB) Data Comp: Off Server Time: 2024-04-24 12:23:18 UTC
Valid HTML 5 and Valid CSS