ALL_ARGUMENTS视图中TABLE与PL/SQL TABLE的差异解析

Great question! The distinction between these two DATA_TYPE values comes down to the scope and SQL compatibility of the collection type, even though their declaration syntax seems nearly identical. Let’s break this down using your examples:

Core Differences #

• Scope & Definition Context

  • A type marked PL/SQL TABLE (like your Bom_Revision_Tbl_Type in package BOM_BO_PUB) is a PL/SQL-only private type. It’s defined inside a PL/SQL unit (package, procedure, function, or anonymous block) and can only be used within PL/SQL logic—you can’t reference it directly in SQL statements (e.g., you can’t run SELECT * FROM TABLE(:your_plsql_table_var) against it).
  • A type marked TABLE (like inv_ebi_name_value_pair_tbl in INV_EBI_ITEM_HELPER) is a SQL-accessible collection type. This means it’s either:
    1. Created at the database level with a standalone CREATE TYPE statement, or
    2. Defined in a package but configured to be visible to SQL (e.g., using Oracle 12c+ features that expose PL/SQL types to SQL).
       These types work seamlessly in both PL/SQL and SQL contexts.

• Usage Capabilities

  • PL/SQL TABLE types are limited to PL/SQL operations: you can loop through them, modify elements, pass them between PL/SQL units, but you can’t use them in SQL queries or as return types for table functions called from SQL.
  • TABLE types are fully compatible with SQL: you can use the TABLE() function to unnest them in queries, declare SQL-level variables of this type, or use them as parameters for SQL-callable procedures/functions.

• How ALL_ARGUMENTS Determines the Value
  Oracle’s ALL_ARGUMENTS view uses the type’s "SQL visibility" to set DATA_TYPE:

  • If the type is part of the SQL type system (standalone CREATE TYPE, or package-exposed SQL-compatible type), it shows TABLE.
  • If the type exists only within PL/SQL’s private namespace with no SQL mapping, it shows PL/SQL TABLE.

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