如何在Oracle数据字典表中查询所有内置函数以替代查阅冗长官方文档？

Absolutely! You can pull a full list of Oracle's built-in functions directly from the data dictionary—no more scrolling through hundreds of pages of official docs just to find a function name or its signature. Here's how to do it effectively:

Basic List of Standalone Built-in Functions #

Start with this query to get a clean list of top-level built-in functions (not part of packages):

SELECT owner, object_name AS function_name
FROM all_objects
WHERE object_type = 'FUNCTION'
  AND owner IN ('SYS', 'PUBLIC')
ORDER BY owner, object_name;

• ALL_OBJECTS gives you access to all database objects you have permissions to view
• Filtering by owner IN ('SYS', 'PUBLIC') targets Oracle's native functions (most live in SYS, while some widely available ones are in PUBLIC)

Get Detailed Function Signatures (Parameters + Return Types) #

If you need more context—like what parameters a function accepts, their data types, and the return type—use this query joining ALL_ARGUMENTS (which holds argument details) with ALL_OBJECTS:

SELECT 
    a.owner,
    a.object_name AS function_name,
    a.argument_name,
    a.data_type,
    a.in_out,
    r.data_type AS return_type
FROM all_arguments a
JOIN all_objects o 
    ON a.owner = o.owner 
    AND a.object_name = o.object_name
LEFT JOIN all_arguments r
    ON a.owner = r.owner 
    AND a.object_name = r.object_name
    AND r.position = 0 -- Position 0 represents the function's return value
WHERE o.object_type = 'FUNCTION'
  AND o.owner IN ('SYS', 'PUBLIC')
ORDER BY a.owner, a.object_name, a.position;

This will show you every input/output parameter and the return data type for each built-in function—way faster than digging through documentation.

Include Functions from System Packages #

A lot of Oracle's useful built-in functionality lives inside system packages (like DBMS_UTIL or UTL_FILE). To list these package-based functions, use this query:

SELECT 
    p.owner,
    p.object_name AS package_name,
    p.procedure_name AS function_name,
    a.argument_name,
    a.data_type,
    a.in_out,
    r.data_type AS return_type
FROM all_procedures p
JOIN all_arguments a 
    ON p.owner = a.owner 
    AND p.object_name = a.object_name
    AND p.procedure_name = a.procedure_name
LEFT JOIN all_arguments r
    ON p.owner = r.owner 
    AND p.object_name = r.object_name
    AND p.procedure_name = r.procedure_name
    AND r.position = 0
WHERE p.object_type = 'PACKAGE'
  AND p.owner IN ('SYS', 'PUBLIC')
  AND a.argument_name IS NOT NULL -- Exclude package-level metadata
ORDER BY p.owner, p.object_name, p.procedure_name, a.position;

This captures functions that are part of packages, which are often critical for administrative or advanced operations.

Quick Tips #

• If you have DBA-level access, replace ALL_OBJECTS/ALL_ARGUMENTS with DBA_OBJECTS/DBA_ARGUMENTS to see every single built-in function (including those restricted to admins)
• Use LIKE to filter for specific function types, e.g., AND object_name LIKE '%DATE%' to find date-related functions

These queries are a game-changer for quickly finding and verifying Oracle's built-in functions without relying solely on documentation.

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