1Z0-144 Multiple Choice Questions

What is wrong in the following code snippet? CREATE OR REPLACE FUNCTION totalCustomers total number(2) := 0; BEGIN SELECT count(*) into total FROM customers; RETURN total; END; A. It doesn't have the RETURN clause in function declaration. B. The RETURN statement is wrong. C. Function definition should not use the IS keyword D. Nothing wrong.

A. It doesn't have the RETURN clause in the function declaration.

Which of the following is a valid reason for deciding to create a subprogram as a function rather than as a procedure? A. It must be callable from SQL statements. B. It must be stored as a named object in the data dictionary. C. It must be callable from both named and anonymous PL/SQL blocks. D. The purpose is to accept one parameter and return multiple values. E. The subprogram will require the use of both IN and OUT parameters.

A. It must be callable from SQL statements.

Which of the following code will open a cursor named cur_employee? A. OPEN cur_employee; B. OPEN CURSOR cur_employee; C. FETCH cur_employee; D. FETCH CURSOR cur_employee;

A. OPEN cur_employee;

Which of the following is not among the types of PL/SQL records? A. Table-based B. View-based C. Cursor-based records D. User-defined records

B. View-based

You need a small lookup table that will be generated in memory every time a PL/SQL procedure is invoked or a package that declares it is initialized. Which of the following is the proper composite data type to use? Choose one answer: A. Nested Table B. Bulk Collection C. Associative array D. FORALL Array E. VARRAY

C. Associative array

Which of the following procedures of DBMS_SQL supports bulk DML operations? A. BIND_VARIABLE_CHAR B. BIND_VARIABLE_RAW C. BIND_ARRAY D. BIND_VARIABLE

C. BIND_ARRAY

Which of the following is used to map an exception to an error code? A. PRAGMA B. SQLCODE C. EXCEPTION_INIT D. RAISE_APPLICATION_ERROR function

C. EXCEPTION_INIT

Consider the exception declared as − emp_exception1 EXCEPTION; Which of the following statement will correctly call the exception in a PL/SQL block? A. IF c_id <= 0 THEN ex_invalid_id; B. IF c_id <= 0 THEN CALL ex_invalid_id; C. IF c_id <= 0 THEN RAISE ex_invalid_id; D. IF c_id <= 0 THEN EXCEPTION ex_invalid_id;

C. IF c_id <= 0 THEN RAISE ex_invalid_id;

Which of the following is not true about PL/SQL package specifications? A. The specification is the interface to the package. B. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. C. It contains all information about the content of the package and the code for the subprograms. D. None of the above.

C. It contains all information about the content of the package and the code for the subprograms.

Which of the following is true about the PL/SQL data structure VARRAY? A. It also has a maximum size that cannot be changed. B. A VARRAY type is created with the CREATE VARRAY statement, at the schema level. C. Maximum size of a VARRAY can be changed using the ALTER TYPE statement. D. Maximum size of a VARRAY can be changed using the ALTER VARRAY statement.

C. Maximum size of a VARRAY can be changed using the ALTER TYPE statement.

What value will be assigned to the variable declared as - counter binary_integer; A. 0 B. 1 C. NULL D. None of the above.

C. NULL

The pre-defined exception TOO_MANY_ROWS is raised when A. PL/SQL ran out of memory or memory was corrupted. B. A cursor fetches value in a variable having incompatible data type. C. SELECT INTO statement returns more than one row. D. None of the above.

C. SELECT INTO statement returns more than one row.

What is wrong in the following code snippet? 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; A. There is nothing wrong. B. The IF statement is not required. C. There should be an END LOOP statement. D. The exit statement should be in capital letters.

C. There should be an END LOOP statement.

All are lexical units except which of the following? A. Comments B. Literals C. Variables D. Identifiers E. Delimiters

C. Variables

