introducing the oracle database 11 g sql and pl/sql new features

1Copyright © 2007, Oracle. All rights reserved.

Introducing the Oracle Database 11g SQL and PL/SQL New Features

Copyright © 2007, Oracle. All rights reserved.1 - 2

Objectives

After completing this lesson, you should be able to:

• Describe the organization of the course

• Review the schemas that are used in this course

• Review the SQL*Plus environment that you can optionally use in this course

• Find additional information about Oracle Database 11g on the Oracle Technology Network


Course Objectives

After completing this course, you should be able to:

• Use the SQL Developer interface with the latest enhancements

• Write SQL statements that include the new functions added to enhance regular expression support functionality

• Monitor dependency tracking and change notification

• List the changes to locking that enable you to specify the maximum number of seconds the statement should wait to obtain a DML lock on the table

• Practice the performance improvements


Course Objectives

• Use the enhancements added to native dynamic SQL and to DBMS_SQL, which enable more interoperability between the two methodologies

• Write compound triggers and use the enhancements made to the triggers

• Use SecureFile LOBS

• Write SQL and PL/SQL calls to sequences that are simpler

• Use the new CONTINUE statement to control loops

• Explore the data warehousing improvements


Course Agenda

Day 1:

• Introducing Oracle Database 11g SQL and PL/SQL enhancements

• Using the SQL Developer enhancements

• Using the language functionality enhancements

• Executing dynamic SQL in PL/SQL with the 11g enhancements

• Implementing the performance improvements


Course Agenda

Day 2:

• Practicing the language usability enhancements

• Developing triggers that utilize the new enhancements

• Administering SecureFile LOBs

• Using the data warehousing usability enhancements


Lesson Agenda

• Appendixes and tables used in this course

• Overview of SQL*Plus

• Oracle Database 11g documentation and additional resources


Appendixes Used in This Course

• Appendix A: Practice Solutions

• Appendix B: Table Descriptions

• Appendix C: Using Oracle SQL Developer

• Appendix D: SQL*Plus

• Appendix E: Working with Collections

• Appendix F: Exploring the Data Warehousing Performance Enhancements


Tables Used in This Course

The sample schemas that are used in this course are:

• The Order Entry (OE) schema

• The Sales History (SH) schema


Order Entry (OE) Schema

WAREHOUSESwarehouse_id

warehouse_namelocation_id

ORDERSorder_id

order_dateorder_modecustomer_idorder_statusorder_total

sales_rep_idpromotion_id

ORDER_ITEMSorder_id

line_item_idproduct_idunit_pricequantity

PRODUCT_INFORMATION

product_idproduct_name

product_descriptioncategory_id

weight_classwarranty_period

supplier_idproduct_status

list_pricemin_price

catalog_url

CUSTOMERScustomer_id

cust_first_namecust_ last_name

cust_ address_typ

phone_numbersnls_language

nls_territorycredit_limitcust_ email

account_mgr_iddate_of_birthmarital_status

genderIncome_level

street_addresspostal_code

citystate_province

country_id

PRODUCT_DESCRIPTIONS

product_idlanguage_idproduct_name

product_description

INVENTORIESproduct_id

warehouse_idquantity_on_hand


Sales History (SH) Schema

COSTSprod_idtime_id

promo_idchannel_id

unit_costunit_price

PROMOTIONSpromo_id

promo_namepromo_subcategory

promo_subcategory_idpromo_category

promo_category_idpromo_cost

promo_begin_datepromo_end_date

promo_totalpromo_total_id

SALESprod_idcust_idtime_id

channel_idpromo_id

quantity_soldamount_sold

CHANNELSchannel_id

channel_descchannel_class

channel_class_idchannel_total

channel_total_id

TIMEStime_id

day_nameday_number_in_weekday_number_in_monthcalendar_week_number

fiscal_week_numberweek_ending_day

week_ending_day_idcalendar_month_number

fiscal_month_numbercalendar_month_desc

calendar_month_idfiscal_month_id

days_in_cal_monthdays_in_fis_monthend_of_cal_ monthend_of_fis_month

calendar _month _namefiscal _month _name

calendar _quarter _desccalendar_quarter_idfiscal _quarter _desc

fiscal _quarter _iddays_in_cal_quarterdays_in_fis_quarterend_of_cal_quarterend_of_fis_quarter

calendar_quarter_numberfiscal_quarter_number

calendar_yearcalendar_year_id

fiscal_yearfiscal_year_id

days_in_cal_yeardays_in_fis_yearend_of_cal_yearend_of_fis_year

PRODUCTS


Sales History (SH) Schema

PRODUCTSprod_id

prod_nameprod_desc

prod_subcategoryprod_subcategory_id

prod_subcategory_descprod_category

prod_category_idprod_category_descprod_weight_class

prod_unit_of_measureprod_pack_size

supplier_idprod_status

prod_list_priceprod_min_price

prod_totalprod_total_idprod_src_id

prod_eff_fromprod_eff_toprod_valid

CUSTOMERScust_id

cust_first_namecust_last_name

cust_gendercust_year_of_birthcust_marital_statuscust_street_address

cust_postal_codecust_city

cust_city_idcust_state_province

cust_state_province_idcountry_id

cust_main_phone_numbercust_income_levelcust_credit_limit

cust_emailcust_total

cust_total_idcust_src_id

cust_eff_fromcust_eff_tocust_valid

COUNTRIEScountry_id

country_iso_codecountry_name

country_subregioncountry_subregion_id

country_regioncountry_region_id

country_totalcountry_total_id

Country_name_hist

COSTS SALESSALES


Class Account Information

• Cloned OE account IDs are set up for you.

• Your account IDs are OE1 – OE20.

• The password matches your account ID.

• Each machine is assigned one account.

• All OE account IDs have SELECT status on the SH schema.

• The instructor has a separate ID.


Lesson Agenda





Overview of SQL*Plus Used in This Course

• Logging in to SQL*Plus

• Describing the table structure

• Executing SQL from SQL*Plus

• Reviewing SQL*Plus file commands


sqlplus [username[/password[@database]]]

Logging In to SQL*Plus

1

2


Displaying Table Structure

DESCRIBE sh.customers Name Null? Type ------------------------------- -------- ----------------- CUST_ID NOT NULL NUMBER CUST_FIRST_NAME NOT NULL VARCHAR2(20) CUST_LAST_NAME NOT NULL VARCHAR2(40) CUST_GENDER NOT NULL CHAR(1) CUST_YEAR_OF_BIRTH NOT NULL NUMBER(4) CUST_MARITAL_STATUS VARCHAR2(20) CUST_STREET_ADDRESS NOT NULL VARCHAR2(40) CUST_POSTAL_CODE NOT NULL VARCHAR2(10) CUST_CITY NOT NULL VARCHAR2(30) CUST_CITY_ID NOT NULL NUMBER CUST_STATE_PROVINCE NOT NULL VARCHAR2(40) CUST_STATE_PROVINCE_ID NOT NULL NUMBER COUNTRY_ID NOT NULL NUMBER CUST_MAIN_PHONE_NUMBER NOT NULL VARCHAR2(25) CUST_INCOME_LEVEL VARCHAR2(30) CUST_CREDIT_LIMIT NUMBER CUST_EMAIL VARCHAR2(30) CUST_TOTAL NOT NULL VARCHAR2(14) CUST_TOTAL_ID NOT NULL NUMBER ...


Executing SQL from SQL*Plus

CUST_LAST_NAME G INCOME_LEVEL-------------------- - --------------------Kinski M D: 70,000 - 89,999Garcia F I: 170,000 - 189,999Olin F F: 110,000 - 129,999Altman F F: 110,000 - 129,999de Funes F D: 70,000 - 89,999Chapman F F: 110,000 - 129,999Gielgud F E: 90,000 - 109,999Prashant F C: 50,000 - 69,999Welles M D: 70,000 - 89,999Rampling M F: 110,000 - 129,999...319 rows selected.

SELECT cust_last_name, cust_gender, cust_income_levelFROM sh.customers;


SQL*Plus File Commands

• SAVE filename• GET filename• START filename• @filename• EDIT filename• SPOOL filename• EXIT


Lesson Agenda



• Overview of Oracle SQL Developer



Oracle Database 11g SQL and PL/SQL Documentation

Navigate to http://www.oracle.com/pls/db111/homepage, then click the Books tab:

• Oracle Database Advanced Application Developer’s Guide 11g, Release 1 (11.1)

• Oracle Database Concepts 11g, Release 1 (11.1)

• Oracle Database 2 Day Developer’s Guide 11g, Release 1 (11.1)

• Oracle Database Security Guide 11g, Release 1 (11.1)


Oracle Database 11g SQL and PL/SQL Documentation

• Oracle Database SQL Language Reference 11g, Release 1

• Oracle Database PL/SQL Language Reference 11g, Release 1

• Oracle Database PL/SQL Packages and Types Reference 11g, Release 1

• Oracle Database Large Objects Developer’s Guide

• SQL*Plus User’s Guide and Reference

• Oracle Database SQL Developer User’s Guide, Release 1.2


Additional Resources

For additional information about the new Oracle 11g SQL and PL/SQL new features, refer to the following:

• Oracle Database 11g: New Features eStudies

• Oracle by Example series (OBE): Oracle Database 11g– http://www.oracle.com/technology/obe/11gr1_db/admin/

11gr1db.html

• What’s New in PL/SQL in Oracle Database 11g on the Oracle Technology Network (OTN):

– http://www.oracle.com/technology/tech/pl_sql/


Summary

In this lesson, you should have learned how to:

• Describe the organization of the course

• Review the schemas that are used in this course

• Review the SQL*Plus environment that you can optionally use in this course

• Find additional information about Oracle Database 11g from the Oracle Technology Network


Practice 1 Overview: Getting Started

This practice covers the following topics:

• Reviewing the schemas for this course

• Using SQL*Plus

• Accessing Oracle Database 11g resources


Using SQL Developer


Objectives

After completing this lesson, you should be able to:• List the key features of Oracle SQL Developer• Install Oracle SQL Developer• Create a database connection• Navigate through the object navigator• Use the SQL Worksheet• Create, save, and use scripts• Develop, compile, and debug PL/SQL• Browse through the available search engines• Change preferences• Create reports• Describe migration


What Is Oracle SQL Developer?

• Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks.

• You can connect to any target Oracle database schema using standard Oracle database authentication.

SQL Developer


Installing SQL Developer

Download the Oracle SQL Developer kit and unzip it into any directory on your machine.


Menus for SQL Developer

1

2

3

4

5

6

7


Creating a Database Connection

• You must have at least one database connection to use SQL Developer.

• You can create and test connections for:– Multiple databases– Multiple schemas

• SQL Developer automatically reads any connections defined in the tnsnames.ora file on your system.

• You can export connections to an XML file.• Each additional database connection created is listed

in the Connections navigator hierarchy.


Creating a Database Connection

1

2

3

4


Browsing Database Objects

Use the Database navigator to:• Browse through many objects in a database schema• Review the definitions of objects at a glance


Exporting Database Objects

Enter the file name destination, and select the Connection.

Select the Objects to export. Click Apply.


Exporting Database Objects

The resulting file contains the object definitions you exported.


Exporting and Importing Data


Using SQL Worksheet

• Use SQL Worksheet to enter and execute SQL, PL/SQL, and SQL*Plus statements.

• Specify any actions that can be processed by the database connection associated with the worksheet.


Using SQL Worksheet

1

2

3

4

5

6

79

8


Executing SQL Statements

Use the Enter SQL Statement box to enter single or multiple SQL statements.

View the results on the Script Output tabbed page.


Saving SQL Scripts

Click the Save icon to save your SQL statement to a file.

The contents of the saved file are visible and editable in your SQL Worksheet window.

Enter a file name and identify a location to save the file in the Windows Save dialog box.


Executing Saved SQL Scripts

Use the @ command followed by the location and name of the file you want to execute. Then click the Run Script icon.

The output from the script is displayed on the Script Output tabbed page.


Using PL/SQL in SQL Developer

Right-click the Procedures node and select New Procedure.



1 2 3 4 5

Enter the header information for the procedure, and then click OK.

Enter your code.



Click Compile.

Compilation messages are displayed on Messages – Log.



Click Run.

The Run dialog box appears with a call to your code wrapped within an anonymous block. Enter the parameter values, and then click OK.



Enter the parameter values, and then click OK.

The results are displayed on the Running – Log tabbed page.


Browsing Through the Available Search Engines

Select a search engine.

Enter a search word, and then press [Enter].

The results are displayed in your browser.


Changing Preferences

From the Tools menu, select Preferences. The Preferences dialog box appears.


Creating Reports

• SQL Developer provides you with a number of predefined reports about your database and objects.

• The reports are organized into categories.

• You can create your own customized reports too.


Creating Reports


Creating PL/SQL Reports

Select a connection, and then click OK.

Select the PL/SQL report type.


Searching PL/SQL Code

Specify either an Object Name search or a Text Search.

Enter the value to search and click Apply.

The results are displayed.


Creating a User-Defined Report

Create and save user-defined reports for repeated use.

Organize reports in folders


Using SQL*Plus

• You can invoke the SQL*Plus command-line interface from SQL Developer.

• Close all SQL Worksheets to enable the SQL*Plus menu option.

Provide the location of the sqlplus.exe

file only for the first time you invoke SQL*Plus.


Introducing Migration Through SQL Developer

• Reduces the effort and risks involved in a migration project

• Enables you to migrate an entire third-party database, including triggers and stored procedures

• Enables you to see and compare the captured model and the converted model, and to customize each

• Provides feedback about the migration through reports

Oracle


Migration in SQL Developer

Destination Oracle

schema

Captured

model

Converted

model

Migration repository

Source data

SQL Developer


Summary

In this lesson, you should have learned how to:• List the key features of Oracle SQL Developer• Install Oracle SQL Developer• Create a database connection• Navigate through the object navigator• Use the SQL Worksheet• Create, save, and use scripts• Develop, compile, and debug PL/SQL• Browse through the available search engines• Change preferences• Create reports• Describe migration


Practice 2 Overview: Using SQL Developer

This practice covers the following topics:• Starting SQL Developer• Creating a database connection in SQL Developer• Executing SQL statements• Setting up your script pathing preference• Creating, compiling, and debugging a procedure• Examining exporting• Creating a SQL report• Setting up and accessing SQL*Plus


Using the Language Functionality Enhancements


Objectives

After completing this lesson, you should be able to:• Use the new regular expression support functions• Track dependencies at the element level• Find and fix exception handlers that do not pass the

exception upward• Dispatch an overridable object type method• Learn about Data Change Notification (DCN) result-

set-change notification• Use the lock enhancements:

– Use the LOCK TABLE … WAIT new syntax– Set the DDL_LOCK_TIMEOUT parameter


Lesson Agenda

• Using the new regular expression support functions• Tracking dependencies at the element level• Fixing exception handlers that do not pass the

exception upward• Dispatching an overridable object type method • Learning about Data Change Notification (DCN)

result-set-change notification• Utilizing the lock enhancements

– Using the LOCK TABLE … WAIT new syntax– Setting the DDL_LOCK_TIMEOUT parameter


Regular Expression Enhancements in SQL and PL/SQL

• Features added:1. Access to the n-th subexpression in the REGEXP_INSTR

and REGEXP_SUBSTR functions2. Return the number of times a pattern match is found

in an input string using the new REGEXP_COUNT function

• Benefits:– Extends the current regular expression functionality

based on customer feedback– Decreases the number of calls to the regular

