Need the PL/SQL Benefits of PL/SQL Different type of PL/SQL blocks Output Message of PL/SQL Block
Valid and Invalid identifier Use of Variables Data Types %TYPE attributes Bind Variables
Lexical Units in a PL/SQL blocks SQL functions in PL/SQL Nested Blocks Sequences in PL/SQL expressions
PL/SQL executable blocks DML statements in PL/SQL TCL in PL/SQL Use of INTO clause in SQL statement Implicit Vs Explicit Cursors
Types of Control Structures IF Statement CASE Statement Loop statement Conditional Control Structure
User defined PL/SQL records %ROWTYPE attribute INDEX BY Table INDEX TABLE of records Different among records tables, and tables of records
Implicit Vs Explicit Cursor Using explicit Cursors Control explicit Cursors Cursor FOR loops Cursors with Parameters WHERE CURRENT OF Clause
PL/SQL Exceptions Unhandled Exceptions Types of exceptions handlers Exception in Nested Blocks PL/SQL exceptions messages
What is Procedure Syntax and Developing Procedure What are Parameters IN/OUT/IN OUT Procedures Calling PL/SQL Using Host Variables and Using Default Options for Parameter Invoking Procedures and Handled Exceptions
Overview of Stored Functions Syntax and Developing Store Functions Advantages of User-Defined Functions in SQL statements Functions in SQL expressions Locations to call User-defined functions Restrictions on Calling Functions from …
PL/SQL Packages Component of PL/SQL Packages Visibility of Package Components Developing PL/SQL Packages Creating Package Specification and Body Invoking Package Subprograms Creating and Using Bodiless Packages Advantages of Using Packages
Using Oracle Supplied Packages List of some Oracle-supplied Packages DBMS_OUTPUT UTL_FILE,IS_OPEN,HTP,UTL_MAIL, DBMS_SCHEDULER Creating a Job, Creating a Job for a Program with Arguments Creating Job Using Scheduler Setting a Repeat …
Execution Flow of SQL Dynamic SQL Native Dynamic SQL Using the EXECUTE IMMEDIATE Statement Dynamic SQL with a DDL Statement Dynamic SQL with DML Statement Dynamic SQL with Single-Row Query …
Standardizing Constants and Exceptions Local Subprograms Definer’s Rights Vs Invoker Rights Autonomous Transactions Returning Clause Bulk Binding Bulk Collect FORALL, BULK COLLECT INTO with RETURNNG Clause Using NOCOPY Hint PARALLEL_ENABLE …
Understanding Dependencies Local Dependencies Displaying Direct Dependencies by Using USER_DEOENDENCIES Understating Remote Dependencies
What is LOB Contrasting LONG and LOB Data Types What is BFILES Securing BFILES Managing BFILES Preparing to use BFILES Using DBMS_LOB Migrating from LONG to LOB DBMS_LOB DBMS_LOB.READ, and …
Types of Triggers Guidelines for Designing Triggers Creating DML Triggers Types of DML Triggers Trigger Timing Trigger Firing Sequences Trigger Event Types and Body Creating a DML Statement Trigger Using …
Creating Database Trigger Creating Trigger on DDL statement Creating Triggers on System Events LOGONG and LOGOFF Trigger CALL statement Reading Data from a Mutating Table Benefits of Database Triggers
Native and Interpreted Compiler Features and Benefits of Native Compiler Parameters Influencing Compilation Switching Between Native and Interpreted Compilation Using Native Compilation DBMS_WARNING Package
Basic SELECT statement Arithmetic expressions and NULL values Column alias Concatenation operator Literal character string Alternative quote operator DISTINCT keyword DESCRIBE command
Single-row SQL Functions Character Functions Number Functions Working with dates Date Functions
Implicit and explicit data type conversion TO_CHAR, TO_DATE, TO_NUMBER functions Nested Functions General Functions CASE and DECODE Conditional expressions
Group Functions MIN MAX, COUNT, AVG,DISTINCT within group functions NULL values in a group functions
Types of JOINS Natural join Self-Join Non-equijoin Outer join Cartesian Products
Single row subquery Group Function in subquery HAVING clause with subquery Multiple-row subquery NULL values in Subquery
INSERT, UPDATE, DELETE TRUNCATE and COMMIT ROLLBACK and SAVEPOINT Read consistency FOR UPDATE clause
Naming rules CREATE Table Data Types Constraint NOT NULL PRIMARY KEY FOREIGN KEY UNIQUE,ALTER Table Create table using a subquery
Managing constraints Creating Index Performing flashback operations Creating and using external tables
Table information Column information Constraint information View information Sequence Information Synonym information Comment to a table
Unconditional INSERT Pivoting INSERT INSERT ALL Merging rows Tracking the chnages
INTERVAL data types EXTRACT TZ_OFFSET FROM_TZ TO_TIMESTAMP TO_YMINTERVAL TO_DSINTERVAL
Using scalar subquery correlated subqueries EXISTS and NOT EXISTS WITH clause
REGEXP_LIKE REGEXP_REPLACE REGEXP_INSTR REGEXP_SUBSTR Accessing subexpressions REGEXP_COUNT
