Home » SQL & PL/SQL » SQL & PL/SQL » Using associated array as SP parameter (12.2)
icon5.gif  Using associated array as SP parameter [message #680562] Thu, 21 May 2020 20:44 Go to next message
thegerman
Messages: 5
Registered: March 2020
Location: Ohio
Junior Member
Hey all, back with another dummy question.

I'm trying to make a SP in package that can accept a collection as a parameter. In this case an associated array but I get an ORA-00902.

Here is my test code I am playing with, I am not sure why I get an invalid datatype error when passing in the array.

CREATE TABLE TEST_TABLE
            ( 
              ID_COL      NUMBER
            ) 
;

INSERT INTO TEST_TABLE (SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=10);
COMMIT;



CREATE OR REPLACE EDITIONABLE PACKAGE PKG_TEST IS

  TYPE T_IDCOL IS TABLE OF TEST_TABLE.ID_COL%TYPE INDEX BY PLS_INTEGER;
  
  PROCEDURE PRC_DELETE_BY_T_IDCOL(vDATE DATE, tCOLIDS T_IDCOL);
  
  PROCEDURE PRC_DELETE_IDS(vDATE DATE);

END PKG_TEST;
/

CREATE OR REPLACE EDITIONABLE PACKAGE BODY PKG_TEST IS

    PROCEDURE PRC_DELETE_BY_T_IDCOL(vDATE DATE,  tCOLIDS T_IDCOL) AS  
    BEGIN
    
        DELETE FROM TEST_TABLE
        WHERE ID_COL IN (SELECT ID_COL FROM TABLE(tCOLIDS));
        COMMIT;
        
    END PRC_DELETE_BY_T_IDCOL;


  PROCEDURE PRC_DELETE_IDS(vDATE DATE) AS
  
    t_DEL_IDS T_IDCOL;
    
  BEGIN
  
    SELECT ID_COL BULK COLLECT INTO t_DEL_IDS 
    FROM TEST_TABLE 
    WHERE ID_COL < 5;
    
    PRC_DELETE_BY_T_IDCOL(vDATE, t_DEL_IDS);
    
  END PRC_DELETE_IDS;

END PKG_TEST;

/


BEGIN
  PKG_TEST.PRC_DELETE_IDS(TRUNC(SYSDATE));
END;

[Updated on: Thu, 21 May 2020 23:03]

Report message to a moderator

Re: Using associated array as SP parameter [message #680563 is a reply to message #680562] Fri, 22 May 2020 00:26 Go to previous messageGo to next message
Michel Cadot
Messages: 67540
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can use a SQL type instead of a PL/SQL one, for instance:
SQL> CREATE TABLE TEST_TABLE
  2              (
  3                ID_COL      NUMBER
  4              )
  5  ;

Table created.

SQL> INSERT INTO TEST_TABLE (SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=10);

10 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE OR REPLACE PACKAGE PKG_TEST IS
  2  --  TYPE T_IDCOL IS TABLE OF TEST_TABLE.ID_COL%TYPE INDEX BY PLS_INTEGER;
  3    PROCEDURE PRC_DELETE_BY_T_IDCOL(vDATE DATE, tCOLIDS sys.odcinumberlist);
  4    PROCEDURE PRC_DELETE_IDS(vDATE DATE);
  5  END PKG_TEST;
  6  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY PKG_TEST IS
  2
  3    PROCEDURE PRC_DELETE_BY_T_IDCOL(vDATE DATE, tCOLIDS sys.odcinumberlist) AS
  4    BEGIN
  5      DELETE FROM TEST_TABLE
  6      WHERE ID_COL IN (SELECT ID_COL FROM TABLE(tCOLIDS));
  7      COMMIT;
  8    END PRC_DELETE_BY_T_IDCOL;
  9
 10    PROCEDURE PRC_DELETE_IDS(vDATE DATE) AS
 11      t_DEL_IDS sys.odcinumberlist;
 12    BEGIN
 13      SELECT ID_COL BULK COLLECT INTO t_DEL_IDS
 14      FROM TEST_TABLE
 15      WHERE ID_COL < 5;
 16      PRC_DELETE_BY_T_IDCOL(vDATE, t_DEL_IDS);
 17    END PRC_DELETE_IDS;
 18
 19  END PKG_TEST;
 20  /

Package body created.

SQL> BEGIN
  2    PKG_TEST.PRC_DELETE_IDS(TRUNC(SYSDATE));
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> select * from test_table;

no rows selected
Note that a procedure should not commit, the caller knows if he wants to commit or not, the procedure can't know what is the global transaction and if it is appropriate to commit.

[Updated on: Fri, 22 May 2020 00:27]

Report message to a moderator

Re: Using associated array as SP parameter [message #680566 is a reply to message #680563] Fri, 22 May 2020 01:47 Go to previous messageGo to next message
thegerman
Messages: 5
Registered: March 2020
Location: Ohio
Junior Member
Thank Michel. I did not know about sys.odcinumberlist or varchar2list. Assuming I had different type, why didn't my original example work and throw the ORA-00902 error? Why did it not understand my type definition?
Re: Using associated array as SP parameter [message #680568 is a reply to message #680566] Fri, 22 May 2020 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 67540
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It understands it in PL/SQL engine as it is a PL/SQL defined type.
It doesn't understand it in SQL engine as it is not defined in SQL.
PL/SQL can use SQL types but the opposite is not true.

If the datatype is different than the predefined one, you can create your own type using CREATE TYPE command.

Re: Using associated array as SP parameter [message #680569 is a reply to message #680568] Fri, 22 May 2020 04:42 Go to previous messageGo to next message
thegerman
Messages: 5
Registered: March 2020
Location: Ohio
Junior Member
The type I defined is run from the PL/SQL engine no? Or because the procedure is called from an anonymous block, it is run by the SQL engine?
Re: Using associated array as SP parameter [message #680571 is a reply to message #680569] Fri, 22 May 2020 05:38 Go to previous messageGo to next message
Michel Cadot
Messages: 67540
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"SELECT ID_COL FROM TABLE(tCOLIDS)" is a SQL statement which runs in the SQL engine: PL/SQL calls the SQL engine to execute it.

Re: Using associated array as SP parameter [message #680572 is a reply to message #680562] Fri, 22 May 2020 06:30 Go to previous message
Solomon Yakobson
Messages: 3006
Registered: January 2010
Location: Connecticut, USA
Senior Member
First of all, you didn't provide Oracle version. I'll assume you are on 12C or higher. Now lets take a look at:

        DELETE FROM TEST_TABLE
        WHERE ID_COL IN (SELECT ID_COL FROM TABLE(tCOLIDS));
First issue is

SELECT ID_COL FROM TABLE(tCOLIDS)

Associative array

TYPE T_IDCOL IS TABLE OF TEST_TABLE.ID_COL%TYPE INDEX BY PLS_INTEGER;

has no named attributes. Construct TABLE(tCOLIDS) returns pseudocolumn COLUMN_VALUE.
Second issue TABLE(tCOLIDS) is "burried" in subquery while associative array reference in PL/SQL ==> SQL is supported just directly.

SY.
Previous Topic: JSON Column for A Single Element with Potentially Multiple Values?
Next Topic: key with different values
Goto Forum:
  


Current Time: Wed Nov 25 07:13:27 CST 2020