PL/SQL Oracle 1z0-144: Practice Exam

QUESTION NO: 100 Which of the following is used to refer to the conditional compilation flags?

A. $$identifier syntax B. $$operator syntax C. $IF directive D. $ERROR directive Answer: A

QUESTION NO: 187 Which of the following cursor attributes is invalid?

A. %FOUND B. %ROWCOUNT C. %NOTOPEN D. %ISOPEN Answer: C

QUESTION NO: 148 Which of the following is NOT an advantage of a cursor variable?

A. Ensures data availability B. Convenient security C. Ease of maintenance D. Encapsulation Answer: A

QUESTION NO: 87 Which of the following directives is NOT used in conditional compilation?

A. Error directive B. Inquiry directive C. Update directive D. Selection directive Answer: C

QUESTION NO: 217 In which of the following sections of a PL/SQL blocks can a variable be defined?

A. Error handling section B. Exception section C. Execution section D. Declaration section Answer: D

QUESTION NO: 99 In which of the following can a subprogram NOT be created?

A. Inside a PL/SQL block B. Inside a trigger C. Inside a package D. At the schema level Answer: B

QUESTION NO: 45 Which of the following is a pattern matching operator in PL/SQL?

A. LIKE B. ISNULL C. IN D. BETWEEN Answer: A

QUESTION NO: 239 Which of the following is a valid identifier?

A. USER-ID B. user-id C. user_id D. user id Answer: C

QUESTION NO: 55 Which of the following correctly represents the name of a variable?

A. _a B. 6a C. a$ D. a $ Answer: C

QUESTION NO: 121 HOTSPOT You have made some changes to the database, but they are not permanent. Now you want to save all those changes in the database. Which tool will you click?

Answer: Explanation:

QUESTION NO: 129 DRAG DROP You want to define a subtype and give it a name of your choice. This is known as used-defined subtype. User-defined subtypes are defined using the keyword SUBTYPE. Below is given an incomplete syntax of defining a subtype. Drag and drop the given keywords and identifiers in the appropriate place holders to complete the syntax.

Answer: Explanation:

QUESTION NO: 240 Martha works as an Application Developer for Z-Tech Inc. The company uses an Oracle database. The database contains a table named Orders. Martha wants to create a database trigger on the Orders table. She wants the trigger to fire after an UPDATE operation is performed on the Orders table. She also wants the trigger to fire for each row updated in the Orders table. Which of the following options will she use when creating the trigger? Each correct answer represents a part of the solution. Choose two.

A. AFTER UPDATE B. BEFORE UPDATE C. FOR EACH ROW D. ROW Answer: A,C

QUESTION NO: 134 Which of the following exceptions can be raised using the RAISE statement?

A. All types of exceptions can be raised B. Pre-defined exceptions C. User-defined exceptions D. Named exceptions Answer: A

QUESTION NO: 236 Which of the following are NOT true about the scope rules for PL/SQL exceptions? Each correct answer represents a complete solution. Choose all that apply.

A. An exception can be declared more than once in a single block. B. The scope of an exception declared within a block is global for all of its sub-blocks. C. The scope of an exception declared within a block is local for the block. D. When a global exception is declared again within a sub-block, the global declaration takes priority over the local declaration. E. The parent block can also refer to the exceptions declared in its sub-blocks. Answer: A,D,E

QUESTION NO: 189 Which of the following is NOT a database trigger?

A. BEFORE trigger B. FORM trigger C. AFTER trigger D. INSTEAD OF trigger Answer: B

QUESTION NO: 138 Which of the following CANNOT be compared with the BFILE? Each correct answer represents a complete solution. Choose all that apply.

A. CLOB B. BLOB C. NCLOB D. BFILE Answer: A,B,C

QUESTION NO: 216 You work as a Database Administrator for uCertify Inc. The company uses Oracle 11g database. You want to create indexes for a set of tables in the marketing department. A table named SALEScontains the sale_id and the cust_name columns. Your manager asks you to create an index on the mentioned columns. In order to accomplish the task, you want to estimate how much space the proposed index would need. Which of the following packages will you use to estimate the required space?

A. DBMS_SPACE B. DBMS_REPAIR C. DBMS_SQL D. DBMS_STATS Answer: A

QUESTION NO: 155 Consider the exhibit below: The given diagram shows the hierarchy of the SCALAR datatype, which is a predefined datatype in PL/SQL. NUMERIC is the subtype of SCALAR and the base type for the NUMBER datatype. The NUMBER datatype is further branched into various subtypes that hold the integer values. Which of the following integer subtypes are held by the NUMBER datatype? Each correct answer represents a complete solution. Choose all that apply.

A. INTEGER B. INT C. SIMPLEJNTEGER D. SMALLINT Answer: A,B,D

QUESTION NO: 173 Which of the following programming constructs can be grouped within a package? Each correct answer represents a complete solution. Choose all that apply.

A. Sequence B. Constant C. Cursor D. Trigger Answer: B,C

QUESTION NO: 93 Which of the following components of a DML trigger determines how many times the trigger body executes?

A. Trigger action B. Triggering event C. Trigger timing D. Trigger restriction E. Trigger type Answer: E

QUESTION NO: 194 Which of the following Oracle supplied packages provides the capability to perform I/O operations on a text file?

A. UTL_HTTP B. D C. DBMS_JOB D. UTL_FILE Answer: D

QUESTION NO: 201 Which of the following PL/SQL packages is used to support the SQL plan management feature?

A. UTL_MAIL B. Utl_Com press C. DBMS_Warning D. DBMS_SPM Answer: D

QUESTION NO: 249 You want to send an email from inside the PL/SQL code. You use the UTL_MAIL.SEND procedure for this. Which of the following is the valid syntax that you will insert into the PL/SQL code to accomplish the task?

A. UTL_MAIL.SEND (subject, receiver, carbon_copy, blind_carbon_copy, subject, message.sender, mime_type, priority) B. UTL_MAIL.SEND (sender, receiver, carbon_copy, blind_carbon_copy, mime_type, subject, message, priority) C. UTL_MAIL.SEND (sender, receiver, carbon_copy, blind_carbon_copy, subject, message, mime_type, priority) D. UTL_MAIL.SEND (subject, sender, receiver, carbon_copy, blind_carbon_copy, subject, message, mime_type, priority) Answer: C

QUESTION NO: 7 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 Answer: C

QUESTION NO: 151 Under which of the following conditions is the use of an explicit cursor necessary?

A. When a query returns more than one row B. When a query does not return any row C. When a query returns only one row D. When any SQL data manipulation language (DML) statement is used Answer: A

QUESTION NO: 132 Martin writes a PL/SQL WHILE...LOOP to display values from a database. In which of the following events will the loop be terminated?

A. When the condition associated with the loop evaluates to TRUE. B. When the condition associated with the loop evaluates to NULL. C. When the condition associated with the loop evaluates to FALSE. D. When a specified number of iterations are performed. E. When the execution control is passed to the EXIT statement. Answer: C

You work as an Application Developer for Gentech Inc. The company uses an Oracle database. The database contains a table named Employees. You are required to create a procedure named Add_Emp that will be used to insert new records in the Employees table. What precautions will you take while creating the procedure in order to minimize the dependency failure in case the Employees table undergoes any change?

Each correct answer represents a complete solution. Choose all that apply. A. Declare variables by using the %TYPE attribute. B. Include a column list with the INSERT statement. C. Specify schema names when referencing objects in the procedure. D. Use the SELECT *notation when performing queries through the procedure. Answer: A,B,D

QUESTION NO: 101 What are the components of a package?

Each correct answer represents a complete solution. Choose all that apply. A. Header B. Specification C. Declaration D. Body Answer: B,D

QUESTION NO: 103 Which of the following procedures are provided by the DBMS_OUTPUT package?

Each correct answer represents a complete solution. Choose all that apply. A. SLEEP B. NEW_LINE C. APPEND D. DISABLE E. ENABLE Answer: B,D,E

QUESTION NO: 225 You work as an Application Developer for Tech-Mart Inc. The company uses an Oracle database. The database contains a function named Calc_Sal. You want to make some changes in the executable part of the function. What will you do to accomplish this?

Each correct answer represents a complete solution. Choose two. A. Drop the function and create a new one. B. Use the MODIFY FUNCTION statement. C. Use the CREATE OR REPLACE FUNCTION statement. D. Use the ALTER FUNCTION statement. Answer: A,C

QUESTION NO: 150 What are the two kinds of numeric literals?

Each correct answer represents a complete solution. Choose two. A. Real B. Numeric C. Binary D. Integer Answer: A,D

QUESTION NO: 231 Which of the following statements about the INSTEAD OF clause of a trigger are true?

Each correct answer represents a complete solution. Choose two. A. The INSTEAD OF clause identifies the table associated with the trigger. B. The INSTEAD OF clause specifies that the trigger is a statement trigger. C. The INSTEAD OF clause identifies the event associated with the trigger. D. The INSTEAD OF clause identifies the view associated with the trigger. Answer: C,D

QUESTION NO: 108 You work as an Application Developer for Tech-Mart Inc. The company uses an Oracle database. The database contains a table named Employees. You want to create a database trigger on the Employees table. You execute the CREATE TRIGGER statement to accomplish the task. During the compilation of the trigger, an error occurs. You want to see the error. What will you do to accomplish this?

Each correct answer represents a complete solution. Choose two. A. Use the SHOW ERRORS statement in SQL*Plus or Enterprise Manager. B. Query the USER_ERRORS data dictionary view. C. Query the TRIGGER_ERRORS data dictionary view. D. Query the USER_TRIGGERS data dictionary view. Answer: A,B

QUESTION NO: 212 Martha works as a Database Administrator for MarcoTech Inc. She writes the following PL/SQL block: How many times will the loop iterate when this PL/SQL block is executed?

A. 1 B. 8 C. 0 D. 4 Answer: A

QUESTION NO: 38 Which of the following statements is NOT true about records?

A. A SELECT or FETCH statement can be used to fetch column values into a record. B. The datatype RECORD allows users to collect information about the attributes of something. C. Records can be tested for nullity, equality, or inequality. D. Nested record types are not supported. Answer: C

QUESTION NO: 228 Sam works as a Database Administrator for SamTech Inc. He writes the following statement in the PL/SQL block: DECLARE New_Cons CONSTANT NUMBER := 8.2 What will happen when this statement is executed?

