# 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
```sql
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
```sql
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
```markdown
### 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:__
```sql
-- 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.