mars/confluence/GET_PACKAGE_DOCUMENTATION_Guide.md

GET_PACKAGE_DOCUMENTATION Function Guide

Overview

GET_PACKAGE_DOCUMENTATION is a standalone Oracle PL/SQL function designed to automatically generate comprehensive markdown documentation from Oracle packages. It extracts procedural and function metadata along with embedded comments to create structured documentation.

Function Details

Purpose

The function parses Oracle package source code and generates formatted markdown documentation, including:

• Function and procedure signatures
• Parameter information
• Usage examples
• Return types
• Embedded comments with special annotations

Syntax

GET_PACKAGE_DOCUMENTATION(package_name VARCHAR2, schema_name VARCHAR2) RETURN CLOB

Parameters

Parameter	Type	Description
package_name	VARCHAR2	Name of the Oracle package to document
schema_name	VARCHAR2	Schema containing the package

Return Type

• CLOB: Returns formatted markdown documentation as a Character Large Object

Usage Examples

Basic Usage

SELECT CT_MRDS.GET_PACKAGE_DOCUMENTATION('FILE_MANAGER', 'CT_MRDS') FROM DUAL;

Documentation Format

The function generates markdown with the following structure:

Function/Procedure Entries

• Header: Function/Procedure name as H3 heading
• Description: Extracted from @desc comments
• Return Type: For functions only
• Parameters Table: Name, IN/OUT direction, and data type
• Usage Example: Code from @example comments
• Example Result: Output from @ex_rslt comments

Example Output Format

### Function FUNCTION_NAME
__Description:__ Function description from comments
__Return:__ RETURN_TYPE
__Parameters:__
|Name|IN/OUT|Data Type|
|----------|----------|----------|
|PARAM1 |IN| VARCHAR2|
|PARAM2 |OUT| NUMBER|
__Example usage:__
```sql
-- Example code

Example result:

-- Expected output

## Special Comment Annotations

The function recognizes these special comment patterns in package source:

| Annotation | Purpose | Example |
|------------|---------|---------|
| `@name` | Function/procedure name | `-- @name FUNCTION_NAME` |
| `@desc` | Description text | `-- @desc Returns formatted data` |
| `@example` | Usage example code | `-- @example SELECT func() FROM dual;` |
| `@ex_rslt` | Expected result | `-- @ex_rslt 42` |

## Requirements

### Database Privileges
The function requires access to these Oracle system views:
- `ALL_SOURCE` - For package source code
- `ALL_PROCEDURES` - For procedure/function metadata
- `ALL_ARGUMENTS` - For parameter information

## Best Practices

### Documentation Standards
1. **Comment Placement**: Place special annotations directly above function/procedure declarations
2. **Example Quality**: Provide realistic, executable examples
3. **Description Clarity**: Write clear, concise descriptions
4. **Parameter Documentation**: Document all parameters with meaningful names

### Usage Recommendations
1. **Output Settings**: Always use appropriate SQL*Plus settings for CLOB output
2. **File Generation**: Redirect output to `.md` files for version control
3. **Regular Updates**: Regenerate documentation when package code changes
4. **Review Process**: Review generated documentation for accuracy

## Troubleshooting

### Common Issues
1. **Truncated Output**: Use proper LINESIZE and LONG settings
2. **Access Denied**: Ensure proper schema privileges
3. **Missing Content**: Verify special comment annotations in source
4. **Formatting Issues**: Check for special characters in comments

### SQL*Plus Settings
For complete output, always use:
```sql
SET PAGESIZE 0
SET LINESIZE 32000
SET LONG 1000000

Integration with Development Workflow

Version Control

• Store generated documentation in repository
• Update documentation with each package change
• Use consistent naming conventions

CI/CD Integration

The function can be integrated into automated documentation pipelines:

1. Package compilation
2. Documentation generation
3. File output to documentation directory
4. Git commit with package changes

Conclusion

The GET_PACKAGE_DOCUMENTATION function provides an automated solution for maintaining up-to-date Oracle package documentation. By leveraging embedded comments and Oracle metadata, it ensures documentation stays synchronized with code changes while providing a consistent, readable format for developers and stakeholders.

The function successfully generates comprehensive documentation as demonstrated with the FILE_MANAGER package, producing 626 lines of detailed markdown including function signatures, parameters, examples, and descriptions.