Oracle Developer Syllabus

Module–1: Introduction to SQL

Introduction to Oracle Database

This section introduces the features of Oracle Database 12c and explains the basic design of a database. Learners will categorize the different types of SQL statements and study the dataset used in the course. They will also practice logging on to the database using the SQL Developer environment and learn how to save queries to files and execute them using script files.

Retrieve Data using the SQL SELECT Statement

Students will explore the capabilities of SQL SELECT statements, including retrieving table data with all or specific columns. They will also learn to use arithmetic and concatenation operators, as well as the DESCRIBE command to display the structure of a table.

Learn to Restrict and Sort Data

This part covers the use of the WHERE clause to limit output, along with comparison and logical operators. The rules of precedence for these operators will be explained, as well as the use of string literals in the WHERE clause. Students will also write queries containing the ORDER BY clause to sort data and apply SQL row-limiting clauses such as FETCH FIRST, OFFSET, and WITH TIES.

Usage of Single-Row Functions to Customize Output

Learners will differentiate between single-row and multiple-row functions. They will manipulate strings using character functions in SELECT and WHERE clauses, work with numbers using ROUND, TRUNC, and MOD functions, perform arithmetic with date data, and manipulate dates with DATE functions.

Invoke Conversion Functions and Conditional Expressions

This section explains implicit and explicit data type conversions, along with the use of TO_CHAR, TO_NUMBER, and TO_DATE functions. Students will learn to nest multiple functions, apply NVL, NULLIF, and COALESCE functions, and implement conditional IF-THEN-ELSE logic within SELECT statements.

Aggregate Data Using the Group Functions

Students will use aggregation functions to produce meaningful reports, group data with the GROUP BY clause, and exclude certain groups using the HAVING clause.

Display Data from Multiple Tables Using Joins

In this section, learners will write SELECT statements to access data from multiple tables. They will explore outer joins to view data that does not meet join conditions and use self-joins to join a table with itself.

Use Sub-queries to Solve Queries

This topic introduces sub-queries, including the types of problems they can solve. Students will define sub-queries, study their types, and practice writing both single-row and multiple-row sub-queries.

The SET Operators

Learners will study the use of SET operators, including UNION, UNION ALL, INTERSECT, and MINUS. They will combine results from multiple queries and control the order of rows returned.

Data Manipulation Statements

This section covers all DML statements. Students will insert rows into tables, update data, delete rows, and manage transactions using COMMIT and ROLLBACK. The concept of read consistency will also be explained.

Use of DDL Statements to Create and Manage Tables

Learners will practice creating simple tables, altering and truncating tables, and dropping tables. They will also learn the concept of the recycle bin. Constraints such as primary key, foreign key, not null, unique, and check will be introduced to prevent invalid data entry.

Other Schema Objects

Finally, students will learn to create both simple and complex views, retrieve data from views, create and maintain sequences, manage indexes, and define both private and public synonyms.

Module-2 Oracle Database Program with PL/SQL

Introduction to PL/SQL
PL/SQL is Oracle’s procedural extension to SQL, designed to enhance the capabilities of SQL by adding programming constructs. This section provides an overview of PL/SQL, highlighting its benefits such as improved performance, modularity, and ease of maintenance. You will learn about the different types of PL/SQL blocks and understand how to create a simple anonymous block. Additionally, it covers methods for generating output from a PL/SQL block, which is essential for debugging and displaying results.

Declare PL/SQL Identifiers
In PL/SQL, identifiers are names that represent variables, constants, cursors, or other elements. This section explains the different types of identifiers used in PL/SQL subprograms and the role of the declarative section in defining them. You will learn how to use variables to store data, identify scalar data types, and use the %TYPE attribute to inherit data types from existing columns. The section also introduces bind variables and sequences in PL/SQL expressions for generating unique numbers and enhancing database interactions.

Write Executable Statements
Executable statements form the core of PL/SQL programs. This section teaches the basic syntax guidelines for writing PL/SQL blocks, how to comment the code for better readability, and the deployment of SQL functions within PL/SQL. You will learn how to convert data types, understand nested blocks, identify operators, and use SQL cursor concepts. The section also explains how to use SQL cursor attributes to obtain feedback on DML operations and manage transactions by saving or discarding changes.

