Oracle® Database Notes for Professionals book

Content list
About
Chapter 1: Getting started with Oracle Database
Section 1.1: Hello World
Section 1.2: SQL Query
Section 1.3: Hello world! from table
Section 1.4: Hello World from PL/SQL
Chapter 2: Getting started with PL/SQL
Section 2.1: Hello World
Section 2.2: Definition of PL/SQL
Section 2.3: Dierence between %TYPE and %ROWTYPE
Section 2.4: Create or replace a view
Section 2.5: Create a table
Section 2.6: About PL/SQL
Chapter 3: Anonymous PL/SQL Block
Section 3.1: An example of an anonymous block
Chapter 4: PL/SQL procedure
Section 4.1: Syntax
Section 4.2: Hello World
Section 4.3: In/Out Parameters
Chapter 5: Data Dictionary
Section 5.1: Describes all objects in the database
Section 5.2: To see all the data dictionary views to which you have access
Section 5.3: Text source of the stored objects
Section 5.4: Get list of all tables in Oracle
Section 5.5: Privilege information
Section 5.6: Oracle version
Chapter 6: Dates
Section 6.1: Date Arithmetic - Dierence between Dates in Days, Hours, Minutes and/or Seconds
Section 6.2: Setting the Default Date Format Model
Section 6.3: Date Arithmetic - Dierence between Dates in Months or Years
Section 6.4: Extract the Year, Month, Day, Hour, Minute or Second Components of a Date
Section 6.5: Generating Dates with No Time Component
Section 6.6: Generating Dates with a Time Component
Section 6.7: The Format of a Date
Section 6.8: Converting Dates to a String
Section 6.9: Changing How SQL/Plus or SQL Developer Display Dates
Section 6.10: Time Zones and Daylight Savings Time
Section 6.11: Leap Seconds
Section 6.12: Getting the Day of the Week
Chapter 7: Working with Dates
Section 7.1: Date Arithmetic
Section 7.2: Add_months function
Chapter 8: DUAL table
Section 8.1: The following example returns the current operating system date and time
Section 8.2: The following example generates numbers between start_value and end_value
Chapter 9: JOINS
Section 9.1: CROSS JOIN
Section 9.2: LEFT OUTER JOIN
Section 9.3: RIGHT OUTER JOIN
Section 9.4: FULL OUTER JOIN
Section 9.5: ANTIJOIN
Section 9.6: INNER JOIN
Section 9.7: JOIN
Section 9.8: SEMIJOIN
Section 9.9: NATURAL JOIN
Chapter 10: Handling NULL values
Section 10.1: Operations containing NULL are NULL, except concatenation
Section 10.2: NVL2 to get a dierent result if a value is null or not
Section 10.3: COALESCE to return the first non-NULL value
Section 10.4: Columns of any data type can contain NULLs
Section 10.5: Empty strings are NULL
Section 10.6: NVL to replace null value
Chapter 11: String Manipulation
Section 11.1: INITCAP
Section 11.2: Regular expression
Section 11.3: SUBSTR
Section 11.4: Concatenation: Operator || or concat() function
Section 11.5: UPPER
Section 11.6: LOWER
Section 11.7: LTRIM / RTRIM
Chapter 12: IF-THEN-ELSE Statement
Section 12.1: IF-THEN
Section 12.2: IF-THEN-ELSE
Section 12.3: IF-THEN-ELSIF-ELSE
Chapter 13: Limiting the rows returned by a query (Pagination)
Section 13.1: Get first N rows with row limiting clause
Section 13.2: Get row N through M from many rows (before Oracle 12c)
Section 13.3: Get N numbers of Records from table
Section 13.4: Skipping some rows then taking some
Section 13.5: Skipping some rows from result
Section 13.6: Pagination in SQL
Chapter 14: Recursive Sub-Query Factoring using the WITH Clause (A.K.A. Common Table Expressions)
Section 14.1: Splitting a Delimited String
Section 14.2: A Simple Integer Generator
Chapter 15: Dierent ways to update records
Section 15.1: Update using Merge
Section 15.2: Update Syntax with example
Section 15.3: Update Using Inline View
Section 15.4: Merge with sample data
Chapter 16: Update with Joins
Section 16.1: Examples: what works and what doesn't
Chapter 17: Functions
Section 17.1: Calling Functions
Chapter 18: Statistical functions
Section 18.1: Calculating the median of a set of values
Chapter 19: Window Functions
Section 19.1: Ratio_To_Report
Chapter 20: Creating a Context
Section 20.1: Create a Context
Chapter 21: Splitting Delimited Strings
Section 21.1: Splitting Strings using a Hierarchical Query
Section 21.2: Splitting Strings using a PL/SQL Function
Section 21.3: Splitting Strings using a Recursive Sub-query Factoring Clause
Section 21.4: Splitting Strings using a Correlated Table Expression
Section 21.5: Splitting Strings using CROSS APPLY (Oracle 12c)
Section 21.6: Splitting Strings using XMLTable and FLWOR expressions
Section 21.7: Splitting Delimited Strings using XMLTable
Chapter 22: Collections and Records
Section 22.1: Use a collection as a return type for a split function
Chapter 23: Object Types
Section 23.1: Accessing stored objects
Section 23.2: BASE_TYPE
Section 23.3: MID_TYPE
Section 23.4: LEAF_TYPE
Chapter 24: Loop
Section 24.1: Simple Loop
Section 24.2: WHILE Loop
Section 24.3: FOR Loop
Chapter 25: Cursors
Section 25.1: Parameterized "FOR loop" Cursor
Section 25.2: Implicit "FOR loop" cursor
Section 25.3: Handling a CURSOR
Section 25.4: Working with SYS_REFCURSOR
Chapter 26: Sequences
Section 26.1: Creating a Sequence: Example
Chapter 27: Indexes
Section 27.1: b-tree index
Section 27.2: Bitmap Index
Section 27.3: Function Based Index
Chapter 28: Hints
Section 28.1: USE_NL
Section 28.2: APPEND HINT
Section 28.3: Parallel Hint
Section 28.4: USE_HASH
Section 28.5: FULL
Section 28.6: Result Cache
Chapter 29: Packages
Section 29.1: Define a Package header and body with a function
Section 29.2: Overloading
Section 29.3: Package Usage
Chapter 30: Exception Handling
Section 30.1: Syntax
Section 30.2: User defined exceptions
Section 30.3: Internally defined exceptions
Section 30.4: Predefined exceptions
Section 30.5: Define custom exception, raise it and see where it comes from
Section 30.6: Handling connexion error exceptions
Section 30.7: Exception handling
Chapter 31: Error logging
Section 31.1: Error logging when writing to database
Chapter 32: Database Links
Section 32.1: Creating a database link
Section 32.2: Create Database Link
Chapter 33: Table partitioning
Section 33.1: Select existing partitions
Section 33.2: Drop partition
Section 33.3: Select data from a partition
Section 33.4: Split Partition
Section 33.5: Merge Partitions
Section 33.6: Exchange a partition
Section 33.7: Hash partitioning
Section 33.8: Range partitioning
Section 33.9: List partitioning
Section 33.10: Truncate a partition
Section 33.11: Rename a partition
Section 33.12: Move partition to dierent tablespace
Section 33.13: Add new partition
Chapter 34: Oracle Advanced Queuing (AQ)
Section 34.1: Simple Producer/Consumer
Chapter 35: constraints
Section 35.1: Update foreign keys with new value in Oracle
Section 35.2: Disable all related foreign keys in oracle
Chapter 36: Autonomous Transactions
Section 36.1: Using autonomous transaction for logging errors
Chapter 37: Oracle MAF
Section 37.1: To get value from Binding
Section 37.2: To set value to binding
Section 37.3: To invoke a method from binding
Section 37.4: To call a javaScript function
Chapter 38: level query
Section 38.1: Generate N Number of records
Section 38.2: Few usages of Level Query
Chapter 39: Hierarchical Retrieval With Oracle Database 12C
Section 39.1: Using the CONNECT BY Caluse
Section 39.2: Specifying the Direction of the Query From the Top Down
Chapter 40: Data Pump
Section 40.1: Monitor Datapump jobs
Section 40.2: Step 3/6 : Create directory
Section 40.3: Step 7 : Export Commands
Section 40.4: Step 9 : Import Commands
Section 40.5: Datapump steps
Section 40.6: Copy tables between dierent schemas and tablespaces
Chapter 41: Bulk collect
Section 41.1: Bulk data Processing
Chapter 42: Real Application Security
Section 42.1: Application
Chapter 43: Assignments model and language
Section 43.1: Assignments model in PL/SQL
Chapter 44: Triggers
Section 44.1: Before INSERT or UPDATE trigger
Chapter 45: Dynamic SQL
Section 45.1: Select value with dynamic SQL
Section 45.2: Insert values in dynamic SQL
Section 45.3: Update values in dynamic SQL
Section 45.4: Execute DDL statement
Section 45.5: Execute anonymous block
Credits
You may also like