What is the output of the following code? ------------------------------------------------------------- DECLARE grade char(1) := 'B'; BEGIN case when grade = 'A' then dbms_output.put_line('Excellent'); when grade = 'B' then dbms_output.put_line('Very good'); when grade = 'C' then dbms_output.put_line('Well done'); when grade = 'D' then dbms_output.put_line('You passed'); when grade = 'F' then dbms_output.put_line('Better try again'); else dbms_output.put_line('No such grade'); end case; END; ------------------------------------------------------------- A. It has syntax error, so there will not be any output. B. B C. Very good D. No such grade

C. Very good

What will be the output of the following code snippet? DECLARE a number(3) := 100; b number(3) := 200; BEGIN IF( a = 100 ) THEN IF( b <> 200 ) THEN dbms_output.put_line(b); END IF; END IF; dbms_output.put_line(a); END; A. It has syntax error, so there will not be any output. B. 200 C. 200 100 D. 100

D. 100

All of the following can be the causes of raising an exception except for which one? A. A throw statement has executed. B. An asynchronous exception has occurred. C. An abnormal execution condition has been synchronously detected. D. A catch statement has executed.

D. A catch statement has executed.

A subprogram can be created − A. At schema level. B. Inside a package. C. Inside a PL/SQL block. D. All of the above.

D. A subprogram can be created at the schema level, inside a package, or inside a PL/SQL block.

Which of the following is true about scalar data types in PL/SQL? A. They hold single values with no internal components. B. Examples of scalar data types are NUMBER, DATE, or BOOLEAN. C. PL/SQL provides subtypes of data types. D. All are true.

D. All are true.

Which of the following statements is used to insert, update, or delete bulk data? A. FOR Loop statement B. BULK COLLECT INTO statement C. BULK statement D. FORALL statement

D. FORALL statement

What are the two subtypes of the IF conditional statement in PL/SQL? A. If-then-endif and if-then-else B. If-then-else and if-then-elseif C. If-then-else and if-then-elseif-endif D. If-then-else and if-then-elseif-then-else

D. If-then-else and if-then-elseif-then-else

In which of the following can a subprogram NOT be created? A. Inside a package B. Inside a PL/SQL block C. At the schema level D. Inside a trigger

D. Inside a trigger

Which of the following is true about the execution section of a PL/SQL block? A. It is enclosed between the keywords BEGIN and END. B. It is a mandatory section. C. It consists of the executable PL/SQL statements. D. All of the above.

D. It is a mandatory section of executable PL/SQL statements between the BEGIN and END keywords

You want to create a trigger that fires whenever rows are deleted from the customer table and that displays the number of rows remaining in the table. Which two statements are correct about the trigger to be created for the above requirement? (Choose two.) A. It can be a before or an after trigger. B. It should be a before trigger. C. It should be a statement-level trigger. D. It should be a row-level trigger. E. It should be an after trigger.

D. It should be a row-level trigger. E. It should be an after trigger.

Which of the following is not true about PL/SQL loop structures? A. In the basic loop structure, sequence of statements is enclosed between the LOOP and END LOOP statements. B. The WHILE loop repeats a statement or group of statements while a given condition is true. C. The FOR loop executes a sequence of statements multiple times and abbreviates the code that manages the loop variable. D. Nesting of loops is not allowed.

D. Nesting of loops is allowed.

Which of the following events CANNOT cause a trigger to fire? A. DELETE B. INSERT C. UPDATE D. DROP E. SELECT F. CREATE

E. SELECT

Which of the following exceptions is raised when a user tries to divide a number by zero? A. VALUE_ERROR B. OUT_OF_RANGE C. ZERO_DIVIDE D. SELF_IS_NULL

C. ZERO_DIVIDE

Which of the following syntax will be used to access a package element? A. package_name element_name; B. element_name.package_name; C. package_name.element_name; D. None of the above.

C. package_name.element_name;

