Oracle Online Test Series 3, Oracle Mock Test, Oracle Online Exam

TIMEOUT_ON_RESOURCE is a predefined oracle exception. A time out occurs while Oracle is waiting for a resource.

The above statement is true. A ref cursor can be passed from one procedure to another. A ref cursor is a variable, defined as a cursor type, which will point to, or reference a cursor result. The advantage that a ref cursor has over a plain cursor that can be passed as a variable to a procedure or a function. A ref cursor can be assigned to another ref cursor variable. This is a powerful capability in that the cursor can be opened, passed to another block for processing, then returned to the original block to be closed. The cursor variable can also be returned by a function and assigned to another variable. A ref cursor variable is not a cursor, but a variable that points to a cursor. Before assigning a cursor variable, a cursor type must be defined.

Explicit cursors can be declared by us for the queries that return more than one row. In explicit cursor, the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row, the current row position moves to next row. Oracle provides some attributes known as Explicit Cursor Attributes to control the data processing while using cursors. We use these attributes to avoid errors while accessing cursors through OPEN, FETCH and CLOSE Statements. An explicit cursor is defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.

Translate function performs a character wise replacement of a string. This function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion.

Varray has fixed upper-bound size that has to be specified while it is declared. Varray is varying array type that is typically used when the number of instances to be stored is small. It has a set of data elements and all are of the same data type. The size of Varray determines the number of elements. Varrays are one-dimensional, arrays. The maximum length is defined in the declaration itself. These can be only used when you know in advance about the maximum number of items to be stored.

Incremental backup stores all files changed since the last FULL, DIFFERENTIAL OR INCREMENTAL backup. The advantage of an incremental backup is that it takes the least time to finish. The disadvantage is that during a restore operation, each increment is processed and this could result in a lengthy restore job. Incremental backup provides a faster method of backing up data than repeatedly running full backups. During an incremental backup, only files changed since the most recent backup are included. That is where it gets its name: each backup is an increment for a previous backup.

A record group is an oracle forms data structure that has a column/row framework similar to a database table. There are three types of record groups:
1. Query record groups
2. Non-query record groups
3. Static record groups.

1. Query record group: A query record group is a record group that has an associated SELECT statement. Query record groups can be created and modified at design time or at runtime.

2. Non-query record group: A non-query record group is a group that does not have an associated query. Non-query record groups can be created and modified only at runtime.

3. Static record group: A static record group is not associated with a query. Static record groups can be created and modified only at design time.

The above statement is true. Privileges of a ROLE in oracle can be dynamically changed. A user privilege is a right to execute a particular type of SQL statement, or a right to access another user’s object. The types of privileges are defined by Oracle. Roles, on the other hand, are created by users (usually administrators) and are used to group together privileges or other roles. They are a means of facilitating the granting of multiple privileges or roles to users.

When a sequence is created using CYCLE option, values for the column are regenerated from MINVALUE once MAXVALUE is reached. Certainly, this option should not be used if the Sequence is created for a primary key column.

When a sequence is created using NO CYCLE option, values for the column are NOT regenerated from MINVALUE once MAXVALUE is reached.

The pragma EXCEPTION_INIT directs the compiler to add the user defined error message to the Oracle error number. The pragma EXCEPTION_INIT associates an exception name with an Oracle error number. You can intercept any ORA- error and write a specific handler for it instead of using the others handler. Pragma is a keyword directive to execute proceed at compile time. The pragma EXCEPTION_INIT function takes two arguments: 1. exception_name and 2. error_number

You can define pragrma EXCEPTION_INIT in DECLARE BLOCK on your program: PRAGMA EXCEPTION_INIT(exception_name, -error_number);

The exception_name and error_number define on yourself, where exception_name is a character string up to 2048 bytes supports and error_number is a negative integer range from -20000 to -20999.

A pragma is a compiler directive that is processed at compile time, not at run time.

MOD_PLSQL is an Apache (Web Server) extension module that allows one to create dynamic web pages from PL/SQL packages and stored procedures. It is ideal for developing fast and flexible applications that can run on the Internet or an Intranet. MOD_PLSQL was formerly called the Oracle PL/SQL Cartridge and OWA (Oracle Web Agent).

To create a trigger on a pluggable database (PDB), you must be connected to that PDB and have the ADMINISTER DATABASE TRIGGER system privilege. ADMINISTER DATABASE TRIGGER is used to allow creating or altering a system trigger on the database.

USER_TRIGGERS describes the triggers owned by the current user. Its columns (except for OWNER) are the same as those in ALL_TRIGGERS.

Migration is the process of copying the schema objects and data from a source third-party (non-Oracle) database, such as MySQL, Microsoft SQL Server, Sybase Adaptive Server, Microsoft Access, or IBM DB2 (UDB), to an Oracle database. You can perform the migration in an efficient, largely automated way.

SQL Developer stores user-related information in several places, with the specific location depending on the operating system and certain environment specifications. User-related information includes user-defined reports, user-defined snippets, SQL Worksheet history, code templates, and SQL Developer user preferences. In most cases, your user-related information is stored outside the SQL Developer installation directory hierarchy, so that it is preserved if you delete that directory and install a new version. The user-related information is stored in or under the IDE_USER_DIR environment variable location.

A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include the following types of objects:
– Clusters
– Database links
– Database triggers
– Dimensions
– External procedure libraries
– Indexes and index types
– Java classes, Java resources, and Java sources
– Materialized views and materialized view logs
– Object tables, object types, and object views
– Operators
– Sequences
– Stored functions, procedures, and packages
– Synonyms
– Tables and index-organized tables
– Views

A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Other types of objects are also stored in the database and can be created and manipulated with SQL but are not contained in a schema:
– Contexts
– Directories
– Profiles
– Roles
– Tablespaces
– Users
– Rollback segments
Schema objects are logical data storage structures. Schema objects do not have a one-to-one correspondence to physical files on disk that store their information. However, Oracle stores a schema object logically within a tablespace of the database. The data of each object is physically contained in one or more of the tablespace’s datafiles. For some objects, such as tables, indexes, and clusters, you can specify how much disk space Oracle allocates for the object within the tablespace’s datafiles. There is no relationship between schemas and tablespaces: a tablespace can contain objects from different schemas, and the objects for a schema can be contained in different tablespaces.

Oracle also provides support for function-based indexes and domain indexes specific to an application or cartridge. Domain indexes are appropriate for special-purpose applications implemented using data cartridges. The domain index helps to manipulate complex data, such as spatial, time-series, audio, or video data. A function-based index is an index built on an expression. It extends your indexing capabilities beyond indexing on a column. A function-based index increases the variety of ways in which you can access data.

Sensitive information is stored in your database or travels over enterprise networks and the Internet can be protected by encryption algorithms. An encryption algorithm transforms information into a form that cannot be deciphered without a decryption key.

PL/SQL wrapper functions that use Java libraries impose a limit on method definitions. All Java class methods accessed by PL/SQL wrapper functions must be static. Therefore, Java libraries that support PL/SQL wrapper functions are not thread safe.

Oracle Advanced Security supports Triple-DES encryption (3DES), which encrypts message data with three passes of the DES algorithm. 3DES provides a high degree of message security, but with a performance penalty. The magnitude of the performance penalty depends on the speed of the processor performing the encryption. 3DES typically takes three times as long to encrypt a data block when compared to the standard DES algorithm.

The Advanced Encryption Standard (AES) [AES] was developed to replace DES [DES]. The AES Federal Information Processing Standard (FIPS) Publication specifies a cryptographic algorithm for use by U.S. Government organizations. However, the AES will also be widely used by organizations, institutions, and individuals outside of the U.S. Government. AES is a subset of the Rijndael cipher[5] developed by two Belgian cryptographers, Joan Daemen and Vincent Rijmen, who submitted a proposal to NIST during the AES selection process.[7] Rijndael is a family of ciphers with different key and block sizes.

Oracle Advanced Security ensures data integrity with sequenced, cryptographic checksums. To ensure that data has not been modified, deleted, or replayed during transmission, Oracle Advanced Security optionally generates a cryptographically secure message digest–through cryptographic checksums using the MD5 algorithm–and includes it with each packet sent across the network. Alternatively, Oracle Advanced Security can use SHA-1 (with SSL). Data integrity algorithms add little overhead, and protect against data modification attacks, deleted packets, and replay attacks.

Single Sign-On (SSO) has become an over-loaded term for many developers. It’s used, sometimes inaccurately, to refer to any tool that simplifies login for the end-user. But SSO has a very specific technical definition, and the confusion increasingly makes it difficult for developers to find the right tool for the job.

Secure Sockets Layer (SSL) is an industry standard protocol originally designed by Netscape Communications Corporation for securing network connections. SSL uses RSA public key cryptography in conjunction with symmetric key cryptography to provide authentication, encryption, and data integrity.

To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multi-row query (SELECT statement), you use the OPEN-FOR, FETCH, and CLOSE statements. The EXECUTE IMMEDIATE statement executes a dynamic SQL statement or anonymous PL/SQL block. You can use it to issue SQL statements that cannot be represented directly in PL/SQL, or to build up statements where you do not know all the table names, WHERE clauses, and so on in advance.

The sp_executesql command is used to execute a dynamically build Transact-SQL statements. The sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. The Transact-SQL statement or batch in the sp_executesql @stmt parameter is not compiled until the sp_executesql statement is executed.

The OPEN FOR statement is used to associate a cursor variable with the dynamic SQL statement. The OPEN-FOR statement executes the SELECT statement associated with a cursor variable. It allocates database resources to process the statement, identifies the result set (the rows that meet the conditions), and positions the cursor variable before the first row in the result set. Use an OPEN FOR statement to associate a cursor variable with the dynamic SQL statement. In the USING clause of the OPEN FOR statement, specify a bind variable for each placeholder in the dynamic SQL statement. The USING clause cannot contain the literal NULL. To work around this restriction, use an uninitialized variable where you want to use NULL.