Using associated array as SP parameter [message #680562] |
Thu, 21 May 2020 20:44 |
|
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 |
|
Michel Cadot
Messages: 68665 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 #680572 is a reply to message #680562] |
Fri, 22 May 2020 06:30 |
Solomon Yakobson
Messages: 3275 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.
|
|
|