PL/SQL Source Code Viewer (dsource.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides Oracle database administration functionality via the dsource.sql script.

The Script

rem  dsource.sql
rem
set linesize 132
rem
ttitle 'Program Source'
rem
col owner format a8 heading 'OWNER'
col name format a22 heading 'NAME'
col type format a12 heading 'TYPE'
col line format 99999 heading 'LINE'
col text format a80 heading 'TEXT'
rem
break on owner skip 1 on name on type
rem
select owner, name, type, line, text
  from sys.dba_source
 where owner like upper('&owner')
   and name like upper('&name')
   and type like upper('&type')
 order by owner, name, type, line;
rem
set linesize 80

What This Script Does

This script displays the complete source code for PL/SQL objects including procedures, functions, packages, triggers, and types. It provides line-by-line source code display with proper formatting and line numbers, making it essential for code review, debugging, development, and documentation purposes. The script allows flexible filtering by owner, object name, and object type.

Key Features

• Complete Source Display: Shows full source code with line numbers
• Multiple Object Types: Supports procedures, functions, packages, triggers, types
• Flexible Filtering: Filter by owner, name pattern, and object type
• Organized Output: Groups by owner, name, and type for clarity
• Wide Format: Uses 132-character width for better code readability
• Line-by-Line Display: Maintains original formatting and structure

Usage

@dsource.sql

Input Parameters:

• Owner: Schema owner name or pattern (use % for all owners)
• Name: Object name or pattern (use % for all objects)
• Type: Object type or pattern (use % for all types)

Required Privileges

SELECT on SYS.DBA_SOURCE

Sample Output

                                            Program Source

OWNER    NAME                 TYPE         LINE TEXT
-------- -------------------- ------------ ---- --------------------------------------------------------------------------------
HR       CALCULATE_SALARY     FUNCTION        1 FUNCTION calculate_salary(emp_id NUMBER)
                                              2 RETURN NUMBER
                                              3 IS
                                              4   v_salary NUMBER;
                                              5   v_commission NUMBER;
                                              6 BEGIN
                                              7   SELECT salary, NVL(commission_pct,0)
                                              8     INTO v_salary, v_commission
                                              9     FROM employees
                                             10    WHERE employee_id = emp_id;
                                             11
                                             12   RETURN v_salary + (v_salary * v_commission);
                                             13 EXCEPTION
                                             14   WHEN NO_DATA_FOUND THEN
                                             15     RETURN 0;
                                             16 END calculate_salary;

         UPDATE_EMPLOYEE      PROCEDURE       1 PROCEDURE update_employee(
                                              2   p_emp_id IN NUMBER,
                                              3   p_salary IN NUMBER,
                                              4   p_dept_id IN NUMBER DEFAULT NULL)
                                              5 IS
                                              6 BEGIN
                                              7   UPDATE employees
                                              8      SET salary = p_salary,
                                              9          last_updated = SYSDATE
                                             10    WHERE employee_id = p_emp_id;
                                             11
                                             12   IF p_dept_id IS NOT NULL THEN
                                             13     UPDATE employees
                                             14        SET department_id = p_dept_id
                                             15      WHERE employee_id = p_emp_id;
                                             16   END IF;
                                             17
                                             18   COMMIT;
                                             19 END update_employee;

Key Output Columns

• OWNER: Schema that owns the PL/SQL object
• NAME: Name of the PL/SQL object
• TYPE: Type of object (PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, TYPE, TYPE BODY)
• LINE: Line number within the source code
• TEXT: Source code text for that line

Supported Object Types

PL/SQL Program Units

Standalone Objects

-- PROCEDURE: Standalone stored procedures
-- FUNCTION: Standalone stored functions
-- TRIGGER: Database triggers
-- TYPE: Object type definitions
-- TYPE BODY: Object type implementations

Package Components

-- PACKAGE: Package specifications
-- PACKAGE BODY: Package implementations
-- Contains procedures, functions, variables, cursors
-- Provides encapsulation and namespace management

Source Code Organization

Package Structure

-- Package specification shows:
-- Public procedure/function declarations
-- Public variable/constant declarations
-- Public type definitions
-- Public exception declarations

-- Package body shows:
-- Implementation of public procedures/functions
-- Private procedures/functions
-- Initialization sections
-- Exception handlers

Common Use Cases

1. Code Review and Analysis

   • Review PL/SQL code for quality
   • Analyze coding standards compliance
   • Identify performance opportunities
   • Document code functionality

2. Development and Debugging

   • View source code during development
   • Debug compilation errors
   • Analyze program logic
   • Trace execution flow

3. Migration and Deployment

   • Extract source code for migration
   • Compare code between environments
   • Document deployment artifacts
   • Backup source code

4. Security and Compliance

   • Review code for security vulnerabilities
   • Audit privileged operations
   • Document business logic
   • Compliance verification

Advanced Usage Examples

View Specific Object Type

-- View all procedures in HR schema:
Enter value for owner: HR
Enter value for name: %
Enter value for type: PROCEDURE

Find Objects with Specific Code Patterns

-- After running dsource, search output for patterns:
-- Look for hardcoded values
-- Find SQL injection vulnerabilities
-- Identify performance anti-patterns
-- Locate deprecated function usage

Compare Object Versions

-- Use for environment comparison:
-- Extract source from DEV environment
-- Extract same objects from PROD
-- Compare for differences
-- Document deployment requirements

Code Analysis Techniques

Security Review

Common Security Issues

-- Look for in source code:
-- Dynamic SQL without bind variables
-- Hardcoded passwords or credentials
-- Insufficient input validation
-- Excessive privilege usage
-- SQL injection vulnerabilities

Code Patterns to Review

-- Security concerns:
-- EXECUTE IMMEDIATE with concatenation
-- DBMS_SQL with user input
-- Hardcoded connection strings
-- Unvalidated input parameters
-- Overprivileged operations

Performance Analysis

Performance Anti-Patterns

-- Identify performance issues:
-- Cursor loops instead of set operations
-- Individual row processing
-- Excessive function calls in SQL
-- Inefficient exception handling
-- Poor SQL statement structure

Optimization Opportunities

-- Look for optimization chances:
-- Bulk collect operations
-- Set-based processing
-- Efficient cursor usage
-- Proper exception handling
-- Optimal SQL patterns

Development Workflow Integration

Code Extraction

Source Code Backup

-- Extract source for backup:
-- Run dsource with specific criteria
-- Spool output to files
-- Include in version control
-- Document code versions

Migration Support

-- For environment migration:
-- Extract all custom code
-- Generate CREATE statements
-- Include in deployment scripts
-- Verify compilation success

Documentation Generation

Automatic Documentation

-- Generate code documentation:
-- Extract procedure/function headers
-- Document parameters and return types
-- Create cross-reference reports
-- Build API documentation

Code Standards Verification

-- Check coding standards:
-- Naming conventions
-- Comment requirements
-- Error handling patterns
-- Performance guidelines

Troubleshooting Applications

Compilation Errors

Error Analysis

-- When objects are invalid:
-- View source code for syntax errors
-- Check for missing dependencies
-- Verify privilege requirements
-- Analyze compilation messages

Dependency Issues

-- For dependency problems:
-- Identify referenced objects
-- Check object existence
-- Verify privilege grants
-- Analyze compilation order

Runtime Issues

Logic Analysis

-- For runtime problems:
-- Review business logic
-- Trace execution paths
-- Identify potential issues
-- Analyze exception handling

Performance Problems

-- For performance issues:
-- Review SQL statements
-- Analyze loop structures
-- Check cursor usage
-- Identify bottlenecks

Integration with Development Tools

Version Control

Source Code Management

-- Version control integration:
-- Extract source periodically
-- Compare with version control
-- Identify unauthorized changes
-- Maintain code history

Change Tracking

-- Track code changes:
-- Compare source between dates
-- Identify modified procedures
-- Document change history
-- Support audit requirements

Code Quality Tools

Static Analysis

-- Code quality checks:
-- Extract source for analysis tools
-- Run quality metrics
-- Identify code smells
-- Generate quality reports

Compliance Scanning

-- Compliance verification:
-- Scan for security patterns
-- Check coding standards
-- Validate naming conventions
-- Verify documentation

Advanced Analysis

Cross-Reference Analysis

-- Find object dependencies:
SELECT DISTINCT ds1.owner, ds1.name, ds1.type
FROM dba_source ds1
WHERE UPPER(ds1.text) LIKE '%' || UPPER('&search_term') || '%'
ORDER BY ds1.owner, ds1.name, ds1.type;

Code Metrics

-- Calculate code complexity:
SELECT owner, name, type,
       COUNT(*) total_lines,
       SUM(CASE WHEN UPPER(text) LIKE '%IF%' OR
                     UPPER(text) LIKE '%LOOP%' OR
                     UPPER(text) LIKE '%CASE%'
                THEN 1 ELSE 0 END) complexity_indicators
FROM dba_source
WHERE owner NOT IN ('SYS','SYSTEM')
GROUP BY owner, name, type
ORDER BY complexity_indicators DESC;

Documentation Extraction

-- Extract comments and documentation:
SELECT owner, name, type, line, text
FROM dba_source
WHERE TRIM(text) LIKE '--%'
   OR TRIM(text) LIKE '/*%'
ORDER BY owner, name, type, line;

Best Practices

Code Review Process

Systematic Review

-- Establish review process:
-- Regular code extraction
-- Automated quality checks
-- Peer review procedures
-- Documentation updates

Quality Assurance

-- Maintain code quality:
-- Enforce coding standards
-- Regular security reviews
-- Performance assessments
-- Compliance verification

Documentation Maintenance

Code Documentation

-- Maintain documentation:
-- Update API documentation
-- Document business rules
-- Maintain change logs
-- Keep examples current

Knowledge Management

-- Knowledge preservation:
-- Document complex algorithms
-- Explain business logic
-- Maintain contact information
-- Preserve institutional knowledge

Related Scripts

• dinvalid.sql - Invalid objects analysis
• dobject.sql - Object listing and details
• derror.sql - Compilation errors
• compile_invalid.gen - Recompilation generator

Security Considerations

1. Sensitive Information

   • Review for hardcoded credentials
   • Check for embedded passwords
   • Identify security vulnerabilities
   • Protect source code access

2. Access Control

   • Limit source code access
   • Use appropriate privileges
   • Monitor code extraction
   • Audit source code access

3. Code Protection

   • Consider code obfuscation
   • Implement access logging
   • Protect intellectual property
   • Secure development practices