Observe the syntax given below − CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition) DECLARE Declaration-statements BEGIN Executable-statements EXCEPTION Exception-handling-statements END; Which of the following holds true for the [REFERENCING OLD AS o NEW AS n] clause? A. This allows you to refer new and old values for various DML statements, like INSERT, UPDATE, and DELETE. B. OLD and NEW references are not available for table level triggers. C. You can use them for record level triggers. D. All of the above.

D. All of the above.

Which of the following terms is given by developers to PL/SQL 2.x programming? A. White Art B. Blue Art C. Gray Art D. Black Art

D. Black Art

The pre-defined exception CASE_NOT_FOUND is raised when A. None of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause. B. PL/SQL has an internal problem. C. A cursor fetches value in a variable having incompatible data type. D. None of the above.

A. None of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause.

All objects placed in a package specification are called A. Public objects. B. Private objects. C. None of the above. D. Both of the above.

A. Public objects.

Which of the following identifiers can include any printable characters, including spaces? A. Quoted identifiers B. Predefined identifiers C. Valid identifiers D. Invalid identifiers

A. Quoted identifiers

Which of the following code correctly create a record named book with two field title and author? A. TYPE book IS RECORD (title varchar(50), author varchar(50), ); B. RECORD book (title varchar(50), author varchar(50), ); C. CREATE RECORD book (title varchar(50), author varchar(50), ); D. CREATE TYPE book (title varchar(50), author varchar(50), );

A. TYPE book IS RECORD (title varchar(50), author varchar(50), );

Which of the following is not a PL/SQL unit? A. Table B. Type C. Trigger D. Package

A. Table A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library

Which of the following is true for querying a table in the same trigger? A. The AFTER keyword should be used, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state. B. The BEFORE keyword should be used, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state. C. None of the points in a. and b. D. Both of the points in a. and b.

A. The AFTER keyword should be used, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state.

Consider the following code snippet: how many times the loop will run? DECLARE a number(2) := 9; BEGIN WHILE a < 30 LOOP a := a + 3; END LOOP; END; A. 10 B. 8 C. 7 D. 9

C. 7

What would be the output of the following code? DECLARE a number; b number; c number; FUNCTION fx(x IN number, y IN number) RETURN number IS z number; BEGIN IF x > 2*y THEN z:= x; ELSE z:= 2*y; END IF; RETURN z; END; BEGIN a:= 23; b:= 47; c := fx(a, b); dbms_output.put_line(c); END; A. 46 B. 47 C. 94 D. 23

C. 94

Observe the syntax given below − CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition) DECLARE Declaration-statements BEGIN Executable-statements EXCEPTION Exception-handling-statements END; The optional [FOR EACH ROW] clause specifies A. A table with index. B. A table with primary key. C. A row level trigger. D. A table with a unique key.

C. A row level trigger.

Which of the following is not a benefit of a database trigger? A. Enforcing referential integrity B. Event logging and storing information on table access C. Allowing invalid transactions D. Generating some derived column values automatically

C. Allowing invalid transactions

Which of the following is not true about the PL/SQL functions? A. A PL/SQL function is same as a procedure except that it returns a value. B. The function body must contain a RETURN statement. C. The RETURN clause does not specify the data type of the return value. D. The AS keyword is used instead of the IS keyword for creating a standalone function.

C. The RETURN clause does specify the data type of the return value.

Which of the following is true about the following code snippet? ---------------------------------------------------------------------- DECLARE a number(3) := 100; BEGIN IF (a = 50 ) THEN dbms_output.put_line('Value of a is 10' ); ELSIF ( a = 75 ) dbms_output.put_line('Value of a is 20' ); ELSE dbms_output.put_line('None of the values is matching'); END IF; dbms_output.put_line('Exact value of a is: '|| a ); END; ---------------------------------------------------------------------- A. It has syntax error. B. It will print 'None of the values is matching'. C. It will print None of the values is matching Exact value of a is: 100 D. None of the above.

