Oracle Mock Test, Oracle Online Test Series 5, Oracle Online Exam

The user-defined exceptions must be declared and must be raised explicitly by RAISE statements. Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package. You declare an exception by introducing its name, followed by the keyword EXCEPTION. You cannot declare an exception twice in the same block. You can, however, declare the same exception in two different blocks.
Exceptions declared in a block are considered local to that block and global to all its sub-blocks. Because a block can reference only local or global exceptions, enclosing blocks cannot reference exceptions declared in a sub-block.

The size of the SGA is not affected by the stored procedure. A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance’s SGA is shared among the users. Consequently, the SGA is sometimes called the shared global area.

A Package procedure is built in PL/SQL procedure. There are two types of Package Procedure:
1. Restricted package procedure: Restricted package procedure affects the basic functions of SQL * Forms. It cannot use in all triggers except key triggers.

2. Unrestricted package procedure: Unrestricted package procedure does not interfere with the basic functions of SQL * Forms. It can be used in any triggers.

Following are the list of restricted and unrestricted procedure:
a. Call – unrestricted
b. User Exit – Unrestricted
c. Call_query – Unrestricted
d. Break – Unrestricted

e. Up – Restricted
f. Execute Query – Restricted
g. Message – Restricted
h. Exit_form – Restricted
i. Post – Restricted

An integrity constraint is automatically checked while data is inserted into or updated in a table while a stored procedure has to be specifically invoked.

SQL*FORMS is a 4GL tool for developing and executing Oracle based interactive applications. SQL*Forms does not work with database tables directly it works with copies of them that are kept in a work space. This arrangement protects you from making mistakes; you can make a change to the workspace and then discard it. If you discard the changes, the table itself will not be affected.

An exception is a PL/SQL error that is raised during program execution, either implicitly by TimesTen or explicitly by your program. Handle an exception by trapping it with a handler or propagating it to the calling environment.

The SYSTEM.EFFECTIVE.DATE variable is write only. It enables you to set the effective database date.

Yes, we can pass RECORD GROUP between FORMS.

Every constant, variable, and parameter has a data type (also called a type) that determines its storage format, constraints, valid range of values, and operations that can be performed on it. PL/SQL provides many predefined data types and subtypes, and lets you define your own PL/SQL subtypes.

1. Scalar: Single values with no internal components.

2. Composite: Data items that have internal components that can be accessed individually.

3. Reference: Pointers to other data items.

4. Large Object (LOB): Pointers to large objects that are stored separately from other data items, such as text, graphic images, video clips, and sound waveforms.

Rollback segment status can be viewed through DBA_ROLLBACK_SEGS. The rollback segments can be either private or public. A rollback segment in the PARTLY AVAILABLE state contains data for an in-doubt or recovered distributed transaction, and yet to be recovered transactions. You can view its status in the data dictionary view DBA_ROLLBACK_SEGS as PARTLY AVAILABLE. The rollback segment usually remains in this state until the transaction is resolved either automatically by RECO, or manually by a DBA. However, you might find that all rollback segments are PARTLY AVAILABLE.

Yes, Oracle forms issue SAVEPOINT when a form is invoked with CALL_FORM.

Cursor is a mechanism by which you can assign a name to a SELECT statement and manipulate the information within that SQL statement. It is a pointer to a private SQL area that stores information about the processing of a SELECT or DML statements like INSERT, UPDATE, DELETE or MERGE. Cursor is a mechanism which facilitates you to assign a name to a SELECT statement and manipulate the information within that SQL statement.

View is a virtual table that does not physically exist. Rather, it is created by a query joining one or more tables. It is stored in Oracle data dictionary and do not store any data. It can be executed when called.

.rdf – This file contains a single report definition in binary format. .RDF files are used to both run and edit reports.
.rep – This file contains a single report definition in binary format. .REP files are used solely to run reports; you cannot edit a .REP file
.rex – This file contains a single report definition in text format. .REX files are portable.

You can write triggers that fire whenever one of the following operations occurs:

– DML statements (INSERT, UPDATE, DELETE) on a particular table or view, issued by any user

– DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database

– Database events, such as logon/logoff, errors, or startup/shutdown, also issued either by a particular schema/user or by any schema/user in the database

Triggers are similar to stored procedures. A trigger stored in the database can include SQL and PL/SQL or Java statements to run as a unit and can invoke stored procedures. However, procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger. Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.

Following are sequences of events takes place while starting a database:
STARTUP NOMOUNT – Instance startup
STARTUP MOUNT – The database is mounted
STARTUP OPEN – The database is opened

The SHOW_ALERT function returns number. This function shows the contents of the alert log in the default editor.

No, we cannot use a RESTRICTED packaged procedure in WHEN_TEXT_ITEM trigger.

The .PLL files are the binary library modules. This file is portable across systems. The file is created when you save the library in the designer.