Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
Some minor associative array features where not included in the patch for MDEV-34319.
Let's implement them eventually.
CAST(NULL AS assoc_array_type)
Oracle does not allow to assign NULL directly to a variable of an associative array data type. However it allows to cast NULL to an associative array data type and then assign the cast result to a variable of this type:
CREATE OR REPLACE PROCEDURE p1 AS |
TYPE assoc_t IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER; |
assoc assoc_t;
|
BEGIN
|
assoc(1):=1;
|
assoc:= CAST(NULL AS assoc_t); |
DBMS_OUTPUT.PUT_LINE(CASE WHEN assoc IS NULL THEN 'IS NULL' ELSE 'IS NOT NULL' END || ' ' || assoc.COUNT); |
END; |
/
|
CALL p1;
|
IS NULL 0
|
Pass associative arrays into CASE and CASE-alike hybrid type functions
Oracle allows to pass associative array variables into CASE and CASE-alike hybrid type functions. It seems the only available operation on such function result is a NULL test:
CREATE OR REPLACE PROCEDURE p1 AS |
TYPE assoc_t IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER; |
assoc0 assoc_t;
|
assoc1 assoc_t;
|
BEGIN
|
assoc0:= CAST(NULL AS assoc_t); |
assoc1(1):=1;
|
DBMS_OUTPUT.PUT_LINE(CASE WHEN COALESCE(assoc0, assoc1) IS NULL THEN 'IS NULL' ELSE 'IS NOT NULL' END); |
END; |
/
|
CALL p1;
|
IS NOT NULL
|
Allow stored functions in associative array key expressions
CREATE OR REPLACE FUNCTION f1 RETURN INT AS |
BEGIN
|
RETURN 1; |
END; |
/
|
CREATE OR REPLACE PROCEDURE p1 AS |
TYPE assoc_t IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER; |
assoc assoc_t;
|
BEGIN
|
assoc(1):=1;
|
DBMS_OUTPUT.PUT_LINE(assoc(f1()));
|
END; |
/
|
CALL p1;
|
1
|
Passing an assoc array as a parameter to a stored routine
This needs MDEV-13139 to be implemented first
Attachments
Issue Links
- is blocked by
-
-
- Stalled
-
-
-
- Closed
-