SQL Extensions  «Prev  Next»

Lesson 1

Oracle SQL Extensions

Welcome to the first course in the Introduction to Oracle Certification Series, Extensions for SQL. As the name implies, this series focuses on preparing you for certification in Oracle.

What is this Series about?

The information covered in this series helps you get started on the Oracle Certified Database Administrator and/or Certified Application Developer tracks. Another important goal of this series is to enable you
  1. to create advanced SQL queries,
  2. write intermediate PL/SQL blocks, and
  3. familiarize you with Oracle's unique SQL extensions.

Oracle SQL Extensions are a set of features that go beyond the ANSI/ISO Standard SQL specification. These extensions provide additional functionality and performance optimizations that are specific to the Oracle Database. Oracle SQL Extensions can be divided into two broad categories:
  • Data type extensions: Oracle supports a number of data types that are not defined in the Standard SQL specification, such as the INTERVAL, ROWID, and XMLType data types.
  • Feature extensions: Oracle supports a number of features that are not defined in the Standard SQL specification, such as MERGE statements, regular expression matching, and advanced windowing functions.
Here are some specific examples of Oracle SQL Extensions:
  1. Data type extensions:
    • INTERVAL data type: This data type is used to store and manipulate time intervals.
    • ROWID data type: This data type is used to store the unique identifier of a row in a table.
    • XMLType data type: This data type is used to store and manipulate XML data.
  2. Feature extensions:
    • MERGE statement: This statement is used to update or insert data into a table in a single operation.
    • Regular expression matching: Oracle supports a number of regular expression functions that can be used to search and manipulate text data.
    • Advanced windowing functions: Oracle supports a number of advanced windowing functions that can be used to perform complex aggregation and analysis on data.


Oracle SQL Extensions can provide a number of benefits, including:

  • Increased functionality: Oracle SQL Extensions provide additional functionality that is not available in Standard SQL. This can make it possible to write more efficient and concise code.
  • Improved performance: Oracle SQL Extensions can be used to optimize the performance of queries and other database operations.
  • Portability: Oracle SQL Extensions are supported by other Oracle products, such as Oracle Real Application Clusters (RAC) and Oracle Exadata.

However, it is important to note that Oracle SQL Extensions are not portable to other database vendors. Therefore, if you are developing applications that need to be portable to other database platforms, you should avoid using Oracle SQL Extensions. To identify the use of Oracle extensions to Entry SQL-92 in your embedded SQL programs, you can use Oracle's FIPS Flagger. The FIPS Flagger is part of the Oracle precompilers and the SQL*Module compiler. The FIPS Flagger can also be enabled in SQL*Plus by using ALTER SESSION SET FLAGGER = ENTRY .

Course goals

This course covers extensions to SQL. This means that the course is especially designed to show you how to use Oracle's own SQL extensions within queries and other SQL commands. By the end of this course, you will know how to:
  1. Use advanced SQL query structures such as correlated sub-queries
  2. Use Oracle's extensions to SQL
  3. Write SQL to create Oracle tables, views, and indexes and the parameters used to create those structures in Oracle
  4. Use SQL to modify or remove existing table structures and constraints
  5. Write SQL to add, change, or delete rows in tables
  6. Use SQL*Plus (Oracle's SQL programming environment) to create SQL command files, including commands to set titles, page breaks, and customized column headings for your queries

The series

In this course, you will learn and practice SQL skills with many kinds of applets, including Tooltips, SlideShow, and simulations. Extensions for SQL is the first of three courses in the Introduction to Oracle Certification Series. In the next lesson, you will learn about prerequisites to this course.

SEMrush Software