Introduction

1. Introduction

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 used in this course • Review the SQL Developer environment that you can optionally use in this course

3. Course Objectives • After completing this course, you should be able to do the following: • Describe the data type enhancements • Use the large object (LOB) enhancements • Use the Nested Table and Varray enhancements • Identify SQL performance, data warehousing, and general enhancements • Use regular expressions • Identify the new PL/SQL enhancements • Explain the SQL MODEL Clause

4. Course Agenda • Day 1: • Introduction • Using Oracle Database 10g Data Types • Using Large Object (LOB) Enhancements • Using Nested Table and VARRAY Enhancements • Handling SQL Performance and Using General SQL Enhancements

5. Course Agenda • Day 2: • Using Regular Expressions in SQL and PL/SQL • Using the New PL/SQL Compiler • Programming with PL/SQL Enhancements • Using the SQL Model Clause • Using SQL Enhancements for Data Warehousing

6. Lesson Agenda • Appendices and tables used in this course • Overview of SQL*Plus • Overview of Oracle SQL Developer • Oracle 10g documentation and additional resources

7. Appendices Used in this Course • Appendix A: Practices • Appendix B: Practice Solutions • Appendix C: Table Descriptions • Appendix D: Using Oracle SQL Developer • Appendix E: Using SQL*Plus and New SQL*Plus Enhancements • Appendix F: Using Oracle JDeveloper • Appendix G: Working with Collections

8. Tables Used in This Course • The sample schemas that are used in this course are: • Order Entry (OE) schema • Human Resources (HR) schema • Sales History(SH) schema • Print Media(PM) schema

9. The Order Entry(OE) Schema CUSTOMERS customer_id cust_first_name cust_ last_name cust_ address_typ phone_numbersnls_language nls_territory credit_limitcust_ email account_mgr_id date_of_birth marital_status gender Income_level ORDERS order_id order_date order_mode customer_id order_status order_total sales_rep_id promotion_id ORDER_ITEM order_id line_item_id product_id unit_price quantity street_address postal_code city state_province country_id PRODUCT_INFORMATIONproduct_id product_name product_description category_id weight_class warranty_period supplier_id product_status list_price min_price catalog_url PRODUCT_DESCRIPTION product_id language_id product_name product_description WAREHOUSES warehouse_id warehouse_name location_id INVENTORIES product_id warehouse_id quantity_on_hand

10. The Human Resources(HR) Schema DEPARTMENTS department_id department_name manager_id location_id LOCATIONS location_id street_address postal_code city state_province country_id JOB_HISTORYemployee_id start_date end_date job_id department_id EMPLOYEES employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id COUNTRIES country_id country_name region_id JOBSjob_id job_title min_salary max_salary REGIONS region_id region_name

11. The Sales History(SH) Schema TIMES time_id day_name day_number_in_week day_number_in_month calendar_week_number fiscal_week_number week_ending_day week_ending_day_id calendar_month_number fiscal_month_number calendar_month_desc calendar_month_id fiscal_month_id days_in_cal_month days_in_fis_month end_of_cal_ month end_of_fis_month calendar _month _name fiscal _month _name calendar _quarter _desc calendar_quarter_id fiscal _quarter _desc fiscal _quarter _id days_in_cal_quarter days_in_fis_quarter end_of_cal_quarter end_of_fis_quarter calendar_quarter_number fiscal_quarter_number calendar_year calendar_year_id fiscal_year fiscal_year_id days_in_cal_year days_in_fis_year end_of_cal_year end_of_fis_year PROMOTIONS promo_id promo_name promo_subcategory promo_subcategory_id promo_category promo_category_id promo_cost promo_begin_date promo_end_date promo_total promo_total_id SALES prod_id cust_id time_id channel_id promo_id quantity_sold amount_sold COSTS prod_id time_id promo_id channel_id unit_cost unit_price CHANNELS channel_id channel_desc channel_class channel_class_id channel_total channel_total_id PRODUCTS

12. The Sales History(SH) Schema COSTS SALES SALES CUSTOMERS cust_id cust_first_name cust_last_name cust_gender cust_year_of_birth cust_marital_status cust_street_address cust_postal_code cust_city cust_city_id cust_state_province cust_state_province_id country_id cust_main_phone_number cust_income_level cust_credit_limit cust_email cust_total cust_total_id cust_src_id cust_eff_from cust_eff_to cust_valid PRODUCTS prod_id prod_name prod_desc prod_subcategory prod_subcategory_id prod_subcategory_desc prod_category prod_category_id prod_category_desc prod_weight_class prod_unit_of_measure prod_pack_size supplier_id prod_status prod_list_price prod_min_price prod_total prod_total_id prod_src_id prod_eff_from prod_eff_to prod_valid COUNTRIEScountry_id country_iso_code country_name country_subregion country_subregion_id country_region country_region_id country_total country_total_id Country_name_hist

13. The Print Media (PM) Schema PRODUCT_INFORMATIONproduct_id product_name product_description category_id weight_class warranty_period supplier_id product_status list_price min_price catalog_url OE Schema ONLINE_MEDIA product_id product_photo product_photo_signature product_thumbnail product_video product_audio product_text product_testimonials PRINT_MEDIA product_id first_name ad_id ad_composite ad_sourcetext ad_finaltext ad_fltexttn ad_textdocs_ntab ad_photo ad_graphic ad_header press_release ADHEADER_TYP header_name creation_date header_text logo TEXTDOC_TYP document_typ formatted_doc

14. Lesson Agenda • Appendices and tables used in this course • Overview of SQL*Plus • Overview of Oracle SQL Developer • Oracle 10g documentation and additional resources

15. 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

16. Logging In to SQL*Plus 1 sqlplus [username[/password[@database]]] 2

17. Displaying Table Structure DESCRIBE departments Name Null? Type ----------------------- -------- ------------ DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)

18. Executing SQL from SQL*Plus SELECT last_name, manager_id, department_id FROM employees LAST_NAME MANAGER_ID DEPARTMENT_ID ------------------------- ---------- ------------- OConnell 124 50 Grant 124 50 Whalen 101 10 Hartstein 100 20 Fay 201 20 Mavris 101 40 Baer 101 70 Higgins 101 110 Gietz 205 110 King 90 ... 107 rows selected.

19. SQL*Plus File Commands • SAVE filename • GET filename • START filename • @filename • EDIT filename • SPOOL filename • EXIT

20. Lesson Agenda • Appendices and tables used in this course • Overview of SQL*Plus • Overview of Oracle SQL Developer • Oracle 10g documentation and additional resources

21. 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

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

23. Menus for SQL Developer 4 6 2 5 1 3

24. 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 • For multiple schemas • SQL Developer automatically imports 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.

25. Creating a Database Connection

26. Browsing Database Objects • Use the Database Navigator to: • Browse through many objects in a database schema • Review the definitions of objects at a glance

27. 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.

28. Using SQL Worksheet 2 4 6 8 1 5 7 3

29. Executing SQL Statements • Use the Enter SQL Statement box to enter single or multiple SQL statements.

30. Lesson Agenda • Appendices and tables used in this course • Overview of SQL*Plus • Overview of Oracle SQL Developer • Oracle 10g documentation and additional resources

31. Oracle 10g SQL and PL/SQL Documentation • Navigate to http://www.oracle.com/pls/db102/homepage > Click the Books tab: • Oracle Database New Features Guide 10g Release 2 (10.2) • Oracle Database Application Developer's Guide – Fundamentals 10g Release 2 (10.2) • Oracle Database Globalization Support Guide 10g Release 2 (10.2) • Oracle Database PL/SQL User's Guide and Reference 10g Release 2 (10.2)

32. Oracle 10g SQL and PL/SQL Documentation • Oracle Database Reference 10g Release 2 (10.2) • Oracle Database SQL Reference 10g Release 2 (10.2) • Oracle Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) • Oracle Database Application Developer's Guide - Large Objects 10g Release 2 (10.2)

33. Additional Resources • For additional information about the new Oracle 10g SQL and PL/SQL new features, refer to the following: • Oracle Database 10g: New Features eStudies • Oracle by Example series (OBE): Oracle Database 10g • http://otn.oracle.com/obe/obe10gdb/index.html • What’s New in PL/SQL in Oracle Database 10g on the Oracle Technology Network (OTN): • http://www.oracle.com/technology/tech/pl_sql/

34. 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 used in this course • Review the SQL Developer environment that you can optionally use in this course

35. Practice 1: Overview • This practice covers the following topics: • Logging on to SQL*Plus and describing the structure of a table, and displaying the table’s data • Starting SQL Developer and creating a new database connection, describing the structure of a table, and displaying the table’s data • Using the SQL Worksheet to execute a SQL statement • Accessing the Oracle Database 10g Release 2 online documentation

36. Using Oracle Database 10g Data Types

37. Objectives • After completing this lesson, you should be able to: • Use the new native floating-point data types • BINARY_FLOAT • BINARY_DOUBLE • Supporting functions • Exceptions • Use NCHAR literals in the data manipulation language (DML) • Convert LONG and LONG RAW columns to LOBs

38. Lesson Agenda • Using the new native floating-point data types • Supporting NCHAR string literals • Changing LONG and LONG RAW columns to LOBs

39. The New BINARY_FLOAT and BINARY_DOUBLENumeric Data Types • Store floating-point data in IEEE 754 format • Benefits: • Are part of numerous other standards and systems such as Java, XML, and so on • Are more efficient than the NUMBER type • Have the arithmetic operations implemented in hardware on most platforms • May use less space in memory or disk • Provide seamless support in SQL and PL/SQL

40. … Sign Exponent Significand Floating-Point Formats • Three components: Signed bit, exponent, significand • Binary Float (32 total bits): • 1 sign • 8 exponent • 23 signficand • Binary Double (64 total bits): • 1 sign • 11 exponent • 23 signficand

41. Numeric Ranges

42. Using BINARY_FLOAT and BINARY_DOUBLEData Types • Not a replacement for NUMBER • Values beyond Oracle NUMBER • Closed arithmetic operations • Transparent rounding

43. Floating-Point Special Values • Numeric literals use suffix f or d: • Constant literals: • BINARY_FLOAT_NAN • BINARY_DOUBLE_NAN • BINARY_FLOAT_INFINITY • BINARY_DOUBLE_INFINITY 103948f 909744d 3.0f 7.9d 4.67543f Not A Number Infinity

44. Using Comparison Operations on Binary Floats • Rules: • NAN is ordered as follows: • All non-NAN < NAN • Any NAN = any other NAN • Infinity can be compared. • -INF is the smallest value and +INF is the largest value. NAN = NAN returns TRUE NAN = 3.0f returns FALSE NAN != NAN returns FALSE NAN != 3.0f returns TRUE NAN > 3.0f returns TRUE –INF < every finite number < +INF INF < 3.0f and +INF = +INF

45. SQL Example CREATE TABLE t (f BINARY_FLOAT, d BINARY_DOUBLE); Table created. INSERT INTO t VALUES (2.0f, 2.0d); 1 row created. INSERT INTO t VALUES (1.5f/0.5f, 1.5d/0.5d); 1 row created. INSERT INTO t VALUES (BINARY_FLOAT_INFINITY, BINARY_DOUBLE_INFINITY); 1 row created. SELECT * FROM t; F D ---------- ---------- 2.0E+000 2.0E+000 3.0E+000 3.0E+000 Inf Inf

46. PL/SQL Example DECLARE bf_var BINARY_FLOAT := 2.0f; bd_var BINARY_DOUBLE := 2.0d; BEGIN bf_var := 1.5f/0.5f; bd_var := 1.5d/0.5d; dbms_output.put_line('Binary float value is: ' || bf_var); dbms_output.put_line('Binary double value is: ' || bd_var); END; Binary float value is: 3.0E+000 Binary double value is: 3.0E+000 PL/SQL procedure successfully completed.

47. Using the Floating-Point Literals INSERT INTO t VALUES (BINARY_FLOAT_NAN, BINARY_DOUBLE_NAN); 1 row created. SELECT * FROM t; F D ---------- ---------- ... Nan Nan 1 DECLARE bf_var BINARY_FLOAT := BINARY_FLOAT_INFINITY; bd_var BINARY_DOUBLE := BINARY_DOUBLE_NAN; BEGIN dbms_output.put_line('Binary float value is: ' || bf_var); dbms_output.put_line('Binary double value is: ' || bd_var); END; 2

48. Floating-Point Exceptions

49. Floating-Point Exceptions: Example SELECT f/0, d/0 FROM t; F/0 D/0 ---------- ---------- Inf Inf Inf Inf Inf Inf Nan Nan 1 DECLARE bf_var BINARY_FLOAT := 2.0f; BEGIN bf_var := 1.5f/0; dbms_output.put_line('Binary float value is: ' || bf_var); END; 2

50. Using the Floating-Point Functions