Oracle数据库禁止修改Schema时，无自定义类型的动态表查询实现方案问询

Sure thing! You don't need custom schema-level types to create dynamic inline "temporary table" datasets for queries, merges, or deletes in Oracle. Here are the most reliable, practical approaches to get this done:

1. Use UNION ALL for simple inline datasets #

This is the most straightforward method, perfect for small, static sets of data. You can construct rows directly with SELECT ... FROM DUAL and union them together:

-- Query the dynamic dataset
SELECT 'A1' AS attr1, 'A2' AS attr2, 'A3' AS attr3, 'A4' AS attr4 FROM DUAL
UNION ALL
SELECT 'B1', 'B2', 'B3', 'B4' FROM DUAL
UNION ALL
SELECT 'C1', 'C2', 'C3', 'C4' FROM DUAL;

You can also embed this directly into DML operations like MERGE or DELETE:

-- Example: Merge dynamic data into a target table
MERGE INTO your_target_table t
USING (
    SELECT 'A1' AS attr1, 'A2' AS attr2, 'A3' AS attr3, 'A4' AS attr4 FROM DUAL
    UNION ALL
    SELECT 'B1', 'B2', 'B3', 'B4' FROM DUAL
) s
ON (t.attr1 = s.attr1)
WHEN MATCHED THEN 
    UPDATE SET t.attr2 = s.attr2, t.attr3 = s.attr3
WHEN NOT MATCHED THEN 
    INSERT (attr1, attr2, attr3, attr4) 
    VALUES (s.attr1, s.attr2, s.attr3, s.attr4);

2. Use XMLTABLE (compatible with Oracle 11g+) #

If you prefer a more structured format (or need to handle larger datasets), XMLTABLE lets you parse an XML document into a relational table without creating any schema objects:

-- Query dynamic data from XML
SELECT x.*
FROM XMLTABLE(
    '/rows/row'
    PASSING XMLTYPE('
        <rows>
            <row attr1="A1" attr2="A2" attr3="A3" attr4="A4"/>
            <row attr1="B1" attr2="B2" attr3="B3" attr4="B4"/>
            <row attr1="C1" attr2="C2" attr3="C3" attr4="C4"/>
        </rows>
    ')
    COLUMNS
        attr1 VARCHAR2(64) PATH '@attr1',
        attr2 VARCHAR2(128) PATH '@attr2',
        attr3 VARCHAR2(128) PATH '@attr3',
        attr4 VARCHAR2(128) PATH '@attr4'
) x;

This works seamlessly with DML operations too—just replace the subquery in a MERGE or DELETE with the XMLTABLE statement.

3. Use JSON_TABLE (Oracle 12c and newer) #

For a more modern, readable approach (especially if you're working with JSON data), JSON_TABLE parses JSON arrays into relational rows. This is cleaner than XML for most developers:

-- Query dynamic data from JSON
SELECT j.*
FROM JSON_TABLE(
    '[
        {"attr1":"A1", "attr2":"A2", "attr3":"A3", "attr4":"A4"},
        {"attr1":"B1", "attr2":"B2", "attr3":"B3", "attr4":"B4"},
        {"attr1":"C1", "attr2":"C2", "attr3":"C3", "attr4":"C4"}
    ]',
    '$[*]' COLUMNS
        attr1 VARCHAR2(64) PATH '$.attr1',
        attr2 VARCHAR2(128) PATH '$.attr2',
        attr3 VARCHAR2(128) PATH '$.attr3',
        attr4 VARCHAR2(128) PATH '$.attr4'
) j;

Like the other methods, you can use this as the source in MERGE, DELETE, or UPDATE statements—no custom types required.

Key Notes: #

• All these approaches are schema-neutral (no need to create OBJECT or TABLE types).
• For large datasets, XMLTABLE and JSON_TABLE are more maintainable than long UNION ALL chains.
• All methods support full DML operations, so you can use them directly in your MERGE and DELETE workflows.

内容的提问来源于stack exchange，提问作者vlddlv4