expression functions in order to get related information


Understanding Subexpressions

Examine this expression:

The subexpressions are:

(1 2 3)(4(5 6)(7 8))

(1 2 3)(4(5 6)(7 8))

1

2

3 4


Using Subexpressions with Regular Expression Support

SELECT REGEXP_INSTR ('0123456789', -- source char or search value '(123)(4(56)(78))', -- regular expression patterns 1, -- position to start searching 1, -- occurrence 0, -- return option 'i', -- match option (case insensitive) 1) -- subexpression on which to search"Position"FROM dual;

Position

----------

2

1234567

REGEXP_INSTR and REGEXP_SUBSTR now have an optional subexpr parameter that lets you target a particular substring of the regular expression being evaluated.


Using Subexpressions with Regular Expression Support: More Examples

SELECT REGEXP_INSTR('0123456789', '(123)(4(56)(78))', 1, 1, 0, 'i', 2) "Position"FROM dual;

Position ---------- 5

SELECT REGEXP_INSTR('0123456789', '(123)(4(56)(78))', 1, 1, 0, 'i', 4) "Position"FROM dual;

Position ---------- 8

1

2


Why Access the n-th Subexpression?

• A more realistic use: DNA sequencing• You may need to find a specific subpattern that

identifies a protein needed for immunity in mouse DNA.CREATE OR REPLACE FUNCTION get_instrsubexp_pos (p_subexp NUMBER)RETURN NUMBER

IS v_dna CLOB; v_location NUMBER;BEGIN v_dna := 'ccacctttccctccactcctcacgttctcacctgtaaagcgtccctccctcatccccatgcccccttac cctgcagggtagagtaggctagaaaccagagagctccaagctccatctgtggagaggtgccatccttgggctgcagagagag

gagaatttgccccaaagctgcctgcagagcttcaccacccttagtctcacaaagccttgagttcatagcatttctt gagttttcaccctgcccagcaggacactgcagcacccaaagggcttcccaggagtagggttgccctcaagaggctc ttgggtctgatggccacatcctggaattgttttcaagttgatggtcacagccctgaggcatgtaggggcgtgggga

tgcgctctgctctgctctcctctcctgaacccctgaaccctctggctaccccagagcacttagagccag'; v_location := REGEXP_INSTR(v_dna, '(gtc(tcac)(aaag))', 1, 1, 0, 'i', p_subexp); RETURN (v_location);END;


REGEXP_INSTR: Examples

SQL> EXECUTE dbms_output.put_line(get_instrsubexp_pos(1));197

PL/SQL procedure successfully completed.

SQL> EXECUTE dbms_output.put_line(get_instrsubexp_pos(2));200PL/SQL procedure successfully completed.

SQL> EXECUTE dbms_output.put_line(get_instrsubexp_pos(3));204


1

2

3


REGEXP_SUBSTR: Example

REGEXP_SUBSTR searches for a regular expression pattern within a given string and returns the matched string.SELECT REGEXP_SUBSTR ('acgctgcactgca', -- source char or search value 'acg(.*)gca', -- regular expression pattern 1, -- position to start searching 1, -- occurrence 'i', -- match option (case insensitive) 1) -- subexpression “Value" FROM dual;

Value------ctgact

123456


REGEXP_COUNT Function

Returns the number of times a pattern appears in a stringCREATE OR REPLACE FUNCTION get_subexp_count (p_subexp VARCHAR2)RETURN NUMBERIS v_dna CLOB; v_count NUMBER;BEGIN v_dna := 'ccacctttccctccactcctcacgttctcacctgtaaagcgtccctccctcatccccatgcccccttaccctgcag ggtagagtaggctagaaaccagagagctccaagctccatctgtggagaggtgccatccttgggctgcagagagaggagaat ttgccccaaagctgcctgcagagcttcaccacccttagtctcacaaagccttgagttcatagcatttcttgagttttcacc ctgcccagcaggacactgcagcacccaaagggcttcccaggagtagggttgccctcaagaggctcttgggtctgatggcca catcctggaattgttttcaagttgatggtcacagccctgaggcatgtaggggcgtggggatgcgctctgctctgctctcct

ctcctgaacccctgaaccctctggctaccccagagcacttagagccag'; v_count := REGEXP_COUNT(v_dna, p_subexp); RETURN (v_count);END;


Lesson Agenda






More Precise Dependency Metadata

• Earlier releases recorded dependency metadata.• Oracle Database 11g records additional, finer-grained

dependency management.• Prior to Oracle Database 11g, adding column D to

table T invalidated the dependent objects.• Starting in Oracle Database 11g, adding column D to

table T does not impact view V and does not invalidate the dependent objects.

Procedure P Function FView V

Column A

Column B

Table T

Column A

Column BAdd Column D


Fine-Grain Dependency Management

In Oracle Database 11g, dependencies are tracked at the level of element within unit. Element-based dependency tracking covers the following:• Dependency of a single-table view on its base table• Dependency of a PL/SQL program unit (package

specification, package body, or subprogram) on the following:

– Other PL/SQL program units– Tables– Views


SQL Fine-Grain Dependency Management: Example

CREATE TABLE t (col_a NUMBER, col_b NUMBER, col_c NUMBER);CREATE VIEW v AS SELECT col_a, col_b FROM T;

SELECT ud.name, ud.type, ud.referenced_name, ud.referenced_type, uo.statusFROM user_dependencies ud, user_objects uoWHERE ud.name = uo.object_name AND ud.name = 'V';

NAME TYPE REFERENCED_NAME REFERENCED_TYPE STATUS---------------- ---------- ---------------- ----------------- -------V VIEW T TABLE VALID

ALTER TABLE t ADD (col_d VARCHAR2(20));

SELECT ud.name, ud.type, ud.referenced_name, ud.referenced_type, uo.statusFROM user_dependencies ud, user_objects uoWHERE ud.name = uo.object_name AND ud.name = 'V';

NAME TYPE REFERENCED_NAME REFERENCED_TYPE STATUS---------------- ---------- ---------------- ----------------- -------V VIEW T TABLE VALID

1

2


SQL Fine-Grain Dependency Management: Example

CREATE TABLE t (col_a NUMBER, col_b NUMBER, col_c NUMBER);

CREATE OR REPLACE VIEW v AS SELECT col_a, col_b FROM T;

SELECT ud.name, ud.referenced_name, ud.referenced_type, uo.statusFROM user_dependencies ud, user_objects uoWHERE ud.name = uo.object_name AND ud.name = 'V';

NAME REFERENCED_NAME REFERENCED_TYPE STATUS-------------- ---------------- ----------------- -------V VIEW T TABLE VALID

ALTER TABLE t MODIFY (col_a VARCHAR2(20));

SELECT ud.name, ud.referenced_name, ud.referenced_type, uo.statusFROM user_dependencies ud, user_objects uoWHERE ud.name = uo.object_name AND ud.name = 'V';

NAME REFERENCED_NAME REFERENCED_TYPE STATUS-------------- ---------------- ----------------- -------V VIEW T TABLE INVALID


PL/SQL Fine-Grain Dependency Management: Example

CREATE PACKAGE sample_pkg IS PROCEDURE p1;END sample_pkg;/CREATE PROCEDURE my_proc IS BEGIN sample_pkg.p1(); END my_proc ;/CREATE OR REPLACE PACKAGE sample_pkg IS PROCEDURE p1; PROCEDURE unheard_of;END sample_pkg;/SELECT status FROM user_objects WHERE object_name = 'MY_PROC';

STATUS--------VALID


Lesson Agenda






PLW 06009 Warning

• A new PLW warning is available to you.• This warning means that the OTHERS handler of your

PL/SQL subroutine can exit without executing some form of RAISE or a call to the standard RAISE_APPLICATION_ERROR procedure.

• Good programming practices suggest that the OTHERS handler must always pass an exception upward.


PLW 06009 Warning: Example

CREATE OR REPLACE PROCEDURE p (i IN VARCHAR2) IS BEGIN INSERT INTO t(col_a) VALUES (i); EXCEPTION WHEN OTHERS THEN null; END p;/

ALTER PROCEDURE P COMPILE PLSQL_warnings = 'enable:all' REUSE SETTINGS;

SP2-0805: Procedure altered with compilation warnings

SQL> SHOW ERRORSErrors for PROCEDURE P:

LINE/COL ERROR-------- ---------------------------------------------------------------6/10 PLW-06009: procedure "P" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR


Lesson Agenda






Support for Generalized Invocation

• Provides the ability to statically dispatch an overridable object type method

• Is compliant with ANSI SQL 2003• Adds new syntax to PL/SQL to support this feature

ASupertype

CSubtype of B

DSubtype of A

BSubtype of A

Supertype of C


Support for Generalized Invocation: Example

The Employee_t supertype has an overridable show_data() member function that displays generic employee information:

CREATE OR REPLACE TYPE employee_t AS OBJECT( emp_id NUMBER, emp_last_name VARCHAR2(100), emp_salary NUMBER, MEMBER PROCEDURE show_data) NOT FINAL NOT INSTANTIABLE/CREATE OR REPLACE TYPE BODY employee_t IS MEMBER PROCEDURE show_data ISBEGIN DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_id || ' Last name: ' || emp_last_name || ' Salary: ' || emp_salary);END show_data;END;/



The Salesperson_t subtype specializes the show_data() method to acknowledge notions such as territory covered:CREATE OR REPLACE TYPE salesperson_t UNDER employee_t ( territory VARCHAR2(100), OVERRIDING MEMBER PROCEDURE show_data)FINAL/CREATE OR REPLACE TYPE BODY salesperson_t IS OVERRIDING MEMBER PROCEDURE show_data ISBEGIN -- First form of the new syntax. show_data((SELF AS employee_t)); DBMS_OUTPUT.PUT_LINE('Sales Territory: ' || territory);END show_data;END;/



The Developer_t subtype specializes the show_data() method to acknowledge notions such as product area of responsibility:

CREATE OR REPLACE TYPE developer_t UNDER employee_t( product_line VARCHAR2(100), OVERRIDING MEMBER PROCEDURE show_data)FINAL/CREATE OR REPLACE TYPE BODY developer_t IS OVERRIDING MEMBER PROCEDURE show_data ISBEGIN -- Second form (and more natural form) of the new syntax.

(SELF AS employee_t).show_data(); DBMS_OUTPUT.PUT_LINE('Product Area: '|| product_line);END show_data;END;/



Create a tester program to try out the new functionality:

CREATE OR REPLACE PROCEDURE Test_It IS TYPE employee_List_t IS TABLE OF employee_t INDEX BY PLS_INTEGER; emp_Objects employee_List_t;BEGIN -- create some sample data for this example: emp_objects(1) := salesperson_t(35, 'Patel', 90000, 'India' ); emp_Objects(2) := salesperson_t(30, 'Jones', 92000, 'UK'); emp_objects(3) := developer_t(25, 'Cline', 96000, '11g XML'); emp_Objects(4) := developer_t(20, 'Smith', 97000, '11g PL/SQL');

FOR j IN 1..emp_objects.Count() LOOP DBMS_OUTPUT.PUT_LINE('Employee Report for: '); emp_Objects(j).show_data(); DBMS_OUTPUT.PUT_LINE(chr(10)); END LOOP;END;/BEGIN Test_It(); END;/


Lesson Agenda


exception upward• Dispatching an overridable object type method• Learning about Data Change Notification (DCN)




Overview of Data Change Notification Enhancements

• Pre-Oracle Database 11g, Release 11.1:– Only object-change notifications, which result from

DML or DDL changes to the objects associated with the registered queries are published.

• Starting with Oracle Database 11g, Release 11.1:– Result-set-change notifications, which result from DML

or DDL changes to the result set associated with the registered queries are published.

– New static data dictionary views allow you to see which queries are registered for result-set-change notifications.


Data Change Notification Process

Client application

Data Dictionary

PL/SQL

Job Queue process

Middle Tier

Invalidationqueue

User objects

Web cache

1

2

8

7

5

6

3

4

9

Client notification

Registration via PL/SQL or

OCI

DML

Oracle Database


Data Change Notification

• With object change notification:– DNS generates an object-change notification for this

query for any DML or DDL change to the ORDERS table, even if the changed row or rows did not satisfy the query predicate (for example, if sales_rep_id = 160)

• With result-set-change notification:– DNS generates a result-set-change notification only

if the query result set itself changed and both of the following are true:

— The changed row or rows satisfy the query predicate (sales_rep_id = 158) either before or after the change.

— The change affected at least one of the columns in the SELECT list (order_id or order_total), as the result of either an UPDATE or an INSERT.

SELECT order_id, order_total FROM orders WHERE sales_rep_id = 158;


Data Dictionary Views for CQN

• To see top-level information about all registrations:– DBA_CHANGE_NOTIFICATION_REGS– USER_CHANGE_NOTIFICATION_REGS

• To see which queries are registered for result-set-change notifications:

– DBA_CQ_NOTIFICATION_QUERIES– USER_CQ_NOTIFICATION_QUERIES


Lesson Agenda






Using the LOCK TABLE Statement with the WAIT Option

Use the WAIT option to identify the maximum number of seconds a statement should wait to obtain a DML lock on the table.• There is no limit on the number of seconds.• A message is returned indicating that the object is

already locked.


Using the LOCK TABLE Statement with the WAIT Option: Example

In session #1:

LOCK TABLE orders IN EXCLUSIVE MODE;

In session #2:

LOCK TABLE orders IN EXCLUSIVE MODE WAIT 60;

ORA-00054: resource busyand acquire with NOWAIT specified or timeoutexpired

Time01.0101.0501.1501.2001.2501.3001.3501.4001.4501.5001.5502.0002.0502.1002.1502.2002.2502.3002.35


Setting the DDL_LOCK_TIMEOUT Parameter

• Use the DDL_LOCK_TIMEOUT parameter to specify a DDL lock timeout.

– The permissible range of values for DDL_LOCK_TIMEOUT is 0 through 1,000,000 (in seconds).

– The default is 0.

• You can set DDL_LOCK_TIMEOUT at the:– System level– Session level (with an ALTER SESSION statement)


Setting the DDL_LOCK_TIMEOUT Parameter: Example

• System level:

• Session level:

DDL_LOCK_TIMEOUT = 50000

ALTER SESSION SET DDL_LOCK_TIMEOUT = 50000;


Summary

In this lesson, you should have learned how to:• Use the new regular expression support functions• Track dependencies at the element level• Find and fix exception handlers that do not pass the

exception upward• Dispatch an overridable object type method• Learn about Data Change Notification (DCN) result-

set-change notification• Use the lock enhancements


Practice 3 Overview: Using the Language Functionality

EnhancementsThis practice covers the following topics:• Using the regular expression support new functions

for find and count patterns• Tracking dependencies at the element level• Writing code that causes the new PLW-06009 warning

and then fix the code• Trying the WAIT option for DDL statements


Executing Dynamic SQL in PL/SQL with the 11g Enhancements


Objectives

After completing this lesson, you should be able to:• Write PL/SQL code that uses dynamic SQL and allows

for SQL statements larger than 32 KB• Use the DBMS_SQL.PARSE() function that is

overloaded for CLOBs• Convert a REF CURSOR to a DBMS_SQL cursor and vice

versa to support interoperability• Program using the enhancements to DBMS_SQL that

include supporting the full range of data types (including collections and object types)


Lesson Agenda

• Overview of native dynamic SQL and DBMS_SQL– Introduction– Previous limitations

• Dynamic SQL support for CLOBs– Native dynamic SQL support for CLOBs– DBMS_SQL.PARSE() for CLOBs

