Information Technology: IBM interview questions

Information Technology: IBM interview questions

The UNIX School: Perl - What is __DATA__ ?

The UNIX School: Perl - What is __DATA__ ?

Oracle Optimizer

How do I display and read the execution plans for a SQL statement

Generating and displaying the execution plan of a SQL statement is a common task for most DBAs, SQL developers, and performance experts as it provides them information on the performance characteristics of a SQL statement. An execution plan shows the detailed steps necessary to execute a SQL statement. These steps are expressed as a set of database operators that consumes and produces rows. The order of the operators and their implantation is decided by the query optimizer using a combination of query transformations and physical optimization techniques.

While the display is commonly shown in a tabular format, the plan is in fact tree-shaped. For example, consider the following query based on the SH schema (Sales History):

select prod_category, avg(amount_sold)
from sales s, products p
where p.prod_id = s.prod_id
group by prod_category;

The tabular representation of this query's plan is:

------------------------------------------
 Id   Operation              Name   
------------------------------------------
   0  SELECT STATEMENT              
   1   HASH GROUP BY                
   2    HASH JOIN                   
   3     TABLE ACCESS FULL   PRODUCTS
   4     PARTITION RANGE ALL        
   5      TABLE ACCESS FULL  SALES  
------------------------------------------

While the tree-shaped representation of the plan is:

   GROUP BY
      |
     JOIN
 _____|_______
 |            |
ACCESS     ACCESS
(PRODUCTS) (SALES)

When you read a plan tree you should start from the bottom up. In the above example begin by looking at the access operators (or the leaves of the tree). In this case the access operators are implemented using full table scans. The rows produced by these tables scans will be consumed by the join operator. Here the join operator is a hash-join (other alternatives include nested-loop or sort-merge join). Finally the group-by operator implemented here using hash (alternative would be sort) consumes rows produced by the join-opertor.

The execution plan generated for a SQL statement is just one of the many alternative execution plans considered by the query optimizer. The query optimizer selects the execution plan with the lowest cost. Cost is a proxy for performance, the lower is the cost the better is the performance. The cost model used by the query optimizer accounts for the IO, CPU, and network usage in the query.

There are two different methods you can use to look at the execution plan of a SQL statement:

1. EXPLAIN PLAN command - This displays an execution plan for a SQL statement without actually executing the statement.


2. V$SQL_PLAN - A dictionary view introduced in Oracle 9i that shows the execution plan for a SQL statement that has been compiled into a cursor in the cursor cache.

Under certain conditions the plan shown when using EXPLAIN PLAN can be different from the plan shown using V$SQL_PLAN. For example, when the SQL statement contains bind variables the plan shown from using EXPLAIN PLAN ignores the bind variable values while the plan shown in V$SQL_PLAN takes the bind variable values into account in the plan generation process.

Displaying an execution plan has been made easier after the introduction of the dbms_xplan package in Oracle 9i and by the enhancements made to it in subsequent releases. This packages provides several PL/SQL procedures to display the plan from different sources:

1. EXPLAIN PLAN command


2. V$SQL_PLAN


3. Automatic Workload Repository (AWR)


4. SQL Tuning Set (STS)


5. SQL Plan Baseline (SPM)

The following examples illustrate how to generate and display an execution plan for our original SQL statement using the different functions provided in the dbms_xplan package.

Example 1 Uses the EXPLAIN PLAN command and the dbms_xplan.display function.

SQL> EXPLAIN PLAN FOR
 2   select prod_category, avg(amount_sold)
 3   from sales s, products p
 4   where p.prod_id = s.prod_id
 5   group by prod_category;

Explained.

SQL> select plan_table_output
 2    from table(dbms_xplan.display('plan_table',null,'basic'));

------------------------------------------
 Id   Operation              Name   
------------------------------------------
   0  SELECT STATEMENT              
   1   HASH GROUP BY                
   2    HASH JOIN                   
   3     TABLE ACCESS FULL   PRODUCTS
   4     PARTITION RANGE ALL        
   5      TABLE ACCESS FULL  SALES  
------------------------------------------

The arguments are for dbms_xplan.display are:

• plan table name (default 'PLAN_TABLE'),


• statement_id (default null),


• format (default 'TYPICAL')

More details can be found in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql.

Example 2 Generating and displaying the execution plan for the last SQL statement executed in a session:

SQL> select prod_category, avg(amount_sold)
 2   from sales s, products p
 3   where p.prod_id = s.prod_id
 4   group by prod_category;

no rows selected

SQL> select plan_table_output
 2    from table(dbms_xplan.display_cursor(null,null,'basic'));

------------------------------------------
 Id   Operation              Name   
------------------------------------------
   0  SELECT STATEMENT              
   1   HASH GROUP BY                
   2    HASH JOIN                   
   3     TABLE ACCESS FULL   PRODUCTS
   4     PARTITION RANGE ALL        
   5      TABLE ACCESS FULL  SALES  
------------------------------------------

The arguments used by dbms_xplay.dispay_cursor are:

• SQL ID (default null, null means the last SQL statement executed in this session),


• child number (default 0),


• format (default 'TYPICAL')

The details are in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql.


Example 3 Displaying the execution plan for any other statement requires the SQL ID to be provided, either directly or indirectly:

1. Directly:

   SQL> select plan_table_output from
    2   table(dbms_xplan.display_cursor('fnrtqw9c233tt',null,'basic'));

   
   
2. Indirectly:

   SQL> select plan_table_output
    2   from v$sql s,
    3   table(dbms_xplan.display_cursor(s.sql_id,
    4                                  s.child_number, 'basic')) t
    5   where s.sql_text like 'select PROD_CATEGORY%';

Example 4 - Displaying an execution plan corresponding to a SQL Plan Baseline. SQL Plan Baselines have been introduced in Oracle 11g to support the SQL Plan Management feature (SPM). In order to illustrate such a case we need to create a SQL Plan Baseline first.

SQL> alter session set optimizer_capture_sql_plan_baselines=true;

Session altered.

SQL> select prod_category, avg(amount_sold)
 2   from sales s, products p
 3   where p.prod_id = s.prod_id
 4   group by prod_category;

no rows selected

If the above statement has been executed more than once, a SQL Plan Baseline will be created for it and you can verified this using the follows query:

SQL> select SQL_HANDLE, PLAN_NAME, ACCEPTED
 2   from dba_sql_plan_baselines
 3   where sql_text like 'select prod_category%';

SQL_HANDLE                     PLAN_NAME                      ACC
------------------------------ ------------------------------ ---
SYS_SQL_1899bb9331ed7772       SYS_SQL_PLAN_31ed7772f2c7a4c2  YES

The execution plan for the SQL Plan Baseline created above can be displayed either directly or indirectly:

1. Directly
   

   select t.* from
   table(dbms_xplan.display_sql_plan_baseline('SYS_SQL_1899bb9331ed7772',
                                              format => 'basic')) t

   
   
2. Indirectly
   

   select t.*
       from (select distinct sql_handle
             from dba_sql_plan_baselines
             where sql_text like 'select prod_category%') pb,
            table(dbms_xplan.display_sql_plan_baseline(pb.sql_handle,
                                                       null,'basic')) t;

The output of either of these two statements is:

----------------------------------------------------------------------------
SQL handle: SYS_SQL_1899bb9331ed7772
SQL text: select prod_category, avg(amount_sold) from sales s, products p
          where p.prod_id = s.prod_id group by prod_category
----------------------------------------------------------------------------

----------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_31ed7772f2c7a4c2
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
----------------------------------------------------------------------------

Plan hash value: 4073170114

---------------------------------------------------------
 Id   Operation                 Name               
---------------------------------------------------------
   0  SELECT STATEMENT                             
   1   HASH GROUP BY                               
   2    HASH JOIN                                  
   3     VIEW                   index$_join$_002   
   4      HASH JOIN                                
   5       INDEX FAST FULL SCAN PRODUCTS_PK        
   6       INDEX FAST FULL SCAN PRODUCTS_PROD_CAT_IX
   7     PARTITION RANGE ALL                       
   8      TABLE ACCESS FULL     SALES              
---------------------------------------------------------

Formatting

The format argument is highly customizable and allows you to see as little (high-level) or as much (low-level) details as you need / want in the plan output. The high-level options are:

1. Basic
   The plan includes the operation, options, and the object name (table, index, MV, etc)
2. Typical
   It includes the information shown in BASIC plus additional optimizer-related internal information such as cost, size, cardinality, etc. These information are shown for every operation in the plan and represents what the optimizer thinks is the operation cost, the number of rows produced, etc. It also shows the predicates evaluation by the operation. There are two types of predicates: ACCESS and FILTER. The ACCESS predicates for an index are used to fetch the relevant blocks because they apply to the search columns. The FILTER predicates are evaluated after the blocks have been fetched.
3. All
   It includes the information shown in TYPICAL plus the lists of expressions (columns) produced by every operation, the hint alias and query block names where the operation belongs. The last two pieces of information can be used as arguments to add hints to the statement.

The low-level options allow the inclusion or exclusion of find details, such as predicates and cost.
For example,

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'basic +predicate +cost'));

-------------------------------------------------------
 Id   Operation              Name      Cost (%CPU)
-------------------------------------------------------
   0  SELECT STATEMENT                    17  (18)
   1   HASH GROUP BY                      17  (18)
*  2    HASH JOIN                         15   (7)
   3     TABLE ACCESS FULL   PRODUCTS      9   (0)
   4     PARTITION RANGE ALL               5   (0)
   5      TABLE ACCESS FULL  SALES         5   (0)
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="S"."PROD_ID")

select plan_table_output from
table(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));

----------------------------------------------------------------------------
 Id   Operation              Name      Rows  Time      Pstart Pstop
----------------------------------------------------------------------------
   0  SELECT STATEMENT                    4  00:00:01             
   1   HASH GROUP BY                      4  00:00:01             
*  2    HASH JOIN                       960  00:00:01             
   3     TABLE ACCESS FULL   PRODUCTS   766  00:00:01             
   4     PARTITION RANGE ALL            960  00:00:01      1     16
   5      TABLE ACCESS FULL  SALES      960  00:00:01      1     16
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="S"."PROD_ID")

Note Section

In addition to the plan, the package displays notes in the NOTE section, such as that dynamic sampling was used during query optimization or that star transformation was applied to the query.
For example, if the table SALES did not have statistics then the optimizer will use dynamic sampling and the plan display will report it as follows (see '+note' detail in the query):

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'basic +note'));

------------------------------------------
 Id   Operation              Name   
------------------------------------------
   0  SELECT STATEMENT              
   1   HASH GROUP BY                
   2    HASH JOIN                   
   3     TABLE ACCESS FULL   PRODUCTS
   4     PARTITION RANGE ALL        
   5      TABLE ACCESS FULL  SALES  
------------------------------------------

Note
-----
- dynamic sampling used for this statement

Bind peeking

The query optimizer takes into account the values of bind variable values when generation an execution plan. It does what is generally called bind peeking. See the first post in this blog about the concept of bind peeking and its impact on the plans and the performance of SQL statements.
As stated earlier the plan shown in V$SQL_PLAN takes into account the values of bind variables while the one shown from using EXPLAIN PLAN does not. Starting with 10gR2, the dbms_xplan package allows the display of the bind variable values used to generate a particular cursor/plan. This is done by adding '+peeked_binds' to the format argument when using display_cursor().
This is illustrated with the following example:

variable pcat varchar2(50)
exec :pcat := 'Women'

select PROD_CATEGORY, avg(amount_sold)
from sales s, products p
where p.PROD_ID = s.PROD_ID
and prod_category != :pcat
group by PROD_CATEGORY;

select plan_table_output
from table(dbms_xplan.display_cursor(null,null,'basic +PEEKED_BINDS'));

------------------------------------------
 Id   Operation              Name   
------------------------------------------
   0  SELECT STATEMENT              
   1   HASH GROUP BY                
   2    HASH JOIN                   
   3     TABLE ACCESS FULL   PRODUCTS
   4     PARTITION RANGE ALL        
   5      TABLE ACCESS FULL  SALES  
------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :PCAT (VARCHAR2(30), CSID=2): 'Women'

How do I display and read the execution plans for a SQL statement

Generating and displaying the execution plan of a SQL statement is a common task for most DBAs, SQL developers, and preformance experts as it provides them information on the performance characteristics of a SQL statement. An execution plan shows the detailed steps necessary to execute a SQL statement. These steps are expressed as a set of database operators that consumes and produces rows. The order of the operators and their implentation is decided by the query optimizer using a combination of query transformations and physical optimization techniques.

While the display is commonly shown in a tabular format, the plan is in fact tree-shaped. For example, consider the following query based on the SH schema (Sales History):

select prod_category, avg(amount_sold)
from sales s, products p
where p.prod_id = s.prod_id
group by prod_category;

The tabular representation of this query's plan is:

------------------------------------------
 Id   Operation              Name   
------------------------------------------
   0  SELECT STATEMENT              
   1   HASH GROUP BY                
   2    HASH JOIN                   
   3     TABLE ACCESS FULL   PRODUCTS
   4     PARTITION RANGE ALL        
   5      TABLE ACCESS FULL  SALES  
------------------------------------------

While the tree-shaped representation of the plan is:

   GROUP BY
      |
     JOIN
 _____|_______
 |            |
ACCESS     ACCESS
(PRODUCTS) (SALES)

