Oracle PL/SQL Programming and Database Administration

In SSIS a Script Component used as a transformation has exactly one input flow and can produce zero or more output flows. This matches the statement, so the True option is correct.

SSIS variables support Object data type for storing complex objects like datasets, recordsets, or .NET objects. This is a valid variable type in the SSIS variable system.

By default, stored procedures execute with the privileges of the user who invokes them (invoker's rights), not the definer's privileges. When User A executes DELETE_TEMP_TABLE, the operations run under User A's privileges, unless explicitly defined with AUTHID DEFINER to use the creator's rights instead.

To answer this question, the user needs to know about SQL triggers and how they can be used to enforce constraints on database operations. Option B is the correct answer because it creates a trigger that runs before each update of the sal (salary) column in the emp table. The trigger checks if the new salary is less than the old salary or greater than the old salary multiplied by 1.1 (i.e., increased by more than 10%). If either of these conditions is true, the trigger raises an error to prevent the update from occurring.

Option A is incorrect because it uses a check constraint to limit the salary increase, but it does not prevent salary decreases.

Option C is almost correct, but it has a syntax error. The FOR EACH ROW clause is not needed in this case, and the semicolon at the end of the RAISE_APPLICATION_ERROR function call is missing.

Option D is also incorrect because it creates a trigger that runs after each update of the sal column, which means the constraint is not enforced until after the update has already occurred.

Therefore, the correct answer is:

The Answer is: B. CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp

• FOR EACH ROW WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1)
• BEGIN
• RAISE_APPLICATION_ERROR ( - 20508, ‘Do not decrease salary not increase by more than 10%’);
• END;

The BONUS.CALC_SALARY function can be called from a SELECT statement because packaged functions are permitted in queries despite containing DML operations internally. The function has purity violations (reads and writes database state), but Oracle allows this in SELECT contexts - INSERT, UPDATE, and DELETE operations would fail due to purity restrictions.

DROP PROCEDURE removes standalone procedures. Packaged procedures cannot be dropped individually - you must drop and recreate the entire package specification and body. The REUSE SETTINGS clause mentioned in option D does not exist in Oracle's syntax for procedure removal.

To solve this question, the user needs to know the concept of triggers in database management systems. The user must also understand the difference between statement-level triggers and row-level triggers.

In this scenario, we need to create a trigger that monitors every row that is changed in the EMP table and inserts that information into the AUDIT_TABLE. Therefore, we need to create a row-level trigger.

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

A. FOR EACH ROW trigger on the EMP table. This option is correct. A FOR EACH ROW trigger on the EMP table will monitor every row that is changed in the EMP table and execute the trigger code for each affected row. This is the type of trigger that we need to create to accomplish the task.

B. Statement-level trigger on the EMP table. This option is incorrect. A statement-level trigger on the EMP table triggers once for each SQL statement that is executed, regardless of the number of rows that are affected. This type of trigger would not be suitable for the task at hand.

C. FOR EACH ROW trigger on the AUDIT_TABLE table. This option is incorrect. A FOR EACH ROW trigger on the AUDIT_TABLE table would monitor every row that is changed in the AUDIT_TABLE table, but this is not what we need. We need to monitor changes in the EMP table.

D. Statement-level trigger on the AUDIT_TABLE table. This option is incorrect. A statement-level trigger on the AUDIT_TABLE table would only trigger once for each SQL statement that is executed, regardless of the number of rows that are affected. This type of trigger would not be suitable for the task at hand.

The Answer is: A. FOR EACH ROW trigger on the EMP table.

Oracle Advanced Queuing enables asynchronous message processing. DBMS_JOB schedules jobs for concurrent execution. Pipelined Functions allow streaming and parallel processing. DBMS_SQL is for dynamic SQL execution within a single program - it does NOT facilitate running multiple PL/SQL programs simultaneously.

The answer to the question is A. NULL.

When a variable is declared in PL/SQL without an initial value, it is initialized to NULL. This means that the variable has no value, and any attempt to use it will result in a NULL value.

Option B is incorrect because the value of a NUMBER variable is not automatically initialized to 0. Option C is incorrect because a compilation error will not occur if a variable is declared without an initial value. Option D is incorrect because an exception will not be raised if a variable is declared without an initial value.

Here is an example of how a NULL value is handled in PL/SQL:

DECLARE
  v_number NUMBER;
BEGIN
  v_number := NULL;
  dbms_output.put_line(v_number); -- This will print 'NULL'
END;

As you can see, the value of v_number is printed as NULL when it is first used in the executable section of the PL/SQL block. This is because the variable was declared without an initial value, and therefore its value is NULL.

USER_SOURCE contains PL/SQL source code for your schema. INSTR(UPPER(text), 'DBMS_OUTPUT.PUT_LINE') searches within the code body for calls to that procedure. DISTINCT removes duplicates. Option A uses dependencies (not body content). B searches wrong column and uses ALL_SOURCE. C uses ALL_SOURCE instead of USER_SOURCE.

PL/SQL's SELECT...INTO expects exactly one row. If NO rows are returned, NO_DATA_FOUND is raised. If MORE than one row is returned, TOO_MANY_ROWS is raised. Datatype mismatch raises VALUE_ERROR, not a standard exception. Missing INTO is a compile-time syntax error, not a runtime exception.

NULL represents unknown or missing data. Any comparison operation involving NULL (including NULL = NULL) yields NULL, not TRUE or FALSE. This is three-valued logic: TRUE, FALSE, or NULL (unknown). Use IS NULL or IS NOT NULL to test for NULL values.

Error trapping uses EXCEPTION handlers within the EXCEPTION section of a PL/SQL block. The EXECUTABLE section contains the runtime code that may raise exceptions. The EXCEPTION section is where handlers are defined. Header has no error handling. Declarative defines variables/types.

The smallest possible PL/SQL block is 'BEGIN NULL; END;'. This is a valid PL/SQL block that does nothing - it executes a NULL statement. PL/SQL requires at least one executable statement between BEGIN and END, and NULL is the minimal statement that satisfies this requirement. Options B and D include additional statements or declarations, making them larger than minimal. Option C is incomplete syntax with '.. statement1' placeholder.

In SQL Server 2005 and earlier versions, a table could have a maximum of 249 non-clustered indexes (along with 1 clustered index). While newer versions (SQL Server 2008 and later) expanded this limit to 999, 249 remains the correct historical limit among the choices, making other options incorrect.

Database systems have specific limits on key column counts. While many systems like SQL Server limit composite keys to 16 columns, certain legacy or specialized database systems (such as some IMS/VSAM configurations) impose different limits, with 31 being one such system-specific constraint.

Hierarchical database systems like IMS/DM limit the number of segment types in a single database. The value 32 represents a specific configuration limit in certain database setups, controlling the complexity of hierarchical structures that can be defined.

In older database systems like Sybase ASE and early versions of SQL Server (using a 2KB page size), the maximum allowable combined size of all columns in an index key is 600 bytes. Other options like 255, 256, or 32 do not represent this specific architectural limit for index key sizes.

Modern database and programming systems typically do not impose fixed length limits on comments. Comments are stored as text and their size is constrained only by available memory, storage, or practical query/batch limits rather than an arbitrary character count.

Sybase ASE allows a maximum of three triggers per table: one each for INSERT, UPDATE, and DELETE operations. This is a fundamental design limit in Sybase, distinguishing it from systems that allow multiple triggers per operation type.