• Converting between a REF CURSOR and a DBMS_SQL cursor

– DBMS_SQL.TO_REF_CURSOR– DBMS_SQL.TO_CURSOR_NUMBER

• DBMS_SQL support for abstract data types


Native Dynamic SQL and DBMS_SQL: Overview

Native dynamic SQL and the DBMS_SQL package are two ways to implement a dynamic SQL statement programmatically.• You use native dynamic SQL on a single operation to

bind any arguments in the dynamic SQL statement and execute the statement.

• You use the DBMS_SQL package to execute a dynamic SQL statement that has an unknown number of input or output variables.


Native Dynamic SQL and DBMS_SQL: Overview

• Use native dynamic SQL when:– The dynamic SQL statement retrieves rows into

records– You want to use the %FOUND, %ISOPEN, %NOTFOUND, or

%ROWCOUNT SQL cursor attributes after issuing a dynamic SQL statement that is an INSERT, UPDATE, DELETE, or single-row SELECT statement

• Use DBMS_SQL when:– You do not know the SELECT list at compile time– You do not know how many columns a SELECT

statement will return, or what their data types are


Dynamic SQL Functional Completeness

• Currently have two flavors of dynamic SQL within PL/SQL: DBMS_SQL and native dynamic SQL

• For functional completeness, interoperability between native dynamic SQL and DBMS_SQL is supported:

– SQL statements larger than 32 KB are allowed in native dynamic SQL.

– DBMS_SQL.PARSE() is overloaded for CLOBs. – A REF CURSOR can be converted to a DBMS_SQL cursor

and vice versa to support interoperability.– DBMS_SQL supports the full range of data types

(including collections and object types).


Lesson Agenda







EXECUTE gen_pl('begin dbms_output.put_line – (''put any code here''); end;')

put any code here