A. Needs a THEN statement after ELSIF

Which of the following is true about PL/SQL package body? A. The package body has the codes for various methods declared in the package specification and other private declarations. B. It is created using the CREATE PACKAGE Statement. C. The codes, methods and types declared in package body are not hidden from code outside the package. D. All of the above.

A. The package body has the codes for various methods declared in the package specification and other private declarations.

What would be printed when the following code is executed? ------------------------------------------------------------- DECLARE x NUMBER; BEGIN x := 5; x := 10; dbms_output.put_line(-x); dbms_output.put_line(+x); x := -10; dbms_output.put_line(-x); dbms_output.put_line(+x); END; ------------------------------------------------------------- A. -10, 10, 10, -10 B. 10, -10, 10, -10 C. -10, +10, +10, -10 D. 10, -10, -10, 10

A. -10, 10, 10, -10

Consider the following code snippet: how many times the loop will run? DECLARE a number(2); BEGIN FOR a in 10 .. 20 LOOP END LOOP; END; A. 11 B. 10 C. 9 D. Infinite loop.

A. 11

What will be printed by the following PL/SQL block? DECLARE a number; b number; c number; PROCEDURE findMin(x IN number, y IN number, z OUT number) IS BEGIN IF x < y THEN z:= x; ELSE z:= y; END IF; END; BEGIN a:= 2; b:= 5; findMin(a, b, c); dbms_output.put_line(c); END; A. 2 B. 5 C. 0 D. Won't print anything

A. 2

What is the maximum size of a trigger? A. 32KB B. 64KB C. 16KB D. 8KB

A. 32KB

What is the output of the following code? DECLARE x number := 4; BEGIN LOOP dbms_output.put_line(x); x := x + 1; exit WHEN x > 5; END LOOP; dbms_output.put_line(x); END; A. 4 5 6 B. 4 5 C. 4 D. None of the above.

A. 4, 5, 6,

Which of the following is not true about PL/SQL cursors? A. A cursor is a view on a table. B. A cursor holds the rows (one or more) returned by a SQL statement. C. The set of rows the cursor holds is referred to as the active set. D. None of the above.

A. A cursor is not a view on a table

From which of the following programming languages are the features of PL/SQL programming fetched? A. Ada programming B. C programming C. C++ programming D. Java programming

A. Ada programming

Which of the following is the correct syntax for creating an explicit cursor? A. CURSOR cursor_name IS select_statement; B. CREATE CURSOR cursor_name IS select_statement; C. CREATE CURSOR cursor_name AS select_statement; D. CURSOR cursor_name AS select_statement;

A. CURSOR cursor_name IS select_statement;

Which of the following built-in packages supports dynamic SQL? A. DBMS_SQL B. DBMS_SCHEDULER C. DBMS_MONITOR D. DBMS_CRYPTO

A. DBMS_SQL

Which of the following is true about the following code snippet? ------------------------------------------------------------- DECLARE a number( 3 ) := 100; BEGIN IF ( a = 50 ) THEN dbms_output.put_line( 'Value of a is 10' ); ELSEIF ( a = 75 ) THEN dbms_output.put_line( 'Value of a is 20' ); ELSE dbms_output.put_line( 'None of the values is matching' ); END IF; dbms_output.put_line( 'Exact value of a is: '|| a ); END; ------------------------------------------------------------- A. It has syntax error. B. It will print 'None of the values is matching'. C. It will print None of the values is matching Exact value of a is: 100 D. None of the above.

A. ELSEIF should be ELSIF

Which of the following is not true about the execution section of a PL/SQL block? A. It should have more than one executable line of code. B. It may have just a NULL command to indicate that nothing should be executed. C. The statements must always end with a semicolon. D. The section may contain SQL commands, logical control commands, assignment commands, as well as other commands.

A. Execution section of PL/SQL block needs at least 1 executable line of code

In which of the following places is the record variable NOT allowed? A. In a SELECT list. B. In the VALUES clause of an INSERT statement. C. On the right side of the SET clause in an UPDATE statement. D. In the INTO subclause of a RETURNING clause.

A. In a SELECT list.

Which of the following is true about character data types and subtypes in PL/SQL? A. LONG is a variable-length character string with maximum size of 32,760 bytes. B. ROWID is a physical column identifier, the address of a column in an ordinary table. C. CHAR is a variable-length character string with maximum size of 32,767 bytes. D. NCHAR is a variable-length national character string with maximum size of 32,767 bytes.

A. LONG is a variable-length character data of variable length up to 2 gigabytes, or 2^31 -1 bytes

Observe the syntax given below − CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition) DECLARE Declaration-statements BEGIN Executable-statements EXCEPTION Exception-handling-statements END; Which of the following holds true for the WHEN clause? A. This provides a condition for rows for which the trigger would fire and this clause is valid only for row level triggers. B. This provides a condition for rows for which the trigger would fire and this clause is valid only for table level triggers. C. This provides a condition for rows for which the trigger would fire and this clause is valid only for view based triggers. D. All of the above.

A. This provides a condition for rows for which the trigger would fire and this clause is valid only for row level triggers.

Observe the syntax given below − CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition) DECLARE Declaration-statements BEGIN Executable-statements EXCEPTION Exception-handling-statements END; The INSTEAD OF clause is used for creating trigger on a − A. View B. Cursor C. Table D. Index

A. View

Consider a variable named greetings declared as − greetings varchar2(11) := 'Hello World'; What will be the output of the code snippet dbms_output.put_line ( SUBSTR (greetings, 7, 5)); A. World B. Hello C. orld D. None of the above.

A. World

To get the server output result and display it into the screen, you need to write − A. set serveroutput on B. set server output on C. set dbmsoutput on D. set dbms output on

A. set serveroutput on

Consider the following code snippet: what will be the output? DECLARE a number(2) ; BEGIN FOR a IN REVERSE 10 .. 20 LOOP END LOOP; dbms_output.put_line(a); END; A. 11 B. 10 C. 29 D. 30

B. 10

Which of the following is not true about large object data types and in PL/SQL? A. BFILE is used to store large binary objects in operating system files outside the database. B. BLOB is used to store character data in the database. C. CLOB is used to store large blocks of character data in the database. D. NCLOB is used to store large blocks of NCHAR data in the database.

B. A BLOB (binary large object) does not store character data. It is a varying-length binary string that can be up to 2,147,483,647 digits long.

The pre-defined exception NO_DATA_FOUND is raised when A. A null object is automatically assigned a value. B. A SELECT INTO statement returns no rows. C. PL/SQL has an internal problem. D. PL/SQL ran out of memory or memory was corrupted.

B. A SELECT INTO statement returns no rows.

Which of the following statement will create the specification for a package named cust_sal A. CREATE PACKAGE BODY cust_sal AS PROCEDURE find_sal(c_id customers.id%type); END cust_sal; B. CREATE PACKAGE cust_sal AS PROCEDURE find_sal(c_id customers.id%type); END cust_sal; C. CREATE PACKAGE SPECIFICATION cust_sal AS PROCEDURE find_sal(c_id customers.id%type); END cust_sal; D. PACKAGE cust_sal AS PROCEDURE find_sal(c_id customers.id%type); END cust_sal;

B. CREATE PACKAGE cust_sal AS PROCEDURE find_sal(c_id customers.id%type); END cust_sal;

Observe the syntax given below − CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition) DECLARE Declaration-statements BEGIN Executable-statements EXCEPTION Exception-handling-statements END; The {INSERT [OR] | UPDATE [OR] | DELETE} clause specifies a A. DDL operation. B. DML operation. C. None of the above. D. Both of the above.

B. DML operation.

Which of the following is true about the following PL/SQL CASE statement syntax? ------------------------------------------------------------- CASE selector WHEN 'value1' THEN S1; WHEN 'value2' THEN S2; WHEN 'value3' THEN S3; ... ELSE Sn; -- default case END CASE; ------------------------------------------------------------- A. It is wrongly written. B. It is perfectly written. C. It is you can specify the literal NULL for all the S expressions and the default Sn. D. All the expressions like the selector, the value and the returns values, need not be of the same data type.

B. It is perfectly written.

Consider the following code ---------------------------------------------------------------------- DECLARE -- Global variables num number := 95; BEGIN dbms_output.put_line('num: ' || num1); DECLARE -- Local variables num number := 195; BEGIN dbms_output.put_line('num: ' || num1); END; END; ---------------------------------------------------------------------- What will happen when the code is executed? A. It won't execute, it has syntax error B. It will print num: 95 num: 195 C. It will print num: 95 num: 95 D. It will print num: 195 num: 195

B. It will print num: 95 num: 195

Oracle supports all of the following types of collections except for which one? A. VARRAYS B. Nested array C. Nested table D. Associative array

B. Nested array

Any subprogram not in the package specification but coded in the package body is called a A. Public object. B. Private object. C. None of the above. D. Both of the above.

B. Private object.

What are the two variables supported by PL/SQL? A. Explicit and implicit variables B. Scalar and composite variables C. Primary and default variables D. Scalar and user-defined variables

B. Scalar and composite variables

Which of the following is not true about PL/SQL constants and literals? A. A constant holds a value that once declared, does not change in the program. B. The CONSTANT declaration cannot impose the NOT NULL constraint. C. A constant is declared using the CONSTANT keyword. D. A CONSTANT declaration requires an initial value.

B. The CONSTANT declaration can impose the NOT NULL constraint.

The following code tries to fetch some information from all the rows in a table named customers for use in a PL/SQL block. What is wrong in the following code? DECLARE c_id customers.id%type; c_name customers.name%type; c_addr customers.address%type; CURSOR c_customers is SELECT id, name, address FROM customers; BEGIN LOOP FETCH c_customers into c_id, c_name, c_addr; EXIT WHEN c_customers%notfound; dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); END LOOP; CLOSE c_customers; END; A. It need not use a cursor. B. The cursor is not opened. C. It will not print information from all the rows. D. There is nothing wrong in the code.

B. The cursor is not opened.

Which of the following is not true about the declaration section of a PL/SQL block? A. This section starts with the DECLARE keyword. B. It is a mandatory section. C. It defines all variables, cursors, subprograms, and other elements to be used in the program. D. None of the above.

B. The declaration section is not a mandatory section.

Which of the following is not true about the PL/SQL data structure VARRAY? A. It is a fixed-size sequential collection of elements. B. The elements can of various data types. C. It is used to store an ordered collection of data. D. Each element in a VARRAY has an index associated with it.

B. The elements can of various data types.

Which of the following is not true about the exception handling section of a PL/SQL block? A. This section starts with the EXCEPTION keyword. B. It is a mandatory section. C. It contains exception(s) that handle errors in the program. D. None of the above.

B. The exception section is not a mandatory section.

Which of the following is not true about labeling PL/SQL loops? A. PL/SQL loops can be labelled. B. The label should be enclosed by angle brackets (< and >). C. The label name appears at the beginning of the LOOP statement. D. The label name can also appear at the end of the LOOP statement or with an EXIT statement.

B. The label should be enclosed by angle brackets (< and >).

Which of the following statements about anonymous PL/SQL blocks is true? A. They are compiled only the first time the block is executed in a session. B. They can be embedded in an OCI program. C. They can only make use of bind variables. D. They can be created as either a function or a procedure. E. They can be called from other PL/SQL blocks.

B. They can be embedded in an OCI program.

