Oracle PL/SQL Database Fundamentals

To view Oracle version: (1) SELECT banner FROM v$version; displays version banners for all components. (2) SELECT version FROM v$instance; shows instance version. Option 3 is invalid - v$version doesn't have a 'version' column (it has 'banner'). So 'Both 1 and 2' is correct, not 'Both 2 and 3'.

PL/SQL provides predefined exceptions like CURSOR_ALREADY_OPEN and INVALID_CURSOR. There is no predefined exception named Cursor_Not_Closed in PL/SQL.

The SIGN function returns: 1 for positive numbers, 0 for zero, and -1 for negative numbers. Option D correctly states it returns 1 for positive values. Options A and C are wrong because positive values return 1, not >0 or 0.

A CURSOR FOR loop implicitly opens and fetches from the cursor automatically. You do NOT use explicit OPEN or FETCH commands - the loop handles this internally. Therefore 'None' (no explicit command) is correct.

To solve this question, the user needs to have knowledge of error handling in SQL.

Now, let's go through each option and explain why it is right or wrong:

A. RAISE_ERROR: This option is not a valid procedure to create a custom error message in SQL. RAISE_ERROR is not a standard Oracle SQL function.

B. SQLERRM: This option is not a procedure to create a custom error message, but it is a function that returns the error message associated with the most recently raised error exception. It is not used to raise custom exceptions.

C. RAISE_APPLICATION_ERROR: This option is correct. RAISE_APPLICATION_ERROR is a procedure used to create a customized error message in Oracle SQL. It allows an application to raise an error with a user-defined error message and error number.

D. RAISE_SERVER_ERROR: This option is not a valid procedure to create a custom error message in SQL. RAISE_SERVER_ERROR is not a standard Oracle SQL function.

Therefore, the correct answer is: C. RAISE_APPLICATION_ERROR

When rows are found using a FETCH statement in SQL, it loads the current row values into variables.

Option B is the correct answer. The FETCH statement is used to retrieve rows from a cursor, and each time it is executed, it fetches the next row from the cursor result set and assigns the values to the specified variables. This allows us to work with the data retrieved from the cursor.

Here is a breakdown of the other options:

A. It causes the cursor to close: This option is incorrect. The FETCH statement does not close the cursor; it only retrieves the rows. The cursor can be closed explicitly using the CLOSE statement.

C. It causes the cursor to open: This option is incorrect. The FETCH statement is used after the cursor has been declared and opened. It does not open the cursor itself.

D. It creates the variables to load the values: This option is incorrect. The variables to hold the row values need to be declared before the FETCH statement is executed. The FETCH statement does not create the variables, but rather assigns the retrieved values to the existing variables.

Therefore, the correct answer is B. It loads the current row values into variables.

Functions MUST return a value and are invoked within expressions (like SELECT or assignments). Procedures do NOT return values - they perform actions. Options B and E are incorrect because procedures don't require return values. Option C is vague - both functions and procedures execute PL/SQL.

The procedure receives v_cost_per_ticket as input (IN) to compare against 8.5, but it may also need to return the fetched value back to the caller (OUT). IN OUT mode allows both passing a value in and returning the modified value. IN alone wouldn't return the fetched result.

Variables declared in the package body are private to that package - only the procedures within the body can access them. Package specification makes variables public (visible externally). Declaring in each procedure separately would create three independent copies, not one shared variable.

NEW and OLD qualifiers reference the row values being changed. They are only available in row-level triggers (FOR EACH ROW) that process individual rows. Statement-level triggers fire once per statement and cannot access row-specific values.

The function declaration fails to compile because the variable v_email_home (or v_raise in the body) is mismatched or contains syntax errors, and the SQL query contains invalid syntax like [email protected] . instead of standard string literals.

A packaged function called from a SQL query cannot execute DML statements (INSERT, UPDATE, DELETE) against the table being queried, as this violates read-consistency rules and causes a runtime error.

Exceptions cannot propagate across RPC boundaries without serialization. An exception raised in a declaration propagates to the enclosing block, not the current block. The RAISE statement without an exception name reraises the current exception, and exceptions in handlers propagate to the enclosing block.

DBMS_OUTPUT.PUT_LINE appends text followed by a newline to the output buffer. PUT appends text without newline. GET and GET_LINE retrieve text. PUT_TEXT_LINE is not a standard procedure - the correct name is PUT_LINE.

The System Monitor (SMON) background process is responsible for performing instance recovery when an Oracle database is restarted after an unexpected crash. PMON cleans up failed user processes, RECO resolves pending distributed transactions, ARCn archives redo logs, and CKPT signals checkpoints, making them incorrect for instance recovery.

Having 30 extents is NOT necessarily bad. In locally managed tablespaces with uniform extent sizes, multiple extents are normal and expected. Extent count alone doesn't indicate performance issues.

A PRIMARY KEY constraint uniquely identifies each row AND implicitly enforces NOT NULL, satisfying both rules. UNIQUE allows nulls, CHECK doesn't enforce uniqueness, NOT NULL doesn't enforce uniqueness, and FOREIGN KEY references other tables.