Just executed the following code: begin dbms_output.put_line('put any code

here'); end;


Dynamic SQL Support for CLOBs

Native dynamic SQL support:CREATE OR REPLACE PROCEDURE gen_pl(p_pgm CLOB)IS dynamic_pl CLOB := p_pgm;BEGIN EXECUTE IMMEDIATE dynamic_pl; -- next line is for learning purposes only DBMS_OUTPUT.PUT_LINE ('Just executed the following code: ' || dynamic_pl);END gen_pl;

1

2


Dynamic SQL Support for CLOBs

• More native dynamic SQL examples:

• For symmetry, DBMS_SQL.PARSE now accepts a CLOB too.

EXECUTE gen_pl('begin null; end;')Just executed the following code: begin null; end;


EXECUTE gen_pl('begin dbms_output.put_line(''hello world''); end;')hello world!Just executed the following code: begin dbms_output.put_line('hello

world!'); end;


PROCEDURE parse (c IN INTEGER, statement IN CLOB,

language_flag IN INTEGER);

4

3


Lesson Agenda







Transforming a DBMS_SQL Cursor into a REF CURSOR

• To add interoperability between native dynamic SQL and DBMS_SQL, you can transform a DBMS_SQL cursor into a PL/SQL REF CURSOR and vice versa.

• Two new functions are added into the DBMS_SQL package to support this feature:

– DBMS_SQL.TO_REFCURSOR (cursor_number IN INTEGER) RETURN SYS_REFCURSOR;

– DBMS_SQL.TO_CURSOR_NUMBER (rc IN OUT SYS_REFCURSOR) RETURN INTEGER;


Transforming a DBMS_SQL Cursor into aREF CURSOR: Example

CREATE OR REPLACE PROCEDURE do_query (rep_id NUMBER) ISTYPE num_list IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE cur_type IS REF CURSOR; src_cur cur_type; c_hndl NUMBER; cust_nos num_list; crdt_nos num_list; ret INTEGER; sql_stmt CLOB;BEGIN c_hndl := DBMS_SQL.OPEN_CURSOR; sql_stmt := 'SELECT customer_id, credit_limit FROM customers WHERE account_mgr_id = :b1'; DBMS_SQL.PARSE(c_hndl, sql_stmt, DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(c_hndl, 'b1', rep_id); ret := DBMS_SQL.EXECUTE(c_hndl); -- continued on next page


Transforming a DBMS_SQL Cursor into a REF CURSOR: Example

-- continued from previous page -- switch from dbms_sql to native dynamic SQL src_cur := DBMS_SQL.TO_REFCURSOR(c_hndl); -- fetch with native dynamic SQL FETCH src_cur BULK COLLECT INTO cust_nos, crdt_nos;

IF cust_nos.COUNT > 0 THEN DBMS_OUTPUT.PUT_LINE ('Customer Credit Limit'); DBMS_OUTPUT.PUT_LINE ('-------- ------------'); FOR i IN 1 .. cust_nos.COUNT LOOP DBMS_OUTPUT.PUT_LINE(cust_nos(i) || ' ' || crdt_nos(i)); END LOOP; END IF;

CLOSE src_cur;END do_query;/


Transforming a DBMS_SQL Cursor into a REF CURSOR: Example

EXECUTE do_query(145)

Customer Credit Limit-------- ------------308 1200309 1200310 5000360 3600344 2400380 3700...934 600PL/SQL procedure successfully completed.


Transforming a REF CURSOR into a DBMS_SQL Cursor: Example

CREATE OR REPLACE PROCEDURE do_query2 (sql_stmt VARCHAR2, rep_id NUMBER) IS TYPE cur_type IS REF CURSOR; src_cur cur_type; c_hndl NUMBER; desctab DBMS_SQL.DESC_TAB; colcnt NUMBER; custid NUMBER; crdvar NUMBER;BEGIN OPEN src_cur FOR sql_stmt USING rep_id; -- switch from native dynamic SQL to DBMS_SQL: c_hndl := DBMS_SQL.TO_CURSOR_NUMBER(src_cur); DBMS_SQL.DESCRIBE_COLUMNS(c_hndl, colcnt, desctab);

-- define columns FOR i in 1 .. colcnt LOOP IF desctab(i).col_type=1 THEN DBMS_SQL.DEFINE_COLUMN(c_hndl, i, custid); ELSIF desctab(i).col_type = 2 THEN DBMS_SQL.DEFINE_COLUMN(c_hndl, i, crdvar); END IF; END LOOP;-- continued on next page


Transforming a REF CURSOR into a DBMS_SQL Cursor: Example

-- continued from previous page

-- fetch rows WHILE DBMS_SQL.FETCH_ROWS(c_hndl) > 0 LOOP FOR i IN 1 .. colcnt LOOP IF desctab(i).col_type=1 THEN DBMS_SQL.COLUMN_VALUE(c_hndl, i, custid); ELSIF desctab(i).col_type = 2 THEN DBMS_SQL.COLUMN_VALUE(c_hndl, i, crdvar); END IF; END LOOP; -- could do more processing...

END LOOP; DBMS_SQL.CLOSE_CURSOR(c_hndl);END do_query2;/

EXECUTE do_query2('SELECT customer_id, credit_limit FROM customers - WHERE account_mgr_id = :b1', 148)



Lesson Agenda







DBMS_SQL Support for Abstract Data Types (ADTs)

Now allows:• Collections

– Varrays– Nested tables

• REFs• Opaque types


DBMS_SQL Support for ADTs: Example

CREATE OR REPLACE PROCEDURE update_phone_nos (p_new_nos phone_list_typ, p_cust_id customers.customer_id%TYPE)IS some_phone_nos phone_list_typ; c_hndl NUMBER; r NUMBER; sql_stmt CLOB := 'UPDATE customers SET phone_numbers = :b1 WHERE customer_id = :b2 RETURNING phone_numbers INTO :b3';BEGIN c_hndl := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(c_hndl, sql_stmt, dbms_sql.native);

DBMS_SQL.BIND_VARIABLE (c_hndl, 'b1', p_new_nos); DBMS_SQL.BIND_VARIABLE (c_hndl, 'b2', p_cust_id); DBMS_SQL.BIND_VARIABLE (c_hndl, 'b3', some_phone_nos);

r := DBMS_SQL.EXECUTE (c_hndl);

DBMS_SQL.VARIABLE_VALUE(c_hndl, 'b3', some_phone_nos); DBMS_SQL.CLOSE_CURSOR(c_hndl);-- continued on next page



-- continued from previous page

-- select the phones nos sql_stmt := 'SELECT phone_numbers FROM customers WHERE customer_id = :b2';

c_hndl := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(c_hndl, sql_stmt, dbms_sql.native); DBMS_SQL.DEFINE_COLUMN(c_hndl, 1, some_phone_nos); DBMS_SQL.BIND_VARIABLE(c_hndl, 'b2', p_cust_id);

r := DBMS_SQL.EXECUTE_AND_FETCH(c_hndl);

DBMS_SQL.COLUMN_VALUE(c_hndl, 1, some_phone_nos); DBMS_SQL.CLOSE_CURSOR(c_hndl);

FOR i IN some_phone_nos.FIRST .. some_phone_nos.LAST LOOP DBMS_OUTPUT.PUT_LINE('Phone number = ' || some_phone_nos(i) || '

updated.'); END LOOP;END update_phone_nos;/



Execute the UPDATE_PHONE_NOS procedure:

DECLARE new_phone_nos phone_list_typ;BEGIN new_phone_nos := phone_list_typ ('12345678', '22222222', '33333333', '44444444'); update_phone_nos(new_phone_nos, 980);END;/

Phone number = 12345678 updated.Phone number = 22222222 updated.Phone number = 33333333 updated.Phone number = 44444444 updated.

PL/SQL successfully completed.


Summary

In this lesson, you should have learned how to use the Oracle Database 11g enhancements to dynamic SQL:• Write PL/SQL code that uses dynamic SQL and allows

for SQL statements larger than 32 KB• Convert a REF CURSOR to a DBMS_SQL cursor and vice

versa to support interoperability• Program using the enhancements to DBMS_SQL that

include supporting collections and object types• Create user-defined collection types and bulk-bind

them using DBMS_SQL


Practice 4 Overview: Using the New Dynamic SQL

EnhancementsThis practice covers the following topics:• Writing code that uses both DBMS_SQL.PARSE and

native dynamic SQL to accept SQL statements larger than 32 KB.

• Using DBMS_SQL with abstract data types and perform bulk binding with them.


Implementing the Performance Improvements


Objectives

After completing this lesson, you should be able to:• List the compiler changes and explain how the

changes impact native compilation• Use the new SIMPLE_INTEGER data type• Describe the process of inlining• Use caching for optimization• Use flashback to store and track all transactional

changes to a record


Lesson Agenda

• Compiler changes and how the changes impact native compilation

• The SIMPLE_INTEGER data type• Inlining• Caching

– SQL result cache– PL/SQL function result cache

• Flashback enhancements– To store and track all transactional changes to a

record over its lifetime


Real Native Compilation

• The compiler translates PL/SQL source directly to the dynamic-link library (DLL) for the current hardware.

• The compiler does the linking and loading so that the file system directories are no longer needed.

• The PL/SQL native compilation works out of the box, without requiring a C compiler on a production box.

• The PLSQL_CODE_TYPE parameter is the on/off switch.• And, real native compilation is faster than the C

native compilation.


Lesson Agenda







SIMPLE_INTEGER Data Type

• Definition:– Is a predefined subtype– Has the range –2147483648 .. 2147483648– Does not include a null value– Is allowed anywhere in PL/SQL where the

PLS_INTEGER data type is allowed

• Benefits:– Eliminates the overhead of overflow

checking– Is estimated to be 2–10 times faster

when compared with the PLS_INTEGER type with native PL/SQL compilation


SIMPLE_INTEGER Data Type: Example

CREATE OR REPLACE PROCEDURE p IS t0 NUMBER :=0; t1 NUMBER :=0;

$IF $$Simple $THEN SUBTYPE My_Integer_t IS SIMPLE_INTEGER; My_Integer_t_Name CONSTANT VARCHAR2(30) := 'SIMPLE_INTEGER'; $ELSE SUBTYPE My_Integer_t IS PLS_INTEGER; My_Integer_t_Name CONSTANT VARCHAR2(30) := 'PLS_INTEGER'; $END

v00 My_Integer_t := 0; v01 My_Integer_t := 0; v02 My_Integer_t := 0; v03 My_Integer_t := 0; v04 My_Integer_t := 0; v05 My_Integer_t := 0;

two CONSTANT My_Integer_t := 2; lmt CONSTANT My_Integer_t := 100000000; -- continued on next page



-- continued from previous pageBEGIN t0 := DBMS_UTILITY.GET_CPU_TIME(); WHILE v01 < lmt LOOP v00 := v00 + Two; v01 := v01 + Two; v02 := v02 + Two; v03 := v03 + Two; v04 := v04 + Two; v05 := v05 + Two; END LOOP;

IF v01 <> lmt OR v01 IS NULL THEN RAISE Program_Error; END IF;

t1 := DBMS_UTILITY.GET_CPU_TIME(); DBMS_OUTPUT.PUT_LINE( RPAD(LOWER($$PLSQL_Code_Type), 15)|| RPAD(LOWER(My_Integer_t_Name), 15)|| TO_CHAR((t1-t0), '9999')||' centiseconds');END p;/



ALTER PROCEDURE p COMPILEPLSQL_Code_Type = NATIVE PLSQL_CCFlags = 'simple:true'REUSE SETTINGS;

Procedure altered.

EXECUTE p()native simple_integer 51 centiseconds


ALTER PROCEDURE p COMPILEPLSQL_Code_Type = native PLSQL_CCFlags = 'simple:false'REUSE SETTINGS;

Procedure altered.

EXECUTE p()native pls_integer 884 centiseconds


1

2

3

4


Lesson Agenda







Intra Unit Inlining

• Definition:– Inlining is defined as the replacement of a call to

subroutine with a copy of the body of the subroutine that is called.

– The copied procedure generally runs faster than the original.

– The PL/SQL compiler can automatically find the calls that should be inlined.

• Benefits:– Inlining can provide large performance gains when

applied judiciously by a factor of 2–10 times.


Use of Inlining

• Influence implementing inlining via two methods:– Oracle parameter PLSQL_OPTIMIZE_LEVEL– PRAGMA INLINE

• Recommend that you:– Inline small programs– Inline programs that are frequently executed

• Use performance tools to identify hot spots suitable for inline applications:

– plstimer


Inlining Concepts

Noninlined program:

CREATE OR REPLACE PROCEDURE small_pgmIS a NUMBER; b NUMBER;

PROCEDURE touch(x IN OUT NUMBER, y NUMBER) IS BEGIN IF y > 0 THEN x := x*x; END IF; END;

BEGIN a := b; FOR I IN 1..10 LOOP touch(a, -17); a := a*b; END LOOP;END small_pgm;


Inlining Concepts

Examine the loop after inlining:

...BEGIN a := b; FOR i IN 1..10 LOOP IF –17 > 0 THEN a := a*a; END IF; a := a*b; END LOOP;END small_pgm;...


Inlining Concepts

The loop is transformed in several steps:a := b; FOR i IN 1..10 LOOP ... IF false THEN a := a*a; END IF; a := a*b; END LOOP; a := b; FOR i IN 1..10 LOOP ... a := a*b; END LOOP;

a := b; a := a*b; FOR i IN 1..10 LOOP ... END LOOP;

a := b*b; FOR i IN 1..10 LOOP ... END LOOP;


Inlining: Example

• Set the PLSQL_OPTIMIZE_LEVEL session-level parameter to a value of 2 or 3:

– Setting it to 2 means no automatic inlining is attempted.

– Setting it to 3 means automatic inlining is attempted and no pragmas are necessary.

• Within a PL/SQL subroutine, use PRAGMA INLINE– NO means no inlining occurs regardless of the level

and regardless of the YES pragmas.– YES means inline at level 2 of a particular call and

increase the priority of inlining at level 3 for the call.

ALTER PROCEDURE small_pgm COMPILE PLSQL_OPTIMIZE_LEVEL = 3 REUSE SETTINGS;


Inlining: Example

After setting the PLSQL_OPTIMIZE_LEVEL parameter, use a pragma:

CREATE OR REPLACE PROCEDURE small_pgmIS a PLS_INTEGER; FUNCTION add_it(a PLS_INTEGER, b PLS_INTEGER) RETURN PLS_INTEGER IS BEGIN RETURN a + b; END;BEGIN pragma INLINE (small_pgm, 'YES'); a := add_it(3, 4) + 6;END small_pgm;


Inlining: Guidelines

• Pragmas apply only to calls in the next statement following the pragma.

• Programs that make use of smaller helper subroutines are good candidates for inlining.

• Only local subroutines can be inlined.• You cannot inline an external subroutine.• Cursor functions should not be inlined.• Inlining can increase the size of a unit.• Be careful about suggesting to inline functions that

are deterministic.


Lesson Agenda







SQL Query Result Cache

• Definition:– Cache the results of the current query or query

fragment in memory and then use the cached results in future executions of the query or query fragments.

– Cached results reside in the result cache memory portion of the SGA.

• Benefits:– Improved performance


SQL Query Result Cache

• Scenario:– You need to find the greatest average value of credit

limit grouped by state over the whole population.– The query results in a huge number of rows analyzed

to yield a few or one row.– In your query, the data changes fairly slowly (say

every hour) but the query is repeated fairly often (say every second).

• Solution:– Use the new optimizer hint /*+ result_cache */ in

your query:SELECT /*+ result_cache */ AVG(cust_credit_limit), cust_state_provinceFROM sh.customersGROUP BY cust_state_province;


PL/SQL Function Result Cache

• Definition:– Enables data that is stored in cache to be shared

across sessions– Stores the function result cache in a shared global

area (SGA), making it available to any session that runs your application

• Benefits:– Improved performance– Improved scalability


PL/SQL Function Result Cache

• Scenario:– You need a PL/SQL function that derives a complex

metric.– The data that your function calculates changes slowly,

but the function is frequently called.

• Solution:– Use the new result_cache clause in your function

definition.


Enabling Result Caching

• Include the RESULT_CACHE option in the function definition.

• Optionally, include the RELIES_ON clause.CREATE OR REPLACE FUNCTION productName (prod_id NUMBER, lang_id VARCHAR2) RETURN NVARCHAR2 RESULT_CACHE RELIES_ON (product_descriptions)IS result VARCHAR2(50);BEGIN SELECT translated_name INTO result FROM product_descriptions WHERE product_id = prod_id AND language_id = lang_id; RETURN result;END;


Lesson Agenda







Flashback Data Archives

• Provide the ability to store and track all transactional changes to a record over its lifetime

• Save development resources because you no longer need to build this intelligence into your application

• Are useful for compliance with record stage policies and audit reports


Flashback Data Archive Process

1. Create the Flashback Data Archive.2. Specify the default Flashback Data Archive.3. Enable the Flashback Data Archive.4. View Flashback Data Archive data.


Flashback Data Archive Scenario

Using Flashback Data Archive to access historical data:

CONNECT sys/oracle@orcl AS sysdba-- create the Flashback Data ArchiveCREATE FLASHBACK ARCHIVE DEFAULT fla1 TABLESPACE example QUOTA 10G RETENTION 5 YEAR;

-- Enable Flashback Data Archive ALTER TABLE oe1.inventories FLASHBACK ARCHIVE; ALTER TABLE oe1.warehouses FLASHBACK ARCHIVE;

-- Specify the default Flashback Data Archive ALTER FLASHBACK ARCHIVE fla1 SET DEFAULT;

1

2

3


Flashback Data Archive Scenario

Using Flashback Data Archive to access historical data:• Examine the data:

• Change the data:

• Examine the flashback data:SELECT product_id, warehouse_id, quantity_on_handFROM oe1.inventories AS OF TIMESTAMP TO_TIMESTAMP ('2007-06-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS')WHERE product_id = 3108;

SELECT product_id, warehouse_id, quantity_on_handFROM oe1.inventories WHERE product_id = 3108;

UPDATE oe1.inventoriesSET quantity_on_hand = 300WHERE product_id = 3108;

1

2

3


Flashback Data Archive Dictionary Views

Viewing the results:

View Name Description

*_FLASHBACK_ARCHIVE Displays information about Flashback Data Archives

*_FLASHBACK_ARCHIVE_TS Displays tablespaces of Flashback Data Archives

*_FLASHBACK_ARCHIVE_TABLES Displays information about tables that are enabled for flashback archiving


Flashback Data Archive Dictionary Views

Viewing information about tables that are enabled for flashback archiving:

DESCRIBE dba_flashback_archive_tables Name Null? Type ----------------------------------- -------- ---------------TABLE_NAME NOT NULL VARCHAR2(30)OWNER_NAME NOT NULL VARCHAR2(30)FLASHBACK_ARCHIVE_NAME NOT NULL VARCHAR2(255)ARCHIVE_TABLE_NAME VARCHAR2(53)

SELECT * FROM dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME------------- ---------- ---------------------- -------------------INVENTORIES OE FLA1 SYS_FBA_HIST_70355WAREHOUSES OE FLA1 SYS_FBA_HIST_70336


Flashback Data Archive DDL Restrictions

Using any of the following DDL statements on a table enabled for Flashback Data Archive causes the error ORA-55610: Invalid DDL statement on history-tracked table• ALTER TABLE statement that does any of the

following:– Drops, renames, or modifies a column– Performs partition or subpartition operations– Converts a LONG column to a LOB column– Includes an UPGRADE TABLE clause, with or without an

INCLUDING DATA clause

• DROP TABLE statement• RENAME TABLE statement• TRUNCATE TABLE statement


Summary

In this lesson, you should have learned how to:• List the compiler changes and explain how the

changes impact native compilation• Use the new SIMPLE_INTEGER data type• Describe the process of inlining• Use caching for optimization• Use flashback to store and track all transactional

changes to a record


Practice 5 Overview: Implementing Performance

ImprovementsThis practice covers the following topics:• Testing the performance of the SIMPLE_INTEGER data

type• Writing code to use SQL caching• Writing code to use PL/SQL caching• Examining inlined code and practicing how to

influence inlining


Practicing the Language Usability Enhancements


Objectives

After completing this lesson, you should be able to:• Implement the sequence calls to NEXTVAL and CURRVAL without using a SQL statement to retrieve the values

• Use the new CONTINUE statement to control the next loop iteration or to leave a loop

• Use both named and mixed notation calls to functions from a SQL statement

• Use the ALTER TABLE statement to change tables to read-only status


Lesson Agenda

• Changes to sequence calls• The new CONTINUE statement• Named and mixed notation calls• Read-only tables


Sequence Enhancement in PL/SQL Expressions

Prior to Oracle Database 11g release:• References to sequences were permitted only

through SQL statements• Use of CURRVAL and NEXTVAL pseudocolumns was not

allowed in PL/SQL unless embedded in a SQL statement

• Using sequences in PL/SQL was cumbersome and required an additional SQL statement in a PL/SQL subroutine

12

34

56

7


Sequence Enhancement in PL/SQL Expressions

Enhancements in Oracle Database 11g:• You can use the CURRVAL and NEXTVAL

pseudocolumns, qualified by a sequence name, directly in a PL/SQL expression.

• Sequence usability is improved.• Less typing is required by the developer.• The resulting code is clearer.

12

34

56

7


Using Sequences in PL/SQL Expressions

Pre-Oracle Database 11g:

Starting in Oracle Database 11g:

declare v_new_id NUMBER;BEGIN SELECT my_seq.NEXTVAL INTO v_new_id FROM Dual;END;/

DECLARE v_new_id NUMBER;BEGIN v_new_id := my_seq.NEXTVAL;END;/


Using Sequences in PL/SQL Expressions

Try to avoid using the old syntax anymore:

SELECT my_seq.NEXTVAL INTO v_new_id FROM dual;


Lesson Agenda



PL/SQL CONTINUE Statement

• Definition:– Adds the functionality to begin the next loop iteration– Provides programmers with the ability to transfer

control to the next iteration of a loop– Uses parallel structure and semantics to the EXIT

statement

• Benefits:– Eases the programming process– May see a small performance improvement over the

previous programming workarounds to simulate the CONTINUE statement


PL/SQL CONTINUE Statement: Usage

• Offers you a simplified means to control loop iterations

• Can be more efficient than previous coding workarounds

• Is commonly used to filter data inside a loop body before the main processing begins


PL/SQL CONTINUE Statement: Example

DECLARE v_total SIMPLE_INTEGER := 0;BEGIN FOR i IN 1..10 LOOP v_total := v_total + i; dbms_output.put_line ('Total is: '|| v_total); CONTINUE WHEN i > 5; v_total := v_total + i; dbms_output.put_line ('End of Loop Total is: '|| v_total); END LOOP;END;/

1

2

Total is: 1End of Loop Total is: 2Total is: 4End of Loop Total is: 6Total is: 9End of Loop Total is: 12Total is: 16End of Loop Total is: 20Total is: 25End of Loop Total is: 30Total is: 36Total is: 43Total is: 51Total is: 60Total is: 70



PL/SQL CONTINUE Statement: Example

CREATE OR REPLACE PROCEDURE two_loop IS v_total NUMBER := 0;BEGIN <<BeforeTopLoop>> FOR i IN 1..10 LOOP v_total := v_total + 1; dbms_output.put_line ('Total is: ' || v_total); FOR j IN 1..10 LOOP CONTINUE BeforeTopLoop WHEN i + j > 5; v_total := v_total + 1; END LOOP; END LOOP;END two_loop;

Procedure created.

--RESULTS:

EXECUTE two_loop

Total is: 1Total is: 6Total is: 10Total is: 13Total is: 15Total is: 16Total is: 17Total is: 18Total is: 19Total is: 20

PL/SQL proceduresuccessfully completed.


CONTINUE Statement: Guidelines

• The CONTINUE statement offers you the functionality to transfer control within a loop back to a new iteration or to leave the loop.

• The CONTINUE statement cannot appear outside a loop at all; this generates a compiler error.

• You cannot use the CONTINUE statement to pass through a procedure, function, or method boundary; this generates a compiler error.


Lesson Agenda



Named and Mixed Notation from SQL

• Definition:– PL/SQL allows arguments in a subroutine call to be

specified using positional, named, or mixed notation.– Before Oracle Database 11g, only the positional

notation was supported in calls from SQL.– Starting in Oracle Database 11g, named and mixed

notation can be used for specifying arguments in calls to PL/SQL subroutines from SQL statements.

• Benefits:– For long parameter lists, with most having default

values, you can omit values from the optional parameters.

– You can avoid duplicating the default value of the optional parameter at each call site.


Named and Mixed Notation from SQL: Example

CREATE OR REPLACE FUNCTION f ( p1 IN NUMBER DEFAULT 1, p5 IN NUMBER DEFAULT 5) RETURN NUMBERIS v number;BEGIN v:= p1 + (p5 * 2); RETURN v;END f;/Function created.

SELECT f(p5 => 10) FROM DUAL;

F(P5=>10)---------- 21


Lesson Agenda



Read-Only Tables

Use the ALTER TABLE syntax to put a table into read-only mode:• Prevents DDL or DML changes during table

maintenance• Changes it back into read/write modeALTER TABLE customers READ ONLY;

-- perform table maintenance and then-- return table back to read/write mode

ALTER TABLE customers READ WRITE;


Summary

In this lesson, you should have learned how to:• Implement the sequence calls to NEXTVAL and CURRVAL without using a SQL statement to retrieve the values.

• Use the new CONTINUE statement to control the next loop iteration or to leave a loop.

• Use both named and mixed notation calls to functions from a SQL statement.

• Use the ALTER TABLE statement to change tables to read-only status.


Practice 6 Overview: Using the New SQL and PL/SQL Usability

FeaturesThis practice covers the following topics:• Trying the new syntax for sequences• Controlling loop iteration with the CONTINUE

statement• Using the named and mixed notation calls to

functions from a SQL statement• Changing the status of a table to read-only


Developing Triggers that Utilize the New Enhancements


Objectives

After completing this lesson, you should be able to:• Describe compound triggers• Create compound triggers• Create disabled triggers• Use the ENABLE clause with a trigger• Control trigger order with the FOLLOWS clause


Compound Trigger: Overview

• Definition:– It is a single trigger on a table that allows you to

specify actions for each of four timing points of the trigger.

– The trigger body supports a common PL/SQL state that the code for each timing point can access.

– You can avoid the mutating table error by allowing rows destined for a second table to accumulate and then bulk-inserting them.

• Benefits:– Improved usability for the PL/SQL programmer– Improved run-time performance and scalability


Compound Trigger: Overview

• Can be used on tables or views• A single trigger with four timing points:

1. Before the firing statement2. Before each row that the firing statement affects3. After each row that the firing statement affects4. After the firing statement

INSERTUPDATEDELETE

Before statement

Before row

After row

After statement


Compound Trigger: Structure

For tables:

CREATE OR REPLACE TRIGGER schema.trigger

FOR dml_event_clause ON schema.table

COMPOUND TRIGGER

-- Initial section -- Declarations -- Subprograms

-- Optional section BEFORE STATEMENT IS ...;

-- Optional sectionBEFORE EACH ROW IS ...;

-- Optional section AFTER EACH ROW IS ...;

-- Optional section AFTER STATEMENT IS ...;

1

2


Compound Trigger: Structure

For views:

CREATE OR REPLACE TRIGGER schema.trigger

FOR dml_event_clause ON schema.view

COMPOUND TRIGGER

-- Initial section -- Declarations -- Subprograms

-- Optional section (exclusive) INSTEAD OF EACH ROW IS ...;


Compound Trigger: Scenario

Track changes on the ORDER_TOTAL column in the ORDERS table to an audit table:

INSERTUPDATE

ORDERS tableORDERTOTALS_AUDIT

table


Supporting Structures for Compound Trigger: Example

CREATE SEQUENCE ordertotals_audit_seq START WITH 2500;

CREATE OR REPLACE TRIGGER gen_ordertotals_audit_id_trg BEFORE INSERT ON orders FOR EACH ROWBEGIN :NEW.order_id := ordertotals_audit_seq.NEXTVAL;END gen_ordertotals_audit_id_trg;

CREATE TABLE ordertotals_audit( order_id NUMBER NOT NULL, change_date DATE NOT NULL, user_id VARCHAR2(30), old_total NUMBER(8, 2) NOT NULL, new_total NUMBER(8, 2) NOT NULL, CONSTRAINT order_total_PK PRIMARY KEY (order_id, change_date), CONSTRAINT orders_FK FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE);


Compound Trigger: Example

CREATE OR REPLACE TRIGGER maintain_ordertotals_audit_trg FOR INSERT OR UPDATE OF order_total ON orders COMPOUND TRIGGER--Initial section begins --Declarations threshhold CONSTANT SIMPLE_INTEGER := 7; TYPE order_totals_t IS TABLE OF ordertotals_audit%rowtype INDEX BY PLS_INTEGER; o_totals order_totals_t; idx SIMPLE_INTEGER := 0;

-- subprogram PROCEDURE Flush_Array IS n CONSTANT SIMPLE_INTEGER := o_totals.Count(); BEGIN FORALL j IN 1..n INSERT INTO ordertotals_audit VALUES o_totals(j); o_totals.Delete(); idx := 0; DBMS_Output.Put_Line('Flushed '||n||' rows'); END Flush_Array;-- Initial section ends


Compound Trigger: Example

-- Optional section BEFORE STATEMENT IS BEGIN o_totals.Delete(); idx := 0; END BEFORE STATEMENT; AFTER EACH ROW IS BEGIN idx := idx + 1; o_totals(idx).order_ID := :New.order_ID; o_totals(idx).Change_Date := SYSDATE(); o_totals(idx).user_id := sys_context('userenv', 'session_user'); o_totals(idx).old_total := :OLD.order_total; o_totals(idx).new_total := :NEW.order_total; IF idx >= Threshhold THEN -- PLW-06005: inlining... done Flush_Array(); END IF; END AFTER EACH ROW; AFTER STATEMENT IS BEGIN -- PLW-06005: inlining... done Flush_Array(); END AFTER STATEMENT;END maintain_ordertotals_audit_trg;


Compiling the Compound Trigger

• The session settings are enabled for inlining and viewing all compiler messages.

• The warning message tells you that inlining is performed.

• The trigger is successfully compiled; this is only an informational warning.


Firing the Compound Trigger

• Execute a statement to force the trigger to fire:

• Examine the results in the audit table:SELECT * FROM ordertotals_audit;

ORDER_ID CHANGE_DATE USER_ID OLD_TOTAL NEW_TOTAL ------------ --------------- ---------- --------- ---------2432 27-JUL-07 OE1 10523 11049.15

2433 27-JUL-07 OE1 78 81.9

2367 27-JUL-07 OE1 144054.8 151257.54 2368 27-JUL-07 OE1 60065 63068.25

2386 27-JUL-07 OE1 21116.9 22172.75

2412 27-JUL-07 OE1 66816 67140

2

UPDATE orders SET order_total = order_total * 1.05 WHERE order_status = 10;

1


Other Trigger Changes

• More control over triggers• CREATE TRIGGER now includes the ENABLE, DISABLE,

and FOLLOWS clauses that give you more control over triggers.

– The DISABLE clause lets you create a trigger in a disabled state so that you can ensure that your code compiles successfully before you enable the trigger.

– The ENABLE clause enables the trigger.– The FOLLOWS clause allows you to specify that the

trigger you are creating fires after certain other triggers.


Creating a Disabled Trigger

• Prior to Oracle Database 11g, if you created a trigger whose body has a PL/SQL compilation error, DML to the table fails with “ORA-04098: trigger 'TRG' is invalid and failed re-validation.”

• It is safer to create it as disabled, and then, to enable it only when you know it will be compiled without an error.CREATE OR REPLACE TRIGGER gen_cust_id

BEFORE INSERT ON customers FOR EACH ROW DISABLEBEGIN :NEW.customer_id := customer_seq.Nextval;END;/


FOLLOWS Clause

To ensure that a trigger fires after certain other triggers defined on the same object, use the FOLLOWS clause when you create the first trigger.

Trig1 Trig2 Trig3 Trig4FOLLOWS FOLLOWS FOLLOWS


FOLLOWS Clause

• Applies to both compound and simple triggers• Lets you order the executions of multiple triggers

relative to each other• Can be placed:

– In the definition of a simple trigger with a compound trigger target

– In the definition of a compound trigger with a simple trigger target

• Applies only to the section of the compound trigger with the same timing point as the simple trigger:

– If the compound trigger has no such timing point, FOLLOWS is quietly ignored.


FOLLOWS Clause: Scenario

CREATE OR REPLACE TRIGGER change_product AFTER UPDATE of product_id ON order_itemsFOR EACH ROWFOLLOWS oe1.compute_totalBEGIN dbms_output.put_line ('Do processing here…');END;

COMPUTE_TOTAL CHANGE_PRODUCT

FOLLOWS


FOLLOWS Clause: Example

CREATE OR REPLACE TRIGGER compute_total AFTER UPDATE OR INSERT OR DELETE of unit_price, quantity ON order_items FOR EACH ROWBEGIN IF UPDATING THEN UPDATE orders SET order_total = order_total – (:old.unit_price * :old.quantity) + (:new.quantity * :new.unit_price) WHERE order_id = :old.order_id; ELSIF DELETING THEN UPDATE orders SET order_total = order_total – (:old.unit_price * :old.quantity) WHERE order_id = :old.order_id; ELSE --inserting UPDATE orders SET order_total = order_total + (:new.quantity * :new.unit_price) WHERE order_id = :old.order_id; END IF; END;


FOLLOWS Clause: Example

UPDATE order_items SET product_id=3165WHERE order_id = 2412 AND line_item_id = 8;Do processing here...

1 row updated.

SELECT order_id, order_date, customer_id, order_status, order_totalFROM orders WHERE customer_id = 170;

ORDER_ID ORDER_DAT CUSTOMER_ID ORDER_STATUS ORDER_TOTAL---------- --------- ----------- ------------ ----------- 2412 29-MAR-04 170 9 67140

UPDATE order_items SET quantity = 100WHERE order_id = 2412 AND line_item_id = 9;

1 row updated.

2

1

3


Summary

In this lesson, you should have learned how to:• Describe compound triggers• Create compound triggers• Create disabled triggers• Use the ENABLE clause with a trigger• Control trigger order with the FOLLOWS clause


Practice 7 Overview: Using the New Trigger Enhancements

This practice covers the following topics:• Creating a disabled trigger and then enabling it• Writing a compound trigger• Controlling the trigger firing order with the new FOLLOWS clause


Implementing SecureFile LOBs


Objectives

After completing this lesson, you should be able to:• Describe SecureFile LOB features• Enable SecureFile LOB deduplication, compression,

and encryption• Migrate BasicFile LOBs to the SecureFile LOB format• Analyze the performance of LOBs


Lesson Agenda

• SecureFile LOB features• Deduplication, compression, and encryption• Migration of BasicFile LOBs to the SecureFile LOB

format• Performance of LOBs


Introducing SecureFile LOBs

Oracle Database 11g offers a reengineered large object (LOB) data type that:• Improves performance• Eases manageability• Simplifies application development• Offers advanced, next-generation functionality such

as intelligent compression and transparent encryption


Storage of SecureFile LOBs

Oracle Database 11g implements a new storage paradigm for LOB storage:• If the SECUREFILE storage keyword appears in the CREATE TABLE statement, the new storage is used.

• If the BASICFILE storage keyword appears in the CREATE TABLE statement, the old storage paradigm is used.

• By default, the storage is BASICFILE, unless you modify the setting for the DB_SECUREFILE parameter in the init.ora file.


Setting Up SecureFile LOBs

• Create a tablespace for the LOB data:

• Create a table to hold the LOB data:CONNECT oe1/oe1@orclCREATE TABLE customer_profiles(id NUMBER, first_name VARCHAR2 (40), last_name VARCHAR2 (80), profile_info BLOB) LOB(profile_info) STORE AS SECUREFILE (TABLESPACE sf_tbs1);

-- have your dba do this:CREATE TABLESPACE sf_tbs1 DATAFILE 'sf_tbs1.dbf' SIZE 1500M REUSE AUTOEXTEND ON NEXT 200M MAXSIZE 3000M SEGMENT SPACE MANAGEMENT AUTO;

1

2


Writing Data to the SecureFile LOB

• Create the procedure to read the MS Word files and load them into the LOB column.

• Call this procedure from the WRITE_LOB procedure (shown on the next page).

CREATE OR REPLACE PROCEDURE loadLOBFromBFILE_proc (dest_loc IN OUT BLOB, file_name IN VARCHAR2)

IS src_loc BFILE := BFILENAME('CWD', file_name); amount INTEGER := 4000;BEGIN DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY); amount := DBMS_LOB.GETLENGTH(src_loc); DBMS_LOB.LOADFROMFILE(dest_loc, src_loc, amount); DBMS_LOB.CLOSE(src_loc);END loadLOBFromBFILE_proc;



Create the procedure to insert LOBs into the table:

CREATE OR REPLACE PROCEDURE write_lob (p_file IN VARCHAR2)IS i NUMBER; v_fn VARCHAR2(15); v_ln VARCHAR2(40); v_b BLOB;BEGIN DBMS_OUTPUT.ENABLE; DBMS_OUTPUT.PUT_LINE('Begin inserting rows...'); FOR i IN 1 .. 30 LOOP v_fn:=SUBSTR(p_file,1,INSTR(p_file,'.')-1); v_ln:=SUBSTR(p_file,INSTR(p_file,'.')+1,LENGTH(p_file)- INSTR(p_file,'.')-4); INSERT INTO customer_profiles VALUES i, v_fn, v_ln, EMPTY_BLOB()) RETURNING profile_info INTO v_b; loadLOBFromBFILE_proc(v_b,p_file); DBMS_OUTPUT.PUT_LINE('Row '|| i ||' inserted.'); END LOOP; COMMIT;END write_lob;



set serveroutput onset verify onset term onset linesize 200

timing start load_data execute write_lob('karl.brimmer.doc');execute write_lob('monica.petera.doc');execute write_lob('david.sloan.doc');timing stop

1

2


Reading LOBs from the Table

Create the procedure to read LOBs from the table:CREATE OR REPLACE PROCEDURE read_lobIS lob_loc BLOB; CURSOR profiles_cur IS SELECT id, first_name, last_name, profile_info FROM customer_profiles; profiles_rec customer_profiles%ROWTYPE;BEGIN OPEN profiles_cur; LOOP FETCH profiles_cur INTO profiles_rec; lob_loc := profiles_rec.profile_info; DBMS_OUTPUT.PUT_LINE('The length is: '|| DBMS_LOB.GETLENGTH(lob_loc)); DBMS_OUTPUT.PUT_LINE('The ID is: '|| profiles_rec.id); DBMS_OUTPUT.PUT_LINE('The blob is read: '|| UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(lob_loc,200,1))); EXIT WHEN profiles_cur%NOTFOUND; END LOOP; CLOSE profiles_cur;END;


Lesson Agenda




Enabling Deduplication and Compression

To enable deduplication and compression, use the ALTER TABLE statement with the DEDUPLICATE and COMPRESS options.• By enabling deduplication with SecureFiles, duplicate

LOB data is automatically detected and space is conserved by storing only one copy.

• Enabling compression turns on LOB compression.

ALTER TABLE tblnameMODIFY LOB lobcolname(DEDUPLICATE option COMPRESS option)


Enabling Deduplication and Compression: Example

1. Check the space being used by the CUSTOMER_PROFILES table.

2. Enable deduplication and compression on the PROFILE_INFO LOB column with the ALTER TABLE statement.

3. Recheck the space being used by the CUSTOMER_PROFILES table.

4. Reclaim the space.



Checking LOB space usage:

execute check_space

anonymous block completed FS1 Blocks = 0 Bytes = 0 FS2 Blocks = 1 Bytes = 8192 FS3 Blocks = 0 Bytes = 0 FS4 Blocks = 4 Bytes = 32768Full Blocks = 0 Bytes = 0=============================================Total Blocks = 5 || Total Bytes = 40960



Enabling deduplication and compression:

ALTER TABLE customer_profilesMODIFY LOB (profile_info)(DEDUPLICATE LOB COMPRESS HIGH);

Table altered.



Rechecking LOB space usage:

execute check_space




Reclaiming the free space:

ALTER TABLE customer_profiles ENABLE ROW MOVEMENT;Table altered.

ALTER TABLE customer_profiles SHRINK SPACE COMPACT;Table altered.

ALTER TABLE customer_profiles SHRINK SPACE;Table altered.

1

2

3



Examining the space after reclaiming:

execute check_space



Encryption

The encryption option enables you to turn on or off the LOB encryption, and optionally select an encryption algorithm.• Encryption is performed at the block level.• You can specify the encryption algorithm:

– 3DES168– AES128– AES192 (default)– AES256

• The column encryption key is derived from PASSWORD.• All LOBs in the LOB column will be encrypted.• DECRYPT keeps the LOBs in cleartext.• LOBs can be encrypted on a per-column or per-

partition basis.


Using Encryption

• You need a directory to store the Transparent Data Encryption (TDE) wallet. This is required for the SecureFiles LOB encryption.

• Edit the $ORACLE_HOME/network/admin/sqlnet.ora file to indicate the location of the TDE wallet.

mkdir $ORACLE_HOME/wallet

ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY

=/u01/app/oracle/product/11.1.0/db_1/wallet)))


