Oracle PL/SQL Programming

Oracle PL/SQL implicitly creates cursors for all DML statements (INSERT, UPDATE, DELETE, MERGE) and SELECT statements (which are PL/SQL statements). These implicit cursors handle the SQL processing automatically - you declare variables to receive the data, but the cursor itself is created implicitly. Option A is incorrect because it's not just 'some' but all statements. Option C is incorrect because implicit cursors are also created for DML, not just PL/SQL. Option D is incorrect because implicit cursors are indeed declared.

The RAISE_APPLICATION_ERROR procedure is a built-in Oracle PL/SQL procedure used to issue user-defined error messages from stored subprograms with error numbers between -20000 and -20999.

Exception handling in PL/SQL follows a two-phase process: first trap the exception with an exception handler (WHEN clause in the EXCEPTION block), then optionally propagate it to the calling environment if the handler cannot fully resolve it. Option C ('a & then b') correctly captures this sequence - handle first, propagate if needed. Option A is incomplete because trapping alone doesn't complete the handling. Option B is incorrect as the first step must always be handling. Option D reverses the correct order - you cannot propagate before trapping.

The procedure references a user-defined exception called STATS_EXIST_EXCEPTION in the EXCEPTION block, but this exception is never declared in the declaration section. In PL/SQL, user-defined exceptions must be explicitly declared before they can be used. This missing declaration is what prevents the procedure from being created successfully. Option A is incorrect because commas after exception names are not required syntax. Option B is incorrect because exceptions are not declared as numbers - they're declared as EXCEPTION type. Option D is incorrect because there is a clear error preventing creation.

Database triggers should be designed to guarantee that when a specific operation is performed, related actions are executed, and for centralized, global operations that must fire regardless of the user or application. Replicating built-in constraints or duplicating other triggers are bad design practices.

Package overloading in PL/SQL allows multiple subprograms with the same name to coexist if they differ in their parameter signatures. Only local subprograms or packaged subprograms can be overloaded - standalone procedures cannot use overloading (A is true). Overloading requires that subprograms differ in the number, type, or order of parameters - having the same parameter signature is not allowed (E is true). Option B is false because overloading based solely on return type is not permitted. Option C is false because overloading requires differences in parameters. Option D is false because subprograms with identical parameters cannot be overloaded.

The REPLACE keyword in CREATE OR REPLACE PROCEDURE performs two actions: if a procedure with the same name already exists, it drops the existing procedure first, then creates the new version. This allows you to update procedure definitions without manually dropping them first. Option A correctly describes this behavior. Option B is incorrect because REPLACE does replace with the new version, not leave it missing. Option C is incorrect because a change does occur - the old version is dropped. Option D is incorrect because REPLACE is a valid and commonly used option.

PL/SQL packages cannot be nested, nor can they accept parameters directly (though their subprograms can). They are loaded into memory once, not on every invocation. The true statements are that package contents can be shared and information hiding is achieved by making constructs private.

The statement in the question is false. In PL/SQL, a function MUST return exactly one value to its calling environment, while a procedure returns zero or more values (through OUT parameters). The question claims the opposite - that functions return zero or more and procedures return one value. Therefore, the correct answer is False (option B). Functions are designed to compute and return a single value, whereas procedures are designed to perform actions and can optionally return values through output parameters.

This is a trick question - all three statements A, B, and C about packages are actually TRUE. Statement A is true - packages cannot be called, parameterized, or nested like subprograms can. Statement B is true - packages do have a format similar to subprograms with specification and body. Statement C is true - packages are designed for code sharing. Since the question asks 'which is false' and none of A, B, or C are false, the correct answer is D (None of the above).

Identifiers defined only in the package body (not in the package specification) are PRIVATE constructs, not public. They are invisible outside the package body and can only be used by other subprograms within the same package body. To make identifiers public and visible outside the package, they must be declared in the package specification. Therefore, the statement in the question is false, making option B the correct answer.

Private constructs defined in the package body are completely hidden from external access. This enforces information hiding and encapsulation, which is a major advantage of PL/SQL packages.

The trigger fails because it references :NEW.ID, which is only available in row-level triggers. A statement-level trigger (default) cannot access :NEW or :OLD values since it fires once per statement, not once per affected row. Converting to a row-level trigger (FOR EACH ROW) allows access to :NEW values for each inserted row.

For stored program units in PL/SQL: Functions must use RETURN (without precision in the RETURN clause) and cannot have OUT parameters - option A is valid. Procedures can have OUT parameters to return values - option C is valid (OUT parameters don't specify precision). Option B is wrong because procedures don't use RETURN. Option D is wrong because RETURN clauses don't specify precision like NUMBER(10,2). Option E is wrong because OUT parameters don't specify precision or length.

A stored procedure must have at least one executable statement in its body - it cannot be empty (just NULL is executable). Option A is wrong because it's DECLARE (not DELCLARE) and DECLARE is used in the body for local variables, not formal parameters. Option B is wrong because procedures don't require parameters - parameterless procedures are valid. Option D is wrong because DECLARE is for local variables in the body, not formal parameters in the specification.

The procedure has two parameters with DEFAULT values (V_ID=10, V_AB=4), so it can be called with no arguments - option A works. Option B uses named notation correctly and overrides defaults. Option C is wrong because it passes 3 arguments but only 2 exist, and 'FOUR'/'TWO' are strings where numbers expected. Option D is wrong because EXECUTE keyword is required in SQLPlus. Option E is wrong because RUN is a SQLPlus command for scripts, not procedures.

Package variables are instantiated per-session. Jones's change to g_comm affects only Jones's session, so Jones sees 15, while Smith's session remains independent.

SELECT * with notifications (A) automatically captures schema changes. %TYPE attribute (B) anchors variables to column datatypes, reducing coupling. %ROWTYPE attribute (D) mirrors table structure for records, minimizing maintenance when tables change. Schema names (C) increase coupling by hardcoding references. Package.procedure notation (E) doesn't affect dependency failure - it's just execution syntax.

Stored procedures in PL/SQL are named program units that can accept parameters (B) and are subprograms that perform specific actions (C). Option A is misleading because stored procedures use PL/SQL (which includes SQL), not pure SQL. Option D incorrectly describes the structure; procedures have a header (specification) and body, but not a separate 'exception handler part'. Option E describes what an executable section does but doesn't define what a stored procedure is.

Within a trigger body, the CALL statement is used to invoke a stored procedure. Triggers can call stored procedures to execute complex business logic or perform operations that cannot be done directly in trigger SQL. You cannot CALL packages, functions (use direct invocation), or other triggers from within a trigger.