admin管理员组文章数量:1122832
This is an extension of an answer to another question: . In the solution provided, primitive data types are handled but I also need to handle nested collections iteratively. For the sake of simplicity, the example only provides a single instance of a named collection but the idea is that the implementation supports deeply nested collections.
Take the following data types:
CREATE TYPE book_page is OBJECT(
book_page number,
content varchar2(4000)
);
/
CREATE TYPE table_of_book_pages IS TABLE OF book_page;
/
CREATE TYPE book is OBJECT(
title varchar(50),
author varchar(50),
subject varchar(100),
book_id number,
first_published DATE,
book_pages table_of_book_pages
);
/
CREATE TYPE table_of_books IS TABLE OF book;
/
With the reflection package provided in the original answer
CREATE PACKAGE reflection IS
TYPE type_info IS RECORD(
prec PLS_INTEGER,
scale PLS_INTEGER,
len PLS_INTEGER,
csid PLS_INTEGER,
csfrm PLS_INTEGER,
schema_name VARCHAR2(30),
type_name VARCHAR2(30),
version VARCHAR2(100),
count PLS_INTEGER
);
TYPE attr_info IS RECORD(
prec PLS_INTEGER,
scale PLS_INTEGER,
len PLS_INTEGER,
csid PLS_INTEGER,
csfrm PLS_INTEGER,
attr_elt_type ANYTYPE,
aname VARCHAR2(30)
);
FUNCTION get_size(
p_anydata IN ANYDATA
) RETURN PLS_INTEGER;
FUNCTION get_attr_name_at(
p_anydata IN ANYDATA,
p_index IN PLS_INTEGER DEFAULT 1
) RETURN VARCHAR2;
FUNCTION get_attr_value_at(
p_anydata IN ANYDATA,
p_index IN PLS_INTEGER DEFAULT 1
) RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY reflection IS
FUNCTION get_type(
p_anydata IN ANYDATA
) RETURN ANYTYPE
IS
v_typeid PLS_INTEGER;
v_anytype ANYTYPE;
v_type_info REFLECTION.TYPE_INFO;
BEGIN
v_typeid := p_anydata.GetType( typ => v_anytype );
RETURN v_anytype;
END;
FUNCTION get_info(
p_anytype IN ANYTYPE
) RETURN type_info
IS
v_typeid PLS_INTEGER;
v_type_info REFLECTION.TYPE_INFO;
BEGIN
v_typeid := p_anytype.GetInfo (
v_type_info.prec,
v_type_info.scale,
v_type_info.len,
v_type_info.csid,
v_type_info.csfrm,
v_type_info.schema_name,
v_type_info.type_name,
v_type_info.version,
v_type_info.count
);
IF v_typeid <> DBMS_TYPES.TYPECODE_OBJECT THEN
RAISE_APPLICATION_ERROR( -20000, 'Not an object.' );
END IF;
RETURN v_type_info;
END;
FUNCTION get_size(
p_anydata IN ANYDATA
) RETURN PLS_INTEGER
IS
BEGIN
RETURN Get_Info( Get_Type( p_anydata ) ).COUNT;
END;
FUNCTION get_attr_name_at(
p_anydata IN ANYDATA,
p_index IN PLS_INTEGER DEFAULT 1
) RETURN VARCHAR2
IS
v_anydata ANYDATA := p_anydata;
v_anytype ANYTYPE;
v_type_info REFLECTION.TYPE_INFO;
v_output VARCHAR2(4000);
v_attr_typeid PLS_INTEGER;
v_attr_info REFLECTION.ATTR_INFO;
BEGIN
v_anytype := Get_Type( v_anydata );
v_type_info := Get_Info( v_anytype );
IF p_index < 1 OR p_index > v_type_info.COUNT THEN
RETURN NULL;
END IF;
v_anydata.PIECEWISE;
v_attr_typeid := v_anytype.getAttrElemInfo(
pos => p_index,
prec => v_attr_info.prec,
scale => v_attr_info.scale,
len => v_attr_info.len,
csid => v_attr_info.csid,
csfrm => v_attr_info.csfrm,
attr_elt_type => v_attr_info.attr_elt_type,
aname => v_attr_info.aname
);
RETURN v_attr_info.aname;
END;
FUNCTION get_attr_value_at(
p_anydata IN ANYDATA,
p_index IN PLS_INTEGER DEFAULT 1
) RETURN VARCHAR2
IS
v_anydata ANYDATA := p_anydata;
v_anytype ANYTYPE;
v_type_info REFLECTION.TYPE_INFO;
v_output VARCHAR2(4000);
BEGIN
v_anytype := Get_Type( v_anydata );
v_type_info := Get_Info( v_anytype );
IF p_index < 1 OR p_index > v_type_info.COUNT THEN
RETURN NULL;
END IF;
v_anydata.PIECEWISE;
FOR i IN 1 .. p_index LOOP
DECLARE
v_attr_typeid PLS_INTEGER;
v_attr_info REFLECTION.ATTR_INFO;
v_result_code PLS_INTEGER;
BEGIN
v_attr_typeid := v_anytype.getAttrElemInfo(
pos => i,
prec => v_attr_info.prec,
scale => v_attr_info.scale,
len => v_attr_info.len,
csid => v_attr_info.csid,
csfrm => v_attr_info.csfrm,
attr_elt_type => v_attr_info.attr_elt_type,
aname => v_attr_info.aname
);
IF DEBUG THEN
DBMS_OUTPUT.PUT_LINE(
'Attribute ' || i || ': '
|| v_attr_info.aname
|| ' (type ' || v_attr_typeid || ')'
);
END IF;
CASE v_attr_typeid
WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
DECLARE
v_value NUMBER;
BEGIN
v_result_code := v_anydata.GetNumber( v_value );
IF i = p_index THEN
RETURN TO_CHAR( v_value );
END IF;
END;
WHEN DBMS_TYPES.TYPECODE_VARCHAR2 THEN
DECLARE
v_value VARCHAR2(4000);
BEGIN
v_result_code := v_anydata.GetVarchar2( v_value );
IF i = p_index THEN
RETURN v_value;
END IF;
END;
WHEN DBMS_TYPES.TYPECODE_DATE THEN
DECLARE
v_value DATE;
BEGIN
v_result_code := v_anydata.GetDate( v_value );
IF i = p_index THEN
RETURN TO_CHAR( v_value, 'YYYY-MM-DD HH24:MI:SS' );
END IF;
END;
ELSE
RETURN NULL;
END CASE;
END;
END LOOP;
RETURN NULL;
END;
END;
/
For the following input
DECLARE
list_of_books table_of_books;
idx PLS_INTEGER := 1;
p_anydata ANYDATA;
p_attr_name VARCHAR2(30);
p_attr_value VARCHAR2(4000);
BEGIN
dbms_output.enable;
list_of_books := table_of_books(
book(
'First book',
'Me',
'Simple Ones',
94321,
DATE '1970-01-01',
table_of_book_pages(
book_page(
1,
'This is the page 1 of the First book'
),
book_page(
2,
'This is the page 2 of the First book'
)
)
),
book(
'Second book',
'You',
'Intermediate Ones',
55555,
DATE '2020-01-01',
table_of_book_pages(
book_page(
1,
'This is the page 1 of the Second book'
)
)
),
book(
'Third book',
NULL,
'Advanced Ones',
77777,
DATE '2099-12-31' + INTERVAL '0 23:59:59' DAY TO SECOND,
table_of_book_pages(
book_page(
1,
'This is the page 1 of the Third book'
),
book_page(
2,
'This is the page 2 of the Third book'
),
book_page(
3,
'This is the page 3 of the Third book'
)
)
)
);
FOR book_no IN 1 .. list_of_books.COUNT LOOP
p_anydata := ANYDATA.ConvertObject( list_of_books(book_no) );
DBMS_OUTPUT.PUT_LINE( 'Book ' || book_no || ':' );
FOR attr_no IN 1 .. REFLECTION.get_size( p_anydata ) LOOP
p_attr_name := REFLECTION.get_attr_name_at( p_anydata, attr_no );
p_attr_value := REFLECTION.get_attr_value_at( p_anydata, attr_no );
DBMS_OUTPUT.PUT_LINE( ' ' || p_attr_name || ': ' || p_attr_value );
-- how can I iterate book_pages here without explicitly declaring a table_of_book_pages type variable?
END LOOP;
END LOOP;
END;
/
I expect the following output:
Book 1:
TITLE: First book
AUTHOR: Me
SUBJECT: Simple Ones
BOOK_ID: 94321
FIRST_PUBLISHED: 1970-01-01 00:00:00
BOOK_PAGES:
PAGE:
PAGE_ID: 1,
CONTENT: This is the page 1 of the First book
PAGE:
PAGE_ID: 2,
CONTENT: This is the page 2 of the First book
Book 2:
TITLE: Second book
AUTHOR: You
SUBJECT: Intermediate Ones
BOOK_ID: 55555
FIRST_PUBLISHED: 2020-01-01 00:00:00
BOOK_PAGES:
PAGE:
PAGE_ID: 1,
CONTENT: This is the page 1 of the Second book
Book 3:
TITLE: Third book
AUTHOR:
SUBJECT: Advanced Ones
BOOK_ID: 77777
FIRST_PUBLISHED: 2099-12-31 23:59:59
BOOK_PAGES:
PAGE:
PAGE_ID: 1,
CONTENT: This is the page 1 of the Third book
PAGE:
PAGE_ID: 2,
CONTENT: This is the page 2 of the Third book
PAGE:
PAGE_ID: 3,
CONTENT: This is the page 3 of the Third book
The goal of the implementation is to handle this in a generic way, supporting any type of named collections. Is this achievable without explicitly declaring variables of the collection data type?
This is an extension of an answer to another question: https://stackoverflow.com/a/64913702/4351488. In the solution provided, primitive data types are handled but I also need to handle nested collections iteratively. For the sake of simplicity, the example only provides a single instance of a named collection but the idea is that the implementation supports deeply nested collections.
Take the following data types:
CREATE TYPE book_page is OBJECT(
book_page number,
content varchar2(4000)
);
/
CREATE TYPE table_of_book_pages IS TABLE OF book_page;
/
CREATE TYPE book is OBJECT(
title varchar(50),
author varchar(50),
subject varchar(100),
book_id number,
first_published DATE,
book_pages table_of_book_pages
);
/
CREATE TYPE table_of_books IS TABLE OF book;
/
With the reflection package provided in the original answer
CREATE PACKAGE reflection IS
TYPE type_info IS RECORD(
prec PLS_INTEGER,
scale PLS_INTEGER,
len PLS_INTEGER,
csid PLS_INTEGER,
csfrm PLS_INTEGER,
schema_name VARCHAR2(30),
type_name VARCHAR2(30),
version VARCHAR2(100),
count PLS_INTEGER
);
TYPE attr_info IS RECORD(
prec PLS_INTEGER,
scale PLS_INTEGER,
len PLS_INTEGER,
csid PLS_INTEGER,
csfrm PLS_INTEGER,
attr_elt_type ANYTYPE,
aname VARCHAR2(30)
);
FUNCTION get_size(
p_anydata IN ANYDATA
) RETURN PLS_INTEGER;
FUNCTION get_attr_name_at(
p_anydata IN ANYDATA,
p_index IN PLS_INTEGER DEFAULT 1
) RETURN VARCHAR2;
FUNCTION get_attr_value_at(
p_anydata IN ANYDATA,
p_index IN PLS_INTEGER DEFAULT 1
) RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY reflection IS
FUNCTION get_type(
p_anydata IN ANYDATA
) RETURN ANYTYPE
IS
v_typeid PLS_INTEGER;
v_anytype ANYTYPE;
v_type_info REFLECTION.TYPE_INFO;
BEGIN
v_typeid := p_anydata.GetType( typ => v_anytype );
RETURN v_anytype;
END;
FUNCTION get_info(
p_anytype IN ANYTYPE
) RETURN type_info
IS
v_typeid PLS_INTEGER;
v_type_info REFLECTION.TYPE_INFO;
BEGIN
v_typeid := p_anytype.GetInfo (
v_type_info.prec,
v_type_info.scale,
v_type_info.len,
v_type_info.csid,
v_type_info.csfrm,
v_type_info.schema_name,
v_type_info.type_name,
v_type_info.version,
v_type_info.count
);
IF v_typeid <> DBMS_TYPES.TYPECODE_OBJECT THEN
RAISE_APPLICATION_ERROR( -20000, 'Not an object.' );
END IF;
RETURN v_type_info;
END;
FUNCTION get_size(
p_anydata IN ANYDATA
) RETURN PLS_INTEGER
IS
BEGIN
RETURN Get_Info( Get_Type( p_anydata ) ).COUNT;
END;
FUNCTION get_attr_name_at(
p_anydata IN ANYDATA,
p_index IN PLS_INTEGER DEFAULT 1
) RETURN VARCHAR2
IS
v_anydata ANYDATA := p_anydata;
v_anytype ANYTYPE;
v_type_info REFLECTION.TYPE_INFO;
v_output VARCHAR2(4000);
v_attr_typeid PLS_INTEGER;
v_attr_info REFLECTION.ATTR_INFO;
BEGIN
v_anytype := Get_Type( v_anydata );
v_type_info := Get_Info( v_anytype );
IF p_index < 1 OR p_index > v_type_info.COUNT THEN
RETURN NULL;
END IF;
v_anydata.PIECEWISE;
v_attr_typeid := v_anytype.getAttrElemInfo(
pos => p_index,
prec => v_attr_info.prec,
scale => v_attr_info.scale,
len => v_attr_info.len,
csid => v_attr_info.csid,
csfrm => v_attr_info.csfrm,
attr_elt_type => v_attr_info.attr_elt_type,
aname => v_attr_info.aname
);
RETURN v_attr_info.aname;
END;
FUNCTION get_attr_value_at(
p_anydata IN ANYDATA,
p_index IN PLS_INTEGER DEFAULT 1
) RETURN VARCHAR2
IS
v_anydata ANYDATA := p_anydata;
v_anytype ANYTYPE;
v_type_info REFLECTION.TYPE_INFO;
v_output VARCHAR2(4000);
BEGIN
v_anytype := Get_Type( v_anydata );
v_type_info := Get_Info( v_anytype );
IF p_index < 1 OR p_index > v_type_info.COUNT THEN
RETURN NULL;
END IF;
v_anydata.PIECEWISE;
FOR i IN 1 .. p_index LOOP
DECLARE
v_attr_typeid PLS_INTEGER;
v_attr_info REFLECTION.ATTR_INFO;
v_result_code PLS_INTEGER;
BEGIN
v_attr_typeid := v_anytype.getAttrElemInfo(
pos => i,
prec => v_attr_info.prec,
scale => v_attr_info.scale,
len => v_attr_info.len,
csid => v_attr_info.csid,
csfrm => v_attr_info.csfrm,
attr_elt_type => v_attr_info.attr_elt_type,
aname => v_attr_info.aname
);
IF DEBUG THEN
DBMS_OUTPUT.PUT_LINE(
'Attribute ' || i || ': '
|| v_attr_info.aname
|| ' (type ' || v_attr_typeid || ')'
);
END IF;
CASE v_attr_typeid
WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
DECLARE
v_value NUMBER;
BEGIN
v_result_code := v_anydata.GetNumber( v_value );
IF i = p_index THEN
RETURN TO_CHAR( v_value );
END IF;
END;
WHEN DBMS_TYPES.TYPECODE_VARCHAR2 THEN
DECLARE
v_value VARCHAR2(4000);
BEGIN
v_result_code := v_anydata.GetVarchar2( v_value );
IF i = p_index THEN
RETURN v_value;
END IF;
END;
WHEN DBMS_TYPES.TYPECODE_DATE THEN
DECLARE
v_value DATE;
BEGIN
v_result_code := v_anydata.GetDate( v_value );
IF i = p_index THEN
RETURN TO_CHAR( v_value, 'YYYY-MM-DD HH24:MI:SS' );
END IF;
END;
ELSE
RETURN NULL;
END CASE;
END;
END LOOP;
RETURN NULL;
END;
END;
/
For the following input
DECLARE
list_of_books table_of_books;
idx PLS_INTEGER := 1;
p_anydata ANYDATA;
p_attr_name VARCHAR2(30);
p_attr_value VARCHAR2(4000);
BEGIN
dbms_output.enable;
list_of_books := table_of_books(
book(
'First book',
'Me',
'Simple Ones',
94321,
DATE '1970-01-01',
table_of_book_pages(
book_page(
1,
'This is the page 1 of the First book'
),
book_page(
2,
'This is the page 2 of the First book'
)
)
),
book(
'Second book',
'You',
'Intermediate Ones',
55555,
DATE '2020-01-01',
table_of_book_pages(
book_page(
1,
'This is the page 1 of the Second book'
)
)
),
book(
'Third book',
NULL,
'Advanced Ones',
77777,
DATE '2099-12-31' + INTERVAL '0 23:59:59' DAY TO SECOND,
table_of_book_pages(
book_page(
1,
'This is the page 1 of the Third book'
),
book_page(
2,
'This is the page 2 of the Third book'
),
book_page(
3,
'This is the page 3 of the Third book'
)
)
)
);
FOR book_no IN 1 .. list_of_books.COUNT LOOP
p_anydata := ANYDATA.ConvertObject( list_of_books(book_no) );
DBMS_OUTPUT.PUT_LINE( 'Book ' || book_no || ':' );
FOR attr_no IN 1 .. REFLECTION.get_size( p_anydata ) LOOP
p_attr_name := REFLECTION.get_attr_name_at( p_anydata, attr_no );
p_attr_value := REFLECTION.get_attr_value_at( p_anydata, attr_no );
DBMS_OUTPUT.PUT_LINE( ' ' || p_attr_name || ': ' || p_attr_value );
-- how can I iterate book_pages here without explicitly declaring a table_of_book_pages type variable?
END LOOP;
END LOOP;
END;
/
I expect the following output:
Book 1:
TITLE: First book
AUTHOR: Me
SUBJECT: Simple Ones
BOOK_ID: 94321
FIRST_PUBLISHED: 1970-01-01 00:00:00
BOOK_PAGES:
PAGE:
PAGE_ID: 1,
CONTENT: This is the page 1 of the First book
PAGE:
PAGE_ID: 2,
CONTENT: This is the page 2 of the First book
Book 2:
TITLE: Second book
AUTHOR: You
SUBJECT: Intermediate Ones
BOOK_ID: 55555
FIRST_PUBLISHED: 2020-01-01 00:00:00
BOOK_PAGES:
PAGE:
PAGE_ID: 1,
CONTENT: This is the page 1 of the Second book
Book 3:
TITLE: Third book
AUTHOR:
SUBJECT: Advanced Ones
BOOK_ID: 77777
FIRST_PUBLISHED: 2099-12-31 23:59:59
BOOK_PAGES:
PAGE:
PAGE_ID: 1,
CONTENT: This is the page 1 of the Third book
PAGE:
PAGE_ID: 2,
CONTENT: This is the page 2 of the Third book
PAGE:
PAGE_ID: 3,
CONTENT: This is the page 3 of the Third book
The goal of the implementation is to handle this in a generic way, supporting any type of named collections. Is this achievable without explicitly declaring variables of the collection data type?
Share Improve this question edited Nov 22, 2024 at 17:41 Joao Pereira asked Nov 22, 2024 at 17:36 Joao PereiraJoao Pereira 5835 silver badges17 bronze badges2 Answers
Reset to default 1Is this achievable without explicitly declaring variables of the collection data type?
No, the ANYDATA type has the methods:
-
MEMBER FUNCTION GetCollection( self IN ANYDATA, col OUT NOCOPY "<collection_type>") RETURN PLS_INTEGER; -
MEMBER FUNCTION GetObject( self IN ANYDATA, obj OUT NOCOPY "<object_type>") RETURN PLS_INTEGER;
Both of them require you to pass in an variable with the correct data-type so there does not appear to be any way to perform a piecewise iteration through an ANYDATA object to retrieve the object or collection attributes in a generic way.
There is a partial solution in this answer but, as it notes, you need to hard-code the data-type for it to work.
You can convert the objects to XML without knowing the structure:
DECLARE
list_of_books table_of_books;
v_output CLOB;
BEGIN
dbms_output.enable;
list_of_books := table_of_books(
book(
'First book',
'Me',
'Simple Ones',
94321,
DATE '1970-01-01',
table_of_book_pages(
book_page(
1,
'This is the page 1 of the First book'
),
book_page(
2,
'This is the page 2 of the First book'
)
)
),
book(
'Second book',
'You',
'Intermediate Ones',
55555,
DATE '2020-01-01',
table_of_book_pages(
book_page(
1,
'This is the page 1 of the Second book'
)
)
),
book(
'Third book',
NULL,
'Advanced Ones',
77777,
DATE '2099-12-31' + INTERVAL '0 23:59:59' DAY TO SECOND,
table_of_book_pages(
book_page(
1,
'This is the page 1 of the Third book'
),
book_page(
2,
'This is the page 2 of the Third book'
),
book_page(
3,
'This is the page 3 of the Third book'
)
)
)
);
SELECT XMLELEMENT(
"OBJECTS",
XMLAGG(XMLTYPE(VALUE(t)))
).getClobVal() AS output
INTO v_output
FROM TABLE(list_of_books) t;
DBMS_OUTPUT.PUT_LINE(v_output);
END;
/
Which outputs:
<OBJECTS><BOOK>
<TITLE>First book</TITLE>
<AUTHOR>Me</AUTHOR>
<SUBJECT>Simple Ones</SUBJECT>
<BOOK_ID>94321</BOOK_ID>
<FIRST_PUBLISHED>1970-01-01 00:00:00</FIRST_PUBLISHED>
<BOOK_PAGES>
<BOOK_PAGE>
<BOOK_PAGE>1</BOOK_PAGE>
<CONTENT>This is the page 1 of the First book</CONTENT>
</BOOK_PAGE>
<BOOK_PAGE>
<BOOK_PAGE>2</BOOK_PAGE>
<CONTENT>This is the page 2 of the First book</CONTENT>
</BOOK_PAGE>
</BOOK_PAGES>
</BOOK>
<BOOK>
<TITLE>Second book</TITLE>
<AUTHOR>You</AUTHOR>
<SUBJECT>Intermediate Ones</SUBJECT>
<BOOK_ID>55555</BOOK_ID>
<FIRST_PUBLISHED>2020-01-01 00:00:00</FIRST_PUBLISHED>
<BOOK_PAGES>
<BOOK_PAGE>
<BOOK_PAGE>1</BOOK_PAGE>
<CONTENT>This is the page 1 of the Second book</CONTENT>
</BOOK_PAGE>
</BOOK_PAGES>
</BOOK>
<BOOK>
<TITLE>Third book</TITLE>
<AUTHOR/>
<SUBJECT>Advanced Ones</SUBJECT>
<BOOK_ID>77777</BOOK_ID>
<FIRST_PUBLISHED>2099-12-31 23:59:59</FIRST_PUBLISHED>
<BOOK_PAGES>
<BOOK_PAGE>
<BOOK_PAGE>1</BOOK_PAGE>
<CONTENT>This is the page 1 of the Third book</CONTENT>
</BOOK_PAGE>
<BOOK_PAGE>
<BOOK_PAGE>2</BOOK_PAGE>
<CONTENT>This is the page 2 of the Third book</CONTENT>
</BOOK_PAGE>
<BOOK_PAGE>
<BOOK_PAGE>3</BOOK_PAGE>
<CONTENT>This is the page 3 of the Third book</CONTENT>
</BOOK_PAGE>
</BOOK_PAGES>
</BOOK>
</OBJECTS>
If you want (approximately) your expected output:
DECLARE
list_of_books table_of_books;
v_xml XMLTYPE;
BEGIN
dbms_output.enable;
list_of_books := table_of_books(
book(
'First book',
'Me',
'Simple Ones',
94321,
DATE '1970-01-01',
table_of_book_pages(
book_page(
1,
'This is the page 1 of the First book'
),
book_page(
2,
'This is the page 2 of the First book'
)
)
),
book(
'Second book',
'You',
'Intermediate Ones',
55555,
DATE '2020-01-01',
table_of_book_pages(
book_page(
1,
'This is the page 1 of the Second book'
)
)
),
book(
'Third book',
NULL,
'Advanced Ones',
77777,
DATE '2099-12-31' + INTERVAL '0 23:59:59' DAY TO SECOND,
table_of_book_pages(
book_page(
1,
'This is the page 1 of the Third book'
),
book_page(
2,
'This is the page 2 of the Third book'
),
book_page(
3,
'This is the page 3 of the Third book'
)
)
)
);
FOR r IN (
SELECT *
FROM XMLTABLE(
'//*'
PASSING (
(
SELECT XMLELEMENT(
"OBJECTS",
XMLAGG(XMLTYPE(VALUE(t)))
)
FROM TABLE(list_of_books) t
)
)
COLUMNS
key VARCHAR2(30) PATH 'name()',
value VARCHAR2(4000) PATH 'text()',
depth NUMBER(5,0) PATH 'count(ancestor-or-self::*)'
)
)
LOOP
DBMS_OUTPUT.PUT_LINE(LPAD(' ', 2 * r.depth - 2, ' ') || r.key || ': ' || r.value);
END LOOP;
END;
/
Which outputs:
OBJECTS:
BOOK:
TITLE: First book
AUTHOR: Me
SUBJECT: Simple Ones
BOOK_ID: 94321
FIRST_PUBLISHED: 1970-01-01 00:00:00
BOOK_PAGES:
BOOK_PAGE:
BOOK_PAGE: 1
CONTENT: This is the page 1 of the First book
BOOK_PAGE:
BOOK_PAGE: 2
CONTENT: This is the page 2 of the First book
BOOK:
TITLE: Second book
AUTHOR: You
SUBJECT: Intermediate Ones
BOOK_ID: 55555
FIRST_PUBLISHED: 2020-01-01 00:00:00
BOOK_PAGES:
BOOK_PAGE:
BOOK_PAGE: 1
CONTENT: This is the page 1 of the Second book
BOOK:
TITLE: Third book
AUTHOR:
SUBJECT: Advanced Ones
BOOK_ID: 77777
FIRST_PUBLISHED: 2099-12-31 23:59:59
BOOK_PAGES:
BOOK_PAGE:
BOOK_PAGE: 1
CONTENT: This is the page 1 of the Third book
BOOK_PAGE:
BOOK_PAGE: 2
CONTENT: This is the page 2 of the Third book
BOOK_PAGE:
BOOK_PAGE: 3
CONTENT: This is the page 3 of the Third book
fiddle
本文标签： databaseOracle PLSQLIterate table of objects through reflectionStack Overflow
版权声明：本文标题：database - Oracle PLSQL - Iterate table of objects through reflection - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1736301864a1931322.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论