1Z0-149 PL/SQL Exam Topics PDF

EXAMTOPICS 1Z0-149 1- Which two PL/SQL elements can be deprecated using the DEPRECATE pragma? (Choose two.) ​ A. PACKAGES ​ B. VARIABLES ​ C. ANONYMOUS BLOCK ​ D. TRIGGER BODY ​ E. DATABASE LINKS 2- Which three are true about functions and procedures? (Choose three.)...

EXAMTOPICS 1Z0-149 1- Which two PL/SQL elements can be deprecated using the DEPRECATE pragma? (Choose two.) ​ A. PACKAGES ​ B. VARIABLES ​ C. ANONYMOUS BLOCK ​ D. TRIGGER BODY ​ E. DATABASE LINKS 2- Which three are true about functions and procedures? (Choose three.) ​ A. The ACCESSIBLE BY clause can be used only for procedures. ​ B. In a function, every execution path must lead to a RETURN statement. ​ C. Both can have only constants as actual parameters for IN mode parameters. ​ D. Both can be invoked from within SQL statements. ​ E. In a procedure the RETURN statement cannot specify an expression. ​ F. In a function every RETURN statement must specify an expression. ​ 3- Which two are true about Conditional Compilation in PL/SQL using $IF, $ELSE, $END, and $ERROR? (Choose two.) ​ A. PL/SQL code can be compiled and executed based on different versions of the operating system. ​ B. PL/SQL code can be compiled and executed based on different versions of Oracle. ​ C. It is newer syntax that works the same way as 'IF , ELSEIF , ELSE, and END IF'. ​ D. Conditional compilation is disabled by default. ​ E. The PL/SQL compiler can conditionally include selected parts of a program. ​ 4- Which three are true about the NOCOPY hint, the PARALLEL ENABLE hint, and the DETERMINISTIC clause? (Choose three.) ​ A. The PARALLEL_ENABLE clause can be used only in the CREATE FUNCTION statement. ​ B. The NOCOPY hint asks the compiler to pass the actual parameters by reference. ​ C. A deterministic function's results always depend on the state of session variables. ​ D. The NOCOPY hint asks the compiler to pass the actual parameters by value. ​ E. A function is deterministic if it always returns the same result for a specific combination of input values. ​ F. The PARALLEL_ENABLE clause can be specified for a nested function. ​ G. A function defined with the PARALLEL_ENABLE clause may be executed in parallel in a SELECT statement or a subquery in a DML statement. 5- Which two are true about INDEX-BY tables? (Choose two.) ​ A. The index can be integer or string. ​ B. The index can be integer only. ​ C. INDEX-BY table types can be created in PL/SQL blocks only. ​ D. INDEX-BY table types can be created both with the CREATE TYPE statement and in PL/SQL blocks. ​ E. INDEX-BY table types can be created with the CREATE TYPE statement. 6- Which three are true about anonymous blocks and subprograms? (Choose three.) ​ A. Named subprograms cannot be called from other packages. ​ B. PROCEDURE subprograms can accept parameters. ​ C. A FUNCTION subprogram must return one or more values. ​ D. Anonymous blocks cannot use packaged variables. ​ E. Named subprograms are stored in the database server. ​ F. Anonymous blocks must always start with the Declare keyword.  ​ G. FUNCTION subprograms must be called and passed through one or more parameters. 7- Which two statements are true about using the OR REPLACE clause when creating named subprograms? (Choose two.) ​ A. Function based indexes remain usable when replacing the function on which the index depends. ​ B. Object privileges to execute a replaced function must be regranted to those users who had the privilege. ​ C. This clause can be used only for procedures and functions. ​ D. A function definition can be modified without dropping and re-creating it. ​ E. Object privileges to execute a replaced function are retained by those users who had the privileges. 8- Examine the SH.PRODUCTS table: A row exists in SH.PRODUCTS with PDT_ID = 1. Now, examine this code and output executed by SH Now, examine this block of code: Which error message(s) does it display on execution by user SH? ​ A. Error in inner block ​ B. Error in inner block Error in outer block ​ C. Error in inner block Error in calling block ​ D. Error in inner block Error in outer block Error in calling block 9- Which two blocks of code display a numerical zero? (Choose two.) ​.A ​ B.  ​ C. ​ D. 10- Which three are valid PL/SQL variable names? (Choose three.) ​ A. printer_name# ​ B. 1to7number ​ C. yesterday's_date ​ D. leap$year ​ E. Number_of_days_between_March_and_April ​ F. #printer_name ​ G. v_fname 11- Which two are true about collections and RECORD types? (Choose two.) ​ A. A variable of RECORD type can contain fields of another RECORD type or any collection type. ​ B. Only associative arrays and nested tables can have elements of RECORD type. ​ C. All collections and RECORD types can be defined in PL/SQL blocks, packages, or at the schema level. ​ D. Collections and RECORD types are always dense. ​ E. All collections and RECORD types can be stored in table columns. ​ F. VARRAYS, nested tables and each field in %ROWTYPE type variables have a default value of null. 12- Examine this table in the SH schema: User SH executes this code: The program must terminate with a user-defined message and no rows displayed if more than one product's price is 1000. With which option must “---placeholder” be replaced?  ​ A. ​ B. ​ C. ​ D. ​ E. 13- Examine this row of data from the EMPLOYEES table: Now, examine this block of code which executes successfully: What is the value of v_commission? ​ A. 5000 ​ B. 15000 ​ C. 2500 ​ D. 10000 14- Examine this table in the SH schema: Now, examine this code: Which two changes are required to ensure that PDT_REPORT executes successfully? (Choose two.) ​ A. In line 1, change IN OUT mode to IN mode. ​ B. In line 2, change IN OUT mode to IN mode. ​ C. In line 3, replace CUR_PRICE with P_PDT_PRICE in the query condition. ​ D. In line 1, add the default parameter DEFAULT 2000. ​ E. In line 6, replace P_PDT_PRICE parameter name with CUR_PRICE. ​ F. In line 2, add the default parameter DEFAULT 2000. 15- User ORA41 executes these statements successfully: Now, examine this statement which is executed successfully by user ORA61 after a successful login: EXECUTE ora41.update_emp_proc(100,25000); Which two are true? (Choose two.) ​ A. The salary will be changed for employee 100 in the EMPLOYEES table owned by ORA41. ​ B. No update happens even though the procedure executes successfully. ​ C. The salary will be changed for employee 100 in the EMPLOYEES table owned by ORA61. ​ D. The UPDATE privilege on ORA41.EMPLOYEES is not inherited by ORA61 through the procedure. ​ E. ORA61 will have been granted the UPDATE privilege explicitly on ORA41.EMPLOYEES before executing the statement. 16- Which two are true about exception handling? (Choose two.) ​ A. Internally defined exceptions can be handled only by the OTHERS exception handler. ​ B. All declared exceptions are raised implicitly by the runtime system. ​ C. User-defined exceptions can be defined in the declarative part of any PL/SQL anonymous block, subprogram, or package. ​ D. Only predefined exceptions and user-defined exceptions can have a user-declared name associated with them.  ​ E. Predefined exceptions are globally declared in the standard package. 17- Examine these statements: Which is true? ​ A. It will result in a compilation error for protected_proc because calling_proc does not exist. ​ B. It will result in a compilation error for protected_proc because calling_proc must be prefixed with the schema name. ​ C. It will result in a successful compilation because objects referenced in an ACCESSIBLE BY clause are not checked at compile time. ​ D. With adequate privileges, PROTECTED_PROC procedure can be called by other programs apart from CALLING_PROC. 18- Examine the EMPLOYEES table structure: Now, examine this code: Which statement is true about the result of executing this block? ​ A. It will execute successfully provided the salary of EMP_ID 200 does not exceed the value 99999. ​ B. It will return an error at line 2. ​ C. It will return an error at line 3. ​ D. It will return an error at line 8. ​ E. It will execute successfully by rounding up the salary of EMP_ID 200 to the appropriate value. 19- Sequence S and table PRODUCTS exist in your schema. Examine the table description: Now, examine this block of code: Which two lines each result in a compilation error? (Choose two.) ​ A. line 1 ​ B. line 6 ​ C. line 8 ​ D. line 2 ​ E. line 3 ​ F. line 7 20- For which three SYSTEM EVENTS can triggers be created? (Choose three.) ​ A. DDL ​ B. AFTER AUDIT ​ C. BEFORE ANALYZE ​ D. SHUTDOWN ​ E. SERVERERROR ​ F. STARTUP ​ G. BEFORE GRANT 21- Which three statements are true about Implicit Cursor and Explicit Cursor? (Choose three.) ​ A. Implicit cursor returns only one record. ​ B. Explicit cursor can return more than one record. ​ C. %Isopen, %Rowtype, %Notfound, and %Found are the attributes of Explicit Cursor. ​ D. %Isopen, %Rowcount, %Notfound, and %Found are the attributes of Explicit Cursor. ​ E. %Isopen, %Type, %Notfound, and %Found are the attributes of Explicit Cursor. ​ F. %Isopen is always false in Explicit Cursor. ​ G. %Isopen is always false in Implicit Cursor. 22- Which three are true about DDL triggers? (Choose three.) ​ A. They cannot include the WHEN clause. ​ B. They must be created in an enabled state. ​ C. They can be fired when a table is truncated. ​ D. They fire only when a DDL statement is executed by the owner of the trigger. ​ E. They can be fired either before or after a DDL statement executes. ​ F. They can be fired when a privilege is granted to a user. ​ G. They must be created in a disabled state. ​ 23- Which code will successfully create a BODILESS PACKAGE to standardize CONSTANTS and EXCEPTIONS declarations? ​ A. ​ B. ​ C.  ​ D. 24- In which type of trigger can :OLD and :NEW identifiers be used? ​ A. ROW ​ B. AFTER SUSPEND ​ C. AFTER STATEMENT ​ D. BEFORE STATEMENT 25- Which three are true about PL/SQL subprograms? (Choose three.) ​ A. Results of a subprogram can be cached in the SGA such that sessions connected to the same instance can reuse these results when available. ​ B. Users granted execute privilege on a procedure compiled with definer's rights require grants to access objects belonging to the definer that are referenced in the procedure. ​ C. Subprograms are cached by default and shared among users, thereby reducing memory requirements. ​ D. Reuse of parsed PL/SQL code from the shared SQL area reduces parsing overhead. ​ E. A subprogram's session state is retained even if any of the session’s instantiated subprograms are invalidated and revalidated. ​ F. Host variables can be referenced inside any PL/SQL subprogram. ​ G. A PL/SQL procedure can invoke an external code block written in a different programming language. 26- Which three PL/SQL-only data types can be used in queries and native dynamic SQL issued from PL/SQL in the server? (Choose three.) ​ A. a record declared in an anonymous block ​ B. a record declared in a procedure ​ C. an associative array indexed by PLS_INTEGER ​ D. a record declared in a package specification ​ E. a predefined PL/SQL-only data type like BOOLEAN ​ F. an associative array indexed by VARCHAR2 27- Which three are true about user-defined functions? (Choose three.) ​ A. They can be used in ORDER BY and GROUP BY clauses. ​ B. They can be executed as standalone commands. ​ C. They must be defined with at least one parameter. ​ D. They need not return any values. ​ E. They can appear in the select list of a SELECT statement. ​ F. Functions can call only other functions. ​ G. They can be used in CONNECT BY and START WITH clauses. 28- Which two are true about implicit data type conversion? (Choose two.) ​ A. RAW data types are always implicitly converted to a CLOB when used in a query. ​ B. Collections can be implicitly converted to records. ​ C. ROWIDS are always implicitly converted to a number when used in a query. ​ D. Comparison between character value and a number value always implicitly converts the character value to the number data type. ​ E. Implicit data type conversion can negatively impact performance. 29- Examine these facts: Table EMP exists in schema USERA with columns SALARY and EMP_ID. EMP_ID is the primary key with values ranging from 1 to 100. USERA now executes these statements successfully: USERA then grants execute privilege on procedure MYPROC to USERB. USERB exists in the database identified by pdb1 but does not have select privilege on USERA.EMP. USERB now executes these statements: conn userB/userB@pdb1 execute userA.myproc; Which is true? ​ A. It results in an error because Authid Current_User is missing from MYPROC. ​ B. It results in an error because Authid Definer is missing from MYPROC. ​ C. It results in an error because USERB doesn't have select privilege on USERA.EMP. ​ D. It executes successfully. 30- Which two are true about named notations in a PL/SQL subprogram? (Choose two.) ​ A. The actual parameters must be specified in the same order as the formal parameters are declared. ​ B. Specifying actual parameters in the wrong order results in the subprogram terminating with an exception. ​ C. Only trailing optional parameters can be omitted in the invocation call.  ​ D. The subprogram invocations must be changed when the formal parameter list acquires new required parameters. ​ E. Any optional parameters can be omitted in the invocation call. ​ 31- Examine this DECLARE section: Which line will cause an error upon execution? ​ A. line 5 ​ B. line 3 ​ C. line 2 ​ D. line 4 ​ E. line 6 32- Examine this DECLARE section: Which two lines are valid? (Choose two.) ​ A. line 5 ​ B. line 6 ​ C. line 7  ​ D. line 4 ​ E. line 3 ​ F. line 2 33- Which is the correct method to implement a local subprogram in an anonymous block? ​ A. ​ B. ​ C.  ​ D. 34- Which three statements are true about passing parameters to subprograms? (Choose three.) ​ A. PL/SQL assigns values to actual parameters in subprograms with unhandled exceptions. ​ B. IN parameters passed to subprograms act like constants, to which values cannot be assigned by the subprogram. ​ C. IN OUT parameters pass initial values to subprograms and return values updated by subprograms to the caller. ​ D. The actual parameter must be a variable when calling a subprogram with an OUT parameter. ​ E. IN parameters passed to subprograms act like variables, to which values can be assigned by the subprogram. ​ F. OUT parameters returning values to calling subprograms act like constants in the called subprogram. ​ G. Actual parameters corresponding to IN OUT formal parameters can be constants or expressions. 35- Examine these statements: Which two are true? (Choose two.) ​ A. Neither table will have a row inserted and committed. ​ B. Both tables will have a row inserted and committed. ​ C. The transaction for the bank_activity_proc is independent of the bank_transaction_proc. ​ D. The bank_activity_proc will not compile because of the commit. ​ E. Only one table will have a row inserted and committed. 36- Which two are true about the PLSQL_CODE_TYPE parameter? (Choose two.) ​ A. Changing the parameter setting automatically changes the setting for existing PL/SQL library units. ​ B. The default value is NATIVE. ​ C. If set to NATIVE, programs are stored in platform dependent machine code. ​ D. It can use the REUSE SETTINGS clause to recompile a program unit without changing to the current session settings. ​ E. If set to NATIVE, programs are stored in a PL/SQL bytecode format. 37- Examine this anonymous block of code: Which two statements are true about the results of executing it? (Choose two.) ​ A. It will set all salaries to 0 if it executes successfully. ​ B. It will always return a compile time error because it lacks an EXCEPTION section. ​ C. It might return a run time error depending on who invokes it. ​ D. It will always automatically initialize v_raise. ​ E. It will set all salaries to NULL if it executes successfully. ​ F. It will always return a run time error because v_raise is not initialized. 38- Examine this table definition in the SH schema. A row with PDT_ID = 1 exists. Which two blocks of code will execute successfully by user SH and give the same output? (Choose two.) ​ A. ​ B. ​ C.  ​ D. 39- Which three SQL statements, embedded in PL/SQL, typically benefit from using variables declared with %ROWTYPE? (Choose three.) ​ A. CREATE ​ B. DROP ​ C. UPDATE ​ D. SELECT ​ E. DELETE ​ F. ALTER ​ G. INSERT 40- Which is true about EXIT and CONTINUE statements? ​ A. They can be used in any type of loop. ​ B. They must have a WHEN condition. ​ C. They have the same effect on the execution of a loop. ​ D. They must use labels. 41- Examine this statement which is submitted for compilation: Which three are true? (Choose three.) ​ A. This is a PACKAGE specification. A PACKAGE BODY is needed to use this. ​ B. This will not compile successfully because the loan_amount variable is declared NOT NULL but lacks an initialization assignment. ​ C. This program unit will compile successfully. ​ D. Initialization of min_bal can be done while using this packaged constant in another program. ​ E. Initialization of loan_amount can be done while using this packaged variable in another program. ​ F. This will not compile successfully because the min_bal constant must be initialized. ​ G. This is BODILESS PACKAGE. A PACKAGE BODY is not required to use this. 42- Which statement is true about user-defined records? ​ A. They can be returned from a function. ​ B. Field types must match column types. ​ C. The number of fields must match the number of columns in a table. ​ D. Field names must match selected column names. 43- The SH schema contains the PRODUCTS table with column PDT_NAME defined as VARCHAR2(10). Which two blocks of code execute successfully when invoked by user SH? (Choose two.) ​ A. ​ B. ​ C. ​ D.  ​ E. 44- Examine the structure of the ora1.depts table: Now, examine these statements issued by user ora1 which execute successfully: Create or replace view dep_vu as select * from depts; Alter table depts add dep_email varchar2(20); Finally, examine this block of code executed by user ora1: Which is true? ​ A. DEP_VU must be manually recompiled to successfully run this code. ​ B. It will run successfully producing a result of 4. ​ C. It will result in an error because table depts has been altered. ​ D. It will run successfully producing a result of 5. 45- Examine this code: ALTER SESSION SET plsql_warnings='ENABLE:ALL'; / You compile this function: What happens when the function is created with PLSQL_WARNINGS set to 'ENABLE: ALL'? ​ A. It fails compilation. ​ B. There are no compilation warnings or errors. ​ C. A severe compilation warning is generated. ​ D. A performance compilation warning is generated. ​ E. An information compilation warning is generated. 46- Which is true about the PLSCOPE_SETTINGS parameter? ​ A. It is deprecated in Oracle 12c. ​ B. It can be used to obtain information about all identifiers when compiling a procedure. ​ C. It can be used to control execution of specific portions of the PL/SQL code conditionally. ​ D. It can be used to control a user's privileges on PL/SQL objects at run time. 47- Which three statements can process a dynamic multi-row query? (Choose three.) ​ A. DECLARE ​ B. OPEN ​ C. INTO ​ D. OPEN-FOR ​ E. FETCH ​ F. WHEN ​ G. CLOSE 48- Which is true about counter variables in a FOR loop? ​ A. It must explicitly be declared. ​ B. It can be modified in the body of the loop. ​ C. It cannot be NULL. ​ D. It is accessible outside the body of the loop. 49- In one of your databases, table HR.EMPLOYEES includes the columns FIRST_NAME and EMPLOYEE_ID. A row exists with EMPLOYEE_ID 201. Examine these packages created by user HR: EXECUTE privilege is granted to user SH, on the HR.API and HR.HELPER packages. Which two will execute successfully? (Choose two.) ​ A. Call HR.HELPER.H1 from the HR schema. ​ B. Call HR.HELPER.H1 from the SH schema. ​ C. Call HR.API.P1 from the HR schema. ​ D. Call HR.API.P1 from the SH schema. ​ E. Create and call a package procedure API.H1 in the SH schema, which calls HR.HELPER.H1. 50- SERVEROUTPUT is enabled. Which is the correct method to use a PACKAGED CONSTANT in SELECT statements? ​ A. ​ B.  ​ C. ​ D. 51- Which three are true about the procedure overloading feature? (Choose three.) ​ A. Each procedure can be a nested subprogram. ​ B. Each procedure's formal parameters can differ in data type or name. ​ C. Each procedure must use named notation to specify the corresponding actual parameters. ​ D. Each procedure's formal parameters must differ in name. ​ E. Each procedure can be a packaged subprogram. ​ F. Each procedure must use positional notation to specify the corresponding actual parameters. ​ G. Each procedure can be a standalone subprogram. 52- Which two are true about packages? (Choose two.) ​ A. Variables and cursors defined in a package specification are visible to all subprograms in the same schema that has the package. ​ B. Modifications to a packaged procedure's body automatically cause recompilation of subprograms that invoke the procedure. ​ C. Package specifications can be compiled without their bodies. ​ D. Standalone subprograms that reference a package can be compiled only after both the package specification and body are compiled. ​ E. A package definition must have a specification and body. 53- Which two statements are true about the RETURNING clause when used with DML? (Choose two.) ​ A. When using the RETURNING INTO clause, the data returned can be only single column or expression. ​ B. The RETURNING INTO clause and bulk binds may not be used together. ​ C. The RETURNING INTO clause can be used with DML that affects multiple rows, if the BULK COLLECT clause is used. ​ D. The RETURNING INTO clause returns column values for rows affected by DML statements. ​ E. The RETURNING clause can be used for remote or parallel deletes. 54- Examine this statement which executes successfully: SQL> SET SERVEROUTPUT ON; Now, examine this code which is executed: What is true about the result? ​ A. It returns an error in line 2. ​ B. It returns an error in line 4. ​ C. It returns an error in line 9. ​ D. It executes and displays output. 55- Which two are true about lexical units? (Choose two.) ​ A. Character literals are case-sensitive. ​ B. Spaces can be embedded in delimiters. ​ C. Identifiers include labels. ​ D. A character literal with zero characters has the BOOLEAN value NULL. ​ E. All user-defined identifiers must start with an alphabet. 56- Which three are true regarding code based access control (CBAC)? (Choose three.) ​ A. In a multitenant environment, the DELEGATE option of CBAC cannot be used. ​ B. CBAC roles can be granted to a program unit only if they are directly granted to its owner. ​ C. CBAC roles can be granted to a program unit only if they are the predefined roles automatically defined by the standard scripts as part of database creation. ​ D. You can use CBAC to attach database roles to a PL/SQL function or procedure only. ​ E. In CBAC, the ADMIN and DELEGATE options cannot both be granted to the same user. ​ F. You can use CBAC to attach database roles to a PL/SQL function, procedure, or package. ​ G. CBAC cannot be used to secure definer's rights. 57- Examine this code: What will be the outcome? ​ A. It will execute successfully and will display the user-defined error message. ​ B. It will result in an error as the range of the error code can only be from “-20000 to -20999.” ​ C. It will result in an error as the range of the error code can only be from “-1000 to -2000.” ​ D. It will result in an error as the range of the error code can only be from “-2000 to -2999." 58- Which block of code displays the error message “Incorrect price value”? ​ A. ​ B. ​ C.  ​ D. 59- Examine these statements from a block of code: Which two are true? (Choose two.) ​ A. The lock acquired when executing the OPEN c1 command will be released only after a COMMIT or ROLLBACK statement is issued. ​ B. Only the PRICE column can be updated in the PRODUCTS table. ​ C. The FOR UPDATE OF clause can be used only if the WHERE CURRENT OF clause is used in the executable part of the block. ​ D. The WHERE CURRENT OF clause can be used only if the FOR UPDATE clause is used in the cursor definition. ​ E. An OPEN c1 command will acquire a lock only on the PRICE column in the PRODUCTS table. 60- Examine these statements issued by user SH which execute successfully: DBMS_OUTPUT.PUT_LINE(p_price(i)); END LOOP; END; END products_pkg; / Now, examine this anonymous block executed by SH: Which is true about the anonymous block? ​ A. It will execute successfully only if PriceList is removed from the DECLARE section and defined as a standalone collection type. ​ B. It will execute successfully only if PriceList is defined as an associative array in the package and anonymous block. ​ C. It will fail at lines 6 and 7. ​ D. It will fail only at line 7. 61- SERVEROUTPUT is enabled. Which code block will display the values from 1 to 10 in descending order? ​ A. ​ B. ​ C. ​ D. 62- Examine these statements and output: The procedure is created successfully. User ora2 has password ora2 in pdb1. Which script will execute successfully? ​ A. ​ B.  ​ C. ​ D. 63- Examine these statements which execute successfully: Which anonymous block executes successfully? ​ A. ​ B.  ​ C. ​ D. 64- Which two are valid MODIFIER values for the PLSQL_WARNINGS parameter? (Choose two.) ​ A. DISABLE ​ B. ENABLE ​ C. ERROR ​ D. ALL ​ E. SEVERE 65- Which two are true about using the ACCESSIBLE BY clause? (Choose two.) ​ A. It can be used in the declaration of object types. ​ B. It must be specified in the heading of a package specification. ​ C. The check is enforced by this clause for direct access and access through dynamic SQL. ​ D. It can be used for individual procedures and functions declared in a package specification. ​ E. It must be specified in the heading of a package body.