A. A constant will be declared and a value will be assigned to it. B. An index identifier will be declared and a value will be assigned to it. C. An error will occur. D. A composite variable will be declared and a value will be assigned to it. Answer: A

QUESTION NO: 230 You work as an Application Developer for Tech-Mart Inc. The company uses an Oracle database. You want to create automated, unattended scheduling of PL/SQL program units. Which of the following Oracle supplied packages will you use to accomplish this?

A. DBMS_DDL B. DBMS_JOB C. DBMS_SQL D. DBMS_OUTPUT Answer: B

QUESTION NO: 144 You work as an Application Developer for Gentech Inc. The company uses an Oracle database. You want to write a PL/SQL program by using dynamic SQL Which of the following Oracle supplied packages will you use to accomplish this?

A. DBMS_JOB B. DBMS_DDL C. DBMS_SQL D. DBMS_PIPE Answer: C

QUESTION NO: 208 Which of the following procedures of the DBMS_OUTPUT package has two overloaded forms?

A. DBMS_OUTPUT.PUT_LINE B. DBMS_OUTPUT.PUT C. DBMS_OUTPUT.GET_LINES D. DBMS_OUTPUT.GET_LINE Answer: C

QUESTION NO: 199 You have created a table named Students to store information of all the students. Earlier at the time of creation, you mentioned a column name 'student_photo' of BLOB type. By default, the BLOBs are stored in the BASICFILE LOB format, but now you want to change it to SECUREFILE LOB. Which of the following packages will you use to accomplish your task?

A. DBMS_REDEFINITION B. DBMS_REGISTRY C. DBMS_REPAIR D. DBMS_REFRESH Answer: A

QUESTION NO: 158 You work as an Application Developer for Z-Tech Inc. The company uses an Oracle database. The database contains a table named Employees. You have defined a database trigger named Raise_Salary on the Employees table. You want to remove the trigger from the database. Which of the following SQL statements will you use to accomplish this?

A. DELETE TRIGGER Raise_Salary B. DROP TRIGGER Raise_Salary C. REMOVE TRIGGER Raise_Salary D. ALTER TRIGGER Raise_Salary REMOVE Answer: B

QUESTION NO: 166 Which of the following files is used to record the physical structure of a database and to define its current state?

A. Default file B. Data file C. Redo log file D. Control file Answer: D

QUESTION NO: 80 Which of the following statements is NOT true about subprograms?

A. Dummy subprograms are known as stuts. B. Subprograms allow users to extend the PL/SQL language. C. A subprogram call has the following form: subprogram_name [ (parameter [,parameter]...)] D. Subprograms support re-usability. Answer: A

QUESTION NO: 128 Which of the following statements is true about implicit cursors?

A. Every implicit cursor has four attributes that can be used to access information about the execution of a multi-row query. B. Implicit cursors are declared implicitly for all the DML and SELECT statements. C. You can implicitly declare a cursor to process rows. D. In implicit cursors, you can use the OPEN, FETCH, and CLOSE statements. Answer: B

QUESTION NO: 226 Anonymous blocks are used when building scripts to seed data or perform one-time processing activities. In which of the following sections of the anonymous-block of PL/SQL can anonymousblock programs be nested?

A. Exception B. Declaration C. Execution D. Definition Answer: C

QUESTION NO: 16 Which of the following correctly represents floating point number literals?

A. F6.34 B. 6.34 C. 6.F34 D. 6.34F Answer: D

QUESTION NO: 156 What are the different modes of parameters that can be used in functions and procedures? Each correct answer represents a complete solution. Choose all that apply.

A. IN OUT B. OUT C. IN D. OUT IN Answer: A,B,C

QUESTION NO: 79 All of the following are PL/SQL compile-time warning categories except for which one?

A. INFORMATIONAL B. PERFORMANCE C. SEVERE D. SIMPLE Answer: D

QUESTION NO: 122 Which of the following clauses is used to specify the variables in which to store the values returned by the statement to which the clause belongs?

A. INSERT INTO B. INTO C. RETURNING INTO D. COLLECT INTO Answer: C

QUESTION NO: 237 Andrew works as an Application Developer for Z-Tech Inc. The company uses an Oracle database. Andrew creates a trigger named Log_Trigger. The trigger executes whenever a database user logs on to the database. Which of the following types of triggers has Andrew created?

A. INSTEAD OF trigger B. Application trigger C. System event trigger D. Database trigger Answer: C

QUESTION NO: 242 Which of the following subprograms of the DBMS_RESULT_CACHE package will you use when you do not want any new results to be stored in the cache?

A. INVALIDATE B. BYPASS C. INVALIDATEOBJECT D. FLUSH Answer: B

QUESTION NO: 235 In which of the following cursors are fetches and DML operations not required to be forward sequential only?

A. Implicit cursor B. Explicit cursor C. Scrollable cursor D. Recursive cursor Answer: C

QUESTION NO: 247 Which of the following are the benefits of PL/SQL? Each correct answer represents a complete solution. Choose all that apply.

A. In PL/SQL, the statements of the block are sent line by line, one at a time to the Oracle ser ver. B. PL/SQL is a high-performance transaction-processing language. C. PL/SQL supports all SQL datatypes and all SQL functions. D. PL/SQL blocks can be named and stored in the Oracle server and reused as required in another PL/SQL program. Answer: B,C,D

QUESTION NO: 218 What are the different ways in which PL/SQL can run? Each correct answer represents a complete solution. Choose all that apply.

A. Interpreted execution B. Native execution C. Default execution D. Integrated execution Answer: A,B

QUESTION NO: 105 Which of the following literals are used to specify the period of time?

A. Interval literals B. Numeric literals C. Integer literals D. Character literals Answer: A

QUESTION NO: 169 Which of the following statements are true about a strong REF CURSOR variable? Each correct answer represents a complete solution. Choose all that apply.

A. It can relate to any query. B. It is less susceptible to errors. C. It is more flexible as compared to a weak REF CURSOR variable. D. It has a return type. E. It is also known as restrictive REF CURSOR variable. Answer: B,D,E

QUESTION NO: 143 Which of the following statements is true about the pragma EXCEPTIONJNIT?

A. It declares an exception. B. It associates an exception name with an Oracle error code. C. It creates an exception. D. It removes an exception. Answer: B

QUESTION NO: 137 You work as an Application Developer for Gentech Inc. The company uses an Oracle database. The database contains a table named Employees. You have created a database view named V_Emp based on the Employees table. You want to create a trigger on the V_Emp view. In order to accomplish this, you execute the following statement: CREATE OR REPLACE TRIGGER Emp_Trig INSTEAD OF INSERT ON V_Emp FOR EACH ROW BEGIN INSERT INTO Employees VALUES (:NEW.employee_id, :NEW.Iast_name, :NEW.salary, :NEW.department_id, :NEW.email, :New.jol>_id, SYSDATE) END Which of the following statements regarding the function of the Emp_Trig trigger is true?

A. It inserts new records in the V_Emp view when an INSERT operation is performed on the V_Emp view. B. It inserts new records in the Employees table when an INSERT operation is performed on the V_Emp view. C. It fails and returns an error when an INSERT operation is performed on the V_Emp view. D. It updates the records in the Employees table with the new records inserted in the V_Emp view. Answer: B

QUESTION NO: 111 You work as an Application Developer for Gentech Inc. The company uses an Oracle database. The database contains a table named Audit_Records. You are required to create a triggers the database level to ensure that whenever a database user logs on to the database, his name and current date are inserted into the Audit_Records table. In order to accomplish this, you execute the following statement: CREATE OR REPLACE TRIGGER Sys_Trig BEFORE LOGON ON DATABASE BEGIN INSERT INTO Audit_Records (User_Name, Date) VALUES (USER, SYSDATE) END Which of the following statements about the Sys_Trig trigger is true?

A. It is an application trigger. B. It is a DDL trigger. C. It is an invalid trigger. D. It is a system event trigger. Answer: C

QUESTION NO: 191 Which of the following statements are TRUE about the EXTEND method? Each correct answer represents a complete solution. Choose all that apply.

A. It is used to increase the size of the index-by tables. B. It is used to increase the size of the nested tables. C. It cannot append elements to a collection that is not initialized. D. It can append only a single null element to the collection. E. It can append multiple copies of a particular element. Answer: B,C,E

QUESTION NO: 221 Which of the following statements is true about the cursor FOR loop?

A. Parameters cannot be passed to a cursor in a cursor FOR loop. B. A cursor does not need to explicitly use the OPEN, FETCH, and CLOSE statements within a cursor FOR loop. C. A cursor needs to explicitly use the OPEN, FETCH, and CLOSE statements within a cursor FOR loop. D. A cursor variable cannot be opened for a type-compatible query in a cursor FOR loop. Answer: B

QUESTION NO: 19 Which of the following exceptions is raised when the value of amount plus the offset exceeds the maximum size of LOB allowed by the database?

A. Procedure B. Function C. Cursor D. Access Answer: D

QUESTION NO: 4 Which of the following identifiers can include any printable characters, including spaces?

A. Quoted identifiers B. Predefined identifiers C. Valid identifiers D. Invalid identifiers Answer: A

QUESTION NO: 37 Which of the following is NOT a supported transaction control statement?

A. SAVEPOINT B. ROLLBACK C. COMMIT D. CHECKPOINT Answer: D

QUESTION NO: 113 You work as an Application Developer for Dolliver Inc. The company uses an Oracle database. You own subprograms that reference to other subprograms on remote locations. Oracle server uses the signature mode of remote dependency in order to manage remote dependencies among the subprograms. Which of the following statements about the signature mode of remote dependency are true? Each correct answer represents a complete solution. Choose two.

A. Signature mode is the default mode of remote dependency. B. Oracle server records only the signature for each PL/SQL program unit. C. Oracle server records both the timestamp and the signature for each PL/SQL program unit. D. Using the signature mode prevents the unnecessary recompilation of dependent local procedures, as it allows remote procedures to be recompiled without affecting the dependent local procedures. Answer: C,D

QUESTION NO: 28 What are the two types of CASE statements in PL/SQL?

A. Simple and compound B. Simple and iterative C. Direct and indirect D. Simple and searched Answer: D

QUESTION NO: 107 SET ROLE is an example of which of the following SQL statements?

A. System control statement B. Transaction control statement C. Embedded SQL statement D. Session control statement Answer: D

QUESTION NO: 227 You want to declare a REF CURSOR variable that can relate to only those queries that are typecompatible. Which of the following will you use to accomplish the task?

