LiveZilla Live Help
India : +91 996 395 7366
USA : +1 214 347 4655

ORACLE 11g

ORACLE 11g Online Training

SQL & PL SQL 

  • The Oracle database environment
  • Reviewing basic architecture concepts, Oracle 10g Architecture,Main features of 9i and 10g

Basic SELECT Statement

  • Writing the statement in sqlplus, Running the SELECT statement

Ordering the output

  • Single Column, Descending order, Multiple column sort

Conditional retrieval of data

  • Working with complex conditions, AND OR NOT ,,,, LIKE BETWEEN

Pseudo columns and functions

  • Pseudo Columns, Rownum, Sysdate, User & UID, the Dual Table

Working with character functions

  • UPPER, LOWER, INITCAP, RPAD(), TRIM(), SUBSTR(), INSTR(), TRANSLATE(), REPLACE(), GREATEST(), LEAST(),

Working with date functions

  • TO_CHAR(), TO_DATE(), MONTHS_BETWEEN(), ADD_MONTHS(), LAST_DAY(), NEXT_DAY()

Using non-character function

  • ROUND(), TRUNC(), SIGN(), Working with multiple tables,
  • Different type of Joins, Writing Outer Joins

Using the SET operators

  • Union, Intersect, Minus

Aggregating data using group functions

  • GROUP BY, HAVING

Creating Subqueries

  • Single Row subqueries, Multiple row Subqueries

Enhancing groups function

  • ROLLUP, CUBE
  • Transaction Control Language
  • Rollback, Commit, Savepoint
  • Processing hierarchies

Creating the Tree structure, LEVEL, CONNECT BY

  • Data Definition Language

Data Manipulation Language

  • INSERT, UPDATE, DELETE

Data Definition Language

  • CREATE, TRUNCATE, ALTER, DROP, RENAME, DESCRIBE

Using Declarative Constraints

  • Not Null Constraint, Check Constraint, Unique Constraint, Primary Key Constraint, References Constraint, On Delete Cascade, On Delete Set Null

Other Database Objects

  • Views, Sequences, Synonyms, Indexes
  • USER_TABLES, USER_TAB_COLUMNS, USER_OBJECTS

Database Security

  • Object Privileges, Granting access to objects
  • PL -SQL

Blocks

  • Structure, Writing Anoyomous Blocks

Variables

  • Oracle Datatypes, TYPE and ROWTYPE declarations, Value assignments

Control structures

  • IF ELSE ENDIF statement, IF ELSIF ELSE ENDIF statement, LOOP END , LOOP statement, WHILE condition, FOR condition

Cursors

  • Implicit and Explicit Cursors, Cursor Manipulation statements, OPEN FETCH CLOSE EXIT WHEN

Error Handling

  • Predefined Exceptions, Non-Predefined Excecptions, User Defined Exceptions

Procedures

  • Creating and Calling Procedures

Functions

  • Creating and Calling Functions

Packages

  • Package Header, Package Body

Design Tips and Techniques

  • Format of standard packages in an application, Spec and Body, NOCOPY hint ————- thoery

Triggers

  • Database triggers, CALLing procedures from triggers

Dynamic SQL

  • EXECUTE IMMEDIATE, DBMS_SQL package

Oracle Supplied packages

  • DBMS_OUTPUT, UTL_FILE

Collection datatypes

  • Associative Arrays, Nested tables, VARRAYs