Control Structures
Control structures allow conditional and iterative processing in PL/SQL. You will learn to perform conditional processing using IF and CASE statements. Additionally, this section covers different looping constructs such as simple loops, WHILE loops, and FOR loops, along with the use of the CONTINUE statement to control loop execution flow effectively.

Composite Data Types
PL/SQL supports composite data types that store multiple related values. This section focuses on using PL/SQL records to manage structured data. It explains the %ROWTYPE attribute for defining record types based on table structures and shows how to perform insert and update operations using records.

Overview of Collections
Collections in PL/SQL are advanced data structures for handling multiple elements. This section introduces the types of collections: index-by tables (associative arrays), nested tables (clustered tables), and VARRAYs (variable-size arrays), and demonstrates their usage in PL/SQL programs.

Explicit Cursors
Explicit cursors provide more control over query processing. This section explains what explicit cursors are and how to declare, open, fetch data, and close them. You will also learn about the cursor FOR loop, the %NOTFOUND and %ROWCOUNT attributes, and advanced clauses like FOR UPDATE and WHERE CURRENT for managing data modifications effectively.

Exception Handling
Exception handling ensures that errors are properly managed in PL/SQL programs. You will understand the types of exceptions and how to handle them. The section covers trapping predefined Oracle server errors, non-predefined errors, and user-defined exceptions. It also explains propagating exceptions and using the RAISE_APPLICATION_ERROR procedure to communicate custom error messages.

Stored Procedures
Stored procedures are modular subprograms that promote code reuse and maintainability. This section teaches how to create a modularized and layered subprogram design, develop PL/SQL blocks modularly, and understand the execution environment. You will learn the benefits of using subprograms, differences between anonymous blocks and subprograms, creating, calling, and removing stored procedures, implementing procedure parameters and modes, and viewing procedure information.

Triggers
Triggers are special PL/SQL blocks that automatically execute in response to certain events. This section explains triggers, their event types, and body structure. It includes business application scenarios for implementing triggers, creating DML triggers using SQL Developer, understanding firing (timing) events, and differentiating between statement-level and row-level triggers. You will also learn to create INSTEAD OF and DISABLED triggers and manage, test, and remove triggers effectively.

Module-3 Oracle Fusion Middleware 11g: Build Applications with Oracle Forms

Running a Forms Application
This section covers how to run a Forms application effectively. You will learn how to run a form, identify the data elements, and navigate through a Forms application. It explains the different modes of operation, querying data, inserting, updating, and deleting records. The section also includes saving changes and displaying errors to ensure data integrity and smooth application operation.

Working in the Forms Builder Environment
This section introduces the Forms Builder environment, including its key features and components. You will learn to navigate the Forms Builder interface and understand the module object hierarchy. It explains how to customize your Forms Builder session, manage forms executables and module types, define environment variables, and test a form using the Run Form button.

Creating a Basic Form Module
You will learn to create a new form module and build a new data block. The section covers using template forms, saving and compiling a form module, understanding module types and storage formats, deploying a form module, and producing documentation to maintain clarity and organization.

Creating a Master-Detail Form
This section explains how to create data blocks with relationships and run a master-detail form module. You will learn to modify the structure and layout of data blocks to establish hierarchical data relationships between master and detail records.

Working Data Blocks and Frames
This section covers managing object properties and creating visual attributes. You will learn how to control the behavior and appearance of data blocks, manage frame properties, display multiple property palettes, set properties on multiple objects, copy properties, and create control blocks for more advanced form functionality.

Working with Text Items
You will learn to create and modify text items, control their data, and alter navigational behavior. The section also explains enhancing the relationship between text items and the database, adding functionality, and displaying helpful messages to users.

Creating LOVs and Editors
This section covers the creation and use of LOVs (List of Values) and record groups. You will learn to create LOVs manually or using the LOV wizard, set LOV properties, map LOV columns, define editors, set editor properties, and associate editors with text items for improved user input functionality.

Creating Additional Input Items
You will learn to create various input items, including check boxes, list items, and radio groups, to capture different types of user input efficiently.

Creating Noninput Items
This section explains how to create noninput items such as display items, image items, push buttons, calculated items, hierarchical tree items, and bean area items, which enhance the functionality and presentation of forms.