A. TYPE IS REF CURSOR cur B. TYPE cur IS REF CURSOR C. TYPE IS REF CURSOR cur RETURN students%ROWTYPE D. TYPE cur IS REF CURSOR RETURN students%ROWTYPE Answer: D

QUESTION NO: 59 Which of the following statements is NOT true about the EXIT statement?

A. The EXIT statement should be placed inside a loop. B. The EXIT statement forces a loop to complete conditionally. C. When an EXIT statement is encountered, the loop gets completed immediately and control is passed to the next statement. D. The RETURN statement can be used to complete a PL/SQL block before its normal end is reached. Answer: B

QUESTION NO: 219 In PL/SQL, there are two kinds of subtypes, which are as follows: 1. Constrained subtypes 2. Unconstrained subtypes Which of the following statements regarding these are true? Each correct answer represents a complete solution. Choose all that apply.

A. The constrained subtypes limit the range of the values that are held by their base types. B. Both the constrained and unconstrained subtypes are an aliased form of their base type. C. The unconstrained subtypes do not limit the range of the values that are held by their base types. D. The constrained subtypes are an aliased form of their base type. E. The unconstrained subtypes are an aliased form of their base type. Answer: A,C,E

QUESTION NO: 162 Which of the following statements is NOT true about PL/SQL?

A. The features of PL/SQL programming are fetched from the Ada programming. B. The PL/SQL run-time engine exists as a resource inside the SQL environment. C. PL/SQL is a robust tool with many options. D. PL/SQL is a case-insensitive programming language like SQL. Answer: B

QUESTION NO: 207 Which of the following statements are true about the DBMSOUTPUT. ENABLE procedure? Each correct answer represents a complete solution. Choose all that apply.

A. The minimum and maximum values of the buffer_size that the user can specify are 20,000 and 1,000,000 respectively. B. The calls to the DBM_SOUTPUT.PUT. DBMS_OUTPUT.PUT_LINE, DBMS_OUTPUT.NEWL_INE, GET_LINE, and DBMS_OUTPUT.GET_LINES procedures are acknowledged only when the DBMS_OUTPUT is activated. C. It is not necessary to call DBMS_OUTPUT.ENABLE procedure when a user uses the SET SERVEROUTPUT option of SQL*Plus. D. When the DBMS_OUTPUT. ENABLE procedure is called more than once, the buffer_size is set to the value passed in the first call. E. When the DBMSOUTPUT. ENABLE procedure is called and the buffer_size is set to NULL, the buffer_size takes 20,000 as its default value. F. The minimum and maximum values of the buffer_size that the user can specify are 2,000 and 1,000,000 respectively. Answer: B,C,E,F

QUESTION NO: 153 Mark works as an Application Developer for Tech-Mart Inc. The company uses an Oracle database. The database contains a table named Departments. Mark writes the following statement to create a package specification named Dept_Pack: Which of the following statements about the Dept_Pack package specification is true?

A. The package specification will not be created successfully, as it declares two procedures with the same name. B. The package specification will be created successfully. C. The package specification will not be created successfully, as a package specification cannot contain procedure declarations. D. The package specification will not be created successfully, as a package specification cannot be created if the associated package body does not exist. Answer: B

QUESTION NO: 147 You have written the following program code: What will happen?

A. The program will generate an error. B. The program will not compile. C. The program will create a quoted identifier "New" . D. The program will not give any output. Answer: C

QUESTION NO: 197 You work as an Application Developer for Gentech Inc. The company uses an Oracle database. The database contains a table named Orders. You want to create a BEFORE row trigger on the Orders table, which will ensure that whenever a new order is added to the Orders table or whenever an existing order is updated, the order's cost falls within the minimum and maximum costs specified for the given order type. To accomplish this, you use a SELECT statement in the trigger body when creating the trigger. When you insert new records in the table, the trigger fails. What is the most likely cause of the issue?

A. The required task cannot be accomplished by using a row trigger. A statement trigger must be used. B. Two triggering statements cannot be used in a trigger. C. A SELECT statement is not allowed on a table that is being modified via a trigger. D. The MINIMUM and MAXIMUM functions are not allowed in a BEFORE row trigger. Answer: C

QUESTION NO: 109 You work as an Application Developer for Tech-Mart Inc. The company uses an Oracle database. The database contains a table named Employees that contains employee records. You are required to define a trigger on the Salary column of the Employees table to ensure that whenever an employee's salary is updated, the salary is not increased by more than 20% at a time nor is it ever decreased. To accomplish this, you execute the following statement What will happen when you execute the statement?