When you read a plan tree you should start from the bottom up. In the above example begin by looking at the access operators (or the leaves of the tree). In this case the access operators are implemented using full table scans. The rows produced by these tables scans will be consumed by the join operator. Here the join operator is a hash-join (other alternatives include nested-loop or sort-merge join). Finally the group-by operator implemented here using hash (alternative would be sort) consumes rows produced by the join-opertor.

The execution plan generated for a SQL statement is just one of the many alternative execution plans considered by the query optimizer. The query optimizer selects the execution plan with the lowest cost. Cost is a proxy for performance, the lower is the cost the better is the performance. The cost model used by the query optimizer accounts for the IO, CPU, and network usage in the query.

There are two different methods you can use to look at the execution plan of a SQL statement:

1. EXPLAIN PLAN command - This displays an execution plan for a SQL statement without actually executing the statement.


2. V$SQL_PLAN - A dictionary view introduced in Oracle 9i that shows the execution plan for a SQL statement that has been compiled into a cursor in the cursor cache.

Under certain conditions the plan shown when using EXPLAIN PLAN can be different from the plan shown using V$SQL_PLAN. For example, when the SQL statement contains bind variables the plan shown from using EXPLAIN PLAN ignores the bind variable values while the plan shown in V$SQL_PLAN takes the bind variable values into account in the plan generation process.

Displaying an execution plan has been made easier after the introduction of the dbms_xplan package in Oracle 9i and by the enhancements made to it in subsequent releases. This packages provides several PL/SQL procedures to display the plan from different sources:

1. EXPLAIN PLAN command


2. V$SQL_PLAN


3. Automatic Workload Repository (AWR)


4. SQL Tuning Set (STS)


5. SQL Plan Baseline (SPM)

The following examples illustrate how to generate and display an execution plan for our original SQL statement using the different functions provided in the dbms_xplan package.

Example 1 Uses the EXPLAIN PLAN command and the dbms_xplan.display function.

SQL> EXPLAIN PLAN FOR
 2   select prod_category, avg(amount_sold)
 3   from sales s, products p
 4   where p.prod_id = s.prod_id
 5   group by prod_category;

Explained.

SQL> select plan_table_output
 2    from table(dbms_xplan.display('plan_table',null,'basic'));

------------------------------------------
 Id   Operation              Name   
------------------------------------------
   0  SELECT STATEMENT              
   1   HASH GROUP BY                
   2    HASH JOIN                   
   3     TABLE ACCESS FULL   PRODUCTS
   4     PARTITION RANGE ALL        
   5      TABLE ACCESS FULL  SALES  
------------------------------------------

The arguments are for dbms_xplan.display are:

• plan table name (default 'PLAN_TABLE'),


• statement_id (default null),


• format (default 'TYPICAL')

More details can be found in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql.

Example 2 Generating and displaying the execution plan for the last SQL statement executed in a session:

SQL> select prod_category, avg(amount_sold)
 2   from sales s, products p
 3   where p.prod_id = s.prod_id
 4   group by prod_category;

no rows selected

SQL> select plan_table_output
 2    from table(dbms_xplan.display_cursor(null,null,'basic'));

------------------------------------------
 Id   Operation              Name   
------------------------------------------
   0  SELECT STATEMENT              
   1   HASH GROUP BY                
   2    HASH JOIN                   
   3     TABLE ACCESS FULL   PRODUCTS
   4     PARTITION RANGE ALL        
   5      TABLE ACCESS FULL  SALES  
------------------------------------------

The arguments used by dbms_xplay.dispay_cursor are:

• SQL ID (default null, null means the last SQL statement executed in this session),


• child number (default 0),


• format (default 'TYPICAL')

The details are in $ORACLE_HOME/rdbms/admin/dbmsxpln.sql.


Example 3 Displaying the execution plan for any other statement requires the SQL ID to be provided, either directly or indirectly:

1. Directly:

   SQL> select plan_table_output from
    2   table(dbms_xplan.display_cursor('fnrtqw9c233tt',null,'basic'));

   
   
2. Indirectly:

   SQL> select plan_table_output
    2   from v$sql s,
    3   table(dbms_xplan.display_cursor(s.sql_id,
    4                                  s.child_number, 'basic')) t
    5   where s.sql_text like 'select PROD_CATEGORY%';

Example 4 - Displaying an execution plan corresponding to a SQL Plan Baseline. SQL Plan Baselines have been introduced in Oracle 11g to support the SQL Plan Management feature (SPM). In order to illustrate such a case we need to create a SQL Plan Baseline first.

SQL> alter session set optimizer_capture_sql_plan_baselines=true;

Session altered.

SQL> select prod_category, avg(amount_sold)
 2   from sales s, products p
 3   where p.prod_id = s.prod_id
 4   group by prod_category;

no rows selected

If the above statement has been executed more than once, a SQL Plan Baseline will be created for it and you can verified this using the follows query:

SQL> select SQL_HANDLE, PLAN_NAME, ACCEPTED
 2   from dba_sql_plan_baselines
 3   where sql_text like 'select prod_category%';

SQL_HANDLE                     PLAN_NAME                      ACC
------------------------------ ------------------------------ ---
SYS_SQL_1899bb9331ed7772       SYS_SQL_PLAN_31ed7772f2c7a4c2  YES

The execution plan for the SQL Plan Baseline created above can be displayed either directly or indirectly:

1. Directly
   

   select t.* from
   table(dbms_xplan.display_sql_plan_baseline('SYS_SQL_1899bb9331ed7772',
                                              format => 'basic')) t

   
   
2. Indirectly
   

   select t.*
       from (select distinct sql_handle
             from dba_sql_plan_baselines
             where sql_text like 'select prod_category%') pb,
            table(dbms_xplan.display_sql_plan_baseline(pb.sql_handle,
                                                       null,'basic')) t;

The output of either of these two statements is:

----------------------------------------------------------------------------
SQL handle: SYS_SQL_1899bb9331ed7772
SQL text: select prod_category, avg(amount_sold) from sales s, products p
          where p.prod_id = s.prod_id group by prod_category
----------------------------------------------------------------------------

----------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_31ed7772f2c7a4c2
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
----------------------------------------------------------------------------

Plan hash value: 4073170114

---------------------------------------------------------
 Id   Operation                 Name               
---------------------------------------------------------
   0  SELECT STATEMENT                             
   1   HASH GROUP BY                               
   2    HASH JOIN                                  
   3     VIEW                   index$_join$_002   
   4      HASH JOIN                                
   5       INDEX FAST FULL SCAN PRODUCTS_PK        
   6       INDEX FAST FULL SCAN PRODUCTS_PROD_CAT_IX
   7     PARTITION RANGE ALL                       
   8      TABLE ACCESS FULL     SALES              
---------------------------------------------------------

Formatting

The format argument is highly customizable and allows you to see as little (high-level) or as much (low-level) details as you need / want in the plan output. The high-level options are:

1. Basic
   The plan includes the operation, options, and the object name (table, index, MV, etc)
2. Typical
   It includes the information shown in BASIC plus additional optimizer-related internal information such as cost, size, cardinality, etc. These information are shown for every operation in the plan and represents what the optimizer thinks is the operation cost, the number of rows produced, etc. It also shows the predicates evaluation by the operation. There are two types of predicates: ACCESS and FILTER. The ACCESS predicates for an index are used to fetch the relevant blocks because they apply to the search columns. The FILTER predicates are evaluated after the blocks have been fetched.
3. All
   It includes the information shown in TYPICAL plus the lists of expressions (columns) produced by every operation, the hint alias and query block names where the operation belongs. The last two pieces of information can be used as arguments to add hints to the statement.

The low-level options allow the inclusion or exclusion of find details, such as predicates and cost.
For example,

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'basic +predicate +cost'));

-------------------------------------------------------
 Id   Operation              Name      Cost (%CPU)
-------------------------------------------------------
   0  SELECT STATEMENT                    17  (18)
   1   HASH GROUP BY                      17  (18)
*  2    HASH JOIN                         15   (7)
   3     TABLE ACCESS FULL   PRODUCTS      9   (0)
   4     PARTITION RANGE ALL               5   (0)
   5      TABLE ACCESS FULL  SALES         5   (0)
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="S"."PROD_ID")

select plan_table_output from
table(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));

----------------------------------------------------------------------------
 Id   Operation              Name      Rows  Time      Pstart Pstop
----------------------------------------------------------------------------
   0  SELECT STATEMENT                    4  00:00:01             
   1   HASH GROUP BY                      4  00:00:01             
*  2    HASH JOIN                       960  00:00:01             
   3     TABLE ACCESS FULL   PRODUCTS   766  00:00:01             
   4     PARTITION RANGE ALL            960  00:00:01      1     16
   5      TABLE ACCESS FULL  SALES      960  00:00:01      1     16
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P"."PROD_ID"="S"."PROD_ID")

Note Section

In addition to the plan, the package displays notes in the NOTE section, such as that dynamic sampling was used during query optimization or that star transformation was applied to the query.
For example, if the table SALES did not have statistics then the optimizer will use dynamic sampling and the plan display will report it as follows (see '+note' detail in the query):

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'basic +note'));

------------------------------------------
 Id   Operation              Name   
------------------------------------------
   0  SELECT STATEMENT              
   1   HASH GROUP BY                
   2    HASH JOIN                   
   3     TABLE ACCESS FULL   PRODUCTS
   4     PARTITION RANGE ALL        
   5      TABLE ACCESS FULL  SALES  
------------------------------------------

Note
-----
- dynamic sampling used for this statement

Bind peeking

The query optimizer takes into account the values of bind variable values when generation an execution plan. It does what is generally called bind peeking. See the first post in this blog about the concept of bind peeking and its impact on the plans and the performance of SQL statements.
As stated earlier the plan shown in V$SQL_PLAN takes into account the values of bind variables while the one shown from using EXPLAIN PLAN does not. Starting with 10gR2, the dbms_xplan package allows the display of the bind variable values used to generate a particular cursor/plan. This is done by adding '+peeked_binds' to the format argument when using display_cursor().
This is illustrated with the following example:

variable pcat varchar2(50)
exec :pcat := 'Women'

select PROD_CATEGORY, avg(amount_sold)
from sales s, products p
where p.PROD_ID = s.PROD_ID
and prod_category != :pcat
group by PROD_CATEGORY;

select plan_table_output
from table(dbms_xplan.display_cursor(null,null,'basic +PEEKED_BINDS'));

------------------------------------------
 Id   Operation              Name   
------------------------------------------
   0  SELECT STATEMENT              
   1   HASH GROUP BY                
   2    HASH JOIN                   
   3     TABLE ACCESS FULL   PRODUCTS
   4     PARTITION RANGE ALL        
   5      TABLE ACCESS FULL  SALES  
------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

1 - :PCAT (VARCHAR2(30), CSID=2): 'Women'

Generate Source Code Scripts of Database Objects

We use a lot of editors to display source code of Oracle Database objects like Toad, PLSQL Developer, Navigator and SQL Developer and others.

I have idea to do like this editors to generate source code of database objects (Table, View, Trigger, Functions, Package, Function, Procedure, ...... etc) using Oracle Database Data Dictionary views.

I can do this using using

1-ALL_SOURCE view which contains source code of package, package body, function, procedure, library, type, type body,java source only.

You can use others view to generate tables and indexes and constraint and database links and ..... etc, but at this post I only use ALL_SOURCE

2-DBMS_METADATA built-in package which contains procedure and functions that help me to get source code directly from database with less efforts.

I created SEKHAR_SOURCE_CODE package with below procedures to get source code of database objects

1-GET_SOURCE_CODE1 

   I use DBMS_METADATA.GET_DDL function 

2-GET_SOURCE_CODE2 

   I use DBMS_METADATA procedure and functions to get source code 

3-GET_SOURCE_CODE3 

   I use ALL_SOURCE view 

Sekhar_SOURCE_CODE package also containts

1- WRITE_CLOB_IN_FILE

    It saves CLOB variable in physical file at directory object

2-TO_CHAR

    It converts CLOB variable to VARCHAR2 variable

The code of SEKHAR_SOURCE_CODE package specification

 CREATE OR REPLACE PACKAGE SEKHAR_SOURCE_CODE  
  AUTHID CURRENT_USER  
 AS  

LSQL  
   GS$VC2MAXSIZE  CONSTANT NU
--MAX SIZE OF VARCHAR2 IN 
PMBER := 32767;  

   --WRITE CLOB FILE TO PHYSICAL FILE  

            IN_DIR_NAME   VARCHAR2,  
              
PROCEDURE WRITE_CLOB_IN_FILE (IN_CLOB     CLOB,  
     
     IN_FILE_NAME  VARCHAR2);  

   -- CONVERT CLOB TO VARCHAR2  
   FUNCTION TO_CHAR (IN_CLOB CLOB)  

RCE_CODE1 (IN_OBJECT_T
RETURN VARCHAR2;  

   --GET SOURCE CODE USING FUNCTION DBMS_METADATA.GET_DDL  
   FUNCTION GET_SO
UYPE  VARCHAR2,  
                IN_OBJECT_NAME  VARCHAR2,  
                IN_SCHEMA     VARCHAR2 DEFAULT USER)  

ECT_TYPE  VARCHAR2
RETURN CLOB;  

   --GET SOURCE CODE USING FUNCTION DBMS_METADATA BUILTINS  
   FUNCTION GET_SOURCE_CODE2 (IN_OB
J,  
                IN_OBJECT_NAME  VARCHAR2,  
                IN_SCHEMA     VARCHAR2 DEFAULT USER)  
    RETURN CLOB;  


