Tag: databases

Questions Related to databases

In a PL/SQL block, a variable is declared as NUMBER without an initial value. What will its value be when it is first used in the executable section of the PL/SQL block?
technology databases

  1. NULL

  2. 0

  3. Results in a compilation error

  4. An exception will be raised

Show answer
Correct Option: A
Explanation:

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.

Which of the following queries return the names of programs (without duplication) defined in the currently-connected schema whose source contains a call to DBMS_OUTPUT.PUT_LINE (assume that this program name does not appear inside comments)?
technology databases

  1. SELECT name FROM USER_DEPENDENCIES WHERE referenced_name = 'DBMS_OUTPUT.PUT_LINE'

  2. SELECT name from ALL_SOURCE WHERE owner = USER AND name = 'DBMS_OUTPUT.PUT_LINE'

  3. SELECT DISTINCT name from ALL_SOURCE WHERE INSTR (text, 'DBMS_OUTPUT.PUT_LINE') > 0

  4. SELECT DISTINCT name from USER_SOURCE WHERE INSTR (UPPER (text), 'DBMS_OUTPUT.PUT_LINE') > 0

Show answer
Correct Option: D

PL/SQL raises an exception, in which TWO of the following cases:
technology databases

  1. When a SELECT statement returns no rows

  2. When a SELECT statement returns more than one row

  3. When the datatypes of SELECT clause and INTO clause do not match

  4. When INTO statement is missing in the SELECT statement

Show answer
Correct Option: A,B

What is the result if two NULL values are compared to each other?
technology databases

  1. TRUE

  2. FALSE

  3. UNDEFINED

  4. NULL

Show answer
Correct Option: D

Functions for error trapping are contained in which section of a PL/SQL block?

technology databases

  1. Header

  2. Declarative

  3. Executable

  4. Exception

Show answer
Correct Option: C,D

AI Explanation

To answer this question, we need to understand the structure of a PL/SQL block.

A PL/SQL block is divided into three sections:

  1. Header: This section is optional and is used to declare the name and parameters of the PL/SQL block.

  2. Declarative: This section is also optional and is used to declare variables, cursors, types, and other program objects that are used within the block.

  3. Executable: This section contains the actual executable code of the PL/SQL block.

The correct answer is:

C. Executable: The executable section is where the main code logic of the PL/SQL block is written. It contains statements and control structures that are executed sequentially.

D. Exception: The exception section is used to handle and trap errors that may occur during the execution of the PL/SQL block. It contains error-handling code that is executed when an exception is raised.

Therefore, the functions for error trapping are contained in the executable and exception sections of a PL/SQL block.

What is the smallest possible PL/SQL block?
technology databases

  1. Begin null; end;

  2. Declare x varchar2(100); begin dbms_output.put_line(x); end;

  3. Begin .. statement1; exception ... end;

  4. Begin dbms_output.put_line(sysdate); end;;

Show answer
Correct Option: A

Maximum no of non clustered index in a table.
technology databases

  1. 192

  2. 255

  3. 249

  4. 300

Show answer
Correct Option: C

Maximum no of columns in a key.
technology databases

  1. 1

  2. 31

  3. 16

  4. 255

Show answer
Correct Option: B

maximum number of segments for any database
technology databases

  1. 8

  2. 1

  3. 31

  4. 32

Show answer
Correct Option: D

The maximum allowable size of the combined index values

technology databases

  1. 255

  2. 600

  3. 256

  4. 32

Show answer
Correct Option: B