Details
-
New Feature
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
Description
Let's add support for TYPE type_name IS RECORD (..) inside the DECLARE section stored routines and anonymous blocks, with these limitations:
- The record members can be of scalar data types only
- The NOT NULL clause inside members won't be supported
- The default values for members won't be supported
Example:
DECLARE
|
TYPE DeptRecTyp IS RECORD ( |
dept_id NUMBER(4),
|
dept_name VARCHAR2(30),
|
mgr_id NUMBER(6),
|
loc_id NUMBER(4)
|
);
|
dept_rec DeptRecTyp:= DeptRecTyp(11,'a',201,1700); |
str VARCHAR(1024); |
BEGIN
|
str:=
|
'dept_id: ' || dept_rec.dept_id || '; ' || |
'dept_name: ' || dept_rec.dept_name || '; ' || |
'mgr_id: ' || dept_rec.mgr_id || '; ' || |
'loc_id: ' || dept_rec.loc_id; |
DBMS_OUTPUT.PUT_LINE(str);
|
END; |
dept_id: 11; dept_name: a; mgr_id: 201; loc_id: 1700
|
Record members should support the anchored %TYPE attribute:
CREATE TABLE t1 |
(
|
dept_id NUMBER(4),
|
dept_name VARCHAR2(30),
|
mgr_id NUMBER(6),
|
loc_id NUMBER(4)
|
);
|
|
DECLARE
|
TYPE DeptRecTyp IS RECORD ( |
dept_id t1.dept_id%TYPE,
|
dept_name t1.dept_name%TYPE,
|
mgr_id t1.mgr_id%TYPE,
|
loc_id t1.loc_id%TYPE
|
);
|
dept_rec DeptRecTyp:= DeptRecTyp(11,'a',201,1700); |
str VARCHAR(1024); |
BEGIN
|
str:=
|
'dept_id: ' || dept_rec.dept_id || '; ' || |
'dept_name: ' || dept_rec.dept_name || '; ' || |
'mgr_id: ' || dept_rec.mgr_id || '; ' || |
'loc_id: ' || dept_rec.loc_id; |
DBMS_OUTPUT.PUT_LINE(str);
|
END; |
Attachments
Issue Links
- blocks
-
MDEV-13139 CREATE PACKAGE: package-wide declarations
-
- Stalled
-
-
MDEV-20037 FUNCTION returning a TYPE fails
-
- Open
-
-
MDEV-34319 Associative arrays: DECLARE TYPE .. TABLE OF .. INDEX BY in stored routines
-
- In Review
-
-
MDEV-34323 Oracle compatibility project 3
-
- Open
-
-
MDEV-34560 Qualified package data types
-
- Open
-
-
MDEV-34569 Oracle compatibility project 4
-
- Open
-
- is part of
-
MDEV-35973 Oracle Compatibility Project 1 for 2025
-
- In Progress
-
- relates to
-
MDEV-34944 Implement anchored data types in ROW members
-
- Open
-
-
MDEV-35274 Anchored ROW TYPE OF variables for a single column table fail to compare
-
- Open
-
-
MDEV-10143 User defined types
-
- Open
-
Declaring records using SUBTYPE will be implemented in a separate task when needed:
(
dept_id NUMBER(4),
dept_name VARCHAR2(30),
mgr_id NUMBER(6),
loc_id NUMBER(4)
);
DECLARE
dept_rec DeptRecTyp;
BEGIN
dept_rec.dept_id := 11;
dept_rec.mgr_id := 201;
dept_rec.loc_id := 1700;
str:=
DBMS_OUTPUT.PUT_LINE(str);
NOT NULL and default clauses will be implemented in a separate task when needed:
DECLARE
mgr_id NUMBER(6) := 200,
loc_id NUMBER(4) := 1700
);
dept_rec DeptRecTyp;
BEGIN
str:=
DBMS_OUTPUT.PUT_LINE(str);
dept_id: 10; dept_name: Administration mgr_id: 200 loc_id: 1700
Records with records in members will be implemented in a separate task when needed:
DECLARE
);
);
person contact;
BEGIN
DBMS_OUTPUT.PUT_LINE (
person.phone
);
John Brown, 1-654-222-1234