JECT_NAME  VARCHAR2,  
                IN_SC
--GET SOURCE CODE USING ALL_SOURCE VIEW  
   FUNCTION GET_SOURCE_CODE3 (IN_OBJECT_TYPE  VARCHAR2,  
                IN_O
BHEMA     VARCHAR2 DEFAULT USER)  
    RETURN CLOB;  



END SEKHAR_SOURCE_CODE;  

The code of SEKHAR_SOURCE_CODE package body

 CREATE OR REPLACE PACKAGE BODY SEKHAR_SOURCE_CODE  
 AS  

   --WRITE CLOB FILE TO PHYSICAL FILE  

OB,  
                  IN_DIR_NAME   VARCHAR2,  
  
PROCEDURE WRITE_CLOB_IN_FILE (IN_CLOB     C
L                IN_FILE_NAME  VARCHAR2)  
   IS  
    LUTL$FILE_HANDLER  UTL_FILE.FILE_TYPE;  

L$FILE_HANDLER := UTL_FILE.FOPEN (IN_D
LN$OFFSET      NUMBER DEFAULT 1;  
    LC$BUFFER      VARCHAR2 (32767);  
   BEGIN  
    LU
TIR_NAME, IN_FILE_NAME, 'A');  
    LOOP  
      EXIT WHEN LN$OFFSET > DBMS_LOB.GETLENGTH (IN_CLOB);  

NDLER, LC$BUFFER);  
      LN$OFFSET := LN$OFFSET + 32767;  
    
LC$BUFFER := DBMS_LOB.SUBSTR (IN_CLOB, 32767, LN$OFFSET);  
      UTL_FILE.PUT_LINE (LUTL$FILE_H
A  LC$BUFFER := NULL;  
    END LOOP;  
    UTL_FILE.FCLOSE (LUTL$FILE_HANDLER);  
   END;  

   -- CONVERT CLOB TO VARCHAR2  
   FUNCTION TO_CHAR (IN_CLOB CLOB)  

TLENGTH (LCLOB$DUMY) 
RETURN VARCHAR2  
   IS  
    LC$RESULT  VARCHAR2 (32767);  
    LCLOB$DUMY  CLOB := EMPTY_CLOB ();  
   BEGIN  
    LCLOB$DUMY := IN_CLOB;  
    IF DBMS_LOB.G
E<= GS$VC2MAXSIZE  
    THEN  
      DBMS_LOB.OPEN (LCLOB$DUMY, DBMS_LOB.LOB_READONLY);  
      LC$RESULT := DBMS_LOB.SUBSTR (LCLOB$DUMY);  
      DBMS_LOB.CLOSE (LCLOB$DUMY);  

URCE CODE 
ELSE  
      RETURN 'You can''t convert CLOB to varchar2 because CLOB is bigger than maximum size of varchar2';  
    END IF;  
    RETURN LC$RESULT;  
   END;  

   --GET S
OUSING FUNCTION DBMS_METADATA.GET_DDL  
   FUNCTION GET_SOURCE_CODE1 (IN_OBJECT_TYPE  VARCHAR2,  
                IN_OBJECT_NAME  VARCHAR2,  

CLOB$SOURCE_CODE :=  
      DBMS_METADATA.GET_DDL (IN_
IN_SCHEMA     VARCHAR2 DEFAULT USER)  
    RETURN CLOB  
   IS  
    LCLOB$SOURCE_CODE  CLOB := EMPTY_CLOB;  
   BEGIN  
    
LOBJECT_TYPE, IN_OBJECT_NAME, IN_SCHEMA);  
    RETURN LCLOB$SOURCE_CODE;  
   END GET_SOURCE_CODE1;  

   --GET SOURCE CODE USING FUNCTION DBMS_METADATA BUILTINS  

RN CLOB  
   IS  
    LN$HANDLE      NUMBER;  
    LN$DUM
FUNCTION GET_SOURCE_CODE2 (IN_OBJECT_TYPE  VARCHAR2,  
                IN_OBJECT_NAME  VARCHAR2,  
                IN_SCHEMA     VARCHAR2 DEFAULT USER)  
    RET
UMY      NUMBER;  
    LCLOB$SOURCE_CODE  CLOB := EMPTY_CLOB;  
   BEGIN  
    LN$HANDLE := DBMS_METADATA.OPEN (IN_OBJECT_TYPE);  
    DBMS_METADATA.SET_FILTER (LN$HANDLE, 'SCHEMA', IN_SCHEMA);  

  
    DBMS_METADATA.CLOSE (LN$HANDLE);  
    RETURN LCLOB$SOURCE_C
DBMS_METADATA.SET_FILTER (LN$HANDLE, 'NAME', IN_OBJECT_NAME);  
    LN$DUMMY := DBMS_METADATA.ADD_TRANSFORM (LN$HANDLE, 'DDL');  
    LCLOB$SOURCE_CODE := DBMS_METADATA.FETCH_CLOB (LN$HANDLE)
;ODE;  
   EXCEPTION  
    WHEN OTHERS  
    THEN  
      DBMS_METADATA.CLOSE (LN$HANDLE);  
   END GET_SOURCE_CODE2; 
 
   --GET SOURCE CODE USING ALL_SOURCE VIEW  
   FUNCTION GET_SOURCE_CODE3 (IN_OBJECT_TYPE  VARCHAR2,  

TRUE;  
    CURSOR LCUR$SOURCE  
    IS  
 
IN_OBJECT_NAME  VARCHAR2,  
                IN_SCHEMA     VARCHAR2 DEFAULT USER)  
    RETURN CLOB  
   IS  
    LCLOB$SOURCE_CODE  CLOB;  
    LC$START_TEXT    VARCHAR2 (50);  
    LB$EXISTS      BOOLEAN :=
       SELECT TEXT  
        FROM ALL_SOURCE  
       WHERE   OWNER = IN_SCHEMA  
          AND NAME = IN_OBJECT_NAME  
          AND TYPE = IN_OBJECT_TYPE  
      ORDER BY LINE;  
   BEGIN  
    DBMS_LOB.CREATETEMPORARY (LCLOB$SOURCE_CODE, TRUE);  

GTH (LC$START_TEXT),  
            LC$START_TEXT);  
      DBMS_
