Articles & Tutorial for Oracle PL/SQL Developer certification
All articles for Oracle PL/SQL Developer certification
Tips and How Tos for Oracle PL/SQL Developer certification
Developing an Oracle Application.
When writing a program to interact with the Oracle database, an application developer has the following choices:
Client/Server Model:
In a traditional client/server program, the code of an application runs on a computer other than the Oracle database server. Database calls are...
continue reading "Developing an Oracle Application." »
How does Oracle server manage dependencies among objects?
Oracle server automatically records dependencies among objects. To manage dependencies, all schema objects have a status (VALID or INVALID) that is recorded in the data dictionary. The status of a schema object can be viewed by querying the USER_OBJECTS data dictionary view. If an object has a...
continue reading "How does Oracle server manage dependencies among objects?" »
How is a new job submitted to the job queue?
The DBMS_JOB.SUBMIT procedure (the SUBMIT procedure is provided with the DBMS_JOB Oracle supplied package) is used to submit a new job to be executed to the job queue. The DBMS_JOB.SUBMIT procedure accepts five parameters and returns the number of a submitted job through the JOB parameter. The...
continue reading "How is a new job submitted to the job queue?" »
How is a SQL statement executed?
All SQL statements have to go through the following stages:
Parse: Every SQL statement must be parsed. Parsing the statement includes checking the statement's syntax and validating the statement, ensuring that all references to objects are correct, and ensuring that relevant privileges to...
continue reading "How is a SQL statement executed?" »
How is a trigger compiled?
A trigger is similar to a PL/SQL anonymous block, but with the additional :NEW and :OLD capabilities. However, their compilation is different. A PL/SQL anonymous block is compiled each time it is loaded into the memory. The compilation involves the following three stages:
Syntax checking:...
continue reading "How is a trigger compiled?" »
How is top-n analysis performed?
Top-n analysis is performed using a top-n analysis query. Following is the structure of a top-n analysis query:
SELECT [column_list], ROWNUM
FROM (SELECT [column_list] FROM table
ORDER BY top-n_column)
WHERE ROWNUM <= n;
A top-n analysis query comprises the following elements:
A...
continue reading "How is top-n analysis performed?" »
How many types of indexes can be created?
Two types of indexes can be created. They are as follows:
Unique index: Oracle automatically creates this index when a column, in a table, is defined as a PRIMARY KEY or UNIQUE constraint.
Non-unique index: This index can be created by the user.
continue reading "How many types of indexes can be created?" »
How to minimize dependency failures occurring during the recompilation of a dependent subprogram?
During the recompilation of a dependent subprogram (procedure or function), the dependency failures can be minimized by following the below-mentioned guidelines:
Declare records by using the %ROWTYPE attribute.
Declare variables with the %TYPE attribute.
Perform queries with the SELECT...
continue reading "How to minimize dependency failures occurring during the recompilation of a dependent subprogram?" »
OCP 1z0-047 Short Notes: Exam passing tips
Retrieving data using the SQL SELECT statement and using single row functions to customize the output /*...*/ and -- are used to put comments in an Oracle program.
The CREATE TABLE does not end immediately with a semicolon, and a column name cannot start with a numeric value. However, a...
continue reading "OCP 1z0-047 Short Notes: Exam passing tips" »
ORACLE CONSTRAINTS
Constraints are rules for a database that limit the acceptable data values for a table. They are the optional schema objects that depend on a table. The existence of a table without any constraint is possible, but the existence of a constraint without any table is not possible. Constraints...
continue reading "ORACLE CONSTRAINTS" »