Which of the following types of message output contains a chronological log of errors, initialization parameter settings, and administration operations, and also records values for overwritten control file records? A. RMAN messages B. alert_SID.log C. sbtio.log D. Oracle trace file

B. alert_SID.log

Which of the following code will successfully declare an exception named emp_exception1 in a PL/SQL block? A. EXCEPTION emp_exception1; B. emp_exception1 EXCEPTION; C. CREATE EXCEPTION emp_exception1; D. CREATE emp_exception1 AS EXCEPTION;

B. emp_exception1 EXCEPTION;

Observe the following code and fill in the blanks − 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; A. %notfound, %found, %rowcount. B. sql%notfound, sql%found, sql%rowcount. C. sql%found, sql%notfound, sql%rowcount. D. %found, %notfound, %rowcount.

B. sql%notfound, sql%found, sql%rowcount.

What will be the output of the following code snippet? ------------------------------------------------------------- DECLARE a number (2) := 21; b number (2) := 10; BEGIN IF ( a <= b ) THEN dbms_output.put_line(a); END IF; IF ( b >= a ) THEN dbms_output.put_line(a); END IF; IF ( a <> b ) THEN dbms_output.put_line(b); END IF; END; ------------------------------------------------------------- A. 2 B. 21 C. 10 D. 21, 10

C. 10 The <> operator is the not equals operator and 21 is not equal to 10.

What will be printed by the following PL/SQL block? DECLARE a number; PROCEDURE squareNum(x IN OUT number) IS BEGIN x := x * x; END; BEGIN a:= 5; squareNum(a); dbms_output.put_line(a); END; A. 5 B. 10 C. 25 D. 0

C. 25

Which of the following is true about comments in PL/SQL? A. Comments are explanatory statements. B. PL/SQL supports both single-line and multi-line comments. C. The PL/SQL single-line comments start with the delimiter -- (double hyphen) and multi-line comments are enclosed by /* and */. D. All of the above.

D. All of the above.

Which of the following is true about data types in PL/SQL? A. Large Object or LOB data types are pointers to large objects that are stored separately from other data items, such as text, graphic images, video clips, and sound waveforms. B. The composite data types have data items that have internal components that can be accessed individually. For example, collections and records. C. References are pointers to other data items. D. All of the above.

D. All of the above.

Which of the following is true about the parameter modes in PL/SQL Subprograms? A. An IN parameter lets you pass a value to the subprogram. It is a read-only parameter. B. An OUT parameter returns a value to the calling program. C. An IN OUT parameter passes an initial value to a subprogram and returns an updated value to the caller. D. All of the above.

D. All of the above.

Which of the following is not true about the PL/SQL language? A. It supports embedded SQL statements. B. It has all the features of a modern structured programming language. C. It is a block-structured language. D. Applications developed using PL/SQL are not portable.

D. Applications developed using PL/SQL are not portable.

Which of the following is not true about PL/SQL records? A. A PL/SQL record is a data structure that can hold data items of different kinds. B. Records consist of different fields, similar to a row of a database table. C. You can create table-based and cursor-based records by using the %ROWTYPE attribute. D. None of the above.

D. None of the above.

Which of the following is not true about PL/SQL triggers? A. Triggers are stored programs. B. They are automatically executed or fired when some events occur. C. Triggers could be defined on the table, view, schema, or database with which the event is associated. D. None of the above.

D. None of the above.

Which of the following is not true about the PL/SQL data structure VARRAY? A. In oracle environment, the starting index for VARRAYs is always 1. B. You can initialize the VARRAY elements using the constructor method of the VARRAY type, which has the same name as the VARRAY. C. VARRAYs are one-dimensional arrays. D. None of the above.

D. None of the above.

Which of the following is not true about PL/SQL packages? A. PL/SQL packages are schema objects that groups logically related PL/SQL types, variables and subprograms. B. A package has two parts: Package specification and Package body or definition. C. Both the parts are mandatory. D. None of the above.