Using Encryption: Example

• Enabling encryption:

• Verify that the LOB is encrypted:

ALTER TABLE customer_profiles MODIFY (profile_info ENCRYPT USING 'AES192');

Table altered.

SELECT *FROM user_encrypted_columns;

TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL----------------- ----------------- ---------------- ---CUSTOMER_PROFILES PROFILE_INFO AES 192 bits key YES


Lesson Agenda




Migrating from BasicFile to SecureFile Format

Scenario: You have data that is stored in the BasicFile format. You want to migrate it to the SecureFile format.

connect oe1/oe1@orclCREATE TABLE customer_profiles(id NUMBER, first_name VARCHAR2 (40), last_name VARCHAR2 (80), profile_info BLOB) LOB(profile_info) STORE AS BASICFILE (TABLESPACE bf_tbs1);

connect system/oracle@orclCREATE TABLESPACE bf_tbs1 DATAFILE 'bf_tbs1.dbf' SIZE 800M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64M SEGMENT SPACE MANAGEMENT AUTO;



Load the data to the BasicFile LOB:

set serveroutput onset verify onset term onset linesize 200

timing start load_data execute write_lob('karl.brimmer.doc');execute write_lob('monica.petera.doc');execute write_lob('david.sloan.doc');timing stop


timing for: load_dataElapsed: 00:00:01.68



Check the timing on reading data in BasicFile format:

set serveroutput onset verify onset term onset lines 200

timing start read_dataexecute read_lob;timing stop


timing for: read_dataElapsed: 00:00:01.15



Check the LOB segment subtype name for BasicFile format:col segment_name format a30col segment_type format 13

SELECT segment_name, segment_type, segment_subtypeFROM dba_segmentsWHERE tablespace_name = 'BF_TBS1'AND segment_type = 'LOBSEGMENT';

SEGMENT_NAME SEGMENT_TYPE SEGME------------------------------ ------------------ -----SYS_LOB0000080068C00004$$ LOBSEGMENT ASSM



• The migration from BasicFile to SecureFiles LOB storage format is performed online.

• This means that the CUSTOMERS_PROFILES table will continue to be accessible during the migration.

• This type of operation is called online redefinition.

CREATE TABLE customer_profiles_interim(id NUMBER, first_name VARCHAR2 (40), last_name VARCHAR2 (80), profile_info BLOB) LOB(profile_info) STORE AS SECUREFILE (TABLESPACE sf_tbs1);



Call the DBMS_REDEFINITION package to perform the online redefinition operation:

connect system/oracle@orclDECLARE error_count PLS_INTEGER := 0;BEGIN DBMS_REDEFINITION.START_REDEF_TABLE ('OE1', 'customer_profiles', 'customer_profiles_interim', 'id id, first_name first_name, last_name last_name, profile_info profile_info', OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID); DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('OE1', 'customer_profiles', 'customer_profiles_interim', 1, true,true,true,false, error_count); DBMS_OUTPUT.PUT_LINE('Errors := ' || TO_CHAR(error_count)); DBMS_REDEFINITION.FINISH_REDEF_TABLE ('OE1', 'customer_profiles', 'customer_profiles_interim');END;


