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