D. None of the above. They are all true

Which of the following is not true about PL/SQL decision making structures? A. The IF statement associates a condition with a sequence of statements enclosed by the keywords THEN and END IF. B. The IF statement also adds the keyword ELSE followed by an alternative sequence of statement. C. The IF-THEN-ELSIF statement allows you to choose between several alternatives. D. PL/SQL does not have a CASE statement.

D. PL/SQL does have a CASE statement.

Which of the following is true about the PL/SQL language? A. PL/SQL provides access to predefined SQL packages. B. PL/SQL provides support for Object-Oriented Programming. C. PL/SQL provides support for Developing Web Applications and Server Pages. D. All of the above.

D. PL/SQL provides access to predefined SQL packages, support for Object-Oriented Programming and Developing Web Applications and Server Pages.

Which of the following is not true about the PL/SQL language? A. PL/SQL's general syntax is based on that of ADA and Pascal programming language. B. Apart from Oracle, PL/SQL is available in TimesTen in-memory database and IBM DB2. C. PL/SQL is tightly integrated with SQL. D. It does not offer error checking.

D. PLSQL does offer error checking.

Which of the following is a way of passing parameters to PL/SQL subprograms? A. Positional notation B. Named notation C. Mixed notation D. All of the above.

D. Positional Notation, Named Notation, Mixed Notation

Which of the following is the correct syntax for creating a VARRAY named grades, which can hold 100 integers, in a PL/SQL block? A. TYPE grades IS VARRAY(100) OF INTEGERS; B. VARRAY grades IS VARRAY(100) OF INTEGER; C. TYPE grades VARRAY(100) OF INTEGER; D. TYPE grades IS VARRAY(100) OF INTEGER;

D. TYPE grades IS VARRAY(100) OF INTEGER;

What is wrong in the following code? ---------------------------------------------------------------------- DECLARE c_id := 1; c_name customers.name%type; c_addr customers.address%type; BEGIN SELECT name, address INTO c_name, c_addr FROM customers WHERE id = c_id; END; ---------------------------------------------------------------------- A. You cannot use the SELECT INTO statement of SQL to assign values to PL/SQL variables. B. The SELECT INTO statement here is wrong. It should be: SELECT c_name, c_address INTO name, addr C. The WHERE statement is wrong. It should be: WHERE id := c_id; D. The variable c_id should be declared as a type-compatible variable as − c_id customers.id%type := 1;

D. The variable c_id should be declared as a type-compatible variable as − c_id customers.id%type := 1;

Triggers are written to be executed in response to any of the following events − A. A database manipulation (DML) statement (DELETE, INSERT, or UPDATE). B. A database definition (DDL) statement (CREATE, ALTER, or DROP). C. A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN). D. All of the above.

D. Triggers are written to be executed in response to a database manipulation, definition, or operation statement.

Which of the following is not one of the benefits of using PL/SQL? A. Ability to declare variables and constants. B. Support for Object-Oriented Programming. C. Capability to develop web applications. D. Mostly OS-independent. E. Tight Integration with SQL.

D. You can run PL/SQL applications on any operating system and platform where Oracle Database runs.

What would be the output of the following code? DECLARE num number; fn number; FUNCTION fx(x number) RETURN number IS f number; BEGIN IF x=0 THEN f := 1; ELSE f := x * fx(x-1); END IF; RETURN f; END; BEGIN num:= 5; fn := fx(num); dbms_output.put_line(fn); END; A. 1 B. 5 C. 10 D. 125

E. 120

Which of the following is a valid implicit cursor attribute? A. SQL%OPEN B. SQL%NODATAFOUND C. SQL%ROWTYPE D. SQL%GROUP_COUNT E. SQL%ROWCOUNT

E. SQL%ROWCOUNT Other Implicit Cursor Attributes %ISOPEN, %FOUND, %NOTFOUND,