DBMS_LOB.OPEN (LCLOB$SOURCE_CODE, DBMS_LOB.LOB_READWRITE);  
    FOR LREC$SOURCE IN LCUR$SOURCE  
    LOOP  
      LB$EXISTS := FALSE;  
      LC$START_TEXT := 'CREATE ';  
      DBMS_LOB.  
      WRITEAPPEND (LCLOB$SOURCE_CODE,  
            LE
NLOB.  
      WRITEAPPEND (LCLOB$SOURCE_CODE,  
            LENGTH (LREC$SOURCE.TEXT),  
            LREC$SOURCE.TEXT);  
    END LOOP;  
    IF LB$EXISTS  
    THEN  
      LC$START_TEXT := IN_OBJECT_TYPE || ' not support in ALL_SOURCE View ';  
      DBMS_LOB.  
      WRITEAPPEND (LCLOB$SOURCE_CODE,  


LENGTH (LC$START_TEXT),  
            LC$START_TEXT);  
    END IF;  
    DBMS_LOB.CLOSE (LCLOB$SOURCE_CODE);  
    RETURN LCLOB$SOURCE_CODE;  
   END GET_SOURCE_CODE3;  

 END SEKHAR_SOURCE_CODE;  


/  

Run SEKHAR_SOURCE_CODE package

I create WRITE_CLOB_IN_FILE procedure to write source code to physical file, the second parameter in this procedure is directory object name.

To create directory named SOURCE_CODE  use below command

 CREATE OR REPLACE DIRECTORY  

 SOURCE_CODE AS  

 'c:\temp\';  

We should grant read and write on directory to user who will use SEKHAR_SOURCE_CODE package by below command

 grant read,write on SOURCE_CODE to SCOTT;  

Now let's run below test script and see the result 

 DECLARE  

   LC$SOURCE_CODE  VARCHAR2 (32767);  
BEGIN  

t source code of TRIGGER MCIT_CMS.ADMIN_PROG_TRG in DBMS Output  
   LC$SO
--pri
nURCE_CODE :=  
    SEKHAR_SOURCE_CODE.  
    TO_CHAR (  

ER', 'ADMIN_PROG_TRG', 'MCIT
SEKHAR_SOURCE_CODE.  
      GET_SOURCE_CODE2 ('TRIG
G_CMS'));  
   DBMS_OUTPUT.PUT_LINE (LC$SOURCE_CODE);  

   --print script of TABLE  

_CODE.  
      GET_SOU
LC$SOURCE_CODE :=  
    SEKHAR_SOURCE_CODE.  
    TO_CHAR (  
      SEKHAR_SOURC
ERCE_CODE2 ('TABLE', 'ADMIN_PROGRAM', 'MCIT_CMS'));  
   DBMS_OUTPUT.PUT_LINE (LC$SOURCE_CODE);  


E (  
    SEKHAR_SOURCE_CODE.GET_SOURCE_CODE2 ('TAB
--Write Source code to $SOURCE_CODE\Sekhar.sql  
   SEKHAR_SOURCE_CODE.  
   WRITE_CLOB_IN_FI
LLE', 'EMP', 'SCOTT'),  
    'SOURCE_CODE',  
    'Sekhar.sqlL');  



END;  

The Result in DBMS Output It will create new file Sekhar.sql in c:\temp and it will print in DBMS Output below paragraph

 CREATE TRIGGER MCIT_CMS.ADMIN_PROG_TRG before insert on ADMIN_PROGRAM  
for each row  
 begin  

nextval into :new.PROGRAM_ID from dual;  
 end  
 CREATE TABLE MCIT_
select ADMIN_PROG_SEQ
.CMS.ADMIN_PROGRAM  
 (  
  PROGRAM_ID           NUMBER     NOT NULL,  

 VARCHAR2(20 BYTE),  
  PROGRAM_OUTBOUND_CD  
PROGRAM_NAME          VARCHAR2(200 BYTE),  
  PROGRAM_INBOUND_CD     
     VARCHAR2(20 BYTE),  
  COMM_DEPT_ID          NUMBER,  
  PROGRAM_OUTBOUND_BARCODE_TYPE VARCHAR2(2 BYTE),  


PROGRAM_INBOUND_BARCODE_TYPE  VARCHAR2(2 BYTE)  


)  

Please notice that I used in script SEKHAR_SOURCE_CODE.GET_SOURCE_CODE2 you can build your script and use any procedure from package to get your source code.

Thanks

Sekhar

We use a lot of editors to display source code of Oracle Database objects like Toad, PLSQL Developer, Navigator and SQL Developer and others.

I have idea to do like this editors to generate source code of database objects (Table, View, Trigger, Functions, Package, Function, Procedure, ...... etc) using Oracle Database Data Dictionary views.

I can do this using using

1-ALL_SOURCE view which contains source code of package, package body, function, procedure, library, type, type body,java source only.

You can use others view to generate tables and indexes and constraint and database links and ..... etc, but at this post I only use ALL_SOURCE

2-DBMS_METADATA built-in package which contains procedure and functions that help me to get source code directly from database with less efforts.

I created MAHMOUD_SOURCE_CODE package with below procedures to get source code of database objects

1-GET_SOURCE_CODE1 

   I use DBMS_METADATA.GET_DDL function 

2-GET_SOURCE_CODE2 

   I use DBMS_METADATA procedure and functions to get source code 

3-GET_SOURCE_CODE3 

   I use ALL_SOURCE view 

Mahmoud_SOURCE_CODE package also containts

1- WRITE_CLOB_IN_FILE

    It saves CLOB variable in physical file at directory object

2-TO_CHAR

    It converts CLOB variable to VARCHAR2 variable

The code of MAHMOUD_SOURCE_CODE package specification

 CREATE OR REPLACE PACKAGE MAHMOUD_SOURCE_CODE  
  AUTHID CURRENT_USER  
 AS  

LSQL  
   GS$VC2MAXSIZE  CONSTANT NU
--MAX SIZE OF VARCHAR2 IN 
PMBER := 32767;  

   --WRITE CLOB FILE TO PHYSICAL FILE  

            IN_DIR_NAME   VARCHAR2,  
              
PROCEDURE WRITE_CLOB_IN_FILE (IN_CLOB     CLOB,  
     
     IN_FILE_NAME  VARCHAR2);  

   -- CONVERT CLOB TO VARCHAR2  
   FUNCTION TO_CHAR (IN_CLOB CLOB)  

RCE_CODE1 (IN_OBJECT_T
RETURN VARCHAR2;  

   --GET SOURCE CODE USING FUNCTION DBMS_METADATA.GET_DDL  
   FUNCTION GET_SO
UYPE  VARCHAR2,  
                IN_OBJECT_NAME  VARCHAR2,  
                IN_SCHEMA     VARCHAR2 DEFAULT USER)  

ECT_TYPE  VARCHAR2
RETURN CLOB;  

   --GET SOURCE CODE USING FUNCTION DBMS_METADATA BUILTINS  
   FUNCTION GET_SOURCE_CODE2 (IN_OB
J,  
                IN_OBJECT_NAME  VARCHAR2,  
                IN_SCHEMA     VARCHAR2 DEFAULT USER)  
    RETURN CLOB;  


JECT_NAME  VARCHAR2,  
                IN_SC
--GET SOURCE CODE USING ALL_SOURCE VIEW  
   FUNCTION GET_SOURCE_CODE3 (IN_OBJECT_TYPE  VARCHAR2,  
                IN_O
BHEMA     VARCHAR2 DEFAULT USER)  
    RETURN CLOB;  



END MAHMOUD_SOURCE_CODE;  

The code of MAHMOUD_SOURCE_CODE package body

 CREATE OR REPLACE PACKAGE BODY MAHMOUD_SOURCE_CODE  
 AS  

   --WRITE CLOB FILE TO PHYSICAL FILE  

OB,  
                  IN_DIR_NAME   VARCHAR2,  
  
PROCEDURE WRITE_CLOB_IN_FILE (IN_CLOB     C
L                IN_FILE_NAME  VARCHAR2)  
   IS  
    LUTL$FILE_HANDLER  UTL_FILE.FILE_TYPE;  

L$FILE_HANDLER := UTL_FILE.FOPEN (IN_D
LN$OFFSET      NUMBER DEFAULT 1;  
    LC$BUFFER      VARCHAR2 (32767);  
   BEGIN  
    LU
TIR_NAME, IN_FILE_NAME, 'A');  
    LOOP  
      EXIT WHEN LN$OFFSET > DBMS_LOB.GETLENGTH (IN_CLOB);  

NDLER, LC$BUFFER);  
      LN$OFFSET := LN$OFFSET + 32767;  
    
LC$BUFFER := DBMS_LOB.SUBSTR (IN_CLOB, 32767, LN$OFFSET);  
      UTL_FILE.PUT_LINE (LUTL$FILE_H
A  LC$BUFFER := NULL;  
    END LOOP;  
    UTL_FILE.FCLOSE (LUTL$FILE_HANDLER);  
   END;  

   -- CONVERT CLOB TO VARCHAR2  
   FUNCTION TO_CHAR (IN_CLOB CLOB)  

TLENGTH (LCLOB$DUMY) 
RETURN VARCHAR2  
   IS  
    LC$RESULT  VARCHAR2 (32767);  
    LCLOB$DUMY  CLOB := EMPTY_CLOB ();  
   BEGIN  
    LCLOB$DUMY := IN_CLOB;  
    IF DBMS_LOB.G
E<= GS$VC2MAXSIZE  
    THEN  
      DBMS_LOB.OPEN (LCLOB$DUMY, DBMS_LOB.LOB_READONLY);  
      LC$RESULT := DBMS_LOB.SUBSTR (LCLOB$DUMY);  
      DBMS_LOB.CLOSE (LCLOB$DUMY);  

URCE CODE 
ELSE  
      RETURN 'You can''t convert CLOB to varchar2 because CLOB is bigger than maximum size of varchar2';  
    END IF;  
    RETURN LC$RESULT;  
   END;  

   --GET S
OUSING FUNCTION DBMS_METADATA.GET_DDL  
   FUNCTION GET_SOURCE_CODE1 (IN_OBJECT_TYPE  VARCHAR2,  
                IN_OBJECT_NAME  VARCHAR2,  

CLOB$SOURCE_CODE :=  
      DBMS_METADATA.GET_DDL (IN_
IN_SCHEMA     VARCHAR2 DEFAULT USER)  
    RETURN CLOB  
   IS  
    LCLOB$SOURCE_CODE  CLOB := EMPTY_CLOB;  
   BEGIN  
    
LOBJECT_TYPE, IN_OBJECT_NAME, IN_SCHEMA);  
    RETURN LCLOB$SOURCE_CODE;  
   END GET_SOURCE_CODE1;  

   --GET SOURCE CODE USING FUNCTION DBMS_METADATA BUILTINS  

RN CLOB  
   IS  
    LN$HANDLE      NUMBER;  
    LN$DUM
FUNCTION GET_SOURCE_CODE2 (IN_OBJECT_TYPE  VARCHAR2,  
                IN_OBJECT_NAME  VARCHAR2,  
                IN_SCHEMA     VARCHAR2 DEFAULT USER)  
    RET
UMY      NUMBER;  
    LCLOB$SOURCE_CODE  CLOB := EMPTY_CLOB;  
   BEGIN  
    LN$HANDLE := DBMS_METADATA.OPEN (IN_OBJECT_TYPE);  
    DBMS_METADATA.SET_FILTER (LN$HANDLE, 'SCHEMA', IN_SCHEMA);  

  
    DBMS_METADATA.CLOSE (LN$HANDLE);  
    RETURN LCLOB$SOURCE_C
DBMS_METADATA.SET_FILTER (LN$HANDLE, 'NAME', IN_OBJECT_NAME);  
    LN$DUMMY := DBMS_METADATA.ADD_TRANSFORM (LN$HANDLE, 'DDL');  
    LCLOB$SOURCE_CODE := DBMS_METADATA.FETCH_CLOB (LN$HANDLE)
;ODE;  
   EXCEPTION  
    WHEN OTHERS  
    THEN  
      DBMS_METADATA.CLOSE (LN$HANDLE);  
   END GET_SOURCE_CODE2; 
 
   --GET SOURCE CODE USING ALL_SOURCE VIEW  
   FUNCTION GET_SOURCE_CODE3 (IN_OBJECT_TYPE  VARCHAR2,  

TRUE;  
    CURSOR LCUR$SOURCE  
    IS  
 
IN_OBJECT_NAME  VARCHAR2,  
                IN_SCHEMA     VARCHAR2 DEFAULT USER)  
    RETURN CLOB  
   IS  
    LCLOB$SOURCE_CODE  CLOB;  
    LC$START_TEXT    VARCHAR2 (50);  
    LB$EXISTS      BOOLEAN :=
       SELECT TEXT  
        FROM ALL_SOURCE  
       WHERE   OWNER = IN_SCHEMA  
          AND NAME = IN_OBJECT_NAME  
          AND TYPE = IN_OBJECT_TYPE  
      ORDER BY LINE;  
   BEGIN  
    DBMS_LOB.CREATETEMPORARY (LCLOB$SOURCE_CODE, TRUE);  

GTH (LC$START_TEXT),  
            LC$START_TEXT);  
      DBMS_
DBMS_LOB.OPEN (LCLOB$SOURCE_CODE, DBMS_LOB.LOB_READWRITE);  
    FOR LREC$SOURCE IN LCUR$SOURCE  
    LOOP  
      LB$EXISTS := FALSE;  
      LC$START_TEXT := 'CREATE ';  
      DBMS_LOB.  
      WRITEAPPEND (LCLOB$SOURCE_CODE,  
            LE
NLOB.  
      WRITEAPPEND (LCLOB$SOURCE_CODE,  
            LENGTH (LREC$SOURCE.TEXT),  
            LREC$SOURCE.TEXT);  
    END LOOP;  
    IF LB$EXISTS  
    THEN  
      LC$START_TEXT := IN_OBJECT_TYPE || ' not support in ALL_SOURCE View ';  
      DBMS_LOB.  
      WRITEAPPEND (LCLOB$SOURCE_CODE,  


LENGTH (LC$START_TEXT),  
            LC$START_TEXT);  
    END IF;  
    DBMS_LOB.CLOSE (LCLOB$SOURCE_CODE);  
    RETURN LCLOB$SOURCE_CODE;  
   END GET_SOURCE_CODE3;  

 END MAHMOUD_SOURCE_CODE;  


/  

Run MAHMOUD_SOURCE_CODE package

I create WRITE_CLOB_IN_FILE procedure to write source code to physical file, the second parameter in this procedure is directory object name.

To create directory named SOURCE_CODE  use below command

 CREATE OR REPLACE DIRECTORY  

 SOURCE_CODE AS  

 'c:\temp\';  

We should grant read and write on directory to user who will use MAHMOUD_SOURCE_CODE package by below command

 grant read,write on SOURCE_CODE to SCOTT;  

Now let's run below test script and see the result 

 DECLARE  

   LC$SOURCE_CODE  VARCHAR2 (32767);  
BEGIN  

t source code of TRIGGER MCIT_CMS.ADMIN_PROG_TRG in DBMS Output  
   LC$SO
--pri
nURCE_CODE :=  
    SEKHAR_SOURCE_CODE.  
    TO_CHAR (  

ER', 'ADMIN_PROG_TRG', 'MCIT
SEKHAR_SOURCE_CODE.  
      GET_SOURCE_CODE2 ('TRIG
G_CMS'));  
   DBMS_OUTPUT.PUT_LINE (LC$SOURCE_CODE);  

   --print script of TABLE  





_CODE.  
      GET_SOU
LC$SOURCE_CODE :=  
    SEKHAR_SOURCE_CODE.  
    TO_CHAR (  
      SEKHAR_SOURC
ERCE_CODE2 ('TABLE', 'ADMIN_PROGRAM', 'MCIT_CMS'));  
   DBMS_OUTPUT.PUT_LINE (LC$SOURCE_CODE);  


E (  
    SEKHAR_SOURCE_CODE.GET_SOURCE_CODE2 ('TAB
--Write Source code to $SOURCE_CODE\Sekhar.sql  
   MAHMOUD_SOURCE_CODE.  
   WRITE_CLOB_IN_FI
LLE', 'EMP', 'SCOTT'),  
    'SOURCE_CODE',  
    'Sekhar.sqlL');  



END;  

The Result in DBMS Output It will create new file Sekhar.sql in c:\temp and it will print in DBMS Output below paragraph

 CREATE TRIGGER MCIT_CMS.ADMIN_PROG_TRG before insert on ADMIN_PROGRAM  
for each row  
 begin  

nextval into :new.PROGRAM_ID from dual;  
 end  
 CREATE TABLE MCIT_
select ADMIN_PROG_SEQ
.CMS.ADMIN_PROGRAM  
 (  
  PROGRAM_ID           NUMBER     NOT NULL,  

 VARCHAR2(20 BYTE),  
  PROGRAM_OUTBOUND_CD  
PROGRAM_NAME          VARCHAR2(200 BYTE),  
  PROGRAM_INBOUND_CD     
     VARCHAR2(20 BYTE),  
  COMM_DEPT_ID          NUMBER,  
  PROGRAM_OUTBOUND_BARCODE_TYPE VARCHAR2(2 BYTE),  


PROGRAM_INBOUND_BARCODE_TYPE  VARCHAR2(2 BYTE)  


)  

Please notice that I used in script Sekhar_SOURCE_CODE.GET_SOURCE_CODE2 you can build your script and use any procedure from package to get your source code.

Thanks

Sekhar

PL/SQL : Pragma Restrict References

Introduction 

In my packages functions and procedure, sometimes I have some rules to control purity rules in my database like reading from tables only, not query the database, ...... etc 

So I will  use PRAGMA RESTRICT_REFERENCES to control and preserve the database state in database packages.

Syntax of PRAGMA RESTRICT_REFERENCES

PRAGMA RESTRICT_REFERENCES(SUBPROGRAM_NAME, IDENTIFIER)

SUBPROGRAM_NAME can be default which will affect all the subprograms in packages or subprogram name(function or procedure) in package

IDENTIFIER can be RNDS, WNDS, RNPS, WNPS and TRUST. I will explain every one separately.

1- RNDS

select query is restricted against any of database tables

2- WNDS

DML operations are restricted against any of database tables 

3- RNPS

Selection of package variables is restricted 

4- WNPS

Modification in packages variables is restricted

5- TRUST

Asserts that the subprogram can be trusted not to violate one or more rules

Demo

Test WNPS 

let's create packages XXX_PKG contains GN$USER package variables and SET_USER function to set GN$USER and I will use WNPS purity check in SET_USER function

 CREATE OR REPLACE PACKAGE XXX_PKG  
 AS  
   GN$USER  VARCHAR2 (100);  


PROCEDURE SET_USER;  
   

   PRAGMA RESTRICT_REFERENCES (SET_USER, WNPS);  

 END;  

ATE OR REPLACE PACKAGE BODY XXX_PKG  
 AS
CR
E  
   PROCEDURE SET_USER  
   IS  

. El-Sayed
BEGIN  
    GN$USER := 'Mahmoud 
A';  
   END;  


END;  

When compile the package body compiler will raise the below error

PLS-00452: Subprogram 'SET_USER' violates its associated pragma

Test RNDS

let's create packages XXX_PKG contains GET_EMPLOYEE_NAME function which query from EMP database table.

 CREATE OR REPLACE PACKAGE XXX_PKG  
 AS  

CTION GET_EMPLOYEE_NAME (IN_EMPLOYEE_ID NUMBER)  
    R
FU
NETURN VARCHAR2;  
   

PRAGMA RESTRICT_REFERENCES (GET_EMPLOYEE_NAME, RNDS);  

END;  


TE OR REPLACE PACKAGE BODY XXX_PKG  
 AS 
CRE
A 

FUNCTION GET_EMPLOYEE_NAME (IN_EMPLOYEE_ID NUMBER)  
  
RETURN VARCHAR2  
   IS  

2 (100);  
   BEGIN  
    SELECT ENAME 
LV$EMPLOYEE_NAME  VARCHA
R 
     INTO LV$EMPLOYEE_NAME  
     FROM EMP  

URN LV$EMPLOYEE_NAME;  
   END;  
 E
WHERE EMPNO = IN_EMPLOYEE_ID;  
   
    RE

TND;  

When compile the package body compiler will raise the below error

PLS-00452: Subprogram 'GET_EMPLOYEE_NAME' violates its associated pragma

You can test the others purity checks, Just I made two examples to illustrate the idea.

Thanks

Sekhar