Home arrow Oracle arrow Page 3 - Working With PL/SQL Code

Working with Postprocessed Code - Oracle

In this sixth part of a nine-part series on managing PL/SQL code, you will learn how to synchronize code with packaged constants, how to work with post-processed code, and more. This article is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O'Reilly; ISBN: 0596009771). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

TABLE OF CONTENTS:
  1. Working With PL/SQL Code
  2. Synchronizing Code with Packaged Constants
  3. Working with Postprocessed Code
  4. Testing PL/SQL Programs
By: O'Reilly Media
Rating: starstarstarstarstar / 9
November 29, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

You can use the DBMS_PREPROCESSOR package to display or retrieve the source text of your program in its postprocessed form. DBMS_PREPROCESSOR offers two programs, overloaded to allow you to specify the object of interest in various ways, as well as to work with individual strings and collections:

DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE

Retrieves the postprocessed source and then displays it with the function DBMS_OUTPUT.PUTLINE.

DBMS_PREPROCESSOR.GET_POST_PROCESSED_SOURCE

Returns the postprocessed source as either a single string or a collection of strings.

When working with the collection version of either of these programs, you will need to declare that collection based on the following package-defined collection:

  TYPE DBMS_PREPROCESSOR.source_lines_t IS TABLE OF VARCHAR2(32767)
     INDEX BY BINARY_INTEGER;

The following sequence demonstrates the capability of these programs. I compile a very small program with a selection directive based on the optimization level. I then display the postprocessed code, and it shows the correct branch of the $IF statement.

  /* File on web: cc_postprocessor.sql  
  CREATE OR REPLACE PROCEDURE post_processed
  IS
  BEGIN
  $IF $$PLSQL_OPTIMIZE_LEVEL = 1
  $THEN 
     
-- Slow and easy
   
NULL;
  $ELSE
    
-- Fast and modern and easy
    
NULL;
 
$END

  END post_processed;
  /

  SQL> BEGIN
   
2     
DBMS_PREPROCESSOR. PRINT_POST_PROCESSED_SOURCE (
   3         
'PROCEDURE', USER, 'POST_PROCESSED');
   4  END;
   5
  /

  PROCEDURE post_processed
  IS
  BEGIN
  -- Fast and modern and easy
  NULL;
  END post_processed;

In the following block, I use the “get” function to retrieve the postprocessed code, and then display it using DBMS_OUTPUT.PUT_LINE:

  DECLARE
     l_postproc_code      DBMS_PREPROCESSOR. SOURCE_LINES_T;
     l_row                PLS_INTEGER;
  BEGIN
    
l_postproc_code := 
   DBMS_PREPROCESSOR.GET_POST_PROCESSED_SOURCE (
      'PROCEDURE', USER, 'POST_PROCESSED');
     l_row := l_postproc_code.FIRST;

     WHILE (l_row IS NOT NULL)
     LOOP
       
DBMS_OUTPUT.put_line ( LPAD (l_row, 3)
                             || ' - '
                             || rtrim ( l_postproc_code (l_row),chr(10))
                           
);
        l_row := l_postproc_code.NEXT (l_row);
     END LOOP;
  END;
  /

Conditional compilation opens up all sorts of possibilities for PL/SQL developers and application administrators. And its usefulness only increases as new versions of Oracle are released and the DBMS_DB_VERSION constants can be put to full use, allowing us to take full advantage of each version’s unique PL/SQL features.



 
 
>>> More Oracle Articles          >>> More By O'Reilly Media
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: