Correct Answer : Block Structured Language
Explanation : PL/SQL is a Block Structured Language that enables developers to combine the power of SQL with procedural statements.All the statements of a block are passed to oracle engine all at once which increases processing speed and decreases the traffic.
Correct Answer : 1991
Explanation : In 1991, Oracle Corporation released Oracle Version 6.0, a major advance in its relational database technology. A key component of Oracle Version 6.0 was the so-called "procedural option" or PL/SQL. At roughly the same time, Oracle released its long-awaited upgrade to SQL*Forms Version 2.3.
Correct Answer : PL/SQL stands for Procedural Language Extension of SQL
Explanation : PL/SQL stands for Procedural Language Extension of SQL.
Correct Answer : All of the above
Explanation :
Explanation : Oracle Database's portability, robustness and security are inherited in it.
Correct Answer : 30
Explanation : A Variable in PL/SQL should not exceed 30 characters.
Correct Answer : Not Case Sensitive
Explanation : PL/SQL Variables are by default not case sensitive.
Explanation : PL/SQL text is made up of lexical units, which are groups of characters and can be classified as Delimiters, Identifiers, Literals and Comments.
Correct Answer : Declaration Section
Explanation : PL/SQL Variable needs to be declared in the Declaration Section.
Correct Answer : variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]
Correct Answer : BLOB is used to store character data in the database.
CASE selector WHEN 'value1' THEN S1; WHEN 'value2' THEN S2; WHEN 'value3' THEN S3; ... ELSE Sn; -- default case END CASE;
Correct Answer : It is perfectly written.
Correct Answer : TYPE grades IS VARRAY(100) OF INTEGER;
DECLARE total_rows number(2); BEGIN UPDATE employees SET salary = salary + 500; IF ____________ THEN dbms_output.put_line('no employees selected'); ELSIF ___________ THEN total_rows := _____________; dbms_output.put_line( total_rows || ' employees selected '); END IF; END;
Correct Answer : sql%notfound, sql%found, sql%rowcount.
Correct Answer : None of the above.
Correct Answer : A collection is an ordered group of elements having the same data type.
Correct Answer : TYPE salary IS TABLE OF INTEGER;
Correct Answer : This error message appears when a number is divided by zero.
Correct Answer : Errors relating to arithmetic, conversion, truncation, or size constraints raise this signal.
Correct Answer : TOO_MANY_ROWS
Explanation : The exception thrown when more than one row is returned from a SELECT INTO statement is TOO_MANY_ROWS.
Correct Answer : STORAGE_ERROR
Explanation : A PL /SQL exception, which occurs when a memory error occurs, is STORAGE_ERROR.
Correct Answer : When a member method is invoked on an object type, but its instance has not been initialized, this exception is raised.
Explanation : SELF_IS_NULL exception is raised when a member method is invoked on an object type, but its instance has not been initialized, this exception is raised.
Correct Answer : Cursors raise this exception when they seek values in variables of incompatible types.
Explanation : Cursors raise the exception ROWTYPE_MISMATCH when they seek values in variables of incompatible types.
Correct Answer : PROGRAM_ERROR
Explanation : PROGRAM_ERROR is raised when there is an internal problem in PL/SQL.
PROGRAM_ERROR
PL/SQL
Correct Answer : NOT_LOGGED_ON
Explanation : An exception which occurs when attempting to access a database without connecting to it is NOT_LOGGED_ON.
Correct Answer : A unique index column with duplicate values is raised when this error occurs.
Explanation : DUP_VAL_ON_INDEX exception is raised when an attempt is made to close an unopened cursor or perform a cursor operation without permission.
DUP_VAL_ON_INDEX
Correct Answer : The exception is thrown when an uninitialized nested table or varray is attempted to be populated with collection methods other than exist, or when an element of an uninitialized nested table or varray is attempted to be assigned values.
Explanation : COLLECTION_IS_NULL exception is raised when an uninitialized nested table or varray is attempted to be populated with collection methods other than exist, or when an element of an uninitialized nested table or varray is attempted to be assigned values.
COLLECTION_IS_NULL
Correct Answer : CASE_NOT_FOUND
Explanation : CASE_NOT_FOUND exception is rose when there is no other clause in the "WHEN" clause of a CASE statement, and none of the choices in the "WHEN" clause have been selected.
Correct Answer : An automatic assignment of a value to a NULL object raises this exception.
Explanation : ACCESS_INTO_NULL exception is raised when an automatic assignment of a value to a NULL object raises this exception.
ACCESS_INTO_NULL
Correct Answer : NO_DATA_FOUND
Explanation : When SELECT INTO returns no rows, NO_DATA_FOUND pre-defined exception is raised.
SELECT INTO
NO_DATA_FOUND
Correct Answer : CLOSE
Explanation : CLOSE clause doesn't come in the syntax while raising an exception.
CLOSE
Correct Answer : DECLARE my-exception EXCEPTION;
Explanation : DECLARE my-exception EXCEPTION is the syntax of user-defined exceptions.
Correct Answer : Both (A) and (B)
Explanation : Users can explicitly raise an exception by using a RAISE statement or DBMS_STANDARD.RAISE_APPLICATION_ERROR can be used to raise a user-defined exception explicitly.
DBMS_STANDARD
RAISE_APPLICATION_ERROR
Correct Answer : RAISE
Explanation : With the help of RAISE command, one can easily raise an exception explicitly.
Explanation : System-defined and User-defined exceptions are both the types of exceptions.
Correct Answer : 2
Explanation : There are 2 types of exceptions.
Correct Answer : EXCEPTION
Explanation : Errors that are encountered during the execution of the program are referred to as exceptions in PL/SQL.
Correct Answer : Constrained declaration
Explanation : The term constrained declaration refers to a declaration with a size, scale, or precision limit.
Correct Answer : An unconstrained declaration requires more memory than a constrained declaration.
Explanation : In order to initialize a variable with the value other than NULL, it can be done using the Assignment Operator and the DEFAULT Keyword both.
Correct Answer : Set serveroutput On
DECLARE x number := 1; BEGIN LOOP dbms_output.put_line(x); x := x + 1; IF x > 10 THEN exit; END IF; dbms_output.put_line('After Exit x is: ' || x); END;
Correct Answer : There should be an END LOOP statement.
DECLARE a number(2) ; BEGIN FOR a IN REVERSE 10 .. 20 LOOP END LOOP; dbms_output.put_line(a); END;
Correct Answer : 10
Correct Answer : All of the above.
Correct Answer : View-based
Correct Answer : SELECT INTO statement returns more than one row.
Correct Answer : Declaration of a nested table is similar to declaration of an index-by table along with the INDEX BY clause.
Correct Answer : Local & Global Variable
Explanation : The two variable scopes in PL/SQL are Local & Global Variables.
Correct Answer : Variables in an inner block are defined as local variables since they cannot be accessed from the outside whereas the outermost block declares global variables.
Correct Answer : Constants
Explanation : Constants are values used in PL/SQL blocks that do not change during execution.
Correct Answer : User-defined
Explanation : PL/SQL Constant is a User-defined literal value.
Correct Answer : constant_name CONSTANT datatype := VALUE;
constant_name CONSTANT datatype := VALUE;
Correct Answer : If the condition is TRUE, then you want statements to be executed
Correct Answer : Depending on whether the condition is TRUE or FALSE, you may want to execute one set of statements or another set.
Correct Answer : As long as condition1 is TRUE, then one set of statements will be executed, and when condition2 is TRUE, another set will be executed.
Correct Answer : IF-THEN-ELSE statement
Explanation : IF-THEN-ELSE executes the corresponding code whenever a TRUE condition is found. Otherwise, the condition cannot be checked again.
IF-THEN-ELSE
Correct Answer : ELSE
Correct Answer : WHEN
Explanation : CASE statement uses WHEN keyword to work like IF statement.
Explanation : The selectors in case of CASE statement are Variable, Function or Expression.
Correct Answer : TOP to BOTTOM
Explanation : Evaluation of CASE statement is done TOP to BOTTOM.
Correct Answer : Loops
Explanation : By using PL/SQL loops, you can repeatedly execute one or more statements over and over again.
Explanation : BEFORE or AFTER or INSTEAD OF clauses are used to specify when the trigger will get executed.
Correct Answer : DML Operation is performed
Explanation : INSERT or UPDATE or DELETE clauses performs the DML Operations in Trigger syntax.
INSERT
UPDATE
DELETE
Correct Answer : Tables are replicated asynchronously
Explanation : Trigger has this advantage of the tables to be replicated synchronously and not asynchronously.
Explanation : SERVERERROR, STARTUP, SHUTDOWN, LOGON and LOGOFF are all the database operations.
Correct Answer : None of the above
Correct Answer : Trigger
Explanation : Whenever a specified event occurs, the trigger is automatically triggered by the Oracle engine.
Explanation : CLOSE clause is used to close the cursor.
Correct Answer : OPEN
Explanation : OPEN is the clause used to open the cursor.
Correct Answer : FETCH cursor_name INTO variable_list;
Explanation : FETCH cursor_name INTO variable_list is the correct syntax to fetch the cursor.
cursor_name
variable_list
Correct Answer : Iterative Control Statements
Explanation : PL/SQL Loops are also known as Iterative Control Statements.
LOOP END LOOP; Sequence of statements;
END LOOP; Sequence of statements; LOOP
END LOOP; LOOP Sequence of statements;
LOOP Sequence of statements; END LOOP;
Correct Answer : LOOP Sequence of statements; END LOOP;
Correct Answer : 4
Explanation : PL/SQL provides four kinds of loop statements :* basic loop* WHILE loop* FOR loop* cursor FOR loop.
Correct Answer : Exit
Explanation : An exit loop in PL/SQL ensures that at least one statement is executed before the loop terminates.
Correct Answer : While
Explanation : The PL/SQL While loop runs a series of statements as long as a condition is true, so it can be used in a series of statements.
LOOP statements; WHILE <condition> END LOOP;
WHILE <condition> LOOP statements; END LOOP;
END LOOP; WHILE <condition> LOOP statements;
Correct Answer : WHILE <condition> LOOP statements; END LOOP;
Correct Answer : For
Explanation : When you want to execute a series of statements repeatedly, you use the PL/SQL for loop.
Correct Answer : 1
Correct Answer : Continue
Explanation : PL/SQL Continue statement initiates the next iteration of a loop, skipping any code in between, by exiting the loop from the reminder in its body either conditionally or unconditionally.
Correct Answer : Oracle 11g
Explanation : Oracle 11g supports PL/SQL Continue statement.
Correct Answer : GOTO
Explanation : A GOTO statement in PL/SQL allows you to jump from this statement to a specific statement label within the same subprogram within a PL/SQL block.
Correct Answer : <<>>
Explanation : The symbol in which the label_name is encapsulated in PL/SQL GOTO statement is << >>.
Correct Answer : Header and Body
Explanation : PL/SQL Procedure consists of Header and Body.
Correct Answer : Parameters and variables are contained in the header of the procedure.
Correct Answer : 3
Explanation : There are 3 numbers of ways to pass the parameters in the procedure.
Correct Answer : Implicit
Explanation : Implicit and Explicit Cursors are the types of PL/SQL Cursor.
Explanation : There are 2 types of PL/SQL Cursors.
Correct Answer : Cursor
Explanation : An SQL cursor refers to a program that retrieves and processes one row at a time, based on the results of the SQL statement.
Correct Answer : Pointer
Explanation : In the contexts, create by Oracle, a cursor represents a Pointer.
Correct Answer : Context Areas
Explanation : Oracle creates context areas when SQL statements are processed.
Correct Answer : DROP FUNCTION
Correct Answer : Recursive Name, Recursive Call
Explanation : It is called recursive call when the subprogram calls itself and recursion is the process.
Correct Answer : Function Name
Explanation : Calling a function requires that the parameters be provided along with the function name, and if the function returns a value, that value can be stored.
Correct Answer : Main Program
Explanation : The call function returns program control to the main program after successful completion of the defined task.
Correct Answer : Standalone Function
Correct Answer : Return Statement
Explanation : PL/SQL function must contain a Return Statement.
Explanation : Function_name, [OR REPLACE], Optional Parameter List, IN are all present in the syntax of PL/SQL function.
Correct Answer : PL/SQL Procedure may or may not return the value whereas PL/SQL Function must have to return the value.
Correct Answer : IN
Explanation : IN pass parameter cannot be overwritten by procedure.
Correct Answer : IN, INOUT
Explanation : IN and INOUT pass parameters can be reference by procedure.