Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Dynamic Code Generation
Oracle Tips by Burleson
 

One common use of SQL scripts is the generation of other SQL scripts. Consider the script shown in Listing 3.2, which dynamically builds and executes an SQL script containing commands to drop all the objects in the specified user’s schema.

Listing 3.2 The DROP_ALL.SQL script.

set head off
set pages 0
set verify off
set lines 80
set feedback off
set termout off
 
spool &&1.sql
 
SELECT 'DROP ' || object_type || ' ' || &&1 || '.' ||
       object_name || ';'
FROM   ALL_OBJECTS
WHERE  owner = upper ('&&1')
ORDER BY object_type desc;
 
spool off
 
@&&1.sql
 
host rm &&1.sql
 
exit

When run for the jschmoe schema (assuming that the person running the script has access to the schema), this script would generate and execute an SQL script containing the following SQL commands:

DROP TABLE JSCHMOE.STUDENTS;
DROP TABLE JSCHMOE.ENROLLED_COURSES;
DROP TABLE JSCHMOE.STUDENT_FINANCIAL_AID;
DROP PROCEDURE JSCHMOE.CALCULATE_GPA;
DROP PACKAGE BODY JSCHMOE.STUDENT_UPDATES;
DROP PACKAGE JSCHMOE.STUDENT_UPDATES;
DROP FUNCTION JSCHMOE.GRANT_FINANCIAL_AID;
 
Oracle SQL Reporting

Before reporting tools were available for Oracle, reports were written using SQL*Plus. A simple report could take a full day to write; complex reports could take a week or more. SQL*Plus includes some very robust controls that support the development of reports, even though most reports are now generated using Oracle Reports or other reporting tools. The script shown in Listing 3.5 generates a report about the code stored in an Oracle data dictionary.

Listing 3.5 An SQL report on code stored in the data dictionary.

clear computes
clear breaks

compute avg of length on type
break on type report skip page

spool code_rep.txt

SELECT type, name, max (line) “length”
FROM   ALL_SOURCE A
WHERE  A.owner = upper ('&&1')
AND    A.line = (SELECT max (B.line)
                FROM   ALL_SOURCE B
                WHERE  A.owner = B.owner
                AND    A.name  = B.name
                AND    A.type  = B.type)
GROUP BY type, name;

spool off

EXIT

This report computes the average number of lines for each type of stored PL/SQL object. By calculating the average number of lines for each type of object, the report (theoretically) indicates the level of modularity in the code (the lower the average number of lines, the more modular the code). The output of the script is shown in Figure 3.1.

 


This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter