PL SQL Oracle 1z0-144 Practice Exam questions and answers 2024

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 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: 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: 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: 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: 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: 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. D. Oracle trace file 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: 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: 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: 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: 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: 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: 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: 13 Which of the following is a sequentially control statement? A. GOTO B. LOOP C. EXIT D. CASE Answer: A 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: 15 In which of the following naming methods are the connect identifiers stored in a localized configuration file named ? A. Oracle Names B. Directory naming C. Local naming D. External naming E. Host naming 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: 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: 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: 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: 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: 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: 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 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: 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: 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: 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: 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: 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: 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: 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_tion_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: 31 All are lexical units except which of the following? A. Comments B. Variables C. Literals D. Identifiers E. Delimiters Answer: B 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: 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: 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: 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 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: 37 Which of the following is NOT a supported transaction control statement? A. SAVEPOINT B. ROLLBACK C. COMMIT D. CHECKPOINT Answer: D 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: 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: 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: 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: 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: 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: 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: 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: 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: 48 Which of the following symbols denotes PL/SQL conditional compilation-time variable? A. & B. && C. $ D. $$ Answer: D 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: 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: 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: 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: 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: 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: 55 Which of the following correctly represents the name of a variable? A. _a B. 6a C. a$ D. a $ Answer: C 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: 57 Which of the following statements is true about the code given below? IF x > y THEN high := x 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: 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: 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: 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: 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: 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: 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: 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: 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: 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 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: 68 Which of the following is used to reference a field in a record? A. field_name B. field_d_name C. record_name D. record__name Answer: D 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: 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: 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: 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: 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: 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 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: 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: 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: 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: 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: 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: 81 What is the maximum size of a trigger? A. 64KB B. 16KB C. 32KB D. 8KB 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 97 Which of the following functions returns an error code and a brief message? A. SQLCODE B. SQLERRM C. SQLMSG D. SQLERROR Answer: B See more Add or Remove Terms