Oracle® Database Notes for Professionals book

Description

What people are saying about Notes for Professionals books Hey Bro! I have been dabbing in Oracle for like a year and a half. This is really helpful! :) Thanks , it's very useful Thanks. Great job! Really nice and condensed collection, thanks to all co-authors Super useful for reference, many thanks for whoever did this. Instead of browsing, clicking, digging infinitely, now I have ONE in one place. Whoah, this is awesome! Thanks!!!!!! The Oracle® Database Notes for Professionals book is compiled from Stack Overflow Documentation, the content is written by the beautiful people at Stack Overflow. Text content is released under Creative Commons BY-SA. See credits at the end of this book whom contributed to the various chapters. Images may be copyright of their respective owners unless otherwise specified Book created for educational purposes and is not affiliated with Oracle® Database group(s), company(s) nor Stack Overflow. All trademarks belong to their respective company owners 118 pages, published on May 2018 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