Creating Windows and Content Canvases
You will learn how to display a form module in multiple windows, create new windows, display a form on multiple layouts, and create new content canvases to manage form content efficiently.

Working with Other Canvas Types
This section introduces different canvas types, including stacked canvases, toolbars, and tab canvases. You will learn how to create these canvas types to enhance the layout and navigation of forms.

Producing and Debugging Triggers
You will learn about triggers and how to create them in Forms Builder. The section covers specifying execution hierarchy, using PL/SQL editor features, database trigger editor, variables in triggers, adding functionality with built-in subprograms, and using the Forms debugger to troubleshoot code.

Adding Functionality to Items
This section explains how to code item interaction triggers and interact with noninput items to add functionality to forms.

Displaying Run-Time Messages and Alerts
You will learn to handle built-ins and system messages, control system messages, handle FORM_TRIGGER_FAILURE exceptions, create triggers to intercept system messages, create and control alerts, and manage server errors.

Validating User Input
This section covers the validation process, controlling validation using properties and triggers, performing client-side validation with PJCs (pluggable Java components), tracking validation status, and using built-ins to control when validation occurs.

Writing Flexible Code
You will learn what flexible code is, using system variables for flexible coding, using built-in subprograms, copying and subclassing objects and code, referencing objects by internal ID, and referencing items indirectly for better maintainability and reuse.

Sharing Objects and Code
This section covers the benefits of reusable objects and code, working with property classes, object groups, object libraries, smart classes, reusing PL/SQL, and working with PL/SQL libraries to ensure consistent functionality across forms.

Introducing Multiple Form Applications
You will learn how to handle multiple form applications, start another form module, define multiple form functionality, and share data among modules to build complex, integrated applications.

Module-4 Oracle Reports Developer 11g: Build Reports

Introduction to Oracle Reports Developer
This section introduces Oracle Reports Developer and its role in business intelligence and enterprise reporting. You will learn about Oracle Database 11g, Oracle Developer Suite 11g, Oracle Application Server 11g, OracleAS Reports Services, and the architecture of OracleAS Reports Services for the Web. It provides the foundation to understand how reports are created, managed, and deployed in an enterprise environment.

Designing and Running Reports
This section covers understanding user requirements and designing reports to meet those requirements. You will learn about various report types, including tabular, master-detail, master with two details, and matrix reports. It explains how to retrieve and share data effectively and how to run a report to view the output.

Enhancing Reports Using the Data Model: Queries and Groups
This section focuses on enhancing reports using the data model. You will learn about data model objects, modifying properties of queries, applying changes, and changing the group structure. It explains group hierarchy, ordering data in a group, query modifications, and filtering data in a group to produce organized and meaningful report outputs.

Enhancing Reports Using the Data Model: Creating Columns
You will learn about data model columns, maintaining data source columns, and producing file content output. The section explains creating a column, creating summary columns, displaying subtotals and percentages, and creating formula columns to enrich report data presentation.

Controlling the Paper Layout: Common Properties
This section teaches how to modify paper layout object properties and use common layout properties to control the appearance and formatting of reports.

Coding Reports Triggers
You will learn about sizing objects, using anchors, managing layout object relationships, and handling pagination icons in the paper layout. The section also explains controlling print frequency and using format triggers to customize report behavior.

Controlling the Paper Layout: Specific Properties
This section covers properties of a repeating frame, specifying print direction, controlling the number of records per page, managing spacing between records, and handling minimum widow records. It also explains system variables, valid source columns, and displaying file contents to produce well-formatted reports.

Web Reporting
You will learn about static and dynamic reporting, adding dynamic content, and creating a report block. The section explains how to invoke the report block wizard, examine web source code, use rw:foreach and rw:field tags, and customize report JSPs for web deployment.

Creating and Using Report Parameters
This section covers creating user parameters, referencing parameters in report queries using bind and lexical references, and tips for parameter referencing. You will learn to create a list of values, reference system parameters, build a paper parameter form, and understand types of triggers in reports. The section also explains using report triggers, data model triggers for PL/SQL group filter and parameter validation, layout triggers, format triggers, and event-based reporting.

Extending Functionality Using the SRW Package
This section introduces the SRW package and its contents. You will learn how to output messages, execute nested reports, restrict data, initialize fields, create a table of contents, perform DDL statements, and set format attributes to extend the functionality of reports.