Lesson Agenda




Comparing Performance

Compare the performance on loading and reading LOB columns in the SecureFile and BasicFile formats:

Performance Comparison

Loading Data

Reading Data

SecureFile format

00:00:00.96 00:00:01.09

BasicFile format 00:00:01.68 00:00:01.15


Summary

In this lesson, you should have learned how to:• Describe SecureFile LOB features• Enable SecureFile LOB deduplication, compression,

and encryption• Migrate BasicFile LOBs to the SecureFile LOB format• Analyze the performance of LOBs


Practice 8 Overview: Using SecureFile Format LOBs

This practice covers the following topics:• Setting up the environment for LOBs• Migrating BasicFile LOBs to SecureFile LOBs• Enabling deduplication and compression


Using the Data Warehousing Usability Enhancements


Objectives

After completing this lesson, you should be able to:• Identify the benefits of pivoting and unpivoting

operations• Write cross-tab queries to pivot (rotate) column

values into new columns and to unpivot (rotate) columns into column values

• Pivot and unpivot with multiple columns and multiple aggregates

• Use wildcards and aliases with pivoting operations


Benefits of Using Pivoting Operations

• Data returned by business intelligence (BI) queries is more useful if presented in a cross-tabular format.

• Pivoting enables you to transform multiple rows of input into fewer rows, generally with more columns.

• When pivoting, an aggregation operator is applied, enabling the query to condense large data sets into smaller, more readable results.

• Performing pivots on the server side can:– Enhance processing speed– Reduce network load


PIVOT and UNPIVOT Clauses of the SELECT Statement

• You can use the PIVOT operator of the SELECT statement to write cross-tabulation queries that rotate the column values into new columns, aggregating data in the process.

• You can use the UNPIVOT operator of the SELECT statement to rotate columns into values of a column.

PIVOT UNPIVOT


Pivoting on the QUARTER Column: Conceptual Example

30,000

40,000

60,000

30,000

40,000

20,000

AMOUNT_SOLD

2,500Q1IUSAKids Jeans

2,000Q2CJapanKids Jeans

2,000Q3SUSAShorts

I

P

C

CHANNEL

Kids Jeans

Shorts

Shorts

PRODUCT

1,000Q2Germany

1,500Q4USA

Q2

QUARTER

2,500Poland

QUANTITY_SOLD

COUNTRY

2,000

Q3

Kids Jeans

Shorts

PRODUCT

3,500

2,000

Q2

1,500 2,500

Q4Q1


PIVOT Clause Syntax

table_reference PIVOT [ XML ] ( aggregate_function ( expr ) [[AS] alias ] [, aggregate_function ( expr ) [[AS] alias ] ]... pivot_for_clause pivot_in_clause )

-- Specify the column(s) to pivot whose values are to -- be pivoted into columns.pivot_for_clause = FOR { column |( column [, column]... ) }

-- Specify the pivot column values from the columns you -- specified in the pivot_for_clause.pivot_in_clause = IN ( { { { expr | ( expr [, expr]... ) } [ [ AS] alias] }... | subquery | { ANY | ANY [, ANY]...} } )


Creating a New View: Example

CREATE OR REPLACE VIEW sales_view ASSELECT prod_name AS product, country_name AS country, channel_id AS channel, SUBSTR(calendar_quarter_desc, 6,2) AS quarter, SUM(amount_sold) AS amount_sold, SUM(quantity_sold) AS quantity_soldFROM sh.sales, sh.times, sh.customers, sh.countries, sh.productsWHERE sales.time_id = times.time_id AND sales.prod_id = products.prod_id AND sales.cust_id = customers.cust_id AND customers.country_id = countries.country_idGROUP BY prod_name, country_name, channel_id,SUBSTR(calendar_quarter_desc, 6, 2);


Selecting the SALES_VIEW Data

SELECT product, country, channel, quarter, quantity_soldFROM sales_view;

PRODUCT COUNTRY CHANNEL QUARTER QUANTITY_SOLD------------ ------------ ---------- -------- -------------Y Box Italy 4 01 21Y Box Italy 4 02 17Y Box Italy 4 03 20. . .Y Box Japan 2 01 35Y Box Japan 2 02 39Y Box Japan 2 03 36Y Box Japan 2 04 46Y Box Japan 3 01 65. . .Bounce Italy 2 01 34Bounce Italy 2 02 43. . .9502 rows selected.


Pivoting on the QUARTER Column in the SALES_VIEW Data: Example

SELECT *FROM (SELECT product, quarter, quantity_sold FROM sales_view) PIVOT (sum(quantity_sold) FOR quarter IN ('01', '02', '03', '04'))ORDER BY product DESC;

PRODUCT '01' '02' '03' '04'------------------ ---------- ---------- ---------- ----------Y Box 1455 1766 1716 1992Xtend Memory 3146 4121 4122Unix/Windows 1-user 4259 3887 4601 4049Standard Mouse 3376 1699 2654 2427Smash up Boxing 1608 2127 1999 2110. . .71 rows selected.


Pivoting on the ORDER_MODE Column in the OE Schema: Example

CREATE TABLE pivot_table ASSELECT * FROM(SELECT EXTRACT(YEAR FROM order_date) year, order_mode,

order_total FROM orders)PIVOT(SUM(order_total) FOR order_mode IN ('direct' AS Store,

'online' AS Internet))ORDER BY year;

SELECT * FROM pivot_table ORDER BY year;YEAR STORE INTERNET---------- ---------- ---------- 1990 61655.7 1996 5546.6 1997 310 1998 309929.8 100056.6 1999 1274078.8 1271019.5 2000 252108.3 393349.46 rows selected.


Pivoting on Multiple Columns

• To pivot on more than one column:– A pivoting column is required to be a column of the

table reference on which the pivot is operating– The pivoting column cannot be an arbitrary

expression

• If you need to pivot on an expression, you should alias the expression in a view before the pivot operation.


Pivoting on Multiple Columns

PRODUCT DIRECT_SALES_Q1 INTERNET_SALES_Q1------------------------- --------------- -----------------Y Box 771 253Xtend Memory 1935 350Unix/Windows 1-user pack 2544 397Standard Mouse 2326 256Smash up Boxing 1114 129. . .71 rows selected.

SELECT *FROM (SELECT product, channel, quarter, quantity_sold FROM sales_view) PIVOT (sum(quantity_sold) FOR (channel, quarter) IN ((3, '01') AS Direct_Sales_Q1, (4, '01') AS Internet_Sales_Q1))ORDER BY product DESC;


Pivoting Using Multiple Aggregations

SELECT *FROM (SELECT product, channel, amount_sold, quantity_sold FROM sales_view) PIVOT (SUM(amount_sold) AS sums, SUM(quantity_sold) as sumq FOR channel IN (3 AS Dir_Sales, 4 AS Int_Sales))ORDER BY product DESC;

PRODUCT DIR_SALES_SUMS DIR_SALES_SUMQ INT_SALES_SUMS INT_SALES_SUMQ------------ -------------- -------------- -------------- --------------Y Box 1081050.96 3552 382767.45 1339Xtend Memory 217011.38 8562 40553.93 1878Unix/Windows 1999882.17 9313 376071.62 1872Standard Mouse 153199.63 6140 28768.04 1195Smash up Boxing 174592.24 5106 27858.84 904...

71 rows selected.


Distinguishing PIVOT-Generated Nulls from Nulls in the Source Data

SELECT * FROM sales2;

PROD_ID QTR AMOUNT_SOLD------- --- -----------100 Q1 10100 Q1 20100 Q2 200 Q1 50

SELECT *FROM ( SELECT prod_id, qtr, amount_sold FROM sales2) PIVOT (SUM(amount_sold), COUNT(*) AS count_total FOR qtr IN ('Q1', 'Q2') )ORDER BY prod_id DESC;

PROD_ID Q1 Q1_COUNT_TOTAL Q2 Q2_COUNT_TOTAL------- --- -------------- --------- ----------------

100 30 2 1200 50 1 0


Using the XML Keyword to Specify Pivot Values: Two Methods

ANY

If you use theXML keyword

with the PIVOT syntax

Use the ANY keyword, or

Use a subquery

XML

The XML string for each row includes only the pivot values found in the input

data for that row.

The XML string includes all pivot values found by the subquery even if there are

no aggregate values.


Specifying PIVOT Values: Using the ANY Keyword

PRODUCT--------------------------------------------------CHANNEL_XML------------------------------------------------------------------------ . . .1.44MB External 3.5" Diskette<PivotSet><item><column name = "CHANNEL">3</column><column name = "SUM(QUANTITY_SOLD)">14189</column></item><item><column name = "CHANNEL">2</column><column name = "SUM(QUANTITY_SOLD)">6455</column></item><item><column name = "CHANNEL">4</column><column name = "SUM(QUANTITY_SOLD)">2464</column></item></PivotSet>71 rows selected.

SET LONG 1024;SELECT *FROM (SELECT product, channel, quantity_sold FROM sales_view ) PIVOT XML (SUM(quantity_sold) FOR channel IN (ANY) )ORDER BY product DESC;


Specifying PIVOT Values: Using Subqueries

PRODUCT----------CHANNEL_XML-----------------------------------------------------------------------. . .Y Box<PivotSet><item><column name = "CHANNEL">9</column><column name = "SUM(QUANTITY_SOLD)">1</column></item><item><column name = "CHANNEL">2</column><column name ="SUM(QUANTITY_SOLD)">2037</column></item><item><column name = "CHANNEL">5</column><column name = "SUM(QUANTITY_SOLD)"></column></item><item><column name = "CHANNEL">3</column><column name ="SUM(QUANTITY_SOLD)">3552</column></item>. . .

SELECT *FROM (SELECT product, channel, quantity_sold FROM sales_view ) PIVOT XML(SUM(quantity_sold) FOR channel IN (SELECT distinct channel_id FROM sh.channels));


Unpivoting on the QUARTER Column: Conceptual Example

2,000

Q3

Kids Jeans

Shorts

PRODUCT

3,500

2,000

Q2

1,500 2,500

Q4Q1

2,500Q1Kids Jeans

2,000Q2Kids Jeans

3,500Q2Shorts

1,500Q4Kids Jeans

Q3

QUARTER

2,000Shorts

SUM_OF_QUANTITYPRODUCT


Using the UNPIVOT Operator

• An UNPIVOT does not reverse a PIVOT operation; instead, it rotates data from columns into rows.

• If you are working with pivoted data, an UNPIVOT operation cannot reverse any aggregations that have been made by PIVOT or any other means.

UNPIVOT


Using the UNPIVOT Clause

• The UNPIVOT clause rotates columns from a previously pivoted table or a regular table into rows. You specify:

– The measure columns to be unpivoted– The names for the columns that will result from the

unpivot operation– The columns that will be unpivoted back into values of

the column specified in the unpivot_for_clause

• You can use an alias to map the column name to another value.


Data Types of the Value Columns in an UNPIVOT Operation

Data Type for the Value Columns

Resulting Unpivoted Column Data Type

If ALL the value columns are CHAR CHAR

If ANY value column is VARCHAR2 VARCHAR2

If ALL the value columns are NUMBER NUMBER

If ANY value column is BINARY_DOUBLE BINARY_DOUBLE

If NO value column is BINARY_DOUBLE but ANY value column is BINARY_FLOAT

BINARY_FLOAT


Unpivot Clause Syntax

table_reference UNPIVOT [{INCLUDE|EXCLUDE} NULLS]-- specify the measure column(s) to be unpivoted.( { column | ( column [, column]... ) } unpivot_for_clause unpivot_in_clause )

-- Specify one or more names for the columns that will-- result from the unpivot operation.

unpivot_for_clause = FOR { column | ( column [, column]... ) }

-- Specify the columns that will be unpivoted into values of -- the column specified in the pivot_for_clause.

unpivot_in_clause = ( { column | ( column [, column]... ) } [ AS { constant | ( constant [, constant]... ) } ] [, { column | ( column [, column]... ) } [ AS { constant | ( constant [, constant]...) } ] ]...)


Creating a New Pivot Table: Example

CREATE TABLE pivotedtable ASSELECT *FROM (SELECT product, quarter, quantity_sold FROM sales_view) PIVOT (sum(quantity_sold) FOR quarter IN ('01' AS Q1, '02' AS Q2, '03' AS Q3, '04' AS Q4));

Table created.

SELECT * FROM pivotedtable ORDER BY product DESC;

PRODUCT Q1 Q2 Q3 Q4------------------ ---------- ---------- ---------- ----------Y Box 1455 1766 1716 1992Xtend Memory 3146 4121 4122. . .

71 rows selected.


Unpivoting on the QUARTER Column in the SH Schema: Example

SELECT *FROM pivotedtable UNPIVOT (quantity_sold For Quarter IN (Q1, Q2, Q3, Q4))ORDER BY product DESC, quarter;

PRODUCT QUARTER QUANTITY_SOLD-------------------------- ------- -------------Y Box Q1 1455Y Box Q2 1766Y Box Q3 1716Y Box Q4 1992Xtend Memory Q1 3146Xtend Memory Q2 4121Xtend Memory Q3 4122Xtend Memory Q4 3802Unix/Windows 1-user pack Q1 4259. . .

284 rows selected.


Unpivoting the ORDER_MODE Column in the OE Schema: Example

SELECT * FROM pivot_table UNPIVOT (yearly_total FOR order_mode IN (store AS 'direct', internet AS 'online'))ORDER BY year, order_mode;

YEAR ORDER_MODE YEARLY_TOTAL----- ----------- ------------1990 direct 61655.71996 direct 5546.61997 direct 3101998 direct 309929.81998 online 100056.61999 direct 1274078.81999 online 1271019.52000 direct 252108.32000 online 393349.4

9 rows selected.


Unpivoting on Multiple Columns in the SH Schema: Example

CREATE TABLE multi_col_pivot ASSELECT *FROM (SELECT product, channel, quarter, quantity_sold FROM sales_view) PIVOT (sum(quantity_sold) FOR (channel, quarter) IN ((3, '01') AS Direct_Sales_Q1, (4, '01') AS Internet_Sales_Q1))ORDER BY product DESC;Table created.

SELECT * FROM multi_col_pivot;

PRODUCT DIRECT_SALES_Q1 INTERNET_SALES_Q1---------------------------- --------------- -----------------Y Box 771 253Xtend Memory 1935 350. . .71 rows selected.


Unpivoting on Multiple Columns in the SH Schema: Example

-- Provide explicit values for the unpivot columns

SELECT *FROM multi_col_pivotUNPIVOT (quantity_sold FOR (channel, quarter) IN ( Direct_Sales_Q1 AS ('Direct', 'Q1'),

Internet_Sales_Q1 AS ('Internet', 'Q1') ) )ORDER BY product DESC, quarter;

PRODUCT CHANNEL QUARTER QUANTITY_SOLD------------------------------ -------- ------- -------------Y Box Internet Q1 253Y Box Direct Q1 771Xtend Memory Internet Q1 350Xtend Memory Direct Q1 1935. . .142 rows selected.


Unpivoting on Multiple Aggregations in the SH Schema: Example

CREATE TABLE multi_agg_pivot ASSELECT *FROM (SELECT product, channel, quarter, quantity_sold, amount_sold FROM sales_view) PIVOT (sum(quantity_sold) sumq, sum(amount_sold) suma FOR channel IN (3 AS Direct, 4 AS Internet) )ORDER BY product DESC;Table created.