A. The statement will execute successfully and the trigger will be created. B. The statement will execute successfully and the trigger will be created, but the trigger will fail when the Salary column of the Employees table is updated. C. The statement will fail because a trigger cannot be defined on a particular column of a table. D. The statement will fail because the OLD and NEW qualifiers are not prefixed with a colon (: >. Answer: A

QUESTION NO: 157 Which of the following statements about dependencies among objects are true? Each correct answer represents a complete solution. Choose all that apply.

A. The status of a schema object can be viewed by querying the USER_STATUS data dictionary view. B. Objects having an invalid status can be described by the DESCRIBE command. C. Oracle server automatically records dependencies among objects. D. All schema objects have a status that is recorded in the data dictionary. Answer: C,D

QUESTION NO: 213 Andrew works as an Application Developer for Federal Inc. The company uses an Oracle database. The database contains a table named Orders. Andrew is required to create a database trigger on the Orders table. He executes the CREATE TRIGGER statement to accomplish this. However, during the compilation of the trigger, an error occurs. Which of the following statements regarding the creation of the trigger are true? Each correct answer represents a complete solution. Choose two.

A. The trigger will be created. However, it will need to be recompiled after the compilation error is rectified. B. The trigger will not be created unless the compilation error is rectified. C. The trigger will be created, and it will be fired successfully by DML statements. D. The trigger will be created. However, when a DML statement fires the trigger, the DML statement will fail. Answer: A,D

QUESTION NO: 21 All of the following statements about a named system exception are true except for which one?

A. These exceptions are caught by referencing the standard name within an exception-handling routine. B. These exceptions are declared explicitly. C. These exceptions are raised when a predefined Oracle error occurs. D. NO_DATA_FOUND is an example of a named system exception. Answer: B

QUESTION NO: 78 Which of the following style functions is written with the help of dynamic SQL?

A. Theta-style function B. Alpha-style function C. Gamma-style function D. Lambda-style function Answer: D

QUESTION NO: 182 In which of the following circumstances should a trigger be created? Each correct answer represents a complete solution. Choose two.

A. To ensure that when a specific operation is performed, related actions are performed. B. To duplicate the functionality of other triggers. C. To perform centralized, global operations that should be fired for the triggering statement, regardless of which user or database application issues the statement. D. To duplicate the functionality of built-in constraints in Oracle. Answer: A,C

QUESTION NO: 149 DRAG DROP What are the stages to process an SQL statement?

Choose and arrange the stages that are required for processing an SQL statement. Answer: Explanation:

QUESTION NO: 127 Which of the following are valid cursor attributes?

Each correct answer represents a complete solution. Choose all that apply. A. %OPEN B. %NOTFOUND C. %FOUND D. %NOTOPEN Answer: B,C

QUESTION NO: 135 In which of the following types of triggers can the OLD and NEW qualifiers NOT be used?

Each correct answer represents a complete solution. Choose all that apply. A. AFTER row trigger B. AFTER statement trigger C. BEFORE statement trigger D. BEFORE row trigger Answer: B,C

QUESTION NO: 168 Which of the following statements are true about dynamic SQL?

Each correct answer represents a complete solution. Choose all that apply. A. Native dynamic SQL allows placing dynamic SQL statements directly into PL/SQL code. B. The EXECUTE IMMEDIATE statement can process dynamic single-row queries. C. Dynamic SQL allows executing Data Definition Language (DDL) statements and other SQL statements that are not supported in purely static SQL programs. D. Dynamic SQL enables writing programs that reference SQL statements whose full text is not known until runtime. Answer: A,B,C,D

QUESTION NO: 163 What are the different categories of PL/SQL program units?

Each correct answer represents a complete solution. Choose all that apply. A. Unnamed B. Primary C. Named D. Default Answer: A,C

QUESTION NO: 229 Which of the following are NOT features of an exception in PL/SQL?

Each correct answer represents a complete solution. Choose all that apply. A. User defined exceptions must be raised explicitly by RAISE statements. B. Exceptions declared in a block are considered local to that block and global to all its sub-blocks. C. It is possible to declare an exception twice in the same block. D. User defined exceptions may or may not be given names. Answer: C,D

QUESTION NO: 200 You work as an Application Developer for Federal Inc. The company uses an Oracle database. The database contains a table named Employees. You are required to create a procedure named Sal_Raise that increases the salary of the specified employee. In order to accomplish this, you execute the following statement: CREATE OR REPLACE PROCEDURE Sal_Raise (Id NUMBER, Sal NUMBER) IS BEGIN UPDATE Employees SET Salary = Salary +- Sal WHERE EmpID = Id COMMIT END You want to invoke the procedure from SQL*Plus. What will you do to accomplish the task?

Each correct answer represents a complete solution. Choose two. A. You cannot invoke the procedure from SQL*Plus. B. Use the EXECUTE statement. C. Use the RUN statement. D. Use an anonymous PL/SQL block. Answer: B,D

QUESTION NO: 114 You work as a Database Administrator for Dolliver Inc. The company uses Oracle as its database

. A user has remotely logged in and connected to the database, as s/he is externally authenticated. Which of the following actions will you take in order to prevent the user from connecting to the database by just logging in from any remote machine? A. Set the REMOTE_LOGIN_PASSWORDFILE parameter to NONE. B. Set the REMOTE_LOGIN_PASSWORDFILE parameter to EXCLUSIVE. C. Set the REMOTE_DEPENDENCIES_MODE parameter to FALSE. D. Set the REMOTE_OS_AUTHENT parameter to FALSE. Answer: D

QUESTION NO: 224 Which of the following evaluates to TRUE when an unsuccessful fetch occurs?

A. %ROWCOUNT B. %ISOPEN C. %FOUND D. %NOTFOUND Answer: D

QUESTION NO: 196 Below are the steps taken to trace PL/SQL code using the dbms_trace package. 1. The PL/SQL code is executed. 2. A trace level is defined using the DBMS_TRACE.SET_PLSQL_TRACE procedure. 3. The particular program units are enabled for gathering trace data. 4. The trace information is read and interpreted. 5. The tracing of data is stopped using the DBMS_TRACE.CLEARP_LSQL_TRACE procedure. Which of the following options provides the correct sequence of these steps?

A. 1,2,3,4,5 B. 5,4,3,2,1 C. 3,2,1,5,4 D. 2,3,1,4,5 Answer: C

QUESTION NO: 220 The following steps are taken to create a PL/SQL anonymous block and display the output: 1. Set the server output on by clicking the Enable DBMS Output icon. 2. Insert the PL/SQL code in the Enter SQL Statement box. 3. Display the output by clicking the DBMS Output tab. 4. Click the Execute Statement icon. 5. Click on the DBMS Output tab. Which of the following is the correct sequence of these steps?

A. 2,5,1,3,4 B. 2,1,5,3,4 C. 2,5,1,4,3 D. 2,1,5,4,3 Answer: C

QUESTION NO: 172 Peter works as a Database Administrator for BlueWell Inc. He writes the following PL/SQL block: What will be the value of the variable New_Value when this PL/SQL code block is executed?

A. 750 B. 500 C. 1000 D. 250 Answer: B

QUESTION NO: 195 How many rows will be returned by the following SQL statement?

A. 99 B. 20 C. You will get an error message. D. 10 E. 1 Answer: B

QUESTION NO: 131 Which of the following is NOT an advantage of a stored procedure?

A. A stored procedure helps to enhance development productivity. B. A stored procedure helps to maintain data consistency in the database. C. A stored procedure helps to enforce data integrity. D. A stored procedure improves the integrity and consistency of applications. Answer: B

QUESTION NO: 110 You work as an Application Developer for Gentech Inc. The company uses an Oracle database. The database contains a table named Departments. You have defined four triggers for the Departments table. Currently, the triggers defined for the table are disabled. You want to enable them by executing a single SQL statement. Which of the following SQL statements will you use to accomplish this?

A. ALTER Departments ENABLE ALL TRIGGERS B. ENABLE ALL TRIGGERS ON Departments C. You cannot simultaneously enable all the triggers defined for the table. D. ALTER TABLE Departments ENABLE ALL TRIGGERS Answer: D

QUESTION NO: 222 Which of the following functions is used to update or insert a reference to an externally stored binary large object?

A. BFILENAME B. DUMP C. COALESCE D. DECODE Answer: A

QUESTION NO: 161 What are the two methods through which a value can be assigned to a variable? Each correct answer represents a complete solution. Choose all that apply.

A. By using the Default statement. B. By using the SQL SELECT INTO or FETCH INTO statement. C. By using the command line argument. D. By using the assignment operator (:=). Answer: B,D

QUESTION NO: 152 Which of the following PL/SQL built-in functions is used to return an ASCII encoding string for a Character?

A. CONCAT B. ASCIISTR C. ASCII D. CHR Answer: B

QUESTION NO: 183 Which of the following system privileges should you have to create or replace a function in your own schema?

A. CREATE ANY PROCEDURE B. CREATE FUNCTION C. CREATE PROCEDURE D. CREATE PACKAGE Answer: C

QUESTION NO: 209 Which of the following statements can be used to create a package? Each correct answer represents a part of the solution. Choose two.

A. CREATE PACKAGE B. CREATE BODY/SPECIFICATION C. CREATE PACKAGE SPECIFICATION D. CREATE PACKAGE BODY Answer: A,D

QUESTION NO: 104 You work as an Application Developer for Dolliver Inc. The company uses an Oracle database. You are required to create a trigger that restricts any database user from dropping any database object from the database. What will you do to accomplish the task?

A. Create a DDL trigger on the database level. B. Create a DDL trigger on the schema level. C. Create an INSTEAD OF trigger on the database level. D. Create a DML trigger on the database level. Answer: A

QUESTION NO: 193 You work as an Application Developer for Dolliver Inc. The company uses an Oracle database. The database contains two tables named Orders and Audit_Records. You are required to create a trigger that monitors every modified row of the Orders table and stores the information about the modified rows in the Audit_Records table. What will you do to accomplish this?

A. Create a statement trigger on the Audit_Records table. B. Create a statement trigger on the Orders table. C. Create a row trigger on the Audit_Records table. D. Create a row trigger on the Orders table. Answer: D

QUESTION NO: 98 Which of the following provides compile time code optimization?

A. Cross-Session PL/SQL Result Cache B. Real Native Compilation C. Compound trigger D. PL/SQL Code In lining Answer: D

QUESTION NO: 120 Which of the following variables is the key to communication between the host program and Oracle?

A. Cursor variable B. Indicator variable C. Pointer variable D. Host variable Answer: D

QUESTION NO: 223 Which of the following packages will you use when you want an application to be notified whenever the values of interest in the database are changed?

A. DBMS_ HPROF B. DBMS_ALERT C. DBMS_LOCK D. DBMS_ASSERT Answer: B

QUESTION NO: 234 Which of the following packages provides subprograms that filter the user input and validate it to avoid SQL injection?

A. DBMS_ALERT B. DBMS_ASSERT C. DBMS_MONITOR D. DBMS_TRACE E. DBMS_REPAIR Answer: B

QUESTION NO: 130 You want to retrieve the complete database object definitions from the data dictionary. Which of the following packages will you use to get the required result?

A. DBMS_ASSERT B. DBMS_METADATA C. DBMS_HPROF D. DBMS_RESOURCE_MANAGER Answer: B

QUESTION NO: 117 You work as an Application Developer for Dolliver Inc. The company uses an Oracle database. The database contains two tables named Players and Player_Bat_Stat. You are required to create a package named Player_Pack. In order to accomplish the task, you first create the package specification by executing the following statement: After creating the package specification, you execute the following statement in order to create the package body: Which of the following statements will assign a value of 20,000 to the v_min_salary variable from within a stand-alone procedure outside of the package? A. Player_Pack.v_min_salary := 20000 B. Player_Pack.Add_Player.v_min_salary := 20000 C. v_min_salary := 20000 D. The v_min_salary variable cannot be assigned a value from outside of the package. Answer: A QUESTION NO: 118 Which of the following Oracle supplied packages is used to access data on the Internet from SQL and PL/SQL?

A. DBMS_JOB B. UTL_HTTP C. DBMS_OUTPUT D. UTL_FILE Answer: B

QUESTION NO: 34 Which of the following functions is used to convert a cursor number to a weakly-typed cursor variable?

A. DBMS_SQL.TO_OPEN_CURSOR B. DBMS_SQL.TO_REFCURSOR C. DBMS_SQL.TO_CURSOR D. DBMS_SQL.OPEN_CURSOR Answer: B

QUESTION NO: 204 Which of the following packages will you use to obtain information about the PL/SQL objects?

A. DBMS_STATS B. DBMS_UTILITY C. DBMS_DESCRIBE D. DBMS_METADATA Answer: C

QUESTION NO: 190 Consider the following declaration of collection: TYPE NbrLst IS TABLE OF INTEGER n NbrLst := NbrLst (1,2,3,4,5) Which of the following methods will remove one element from the end of a collection?

A. DELETE(1,5) B. DELETE(1) C. TRIM D. DELETE E. TRIM(5) Answer: C

QUESTION NO: 244 Which of the following is a data manipulation language (DML) statement?

A. DESCRIBE B. EXPLAIN PLAN C. SAVEPOINT D. TRUNCATE Answer: B

QUESTION NO: 246 In which of the following sections of a PL/SQL block a user-defined exception can be raised?

A. Declarative section B. Comments section C. Exception section D. Executable section Answer: D

QUESTION NO: 175 Which of the following statements is NOT true about parameters in cursor?

A. Default values can be assigned to Cursor parameters. B. The scope of the cursor parameters is global to the cursor. C. The mode of parameters can only be IN. D. Cursor becomes more reusable with the Cursor parameters. Answer: B

QUESTION NO: 139 You work as an Application Developer for Z-Tech Inc. The company uses an Oracle database. The database contains three tables named Employees, Departments, and Locations. Currently, the tables do not contain any row. You create the following two procedures. After creating the procedures, you execute the following statement: Which of the following tables will have rows inserted into it?

A. Departments B. No table will have a row inserted into it. C. Locations D. Employees Answer: B

QUESTION NO: 126 Samantha designs a database for Oliver Inc. She wants to retrieve the text associated with the current exception. Which of the following functions will she use?

A. ERRORTEXT B. ERRTEXT C. SQLERRMSG D. SQLERRM Answer: D

QUESTION NO: 133 You have created a PL/SQL block. Now, you want to embed it in a Pro*C/C++ program. Which of the following will you use to accomplish the specified task?

A. EXEC SQL EXECUTE DECLARE .... BEGIN .... END-EXEC END B. EXEC SQL EXECUTE DECLARE .... BEGIN .... END END-EXEC C. EXEC SQL EXECUTE DECLARE .... BEGIN .... END END-EXEC D. EXEC SQL EXECUTE .... DECLARE .... BEGIN END EXEC-END Answer: B

QUESTION NO: 145 You work as a Database Administrator for Tech-Mart Inc. The company uses an Oracle database. Andrew is an employee in the company. He owns a table named Customers. He is required to create a procedure named Add_Customer that will be used to add new customers to the Customers table. You grant the CREATE ANY PROCEDURE system privilege to Andrew so that he is able to create the procedure. You also want to ensure that Andrew is capable of executing the Add_Customer procedure as well as other procedures in the database. Which other privilege will you grant to Andrew?

A. EXECUTE object privilege B. RUN ANY PROCEDURE system privilege C. Andrew does not need any additional privilege. D. EXECUTE ANY PROCEDURE system privilege Answer: C

QUESTION NO: 250 You work as an Application Developer for Gentech Inc. The company uses an Oracle database. Currently, the Oracle server uses the timestamp mode for managing remote dependencies among objects. You want the Oracle server to use the signature mode. You also want to ensure that the signature mode is applicable to all sessions in the database. What will you do to accomplish this? Each correct answer represents a complete solution. Choose two.

A. Edit the init.ora file and remove the REMOTE_DEPENDENCIES_MODE parameter. B. Set the REMOTE_DEPENDENCIES_MODE parameter to SIGNATURE by using the ALTER SESSION statement. C. Set the REMOTE_DEPENDENCIES_MODE parameter to SIGNATURE by using the ALTER SYSTEM statement. D. Edit the init.ora file and set the REMOTE_DEPENDENCIES_MODE parameter to SIGNATURE . Answer: C,D

QUESTION NO: 238 Which of the following tasks a user cannot perform with the help of the data manipulation language (DML) statement?

A. Establish properties for a transaction. B. Add new rows of data into a table or view with the help of the INSERT statement. C. View the execution plan for a SQL statement with the help of the EXPLAIN PLAN statement. D. Retrieve or fetch data from one or more tables or views with the help of the SELECT statement. Answer: A

QUESTION NO: 177 Which of the following clauses allows users to update or delete the record that was last fetched by the cursor?

A. FOR UPDATE B. INTO C. WHERE CURRENT OF D. DISTINCT Answer: C QUESTION NO: 178

QUESTION NO: 119 You have created a cursor named emp_cur to retrieve the names of those employees whose salary is greater than 10,000. CURSOR emp_cur IS SELECT emp_name, salary FROM employees WHERE salary > 10000 Now, you want to display the details of all such employees. You have opened the cursor, fetched a row from it, and then closed it various times within a program. This way the code of the program has now become a bit lengthy. Therefore, you decide to use the cursor FOR loop to decrease the length of the program so that the data can be retrieved easily and quickly. Which of the following is the valid syntax of the cursor FOR loop that you will insert into the code?

A. FOR emp_rec IN emp_cur OPEN emp_cur LOOP DBMS_OUTPUT.PUT_LINE (emp_rec.emp_name || " || emp_rec.salary) END LOOP CLOSE emp_cur B. FOR emp_rec IN emp_cur LOOP DBMS_OUTPUT.PUT_LINE (emp_rec.emp_name || " || emp_rec.salary) END LOOP C. FOR emp_cur IN emp_rec LOOP DBMS_OUTPUT.PUT_LINE (emp_rec.emp_name || " || emp_rec.salary) END LOOP D. FOR emp_rec IN emp_cur LOOP DBMS_OUTPUT.PUT_LINE (emp_cur.emp_name || " || emp_cur.salary) END LOOP E. LOOP FOR emp_rec IN emp_cur DBMS_OUTPUT.PUT_LINE (emp_rec.emp_name || " || emp_rec.salary) END LOOP Answer: B

QUESTION NO: 102 You work as an Application Developer for Tech-Mart Inc. The company uses an Oracle database. The database contains a table named Employees. You want to create a trigger on the Employees table. You want the trigger to fire when an INSERT statement is issued on the table. Which of the following types of triggers will you create on the Employees table?

A. INSTEAD OF trigger B. Application trigger C. Database trigger D. System event trigger Answer: C

QUESTION NO: 243 You work as an Application Developer for Dolliver Inc. The company uses an Oracle database. The database contains a table named Audit_Records. Following is the structure of the table: Sequence NUMBER User_Name VARCHAR2 (25) Login_Time DATE Job VARCHAR2 (25) Emp_ID NUMBER You create a procedure as follows: CREATE OR REPLACE PROCEDURE Login_Pro (Name VARCHAR2) AS BEGIN INSERT INTO Audit_Records (User_Name) VALUES (Name) END Login_Pro You also create a database trigger as follows: CREATE OR REPLACE TRIGGER Logon_Trig AFTER LOGON ON DATABASE CALL Login_Pro (ORA_LOGIN_USER) Which of the following statements regarding the functionality of the Logon_Trig trigger is true?

A. It marks a database user as logged on to the database before an audit statement is issued. B. It invokes the Login_Pro procedure after a database user logs on to the database and records the username of the database user in the Audit_Records table. C. The trigger fails to execute. D. It invokes the Login_Pro procedure before a database user logs on to the database and records the username of the database user in the Audit_Records table. Answer: B

QUESTION NO: 142 Mark works as an Application Developer for Tech-Mart Inc. The company uses an Oracle database. The database contains a table named Employees. Following is the structure of the table: Emp_ID NUMBER Emp_Name VARCHAR2 (30) Dept_ID NUMBER Salary NUMBER Mark wants to create a procedure named Add_Emp that will be used to insert new records in the Employees table. In order to accomplish this, Mark executes the following statement: CREATE OR REPLACE PROCEDURE Add_Emp (p_emp_id IN NUMBER, p_name Emplovees.Emp_Name0/oTYPE, p_dept_id NUMBER, p_salary Em ploy ees. Salary % TYPE) RETURN NUMBER IS BEGIN INSERT INTO Employees (Emp_ID, Emp_Name, Dept_ID, Salary) VALUES (p_emp_id, p_name, p_dept_id, p_salary) END What will happen when Mark executes the statement?

A. It will execute successfully and the procedure will be created. B. It will return an error because the %TYPE attribute cannot be used in a procedure to declare parameters. C. It will return an error because the RETURN clause cannot be used in a procedure declaration. D. It will return an error because the IN parameter mode has not been specified for all the parameters. Answer: C

QUESTION NO: 188 David works as an Application Developer for Blue Well Inc. The company uses an Oracle database. The database contains a table named Employees. David is required to create a function named Calc_Tax that calculates the tax applicable on the salary of an employee. To accomplish this, David executes the following statement: Which of the following statements regarding the above statement is true?

A. It will return an error because a length or precision cannot be specified for the return value of afunction. B. It will return an error because a function cannot contain a RETURN statement in its executable section. C. It will return an error because the %TYPE attribute cannot be used for parameter declaration in a function. D. It will execute successfully and the function will be created. Answer: A

QUESTION NO: 202 Martha works as an Application Developer for Z-Tech Inc. The company uses an Oracle database. The database contains a table named Employees that contains employee records. Martha wants to create a function named Get_Sal that will be used to retrieve the salary of an employee of the company. In order to accomplish this, she executes the following statement: CREATE OR REPLACE FUNCTIONS! Get_Sal (p_id IN emplDvees.enipJd'VoTYPE) RETJRN NUMBER (5, 2) IS v_salary eniployees.salary%TYPE := 0 BEGIN SELECT salary INTO v_salary FROM employees WHERE emp_id = p_id RETURN v_salary ENDGet_Sal What will happen when Martha executes the statement?

A. It will return an error because the %TYPE attribute cannot be used for parameter declaration in a function. B. It will return an error because the function cannot contain a RETURN statement in its executable section. C. It will execute successfully and the function will be created. D. It will return an error because a length or precision cannot be specified for the return value of the function. Answer: D

QUESTION NO: 115 Mark works as an Application Developer for Gentech Inc. The company uses an Oracle database. The database contains a table named Employees. Following is the structure of the table: Emp_ID NUMBER Emp_Name VARCHAR2 (30) Emp_Salarv NUMBER (5, 2) Mark wants to create a procedure named Add_Emp that will be used to insert new records in the Employees table. In order to accomplish this, Mark executes the following statement: CREATE OR REPLACE PROCEDURE Add_Emp (p_id IN NUMBER, p_name VARCHAR2 (30), p_sal NUMBER (5, 2)) IS BEGIN INSERT INTO Employees (Emp_ID, Emp_Name, Emp_Salary) VALUES (p_id, p_name, p_sal) END What will happen when Mark executes the statement?

A. It will return an error because the RETURN clause is missing from the header section of the procedure. B. It will return an error because the datatypes of the parameters in the procedure's parameter list cannot have length or precision specified. C. It will return an error because the RETURN statement is missing from the executable section of the procedure. D. It will execute successfully and the procedure will be created. Answer: B

QUESTION NO: 210 Which of the following PL/SQL functions is used to find the position where a substring starts in a string?

A. LENGTH B. INSTR C. CONCAT D. INITCAP Answer: B QUESTION NO: 211

QUESTION NO: 136 Which of the following methods will you use to find the maximum size of a collection!

A. LIMIT B. EXISTS C. COUNT D. LAST Answer: A

QUESTION NO: 179 You have written the following PL/SQL code: But the above code does not execute successfully and gives an error. Which of the following lines is the cause of the error and what may be the reason for it?

A. Line 4 B. Line 2 C. Line 6 D. Line 5 Answer: C

QUESTION NO: 241 In order to multiply two numbers, you have written the following code: Line 1: DECLARE Line 2: v_number1 INTEGER := &sv_number1 Line 3: v_number2 INTEGER := &sv_number2 Line 4: v_result NUMBER Line 5: BEGIN Line 6: v_result = v_number 1 / v_number2 Line 7: DBMS_OUTPUT.PUT_LINE ('v_result: '| | v_result) Line 8: END The above code generates an error. Which of the following lines is responsible for the error?

A. Line 4 B. Line 7 C. Line 2 D. Line 6 Answer: D

QUESTION NO: 192 What happens if an exception is raised in the program, and that exception is not handled by an exception section in either the current or enclosing PL/SQL blocks?

A. Nothing will happen the program will execute successfully. B. The program will return an error. C. The program will generate an output. D. The program will pause for a moment and then resume its execution. Answer: B

QUESTION NO: 106 Which of the following services is a collection of a pre-defined Web-based units for performing directory operations on behalf of a user?

A. Oracle Delegated Administration Services B. Oracle Net Services C. Oracle WebCenter Services D. Oracle Database Support Services Answer: A

QUESTION NO: 160 Which of the following is used for parsing PL/SQL compilation units and performing their semantic and syntactic validation?

A. PL/SQL Virtual Machine (PVM) B. PL/SQL compiler back-end C. PL/SQL wrappers D. PL/SQL compiler-front end Answer: D

QUESTION NO: 146 Which of the following is used for executing a PL/SQL program's bytecode?

A. PL/SQL Virtual Machine (PVM) B. Set Operators C. SQL Parser D. Database Resident Connection Pooling (DRCP) Answer: A

QUESTION NO: 123 Which of the following types of exception is raised when the host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types?

A. PROGRAM_ERROR B. NO_DATA_FOUND C. ROWTYPE_MISMATCH D. SELF_IS_NULL E. STORAGE_ ERROR Answer: C

QUESTION NO: 116 Which of the following procedures of the UTL_FILE package is used to write a line to a file, and appends an OS-specific line terminator?

A. PUTF B. PUT_LINE C. FFLUSH D. PUT Answer: B

QUESTION NO: 159 Which of the following stored programs of Oracle is used to compute a value and returns a value to a calling environment?

A. Package B. Trigger C. Function D. Procedure Answer: C

QUESTION NO: 248 You have written the following PL/SQL code: SET SERVEROUTPUT ON SIZE 1000000 BEGIN END / But this code fails run-time compilation. What should you do to remove this error?

A. Place at least one statement between the BEGIN and END statements of the PL/SQL block. B. Remove the semicolon. C. Remove the forward slash (/). D. Remove the SQL*Plus SERVEROUTPUT variable. Answer: A

QUESTION NO: 167 Which of the following functions is used by users to add a character one or more times at the beginning of a string?

A. REPLACE B. LTRIM C. CONVERT D. LPAD Answer: D

QUESTION NO: 124 Which of the following subprograms of DBMS_LOCK package are NOT procedures? Each correct answer represents a complete solution. Choose all that apply.

A. REQUEST B. CONVERT C. ALLOCATE_UNIQUE D. RELEASE E. SLEEP Answer: A,B,D

QUESTION NO: 141 Which of the following identifiers are declared globally?

A. Reserve words identifiers B. Predefined identifiers C. Default identifiers D. Quoted identifiers Answer: B

QUESTION NO: 170 Which of the following is an Oracle Database facility that keeps a list of references?

A. Resolver B. Profiler C. Listener D. Compiler Answer: A

QUESTION NO: 112 Which of the following requires an explicit cursor to process more than one row in PL/SQL?

A. SELECT B. INSERT C. UPDATE D. DELETE Answer: A

QUESTION NO: 165 Which of the following is NOT a procedure in the UTL_MAIL package?

A. SEND_ATTACH_RAW B. SEND_MAIL C. SEND_ATTACH_VARCHAR2 D. SEND Answer: B

QUESTION NO: 205 DRAG DROP Parsing is a process in Oracle, which is used to check for the identical statements that have been fired, with the intention of reducing processing overheads. Choose different processes/steps included in parsing to arrive at the output of an SQL statement. Answer: QUESTION NO: 206 Martha designs a database for Oliver Inc. She wants to retrieve the error number for the current exception. Which of the following functions will she use?

A. SQLNUM B. SQLCODE C. ERRORNUM D. SQLERRM Answer: B

QUESTION NO: 186 You work as an Application Developer for Tech-Mart Inc. The company uses an Oracle database. The database contains a table named Employees. You create a package named Tax_Pack by executing the following statements: You call the Tax_Pack package from a SELECT statement as follows: SELECT Tax_Pack.Tax(Salary), Salary, Last_Name FROM Employees What will you do to check the purity level of the Tax function?

A. Specify the PRAGMA RESTRICTREFERENCES compiler directive in the package body of the Tax_Pack package. B. Specify the PRAGMA RESTRICTREFERENCES compiler directive in the package specification of the Tax_Pack package. C. You do not have to do anything. D. Specify the PRAGMA RESTRICTREFERENCES compiler directive in the body of the Tax function. Answer: C

QUESTION NO: 198 Which of the following is NOT an advantage of a package?

A. Stored packages allow users to encapsulate or group stored procedures, datatypes, variables, etc. in a single named, stored unit in the database. B. The methods of package definition allow users to specify which variables, cursors, and procedures are private and public. C. Packages implement data consistency. D. Packages provide better performance. Answer: C

QUESTION NO: 125 Which of the following statements about subprograms are true? Each correct answer represents a complete solution. Choose all that apply.

A. Subprograms are named PL/SQL blocks that can accept parameters and be invoked. B. Subprograms can be called from other procedures and triggers. C. Subprograms can be stored only at the server level. D. Subprograms provide easy maintenance, improved data security and code clarity, and enhanced performance. Answer: A,B,D

QUESTION NO: 184 Which of the following literals is used to store year, month, day, hour, minute, and fractional second values?

A. TIMESTAMP B. TIMESTAMP WITH LOCAL TIME ZONE C. DATE D. TIMESTAMP WITH TIME ZONE Answer: A

QUESTION NO: 174 Consider the following declaration of collection: TYPE NbrLst IS TABLE OF INTEGER n NbrLst := NbrLst (1,2,3,4,5) Which of the following collection methods will set the value of COUNT to 0? Each correct answer represents a complete solution. Choose two.

A. TRIM B. DELETE(5) C. DELETE(1,5) D. DELETE Answer: C,D

QUESTION NO: 245 You work as an Application Developer for Gentech Inc. The company uses an Oracle database. The database contains a package named Ora_Pack. The Ora_Pack package has a procedure named Add_Dept. You want to submit the Add_Dept procedure of the Ora_Pack package to the job queue. In order to accomplish this, you write the following PL/SQL block: VARIABLE jobno NUMBER BEGIN DBMS_JOB.SUBMIT (JOB => :jobno, WHAT => 'Ora_Pack.Add_Dept("Marketing", 2710)', NEXTDATE =>TRUNC(SYSDATE + 1), INTERVAL => TRUNC(SYSDATE + 1)') COMMIT END Which of the following statements regarding the above PL/SQL block are true? Each correct answer represents a complete solution. Choose all that apply.

A. The DBMS_JOB.RUN procedure must be executed to run the job. B. The COMMIT statement commits the transaction. C. The JOB parameter returns the job number. D. It executes successfully and submits the Add_Dept procedure of the Ora_Pack package to the job queue. Answer: B,C,D

QUESTION NO: 215 Which of the following are the features of a cursor FOR loop? Each correct answer represents a complete solution. Choose all that apply.

A. The cursor gets closed automatically when a user leaves the loop. B. Cursor FOR loop requires the record type declaration. C. Before each iteration of the FOR loop, PL/SQL fetches into the implicitly declared record. D. Cursor FOR loop fetches records from the cursor. Answer: A,B,C,D

QUESTION NO: 181 Martin works as a Database Administrator for Malcons Inc. He writes the following PL/SQL code block: The above code block fails on execution. Which of the following is the most likely cause of the failure?

A. The exception section is missing. B. Emp_Salary variable is not initialized. C. INTO clause is missing. D. The SELECT statement cannot include alias name for column. Answer: C

QUESTION NO: 171 You work as an Application Developer for Dolliver Inc. The company uses an Oracle database. The database contains a table named Employees. You are required to create a trigger on the Employees table in order to ensure that certain records are inserted in a table named Audit_Emp_Table whenever an INSERT operation is performed on the Employees table. In order to accomplish this, you execute the following statement: CREATE OR REPLACE TRIGGER Audit_Emp_Trig AFTER INSERT ON Employees BEGIN INSERT INTO Audit_Emp_Table (User_Idr New_Last_Name, New_Salary) VALUES (Emp_Id, :NEW.Last_Namer :NEW.Salary) END What will happen when you execute the statement?

A. The statement will fail because the NEW qualifier cannot be prefixed with a colon (:). B. The statement will fail because the NEW qualifier cannot be used in a statement trigger. C. The trigger will be created successfully. D. The statement will fail because the NEW qualifier cannot be used in an AFTER trigger. Answer: B

QUESTION NO: 154 You work as a Database Administrator for Z-Tech Inc. The company uses an Oracle database. The database contains a table named Employees. You want to write a query that, when executed, prompts the user to enter a value and displays the names of all the employees who get a salary equal to the value entered. To accomplish this, you execute the following statement: Which of the following statements about the above SELECT statement is true?

A. The statement will return an error because the v_sal substitution variable has not been defined before using it in the SELECT statement. B. The statement will return an error because a substitution variable cannot be used in the WHERE clause of a SELECT statement. C. The statement will return an error because the v_sal substitution variable has not been preceded by an &. D. The statement will be executed successfully. Answer: C

QUESTION NO: 203 You work as an Application Developer for Blue-Well Inc. The company uses an Oracle database. The database contains a table named Employees that contains employee records. You are required to create subprograms (procedures and functions) that perform different operations on the Employees table. You want to group the subprograms in a package named Manage_Employees. In order to accomplish the task, you first create the package specification by executing the following statement: CREATE OR REPLACE PACKAGE Manage_Employees IS tax_rate CONSTANT NUMBER(5, 2) := .28 v_id NUMBER PROCEDURE Insert_Emp (p_deptno NUMBER, p_sal NUMBER) PROCEDURE Delete_Emp PROCEDURE Update_Emp FUNCTION Calc_Tax (p_sal NUMBER) RETURN NUMBER END Manage_Employees After creating the package specification, you execute the following statement in order to create the package body: Which of the following subprograms in the Manage_Employees package is a private subprogram?

A. Update_Sal B. Update_Emp C. Calc_Tax D. lnsert_Emp E. Delete_Emp Answer: A

QUESTION NO: 214 You work as an Application Developer for Dolliver Inc. The company uses an Oracle database. The database contains a table named Employees that contains employee records. You are required to create a database trigger on the Employees table. In order to accomplish this, you execute the following statement: Which of the following statements regarding the function of the Secure_Emp trigger is true?

A. You can perform INSERT, UPDATE, and DELETE operations on the Employees table only from Monday through Friday between 8 AM and 6 PM. B. You cannot perform an INSERT, UPDATE, or DELETE operation on the Employees table from Monday through Friday between 8 AM and 6 PM. C. The trigger will fail when you attempt to insert, update, or delete records from the Employees table. D. You cannot perform queries (SELECT operations) on the Employees table from Monday through Friday between 8 AM and 6 PM. Answer: A

QUESTION NO: 140 Which of the following are the valid requirements for a user-defined function to be called from a SQL expression?

Each correct answer represents a complete solution. Choose all that apply. A. Parameters of the function must be in the OUT or IN OUT parameter mode. B. Parameters of the function must use the named notation method. C. The function cannot contain statements that end the transaction. D. If the function is being called from a SELECT statement, the function cannot contain DML statements. E. Parameters of the function and the values returned by the function must be PL/SQL- specific datatypes. F. The function must be a stored function. Answer: C,D,F

QUESTION NO: 180 Which of the following are the advantages of SQL*Plus?

Each correct answer represents a complete solution. Choose all that apply. A. Performing database administration. B. Sending messages to, and accepting responses from, an end user. C. Listing column definitions for any table access, and copying data between SQL databases. D. Deleting or removing invalid data from the columns of a table in any particular database. Answer: A,B,C

QUESTION NO: 164 Which of the following statements are true about the DBMS_OUTPUT.PUT and DBMS_OUTPUT.PUTLINE procedures?

Each correct answer represents a complete solution. Choose all that apply. A. The DBMS_OUTPUT.PUT_LINE procedure is used to assign a line in the buffer. B. The DBMS_OUTPUT.PUT procedure is used to assign a partial line in the buffer. C. The DBMS_OUTPUT.PUT procedure is followed by an end-of-line marker. D. The DBMS_OUTPUT.PUT_LINE procedure is not followed by an end-of-line marker. Answer: A,B

QUESTION NO: 176 You work as a Database Administrator for TechMart Inc. The company has a server in which Oracle 11g database is installed. You tried to execute a stored procedure that had cursor variables as parameters. But the server generated the following error: ORA-06514: PL/SQL: The remote call cannot be handled by the server What action should you take to resolve this Oracle error?

Each correct answer represents a complete solution. Choose all that apply. A. Try to avoid using cursor variables as parameters for stored procedures. B. Reinstall the server. C. Upgrade your server to support cursor variables as parameters for stored procedures. D. Drop the stored procedure. Answer: A,C

You want to call a PL/SQL user-defined function from within the SQL expression. Which of the following clauses can you use to do so?

Each correct answer represents a complete solution. Choose all that apply. A. WHERE B. ORDER BY C. FROM D. SELECT E. GROUP BY Answer: A,B,D,E

QUESTION NO: 232 You work as a Database Administrator for Dolliver Inc. The company uses Oracle 10g as its database. There are numerous enhancements with the release of Oracle 10g. One of them is the quote operator. You want to assign "look at John's bike" to a variable in PL/SQL and also specify your own delimiter by using the quote operator. Which of the following will you use to accomplish the task? A. vr := look at John"s bike B. vr := qllook at John's bike!q C. vr := q'[look at John's bike]'q D. vr := q'[look at John's bike]' Answer: D

QUESTION NO: 233 Which of the following statements are true about the RAISE statement? Each correct answer represents a complete solution. Choose three. A. It can be used to raise pre-defined exceptions. B. It is never used to raise pre-defined exceptions. C. It stops normal execution of a PL/SQL block. D. It is used to raise user-defined exceptions. Answer: A,C,D

QUESTION NO: 185 You work as an Application Developer for Dolliver Inc. The company uses an Oracle database. The database contains two tables named Players and Player_Bat_Stat. You are required to create a package named Player_Pack. In order to accomplish the task, you first create the package specification by executing the following statement: CREATE OR REPLACE PACKAGE Player_Pack IS v_max_team_salary NUMBER (12, 2) PROCEDURE Add_Player (v_id IN NUMBER, v_last_name VARCHAR 2, v_saiary NUMBER) END Player_Pack After creating the package specification, you execute the following statement in order to create the package body: You want to assign a value to the v_player_avg variable from a procedure outside the package.

Which of the following statements will you use to accomplish this? A. The v_player_avg variable cannot be assigned a value from outside of the package. B. v_player_avg := .25 C. Player_Pack.Add_Player.v_player_avg := .25 D. Player_Pack.v_player_avg := .25 Answer: A

QUESTION NO: 53 Which of the following is NOT a component of static explicit cursor?

A. Update B. Close a Cursor C. Open D. Fetch from Answer: A

QUESTION NO: 58 Which of the following provides the data type of a previously declared user-defined record?

A. %ROWTYPE B. %FOUND C. %SET D. %TYPE Answer: D

QUESTION NO: 48 Which of the following symbols denotes PL/SQL conditional compilation-time variable?

A. & B. && C. $ D. $$ Answer: D

QUESTION NO: 81 What is the maximum size of a trigger?

A. 64KB B. 16KB C. 32KB D. 8KB Answer: C

QUESTION NO: 74 In which of the following categories will the EVAL_CHANGE_TRIGGER fall?

A. A Statement-level trigger and an AFTER trigger B. An AFTER trigger and a BEFORE trigger C. A Row-level trigger and a statement-level trigger D. An INSTEAD OF trigger and a statement-level trigger Answer: A

One of the users creates the following PL/SQL object. What type of PL/SQL object is this considered as?

A. A package body B. A trigger C. A type specification D. A package specification Answer: D

QUESTION NO: 29 Which of the following is NOT a part of a PL/SQL exception?

A. A return value B. A message C. Type of exception D. An Error Code Answer: A

QUESTION NO: 8 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. Answer: D

QUESTION NO: 73 Which of the following data dictionary views describes all dependencies between objects (procedures, packages, functions, package bodies, and triggers), including dependencies on views created without any database links in the database?

A. ALL_DEPENDENCIES B. USER_DB_LINKS C. DBA_DEPENDENCIES D. USER_DEPENDENCIES Answer: C

QUESTION NO: 82 In which of the following ways can a PL/SQL function NOT be executed?

A. As part of a Select statement. B. By assigning it to a variable. C. In a PL/SQL statement. D. By calling it from a file. Answer: D

QUESTION NO: 90 Which of the following triggers can be specified on a view?

A. BEFORE B. DEFAULT C. INSTEAD OF D. AFTER Answer: C

QUESTION NO: 89 Which of the following procedures of DBMS_SQL supports bulk DML operations?

A. BIND_ARRAY B. BIND_VARIABLE C. BIND_VARIABLE_CHAR D. BIND VARIABLE_RAW Answer: A

QUESTION NO: 32 Which of the following terms is given by developers to PL/SQL 2.x programming?

A. Black Art B. Gray Art C. White Art D. Blue Art Answer: A

QUESTION NO: 31 All are lexical units except which of the following?

A. Comments B. Variables C. Literals D. Identifiers E. Delimiters Answer: B

QUESTION NO: 71 Which of the following is NOT a type of PL/SQL exception?

A. Controlled Exception B. User-defined Exception C. Unnamed System Exception D. Named System Exception Answer: A

QUESTION NO: 50 Which of the following is NOT a procedure?

A. DBMS_RESULT_CACHE.FLUSH B. DBMS_RESULT_CACHE.INVALIDATE C. DBMS_RESULT_CACHE.INVALIDATE_OBJECT D. DBMS_RESULT_CACHE.STATUS Answer: D

QUESTION NO: 27 Which of the following correctly represents the default format mask for dates in PL/SQL programming?

A. DD-MON-YY B. MON-DD-YY C. MON-DD-YYYY D. DD-MON-YYYY Answer: D

QUESTION NO: 52 To which of the following statements does the dynamic RETURNING INTO clause belong?

A. DELETE B. UPDATE C. EXECUTE IMMEDIATE D. INSERT Answer: C

QUESTION NO: 95 Which of the following events CANNOT cause a trigger to fire?

A. DROP B. DELETE C. INSERT D. UPDATE E. SELECT F. CREATE Answer: E

QUESTION NO: 62 All of the following are the advantages of PL/SQL except for which one?

A. Data sequencing B. Error Handling C. Better Performance D. Procedural Language Capability Answer: A

QUESTION NO: 49 Which of the following statements is NOT true about datatype conversions?

A. Datatype conversion is often done explicitly in PL/SQL. B. There are two types of datatype conversions: Implicit and Explicit. C. CONVERT is a datatype conversion function. D. Datatype conversion is also known as casting. Answer: A

QUESTION NO: 84 Which of the following utilities is used to obfuscate the contents of a PL/SQL source file?

A. SQL*Loader utility B. Import utility C. JPublisher utility D. Wrap utility Answer: D

QUESTION NO: 60 Which of the following is used to map an exception to an error code?

A. SQLCODE B. EXCEPTION_INIT C. RAISE_APPLICAT10N_ERROR function D. PRAGMA Answer: B

QUESTION NO: 97 Which of the following functions returns an error code and a brief message?

A. SQLCODE B. SQLERRM C. SQLMSG D. SQLERROR Answer: B

QUESTION NO: 75 With which of the following database operations is the triggering_event NOT associated?

A. STARTUP B. SHUTDOWN C. SERVERERROR D. WAIT EVENTS Answer: D

QUESTION NO: 26 Which of the following operators allows users to check whether a VARRAY or NESTED TABLE collection is a subset of a mirrored datatype?

A. SUBMULTISET B. MEMBER OF C. EMPTY D. ISA SET Answer: A

QUESTION NO: 42 Which of the following collection exceptions is raised when an attempt is made to use a data type that cannot be converted to a PLS_INTEGER?

A. SUBSCRIPT_OUTSIDE_LIMIT B. SUBSCRIPT_BEYOND_COUNT C. VALUE_ERROR D. NO_DATA_FOUND Answer: C

QUESTION NO: 70 Which of the following are the two case statements present in PL/SQL?

A. Simple CASE statement and Compound CASE statement B. Primary CASE statement and Secondary CASE statement C. Compound CASE statement and Default CASE statement D. Simple CASE statement and Searched CASE statement Answer: D

QUESTION NO: 72 Which of the following two methods are provided by Oracle for using dynamic SQL within PL/SQL?

A. Static SQL and DBMS_SQL package B. Dynamic SQL and DBMS_PL/SQL package C. Native dynamic SQL and DBMS_SQL package D. DBMS_SQL package and DBMS_PL/SQL package Answer: C

QUESTION NO: 40 Which of the following functions can be used to explicitly assign data literals?

A. TO_CHAR( ) and CAST( ) B. TO_CHAR( ) and DATE( ) C. TO_CHAR( ) and TO_NCHAR() D. TO_DATE( ) and CAST() Answer: D

QUESTION NO: 41 What are the two new functions added by the DBMS_SQL built-in package?

A. TO_LOB and TO_REFCURSOR B. TO_NCLOB and TO_TIMESTAMP C. TO_REFCURSOR and TOCURSOR_NUMBER D. TO_CLOB and TO_NCLOB Answer: C

QUESTION NO: 94 Which of the following statements is NOT true about the BULK COLLECT clause?

A. The BULK COLLECT clause can be used both in server-side and client-side programs. B. It is not possible to bulk collect into an associative array that has a string type for the key. C. A user can combine the BULK COLLECT clause with a FORALL statement. D. When implicit datatype conversions are needed, multiple composite targets cannot be used in the BULK COLLECT INTO clause. Answer: A

QUESTION NO: 43 All of the following statements are true about PL/SQL except for which one?

A. Users can call PL/SQL directly from the command-line SQL*Plus interface. B. It is a case-insensitive programming language. C. It is a purely procedural language. D. It was developed by Oracle in the late 1980s. Answer: C

QUESTION NO: 3 Oracle supports all of the following types of collections except for which one?

A. VARRAYS B. Nested array C. Nested table D. Associative array Answer: B

QUESTION NO: 20 Which of the following terms is used for the declaration section of the named-block programs?

A. Wrapper B. Header C. Declarer D. Cursor Answer: B

QUESTION NO: 57 Which of the following statements is true about the code given below?

IF x > y THEN high := x

QUESTION NO: 44 In which of the following naming methods are the connect identifiers stored in a centralized LDAPcompliant directory server including Oracle Internet Directory and Microsoft Active Directory?

A. Oracle Names B. Host naming C. Local naming D. External naming E. Directory naming Answer: E

QUESTION NO: 17 Which of the following clauses indicates that a function is cached only once in the SGA and is available across sessions?

A. PIPELINED B. PARALLEL_ENABLE C. RESULT_CACHE D. DETERMINISTIC Answer: C

QUESTION NO: 69 Which of the following methods always equals LAST for varrays?

A. PRIOR B. COUNT C. LIMIT D. END Answer: B

QUESTION NO: 36 Which of the following is another name for an unnamed program unit in PL/SQL?

A. Pseudonymous block B. Eponymous block C. Exotic block D. Anonymous block Answer: D

QUESTION NO: 63 Which of the following attributes is used to return the number of rows changed by a DML statement or the number of rows returned by the SELECT INTO statement?

A. %ISOPEN B. %NOTFOUND C. %ROWCOUNT D. %FOUND Answer: C

QUESTION NO: 10 From which of the following programming languages are the features of PL/SQL programming fetched?

A. C++ programming B. Java programming C. C programming D. Ada programming Answer: D

QUESTION NO: 9 Which of the following built-in packages supports dynamic SQL?

A. DBMS_SQL B. DBMS_SCHEDULER C. DBMS_MONITOR D. DBMS_CRYPTO Answer: A

QUESTION NO: 85 Which of the following statements is NOT true about dynamic SQL?

A. Dynamic SQL enables a user to write programs that reference SQL statements whose full text is not known until runtime. B. Dynamic SQL allows users to execute data definition language (DDL) statements and other SQL statements that are not supported in purely static SQL programs. C. Dynamic SQL programs can handle changes in data definitions, with the need to recompile. D. Dynamic SQL allows users to write reusable code because the SQL can be easily adapted for different environments. Answer: C

QUESTION NO: 64 Which of the following sections is mandatory in the anonymous block in PL/SQL?

A. Exception B. Declaration C. Execution D. Definition Answer: C

QUESTION NO: 2 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 Answer: B

QUESTION NO: 6 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 Answer: D

QUESTION NO: 76 Which of the following subprograms physically writes all pending output to a file?

A. FSEEK B. FGETPOS C. FRENAME D. FFLUSH Answer: D

QUESTION NO: 13 Which of the following is a sequentially control statement?

A. GOTO B. LOOP C. EXIT D. CASE Answer: A

QUESTION NO: 91 For which of the following types of triggers can the BEFORE and AFTER options NOT be used?

A. INSTEAD OF trigger B. Row trigger C. Statement trigger D. System event trigger E. DDL trigger F. DML trigger Answer: A

QUESTION NO: 77 In which of the following clauses should a dynamic SQL statement be placed if it is a DML with input-only placeholders?

A. INTO B. USING C. BULK COLLECT INTO D. RETURNING INTO Answer: B

QUESTION NO: 35 In which of the following places is the record variable NOT allowed?

A. In the VALUES clause of an INSERT statement. B. In the INTO subclause of a RETURNING clause. C. On the right side of the SET clause in an UPDATE statement. D. In a SELECT list. Answer: D

END IF

A. It will give a compilation error. B. It will generate an infinite loop. C. It will terminate the program. D. It will execute successfully. Answer: D

QUESTION NO: 56 Which of the following is the return result from the SQL SELECT statement?

A. Iterative structure B. Nested structure C. Conditional structure D. Cursor structure Answer: D

QUESTION NO: 65 Which of the following processes of the Oracle database server is used for batch processing?

A. Job Queue Process B. Log Writer Process C. Process Monitor Process D. Database Writer Process Answer: A

QUESTION NO: 46 Which of the following collection methods returns the lowest subscript value that is used in a collection?

A. LAST method B. NEXT method C. FIRST method D. PRIOR method Answer: C

QUESTION NO: 96 Which of the following functions returns the SQL function code for the statement?

A. LAST_ROW_ID B. LAST_ERROR_POSITION C. LAST_ROW_COUNT D. LAST_SQL_FUNCTION_CODE Answer: D

QUESTION NO: 33 Which of the following PL/SQL built-in functions is used to find the position where a substring starts in a string?

A. LPAD B. INITCAP C. INSTR D. CHR Answer: C

QUESTION NO: 51 Which of the following correctly represents the if-then-else conditional statement?

A. Multiple branching statement without fall-through B. Single branching statement C. Single branching statement with fall-through D. Multiple branching statement with fall-through Answer: B

QUESTION NO: 92 Which of the following datatypes makes PL/SQL code to perform better under Real Native Compilation?

A. NATIVE_INTEGER B. SIMPLE_ INTEGER C. PLS_INTEGER D. BINARY_ INTEGER Answer: B

QUESTION NO: 14 Which of the following cannot be created and stored in the database?

A. NESTED TABLE B. VARRAY C. RECORD D. ASSOCIATIVE ARRAY Answer: C

QUESTION NO: 23 Which of the following errors is raised when placeholders are put inside the overriding signature of an INSERT statement?

A. ORA-01006 B. ORA-06502 C. ORA-00928 D. PLS-00049 Answer: C

QUESTION NO: 12 Which of the following errors maps to the INVALID_CURSOR predefined exception in PL/SQL?

A. ORA-10100 B. ORA-10001 C. ORA-01000 D. ORA-01001 Answer: D

QUESTION NO: 24 Which of the following is the first step in using a cursor?

A. Open the cursor B. Fetch from the cursor C. Define the cursor D. Close the cursor Answer: C

QUESTION NO: 15 In which of the following naming methods are the connect identifiers stored in a localized configuration file named tnsnames.ora?

A. Oracle Names B. Directory naming C. Local naming D. External naming E. Host naming Answer: C

QUESTION NO: 25 All of the following statements about the RAISE_APPLICATION_ERROR procedure are true except for which one?

A. RAISE_APPLICATION_ERROR is used to create a unique id for a user-defined exception. B. RAISE_APPLICATION_ERROR raises an exception and handles it. C. RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the userdefined error messages along with the error number. D. Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous transactions which are not committed within the PL/SQL block are rolled back automatically. Answer: B

QUESTION NO: 47 Which of the following data types is NOT available for session variables?

A. REFCURSOR B. NUMBER C. CHAR D. VARRAY Answer: D

QUESTION NO: 5 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 Answer: B

QUESTION NO: 39 Which of the following PL/SQL functions is used to remove a set of characters from the beginning of a string?

A. RTRIM B. LTRIM C. RPAD D. LPAD Answer: B

QUESTION NO: 11 Which of the following is ideal for querying a database table or view?

A. Range FOR Loop statement B. Cursor FOR Loop statement C. While Loop statement D. Simple Loop statement Answer: B

QUESTION NO: 67 What are the two composite generalized data types?

A. Records and BFILE B. TIMESTAMP and CLOB C. Records and collections D. BLOB and CLOB Answer: C

QUESTION NO: 61 All of the following are security-related changes to DBMS_SQL, introduced in Oracle Database 11g to guard against a program that uses DBMS_SQL and raises an exception, except for which one?

A. Rejection of a DBMS_SQL operation when the current user attempting to use the cursor has changed from the user that opened the cursor B. Restriction of the use of the DBMS_SQL package whenever an invalid cursor number is passed to a DBMSSQL program C. Generation of unpredictable, probably randomized, cursor numbers D. Deletion of the DBMS_SQL package whenever the DBMS_SQL program encounters an error Answer: D

QUESTION NO: 66 Which of the following statements is NOT true about cursor attributes?

A. The %ISOPEN cursor attribute is always TRUE for SQL cursors. B. Cursor attributes return information about the execution of DML and DDL statements. C. The value of the cursor attributes always refer to the most recently executed SQL statement. D. The %NOTFOUND cursor attribute is not useful in combination with the SELECT INTO statement. Answer: A

You work as a Database Administrator for Hitech Inc. The company uses Oracle as its database. The database contains a table named Employee, which in turn have a view named ename. There is some issue with the ename view. As a DBA, you need to repair and compile the view. You issue the ALTER view ename COMPILE statement, however, the compilation failed. Which of the following will you use to accomplish the task? Each correct answer represents a part of the solution. Choose two.

A. The SHOW ERROR command B. The DESC command C. The DBA_OBJECTS view D. The DBA_DEPENDENCIES view Answer: B,D

QUESTION NO: 54 What happens when an implicit or explicit cursor is run and no data is found?

A. The program terminates. B. The implicit or explicit is re-declared. C. An exception is raised. D. No error is raised. Answer: D

QUESTION NO: 30 All of the following statements are true about an exception except for which one?

A. The scope of an exception declared within a block is global for the block and local for all of its sub blocks. B. The sub-block can refer to the global exception only when the exception name is qualified with the block label (block_label.exception_name). C. When a global exception is declared again within a sub-block, the local declaration takes priority over the global declaration. D. An exception cannot be declared more than once in a single block. Answer: A

QUESTION NO: 83 Which of the following clauses of a trigger is used to determine the triggering event?

A. UPDATE B. AFTER C. INSTEAD OF D. BEFORE Answer: A

QUESTION NO: 88 Which of the following packages is replaced by the UTL_MAIL package in Oracle 11g to allow email to be sent from PL/SQL?

A. UTL_FTP B. UTL_FILE C. UTL_SMTP D. UTL_COMPRESS Answer: C

QUESTION NO: 18 Which of the following is another name for bind variable?

A. User-defined variable B. System variable C. Session-level variable D. Default variable Answer: C

QUESTION NO: 68 Which of the following is used to reference a field in a record?

A. field_name B. field_name.record_name C. record_name D. record_name.field_name Answer: D

QUESTION NO: 1 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-endifD. if-then-else and if-then-elseif-then-else Answer: D

QUESTION NO: 86 In which of the following three categories does the PL/SQL binding operation not fall into?

A. in-bind B. predefine C. out-bind D. define Answer: B

QUESTION NO: 22 All of the following statements are true about an explicit cursor except for which one? A. An explicit cursor can be declared in any declaration section of a PL/SQL block.

B. A cursor name is not a PL/SQL variable. C. The name of an explicit cursor can be up to 30 characters in length. D. Values can be assigned to a cursor. Answer: D