SELECT * FROM multi_agg_pivot;

PRODUCT QUARTER DIRECT_SUMQ DIRECT_SUMA INTERNET_SUMQ INTERNET_SUMA---------- ------- ----------- ----------- ------------- -------------. . .Bounce 01 1000 21738.97 347 6948.76Bounce 02 1212 26417.37 453 9173.59Bounce 03 1746 37781.27 528 10029.99Bounce 04 1741 38838.63 632 12592.07. . .283 row selected.


Unpivoting on Multiple Aggregations in the SH Schema: Example

SELECT *FROM multi_agg_pivotUNPIVOT ((total_amount_sold, total_quantity_sold)FOR channel IN ((Direct_sumq, Direct_suma) AS 3, (Internet_sumq, Internet_suma) AS 4 ))ORDER BY product DESC, quarter, channel;

PRODUCT QUARTER CHANNEL TOTAL_AMOUNT_SOLD TOTAL_QUANTITY_SOLD------- ------- ------- ----------------- -------------------Bounce 01 3 1000 21738.97Bounce 01 4 347 6948.76Bounce 02 3 1212 26417.37Bounce 02 4 453 9173.59Bounce 03 3 1746 37781.27Bounce 03 4 528 10029.99Bounce 04 3 1741 38838.63Bounce 04 4 632 12592.07. . .566 rows selected.


Summary

In this lesson, you should have learned how to:• Identify the benefits of pivoting and unpivoting

operations• Write cross-tab queries to pivot (rotate) column

values into new columns and to unpivot (rotate) columns into column values

• Pivot and unpivot with multiple columns and multiple aggregates

• Use wildcards and aliases with pivoting operations


Practice 9 Overview: Pivoting

This practice covers the following topics:• Using the PIVOT clause with the SELECT statement.• Examining special pivoting cases

Copyright © 2007, Oracle. All rights reserved.C - 318

Objectives

After completing this lesson, you should be able to do the following:• Use the PL/SQL Debugger tool in SQL Developer• Step through PL/SQL code line by line• Step through called PL/SQL subroutines• Analyze data as the routine is running• Change variable values at run time• Control the execution of a PL/SQL block


Debugging PL/SQL Subprograms Using the SQL Developer Debugger

• You can use the debugger to control the execution of your PL/SQL program.

• To debug a PL/SQL subprogram, a security administrator needs to grant the following privileges to the application developer:

– DEBUG ANY PROCEDURE– DEBUG CONNECT SESSION


Debugging a Subprogram: Overview

1. Edit procedure 2. Add breakpoints 3. Compile for Debug

4. Debug5. Enter parameter value(s)

6. Choose debugging tool,and monitor data


The Procedure or Function Code Editing Tab


The Procedure or Function Tab Toolbar

Icon Description

1. Run Starts normal execution of the function or procedure, and displays the results on the Running - Log tabbed page

2. Debug Starts execution of the subprogram in debug mode, and displays the Debugging - Log tabbed page, which includes the debugging toolbar for controlling the execution

3. Compile Performs a PL/SQL compilation of the subprogram

4. Compile for Debug

Performs a PL/SQL compilation so that it can be debugged

1

2

3

4


The Debugging – Log Tab Toolbar

Icon Description

1. Find Execution Point

Goes to the next execution point

2. Resume Continues execution

3. Step Over Bypasses the next subprogram and goes to the next statement after the subprogram

4. Step Into Executes a single program statement at a time. If the execution point is located on a call to a subprogram, it steps into the first statement in that subprogram.

5. Step Out Leaves the current subprogram and goes to the next statement with a breakpoint

1

2 3 4 5


The Debugging – Log Tab Toolbar

Icon Description

6. Step to End of Method

Goes to last statement in the current subprogram (or to the next breakpoint if there are any in the current procedure)

7. Pause Halts execution but does not exit

8. Terminate Halts and exits the execution

6

7

8

Displays breakpoints


Additional Tabs

Tab Description

Data Located under the code text area, displays information about all variables

Watches Located under the code text area, displays information about watchpoints


Debugging a Procedure Example:Creating a New emp_list Procedure


Debugging a Procedure Example:Creating a New get_location Function


Setting Breakpoints and Compiling emp_list in Debug Mode


Compiling the get_location Function for Debug Mode


Debugging emp_list and Enter Value for PMAXROWS Parameter

Enter the procedure’sParameter value using the

Anonymous block.


Debugging emp_list: Step Into the Code

Program control stops at first breakpoint.

1

2


Debugging emp_list: Step Into the Code

Step Into [F7]:Steps into and

executes the cursorcode. Control is transferred to

Cursor definition

1

2


Viewing the Data


Modifying the Variables While Debugging the Code


Debugging emp_list:Step Over the Code

1

2

Step Over [F8]:Executes the Cursor (same as [F7])

But control is not transferred

to Open Cursor code


Debugging emp_list:Step Out of the Code ([SHIFT]+[F7])

1

2

3

4

5

6


Debugging emp_list: Run to Cursor [F4]

Run to Cursor (F4): Run to your cursor location

without having to single step or set a breakpoint.


Debugging emp_list: Step to End of Method

Loops until i <> PMAXROWS


Summary

In this lesson, you should have learned how to debug procedures and functions.

Copyright © 2007, Oracle. All rights reserved.D - 342

Objectives

After completing this appendix, you should be able to do the following:• Log in to SQL*Plus• Edit SQL commands• Format output using SQL*Plus commands• Interact with script files


SQL and SQL*Plus Interaction

Buffer

Server

SQL statements

Query results

SQL scripts

SQL*Plus


SQL Statements VersusSQL*Plus Commands

• A language• ANSI-standard• Keywords cannot be

abbreviated• Statements manipulate

data and table definitions in the database

SQLstatements

SQL SQL*Plus

SQLbuffer

SQL*Plus commands

SQL*Plus buffer

• An environment• Oracle-proprietary• Keywords can be

abbreviated• Commands do not allow

manipulation of values in the database


Overview of SQL*Plus

• Log in to SQL*Plus• Describe the table structure• Edit your SQL statement• Execute SQL from SQL*Plus• Save SQL statements to files and append SQL

statements to files• Execute saved files• Load commands from file to buffer to edit


sqlplus [username[/password[@database]]]

Logging In to SQL*Plus

1

2


Changing the Settings of SQL*Plus Environment



Use the SQL*Plus DESCRIBE command to display the structure of a table:

DESC[RIBE] tablename



Name Null? Type----------------------- -------- ------------DEPARTMENT_ID NOT NULL NUMBER(4)DEPARTMENT_NAME NOT NULL VARCHAR2(30)MANAGER_ID NUMBER(6)LOCATION_ID NUMBER(4)

DESCRIBE departments


SQL*Plus Editing Commands

• A[PPEND] text• C[HANGE] / old / new• C[HANGE] / text /• CL[EAR] BUFF[ER]• DEL• DEL n• DEL m n


SQL*Plus Editing Commands

• I[NPUT]• I[NPUT] text• L[IST]• L[IST] n• L[IST] m n • R[UN]• n• n text• 0 text


Using LIST, n, and APPEND

LIST 1 SELECT last_name 2* FROM employees

1 1* SELECT last_name

A , job_id 1* SELECT last_name, job_id

LIST 1 SELECT last_name, job_id 2* FROM employees


Using the CHANGE Command

LIST 1* SELECT * from employees

c/employees/departments 1* SELECT * from departments

LIST

1* SELECT * from departments


SQL*Plus File Commands

• SAVE filename• GET filename• START filename• @ filename• EDIT filename• SPOOL filename• EXIT


Using the SAVE, START, and EDIT Commands

LIST 1 SELECT last_name, manager_id, department_id 2* FROM employees

SAVE my_query Created file my_query

START my_query

LAST_NAME MANAGER_ID DEPARTMENT_ID------------------------- ---------- -------------King 90Kochhar 100 90...107 rows selected.


Using the SAVE, START, and EDIT Commands

EDIT my_query


SERVEROUTPUT Command

• Use the SET SERVEROUT[PUT] command to control whether to display the output of stored procedures or PL/SQL blocks in SQL*Plus.

• The DBMS_OUTPUT line length limit is increased from 255 bytes to 32,767 bytes.

• The default size is now unlimited.• Resources are not preallocated when SERVEROUTPUT

is set.• Because there is no performance penalty, use UNLIMITED unless you want to conserve physical memory.

SET SERVEROUT[PUT] {ON | OFF} [SIZE {n | UNL[IMITED]}] [FOR[MAT] {WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]}]


Using the SQL*Plus SPOOL Command

SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]

Option Description

file_name[.ext] Spools output to the specified file name

CRE[ATE] Creates a new file with the name specified

REP[LACE] Replaces the contents of an existing file. If the file does not exist, REPLACE creates the file.

APP[END] Adds the contents of the buffer to the end of the file that you specify

OFF Stops spooling

OUT Stops spooling and sends the file to your computer’s standard (default) printer


Using the AUTOTRACE Command

• It displays a report after the successful execution of SQL DML statements, such as SELECT, INSERT, UPDATE, or DELETE.

• The report can now include execution statistics and the query execution path.

SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

SET AUTOTRACE ON-- The AUTOTRACE report includes both the optimizer-- execution path and the SQL statement execution -- statistics.


Summary

In this appendix, you should have learned how to use SQL*Plus as an environment to do the following:• Execute SQL statements• Edit SQL statements• Format output• Interact with script files

Copyright © 2007, Oracle. All rights reserved.E - 362

Objectives

After completing this lesson, you should be able to do the following:• Describe an object type• Create an object type specification• Implement the constructor method on objects• Create collections

– Nested table collections– Varray collections

• Use collections methods• Manipulate collections


Understanding Collections

• A collection is a group of elements, all of thesame type.

• Collections work like arrays.• Collections can store instances of an object type and,

conversely, can be attributes of an object type.• Types of collections in PL/SQL:

– Nested tables– Varrays– Associative arrays– String-indexed collections– INDEX BY pls_integer


Nested table: Varray:

Describing the Collection Types


Listing Characteristics for Collections

PL/SQL Nested Tables

DB Nested Tables

PL/SQLVarrays

DBVarrays

Maximum size

No No Yes Yes

Sparsity Can be No Dense Dense

Storage N/A Stored out of line

N/A Storedinline (if< 4,000 bytes)

Ordering Does not retain ordering and subscripts

Does not retain ordering and subscripts

Retains ordering and subscripts

Retains ordering and subscripts


Using Collections Effectively

• Varrays involve fewer disk accesses and are more efficient.

• Use nested tables for storing large amounts of data.• Use varrays to preserve the order of elements in the

collection column.• If you do not have a requirement to delete elements

in the middle of a collection, favor varrays.• Varrays do not allow piecewise updates.


Creating Collection Types

• Nested table in the database:

• Nested table in PL/SQL:

• Varray in the database:

• Varray in PL/SQL:

CREATE [OR REPLACE] TYPE type_name AS TABLE OF Element_datatype [NOT NULL];

CREATE [OR REPLACE] TYPE type_name AS VARRAY (max_elements) OF element_datatype [NOT NULL];

TYPE type_name IS TABLE OF element_datatype

[NOT NULL];

TYPE type_name IS VARRAY (max_elements) OF

element_datatype [NOT NULL];


Declaring Collections: Nested Table

• First, define an object type:

• Second, declare a column of that collection type:

CREATE TYPE typ_item AS OBJECT --create object (prodid NUMBER(5), price NUMBER(7,2) )/CREATE TYPE typ_item_nst -- define nested table type AS TABLE OF typ_item/

CREATE TABLE pOrder ( -- create database table ordid NUMBER(5), supplier NUMBER(5), requester NUMBER(4), ordered DATE, items typ_item_nst) NESTED TABLE items STORE AS item_stor_tab/

1

2

3


Supplier Requester Ordered Items

pOrder nested table

123

321

456

789

10-MAR-97

12-FEB-97

Understanding Nested Table Storage

Nested tables are stored out-of-line in storage tables.

Storage table

$ 45.95$ 99.99

$ 0.22$300.00

901879

333112

NESTED_TABLE_ID ProdID Price


CREATE TABLE department ( -- create database table dept_id NUMBER(2), name VARCHAR2(15), budget NUMBER(11,2), projects typ_ProjectList) -- declare varray as column/

Declaring Collections: Varray

• First, define a collection type:

• Then, declare a collection of that type:

CREATE TYPE typ_Project AS OBJECT( --create object project_no NUMBER(2), title VARCHAR2(35), cost NUMBER(7,2))/CREATE TYPE typ_ProjectList AS VARRAY (50) OF typ_Project

-- define VARRAY type/

1

2

3


CREATE OR REPLACE PACKAGE manage_dept_proj AS TYPE typ_proj_details IS TABLE OF typ_Project; ... PROCEDURE allocate_proj (propose_proj IN typ_proj_details); FUNCTION top_project (n NUMBER) RETURN typ_proj_details; ...

Working with Collections in PL/SQL

• You can declare collections as formal parameters of procedures and functions.

• You can specify a collection type in the RETURN clause of a function specification.

• Collections follow the usual scoping and instantiation rules.


Initializing Collections

Three ways to initialize:• Use a constructor.• Fetch from the database.• Assign another collection variable directly.

DECLARE --this example uses a constructor v_accounting_project typ_ProjectList;BEGIN v_accounting_project := typ_ProjectList (typ_Project (1, 'Dsgn New Expense Rpt', 3250), typ_Project (2, 'Outsource Payroll', 12350), typ_Project (3, 'Audit Accounts Payable',1425)); INSERT INTO department VALUES(10, 'Accounting', 123, v_accounting_project);...END;/


DECLARE -- this example uses a fetch from the database v_accounting_project typ_ProjectList;BEGIN SELECT projects INTO v_accounting_project FROM department WHERE dept_id = 10; ...END;/

Initializing Collections

DECLARE -- this example assigns another collection -- variable directly v_accounting_project typ_ProjectList; v_backup_project typ_ProjectList;BEGIN SELECT projects INTO v_accounting_project FROM department WHERE dept_id = 10; v_backup_project := v_accounting_project;END;/

1

2


Referencing Collection Elements

Use the collection name and a subscript to reference a collection element:• Syntax:

• Example:

• To reference a field in a collection:

collection_name(subscript)

v_accounting_project(1)

v_accounting_project(1).cost


Using Collection Methods

• EXISTS• COUNT• LIMIT• FIRST and LAST• PRIOR and NEXT• EXTEND• TRIM• DELETE

collection_name.method_name [(parameters)]


DECLARE i INTEGER; v_accounting_project typ_ProjectList;BEGIN v_accounting_project := typ_ProjectList( typ_Project (1,'Dsgn New Expense Rpt', 3250), typ_Project (2, 'Outsource Payroll', 12350), typ_Project (3, 'Audit Accounts Payable',1425)); i := v_accounting_project.FIRST ; WHILE i IS NOT NULL LOOP IF v_accounting_project(i).cost > 10000 then DBMS_OUTPUT.PUT_LINE('Project too expensive: ' || v_accounting_project(i).title); END IF; i := v_accounting_project.NEXT (i); END LOOP;END;/


Traverse collections with methods:


DECLARE v_my_projects typ_ProjectList; v_array_count INTEGER; v_last_element INTEGER;BEGIN SELECT projects INTO v_my_projects FROM department WHERE dept_id = 10; v_array_count := v_my_projects.COUNT ; dbms_output.put_line('The # of elements is: ' || v_array_count); v_my_projects.EXTEND ; --make room for new project v_last_element := v_my_projects.LAST ; dbms_output.put_line('The last element is: ' || v_last_element); IF v_my_projects.EXISTS(5) THEN dbms_output.put_line('Element 5 exists!'); ELSE dbms_output.put_line('Element 5 does not exist.'); END IF;END;/



CREATE OR REPLACE PROCEDURE add_project ( p_deptno IN NUMBER, p_new_project IN typ_Project, p_position IN NUMBER ) IS v_my_projects typ_ProjectList;BEGIN SELECT projects INTO v_my_projects FROM department WHERE dept_id = p_deptno FOR UPDATE OF projects; v_my_projects.EXTEND; --make room for new project /* Move varray elements forward */ FOR i IN REVERSE p_position..v_my_projects.LAST - 1 LOOP v_my_projects(i + 1) := v_my_projects(i); END LOOP; v_my_projects(p_position) := p_new_project; -- add new -- project UPDATE department SET projects = v_my_projects WHERE dept_id = p_deptno;END add_project;/

Manipulating Individual Elements


Avoiding Collection Exceptions

Common exceptions with collections:• COLLECTION_IS_NULL • NO_DATA_FOUND • SUBSCRIPT_BEYOND_COUNT • SUBSCRIPT_OUTSIDE_LIMIT • VALUE_ERROR


Common exceptions with collections:DECLARE TYPE NumList IS TABLE OF NUMBER; nums NumList; -- atomically nullBEGIN /* Assume execution continues despite the raised exceptions. */ nums(1) := 1; -- raises COLLECTION_IS_NULL nums := NumList(1,2); -- initialize table nums(NULL) := 3 -- raises VALUE_ERROR nums(0) := 3; -- raises SUBSCRIPT_OUTSIDE_LIMIT nums(3) := 3; -- raises SUBSCRIPT_BEYOND_COUNT nums.DELETE(1); -- delete element 1 IF nums(1) = 1 THEN -- raises NO_DATA_FOUND...

Avoiding Collection Exceptions


Summary

In this lesson, you should have learned how to:• Identify types of collections

– Nested tables– Varrays

• Define nested tables and varrays in the database• Define nested tables and varrays in PL/SQL

– Access collection elements– Use collection methods in PL/SQL– Identify raised exceptions with collections– Decide which collection type is appropriate for each

scenario

Copyright © 2007, Oracle. All rights reserved.F - 384

Objectives

After completing this lesson, you should be able to:• List the materialized view enhancements:

– Review the materialized view concepts– List the new and updated MV catalog views– Describe the updated columns in the MV catalog

views– Identify the refresh performance enhancements– Review the benefits of using materialized views


Objectives

• List the query rewrite enhancements:– Review the benefits of query rewrite– Use the query rewrite enhancement to support

queries with inline views– Identify the query rewrite enhancement that supports

queries with remote tables


Lesson Agenda

• Materialized view enhancements:– Concepts– New and updated MV catalog views– Refresh performance enhancements– Benefits of using materialized views

• Query rewrite enhancements:– Query rewrite benefits– Query rewrite enhancements with inline views– Query rewrite enhancements with remote tables


Materialized View: Overview

Materialized views are:• Schema objects• Used to summarize, compute, replicate, and

distribute data• Used in data warehouse, decision support, distributed

or mobile computing environments


Materialized View: Overview

Sample materialized view (in the SH sample schema):

DESCRIBE sh.FWEEK_PSCAT_SALES_MVName Null Type

------------------------------ -------- ---------------- WEEK_ENDING_DAY NOT NULL DATE

PROD_SUBCATEGORY NOT NULL VARCHAR2(50)

DOLLARS NUMBER

CHANNEL_ID NOT NULL NUMBER

PROMO_ID NOT NULL NUMBER


New and Updated MV Catalog Views

• New catalog views display the partition change tracking (PCT) information for a given materialized view.

• New catalog views display which sections of the materialized views data are fresh or stale.

• You can view the partition staleness information of the materialized view.

• It affects the usability and maintainability of the materialized view.


PCT Catalog Views Showing Stalessness Corresponding to Base Partitions

USER/ALL/DBA_MVIEW_DETAIL_RELATIONS

views (UPDATED)

USER/ALL/DBA_MVIEW_DETAIL_SUBPARTITION views (NEW)

USER/ALL/DBA_MVIEW_DETAIL_PARTITION

views (NEW)

USER/ALL/DBA_MVIEWS views (UPDATED)


New Columns in the USER/ALL/DBA_MVIEWS Catalog View

• NUM_PCT_TABLES: Specifies the number of PCT detail tables

• NUM_FRESH_PCT_REGIONS: Specifies the number of fresh PCT partition regions

• NUM_STALE_PCT_REGIONS: Specifies the number of stale PCT partition regions

SELECT mview_name, num_pct_tables, num_fresh_pct_regions, num_stale_pct_regionsFROM all_mviewsWHERE owner = 'SH';

MVIEW_NAME NUM_PCT_TABLES NUM_FRESH_PCT_REGIONS NUM_STALE_PCT_REGIONS ------------ -------------- --------------------- ---------------------FWEEK_PSCAT_ 1 28 0SALES_MV


• DETAILOBJ_PCT: Is the detail object PCT supported• NUM_FRESH_PCT_PARTITIONS: Specifies the number of

fresh PCT partitions• NUM_STALE_PCT_PARTITIONS: Specifies the number of

stale PCT partitions

DESCRIBE all_mview_detail_relationsName Null Type ------------------------------ -------- ----------------OWNER NOT NULL VARCHAR2(30) MVIEW_NAME NOT NULL VARCHAR2(30) DETAILOBJ_OWNER NOT NULL VARCHAR2(30) DETAILOBJ_NAME NOT NULL VARCHAR2(30)DETAILOBJ_TYPE VARCHAR2(9)DETAILOBJ_ALIAS VARCHAR2(30)DETAILOBJ_PCT VARCHAR2(1) NUM_FRESH_PCT_PARTITIONS NUMBER NUM_STALE_PCT_PARTITIONS NUMBER

New Columns in USER/ALL/DBA_MVIEW_DETAIL_RELATIONS


New USER/ALL/DBA_MVIEW_DETAIL_PARTITION Catalog View

SELECT detailobj_owner, detailobj_name, detail_partition_name, detail_partition_position POSITION, freshness FRESHFROM all_mview_detail_partitionWHERE mview_name = 'FWEEK_PSCAT_SALES_MV';DETAIL_OBJ_OWNER DETAIL_OBJ_NAME DETAIL_PARTITION_NAME POSITION FRESH---------------- --------------- --------------------- -------- -----SH SALES SALES_1995 1

FRESHSH SALES SALES_1996 2

FRESHSH SALES SALES_H1_1997 3 FRESHSH SALES SALES_H2_1997 4 FRESHSH SALES SALES_Q1_1998 5

FRESH...SH SALES SALES_Q1_2003 25 FRESHSH SALES SALES_Q2_2003 26 FRESHSH SALES SALES_Q3_2003 27 FRESHSH SALES SALES_Q4_2003 28

FRESH

28 rows selected


New Catalog View: USER/ALL/DBA_MVIEW_DETAIL_SUBPARTITION

DESCRIBE all_mview_detail_subpartitionName Null Type ------------------------------ -------- ------ OWNER NOT NULL VARCHAR2(30)MVIEW_NAME NOT NULL VARCHAR2(30)DETAILOBJ_OWNER NOT NULL VARCHAR2(30)

DETAILOBJ_NAME NOT NULL VARCHAR2(30)DETAIL_PARTITION_NAME VARCHAR2(30) DETAIL_SUBPARTITION_NAME VARCHAR2(30)DETAIL_SUBPARTITION_POSITION NUMBER FRESHNESS CHAR(5)


Refresh Performance Improvementsin Oracle Database 11g

Refresh operations on materialized views are now faster with the following improvements:• Refresh statement combinations (merge and delete)• Removal of unnecessary refresh hint• Index creation for UNION ALL MV• PCT refresh possible for UNION ALL MV


Using Summaries to Improve Performance

• Special types of aggregate views• Improve query execution time by precalculating

expensive joins and aggregation operations prior to execution and storing results in a database table

• Created with a materialized view


Summary Management

DBA creates materialized view (summary table)

End user queries tables and views

Oracle server rewrites SQL query to use summary tables


Summary Management Components

• Mechanisms to define materialized views and dimensions

• Refresh mechanism to ensure materialized views contain the latest data

• Query rewrite capability to transparently rewrite a query to use a materialized view

• SQL Access Advisor: Recommends materialized views and indexes to be created

• DBMS_ADVISOR.TUNE_MVIEW procedure: Shows you how to make your materialized view fast refreshable and use general query rewrite


Using Summary Management

1. Use the SQL Access Advisor to determine how you will use materialized views.

2. Create materialized views and design how queries will be rewritten.

3. Use DBMS_ADVISOR.TUNE_MVIEW to obtain an optimized materialized view as necessary.

4. View the CREATE output results by querying USER_TUNE_MVIEW or DBA_TUNE_MVIEW.

DBMS_ADVISOR.TUNE_MVIEW ( name, 'CREATE MATERIALIZED VIEW my_mv_name REFRESH FAST AS SELECT_statement_goes_here);


Lesson Agenda

• Materialized view enhancements:– Concepts– New and updated MV catalog views– Refresh performance enhancements– Benefits of using materialized views

• Query rewrite enhancements:– Query rewrite benefits– Query rewrite enhancements with inline views– Query rewrite enhancements with remote tables


Query Rewrite: Overview

• Tries to use materialized views instead of base tables to return query results

• Can save orders of magnitude of CPU and elapsed time to return results as queries are precomputed

• Query does not necessarily have to be in the exact form of the materialized view query to rewrite

• Various requirements for query rewrite to occur

Query rewrite


Cost-Based Query Rewrite Process

Query rewrite Generate plan

Compare plan costs

Generate plan Pick best plan

End user queries tables and views


What Can Be Rewritten?

• Queries and subqueries in the following types of SQL statements:

– SELECT– CREATE TABLE … AS SELECT– INSERT INTO … SELECT

• Subqueries in DML statements:– INSERT– UPDATE– DELETE

• Subqueries in the set operators:– UNION– UNION ALL– INTERSECT– MINUS


Query Rewrite Enhancement to Support Queries Containing Inline Views

Query with inline view

Query inline view’s (IV) text matches the MV’s IV text?

Rewrite query

Yes

Query inline view’s (IV) text equivalent to the MV’s IV text?

No query rewrite

Yes

No

No


When Are Two Inline Views Equivalent?

Two inline views are considered equivalent when:• The SELECT lists and GROUP BY lists are equivalent• The FROM clauses contain the same or equivalent

objects• The join graphs, including all the selections in the WHERE clauses are equivalent

• The HAVING clauses are equivalent


An MV Inline View Text Matches a Query’s Inline View Text: Example

CREATE MATERIALIZED VIEW SUM_SALES_MVENABLE QUERY REWRITE AS SELECT mv_iv.prod_id, mv_iv.cust_id, sum(mv_iv.amount_sold) sum_amount_sold FROM (SELECT prod_id, cust_id, amount_sold

FROM sales, products WHERE sales.prod_id = products.prod_id) MV_IV

GROUP BY mv_iv.prod_id, mv_iv.cust_id;

-- The text of the IV matches exactly the text of the-- MV; therefore, the query is rewritten with the MVSELECT iv.prod_id, iv.cust_id, SUM(iv.amount_sold) sum_amount_soldFROM (SELECT prod_id, cust_id, amount_sold FROM sales, products WHERE sales.prod_id = products.prod.id) IVGROUP BY iv.prod_id, iv.cust_id;

1

2


An MV Inline View Text is Equivalent to a Query’s Inline View Text: Example

CREATE MATERIALIZED VIEW SUM_SALES_MVENABLE QUERY REWRITE ASSELECT mv_iv.prod_id, mv_iv.cust_id, sum(mv_iv.amount_sold) sum_amount_soldFROM (SELECT prod_id, cust_id, amount_sold

FROM sales, products WHERE sales.prod_id = products.prod_id) MV_IV

GROUP BY mv_iv.prod_id, mv_iv.cust_id;

-- The text of the IV doesn’t match the text of the MV;-- however, they are equivalentSELECT iv.prod_id, iv.cust_id, SUM(iv.amount_sold) sum_amount_soldFROM (SELECT prod_id, cust_id, amount_sold FROM products, sales WHERE sales.prod_id = products.prod.id) IVGROUP BY iv.prod_id, iv.cust_id;

1

2


Transforming and Rewriting the Query from the Two Previous Examples

-- Both above queries are finally re-written as follows:SELECT p_id, c_id, sum_soldFROM SUM_SALES_MV;

-- Both above queries are first transformed as follows:SELECT prod_id, cust_id, sum(amount_sold)FROM MV_IVGROUP BY prod_id, cust_id;

SELECT iv.prod_id, iv.cust_id,SUM(iv.amount_sold) sum_amount_soldFROM (SELECT prod_id, cust_id, amount_sold FROM products, sales WHERE sales.prod_id = products.prod.id) IVGROUP BY iv.prod_id, iv.cust_id;

SELECT iv.prod_id, iv.cust_id,SUM(iv.amount_sold) sum_amount_soldFROM (SELECT prod_id, cust_id, amount_sold FROM sales, products WHERE sales.prod_id = products.prod.id) IVGROUP BY iv.prod_id, iv.cust_id;


Did Query Rewrite Occur?

Execute the query

Use the DBMS_MVIEW.EXPLAIN_REWRITE procedure

Was the query rewritten?

Use the EXPLAIN PLAN statement


Query Rewrite Using Remote Tables in Oracle Database 11g

• Oracle supports query rewrite with MVs that reference tables at a single remote database site.

• The MV should be present at the site where the query is being issued.

• Because any remote table update cannot be propagated to the local site simultaneously, query rewrite will only work in the stale_tolerated mode.

• Whenever a query contains columns that are not found in the MV, a join back is used to rewrite the query.

• If the join back table is not found at the local site, query rewrite will not take place.


Query Rewrite Using Remote Tables: Example

SELECT p.prod_id, t.week_ending_day, s.cust_id, SUM(s.amount_sold) AS sum_amount_soldFROM sales@remotedbl s, products@remotedbl p, times@remotedbl tWHERE s.time_id=t.time_id AND s.prod_id=p.prod_idGROUP BY p.prod_id, t.week_ending_day, s.cust_id;

CREATE MATERIALIZED VIEW sum_sales_prod_week_mvENABLE QUERY REWRITE ASSELECT p.prod_id, t.week_ending_day, s.cust_id, SUM(s.amount_sold) AS sum_amount_soldFROM sales@remotedbl s,products@remotedbl p, times@remotedbl tWHERE s.time_id=t.time_id AND s.prod_id=p.prod_idGROUP BY p.prod_id, t.week_ending_day, s.cust_id;

SELECT prod_id, week_ending_day, cust_id, sum_amount_soldFROM sum_sales_prod_week_mv;


Summary

In this lesson, you should have learned how to use:• The materialized view enhancements:

– List the new and updated MV catalog views– Describe the updated columns in the MV catalog

views– Identify the refresh performance enhancements– Review the benefits of using materialized views

• The query rewrite enhancements:– Use the query rewrite enhancement to support

queries with inline views– Identify the query rewrite enhancement that supports

queries with remote tables

introducing the oracle database 11 g sql and pl/sql new features

Documents