PL/SQL 1Z0-144 Exam

Which statements correctly describe the features of functions and procedures? Choose all that apply. A. A procedure can contain a return statement without a value. B. A function can return multiple values using a single return clause. C. A procedure can be executed as part of a SQL expression or as a PL/SQL statement. D. A function can contain zero or more parameters that are transferred from the calling environment.

A. A procedure can contain a return statement without a value. D. A function can contain zero or more parameters that are transferred from the calling environment.

Which statement is true about transactions in PL/SQL? A. A transaction can span multiple blocks. B. A block can contain only a single transaction. C. SERVERPOINTS cannot be created in a PL/SQL block. D. The END keyword signals the end of a PL/SQL block and automatically commits the transaction in the block.

A. A transaction can span multiple blocks.

Which system events can be used to create triggers that fire both at database and schema levels? (Choose all that apply) A. AFTER LOGON B. AFTER STARTUP C. BEFORE SHUTDOWN D. AFTER SERVERERROR

A. AFTER LOGON D. AFTER SERVERERROR

Which two statements are true about the handling of internally defined or user-defined PL/SQL exceptions? (Choose two.) A. Add exception handlers whenever errors occur. B. An exception handler should commit the transaction. C. Handle named exceptions whenever possible instead of using when others in exception handlers D. Instead of adding exception handlers to your PL/SQL block, check for errors at every point where they may occur.

A. Add exception handlers whenever errors occur. C. Handle named exceptions whenever possible instead of using when others in exception handlers

View the Exhibit and examine the blocks of code that you plan to execute. Which statement is true about the blocks of code? CREATE OR REPLACE FUNCTION dflt RETURN NUMBER IS cnt NUMBER := 0; BEGIN cnt := cnt + 1; RETURN 45; END dflt; CREATE OR REPLACE PROCEDURE p(i IN NUMBER DEFAULT dflt()) IS BEGIN DBMS_OUTPUT.PUT_lINE(i); END p; DECLARE cnt NUMBER := dflt(); BEGIN FOR j IN 1..3 LOOP p(j); END LOOP; DBMS_OUTPUT.PUT_LINE('cnt: '||cnt); p(); DBMS_OUTPUT.PUT_LINE('cnt: '||cnt); END; A. All the blocks execute successfully and the anonymous block displays 1 2 3 cnt: 45 45 cnt: 45 B. All the blocks execute successfully and the anonymous block displays 1 2 3 cnt: 0 45 cnt: 1 C. The anonymous block gives an error because the function invocation in line 2 is not valid. D. The procedure creation gives an error because the function invocation in line 1 is not valid.

A. All the blocks execute successfully and the anonymous block displays 1 2 3 cnt: 45 45 cnt: 45

User SCOTT has been granted CREATE ANY TRIGGER AND ALTER ANY TABLE by the DBA. HR is an existing schema in the database. SCOTT creates the following trigger: CREATE OR REPLACE TRIGGER drop_trigger BEFORE DROP ON hr.SCHEMA BEGIN RAISE_APPLICATION_ERROR (-20000, 'Cannot drop object'); END; SCOTT does not grant the execute privilege on this trigger to any other users. For which user(s) would this trigger fire by default when they drop an object in the hr schema? A. Only HR B. SCOTT and HR C. Only SCOTT D. SCOTT, HR, and SYS

A. Only HR

Examine the following code: CREATE OR REPLACE FUNCTION increase (emp_num NUMBER) RETURN NUMBER IS inc_amt NUMBER; sal NUMBER; BEGIN SELECT salary INTO sal FROM employees WHERE employee_id = emp_num; inc_amt := sal * .10; RETURN inc_amt; END increase; CREATE OR REPLACE PROCEDURE calc_sal IS emp_num NUMBER(6) := 120; amt NUMBER := 0; PROCEDURE raise_salary (emp_id NUMBER) IS BEGIN amt := increase(emp_num); UPDATE employees SET salary = salary + amt WHERE employee_id = emp_id; END raise_salary; BEGIN raise_salry(emp_nmu); END calc_sal; A. Both blocks compile and execute successfully when called. B. Both blocks compile successfully but the CALC_SAL procedure gives an error on execution. C. The CALC_SAL procedure gives an error on compilation because the amt variable should be declared in the RAISE_SALARY procedure. D. The CALC_SAL procedure gives an error on compilation because the RAISE_SALARY procedure cannot call the stand-alone increase function.

A. Both blocks compile and execute successfully when called.

You execute the following block of code: DECLARE v_customer VARCHAR2(50) := 'Womansport'; v_credit_rating VARCHAR2(50) := 'EXCELLENT'; BEGIN DECLARE v_customer NUMBER(7) := 201; v_name VARCHAR2(25) := 'Unisports'; BEGIN v_credit_rating := 'GOOD'; DBMS_OUTPUT.PUT_LINE('Customer ' || v_customer || ' rating is ' || v_credit_rating); ENd; DBMS_OUTPUT.PUT_LINE('Customer ' || v_customer || ' rating is ' || v_credit_rating); END; What statement is true about the outcome? A. Both output statements show different values. B. Both output statements show exactly the same values. C. It gives an error because the nested blocks are labeled. D. It gives an error because the v_customer variable have different types in the nested blocks.

A. Both output statements show different values.

Examine the following trigger code: CREATE OR REPLACE TRIGGER max_credit_limit BEFORE INSERT OR UPDATE OF cust_category ON customer FOR EACH ROW WHEN (NEW.cust_category IS NULL) BEGIN IF INSERTING THEN :NEW.cust_category := 'C'; :NEW.cust_credit_limit := 8000; ELSIF UPDATING THEN :NEW.cust_category := OLD.cust_category; :NEW.cust_credit_limit := :OLD.cust_credit_limit; END IF; END; What is the outcome when the above trigger is compiled? A. It compiles successfully. B. It gives an error because the when condition is not valid. C. It gives an error because when cannot be used for row-level triggers. D. It gives an error because the statements under updating are not valid. E. It gives an error because the new qualifier in the when clause requires a colon prefix.

A. It compiles successfully.

Examine the following block of code: BEGIN BEGIN INSERT INTO employees(employee_id, first_name, last_name, email, hire_date, job_id, salary) VALUES (employees_seq.NEXTVAL, 'Ruth', 'Cores', 'RCORES', CURRENT_DATE, 'AD_AST', 6000); END; BEGIN INSERT INTO employees(employee_id, first_name, last_name, email, hire_date, job_id, salary) VALUES (employees_seq.NEXTVAL, 'Tom', 'Jones', 'TJONES', CURRENT_DATE, 'AD_MGR', 6000); END; END; Which statement is true about the above block of code? A. It consists of two transactions. B. It consists of a single transaction. C. The data is automatically committed after the block execution ends. D. It gives an error on execution because sequences cannot be used in anonymous blocks.

A. It consists of two transactions.

Examine the following code: CREATE TABLE debug_output (msg VARCHAR2(100)); CREATE OR REPLACE PROCEDURE debugging (msg VARCHAR2) AS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO debug_output VALUES (msg); COMMIT; END debugging; CREATE OR REPLACE PROCEDURE delete_details(p_id NUMBER) AS msg VARCHAR2(100); BEGIN DELETE FROM products WHERE prod_id = p_id; COMMIT; EXCEPTION WHEN OTHERS THEN msg := SUBSTR(sqlerrm, 100); debugging (msg); END delete_details; Which statement is true when the procedure DELETE_DETAILS is invoked? A. It executes successfully but no error messages get recorded in the DEBUG_OUTPUT table B. It executes successfully and any error messages get recorded in the DEBUG_OUTPUT table. C. It gives an error because PRAGMA AUTONOMOUS_TRANSACTION can be used only in packaged procedures. D. It gives an error because procedures containing PRAGMA AUTONOMOUS_TRANSACTION cannot be called from the exception section.

A. It executes successfully but no error messages get recorded in the DEBUG_OUTPUT table (The delete will not raise any exception, so debug_output will never be invoked.)

Examine the following code: DECLARE emp_num NUMBER96) := 120; sal NUMBER; FUNCTION increase (emp_num NUMBER) RETURN number IS inc_amt NUMBER; BEGIN SELECT salary INTO sal FROM employees WHERE employee_id = emp_num; inc_amt := sal * .10; RETURN inc_amt; END; PROCEDURE raise_salary (emp_id NUMBER) IS amt NUMBER; BEGIN amt := increase(emp_num); UPDATE employees SET salary = salary + amt WHERE employee_id = emp_id; END raise_salary; BEGIN raise_salary(emp_num); COMMIT; END; What would be the outcome when the code is executed? A. It executes successfully. B. It gives an error because the SAL variable is not visible in the increase function. C. It gives an error because the increase function cannot be called from the RAISE_SALARY procedure. D. It gives an error because the increase function and the RAISE_SALARY procedure should be declared at the beginning of the declare section before all the other declarations.

A. It executes successfully.

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

A. It should be an after trigger. D. It should be a statement-level trigger.

View the exhibit and examine the structure of the departments table in SCOTT's schema. Examine the following block of code: CREATE OR REPLACE PROCEDURE add_dept (p_id NUMBER, p_name VARCHAR2) IS BEGIN INSERT INTO departments VALUES (p_id, p_name, NULL, NULL); END; The above procedure is created by user SCOTT. Another user JONES needs to use the procedure. Which two statements are true in the above scenario? Choose two. View the Exhibit and examine the structure of the departments table in SCOTT's schema: A. JONES executes the procedure with definer's rights. B. JONES executes the procedure with invoker's rights. C. SCOTT should grant only the execute privilege for the procedure to JONES. D. SCOTT should grant both the EXECUTE privilege for the procedure and INSERT privilege for the table to JONES

A. JONES executes the procedure with definer's rights. C. SCOTT should grant only the execute privilege for the procedure to JONES.

Which two guidelines are recommended by Oracle to reduce invalidation of dependent objects? (Choose two.) A. Reference tables indirectly by using views. B. Reference tables directly avoid using views. C. When adding new items to a package, add them to the end of the package. D. When adding new items to a package, add them to the beginning of the package.

A. Reference tables indirectly by using views. C. When adding new items to a package, add them to the end of the package.

View the exhibit and examine the package code created by SCOTT. The execute privilege on this package is granted to GREEN. Examine the following sequence of commands issued by SCOTT: CREATE OR REPLACE PACKAGE pkg1 IS PRAGMA SERIALLY_REUSABLE; num NUMBER := 0; PROCEDURE init_pkg_state(n NUMBER); PROCEDURE print_pkg_state; END pkg1; CREATE OR REPLACE PACKAGE BODY pkg1 IS PRAGMA SERIALLY_REUSABLE; PROCEDURE init_pkg_State(n NUMBER) IS BEGIN pkg1.num := n; DBMS_OUTPUT.PUT_LINE('Num: ' || pkg1.num); END; PROCEDURE print_pkg_State IS BEGIN DBMS_OUTPUT.PUT_LINE('Num: ' || pkg1.num); END; END pkg1; SET SERVEROUTPUT ON EXEC pkg1.init_pkg_state(5) EXEC pkg1.print_pkg_state GREEN logs in and issues the following commands: SET SERVEROUTPUT ON EXEC scott.pkg1.print_pkg_state What is the outcome? A. SCOTT'S session displays 5, and then 0, green's session displays 0. B. SCOTT'S session displays 5, and then 0; green's session displays 5. C. SCOTT'S session displays 5, and then 5 again, green's session displays 0. D. SCOTT'S session displays 5, and then 5 again; green's session displays 5.

A. SCOTT'S session displays 5, and then 0, green's session displays 0.

Identify two features of obfuscation. (Choose two.) A. The Import and Export utilities accept wrapped files. B. SQL* Plus cannot process the obfuscated source files. C. Only the wrap utility can obfuscate multiple programs at a time. D. Both the DBMS_DDL package and the Wrap utility can obfuscate multiple programs at a time. E. The source code is visible only through the DBA_SOURCE view and not through the USER_SOURCE or ALL_SOURCE View

A. The Import and Export utilities accept wrapped files. C. Only the wrap utility can obfuscate multiple programs at a time.

Which three statements are true about wrapping? Choose three. A. The PL/SQL wrapper detects and reports only syntax errors. B. The PL/SQL wrapper detects and reports both syntax and semantic errors. C. When wrapping a package or object type, both the body and specification should be wrapped. D. When wrapping a package or object type, only the body should be wrapped, not the specification. E. To change a wrapped object, the original source code needs to be modified and then wrapped again. F. To change a wrapped object, the wrapped code can be unwrapped, modified in a text file, and then wrapped again.

A. The PL/SQL wrapper detects and reports only syntax errors. D. When wrapping a package or object type, only the body should be wrapped, not the specification. E. To change a wrapped object, the original source code needs to be modified and then wrapped again.

Examine the following code that you plan to execute: CREATE OR REPLACE PACKAGE p1 IS x NUMBER; PROCEDURE proc1; PROCEDURE proc2; END p1; CREATE OR REPLACE PACKAGE BODY p1 IS PROCEDURE proc1 IS BEGIN x := 1; END; PROCEDURE proc3 IS BEGIN DBMS_OUTPUT.PUT_LINE(x); END proc3; END p1; What correction should be performed in the above code? A. The PROC2 procedure code should be defined in the package body. B. The PROC3 procedure should be declared in the package specification. C. The PROC3 procedure header should be declared at the beginning of the package body. D. The variable x must be declared in the package body and removed from the specification

A. The PROC2 procedure code should be defined in the package body. B. The PROC3 procedure should be declared in the package specification.

Examine the following PL/SQL code: DECLARE CURSOR c_emp_cursor IS SELECT employee_id, last_name FROM employees WHERE department_id = 30; BEGIN FOR emp_record IN c_emp_cursor LOOP DBMS_OUTPUT.PUT_LINE(emp_record.employee_id||' '||emp_record.last_name); END LOOP; END; The server output is on for the session. Which statement is true about the execution of the code? A. The code executes successfully and gives the desired output. B. The code generates an error because the EMP_RECORD variable is not declared. C. The code generates an error because the cursor is not opened before the FOR loop. D. The code generates an error because the loop does not have the exit when clause.

A. The code executes successfully and gives the desired output.

Examine the following PL/SQL code: DECLARE v_lname VARCHAR2(15); BEGIN SELECT last_name INTO v_lname FROM employees WHERE first_name = 'John'; IF v_lname IS NULL THEN DBMS_OUTPUT.PUT_LINE('No rows found'); ELSE DBMS_OUTPUT.PUT_LINE('John''s last name is :'||v_lname); END IF; END; Which statement is true about the execution of the code if the query in the PL/SQL block returns no rows? A. The program abruptly terminates and an exception is raised. B. The program executes successfully and the output is No Rows Found. C. The program executes successfully and the query fetches a null value in the V_LNAME variable. D. Program executes successfully, fetches a NULL value in the V_LNAME variable and an exception is raised.

A. The program abruptly terminates and an exception is raised.

View the Exhibit and examine the code: CREATE OR REPLACE PROCEDURE wording IS TYPE definition IS RECORD (word VARCHAR2(20), meaning VARCHAR2(200)); lexicon definition; PROCEDURE add_entry (word_list IN OUT definition) IS BEGIN word_list.word := 'aardvark'; lexicon.word := 'aardwolf'; END add_entry; BEGIN add_entry(lexicon); DBMS_OUTPUT.PUT_LINE(word_list.word); DBMS_OUTPUT.PUT_LINE(lexicon.word); END wording; Why does the code give an error on execution? A. because the WORD_LIST variable is not visible in procedure wording B. because the lexicon variable is not visible in procedure ADD_ENTRY C. because the lexicon variable is not initialized in procedure wording D. because the WORD_LIST parameter in out mode cannot be of a record data type

A. because the WORD_LIST variable is not visible in procedure wording

Examine the following package specification: Which statement is true? CREATE OR REPLACE PACKAGE comm_package IS g_comm NUMBER := 10; PROCEDURE reset_comm(p_comm IN NUMBER); END comm_package; User JONES starts his session and executes the following code at 9:01AM. EXECUTE comm_package.g_comm := 15; User SMITH starts his session and executes the following code at 9:05AM. EXECUTE comm_package.g_comm := 20; A. g_comm has a value of 15 at 9:06AM only for JONES B. g_comm has a value of 10 at 9:03AM for both JONES and SMITH C. g_comm has a value of 15 at 9:03AM for both JONES and SMITH D. g_comm has a value of 20 at 9:06AM for both JONES and SMITH

A. g_comm has a value of 15 at 9:06AM only for JONES

The string_tab has the following structure: STRING1 VARCHAR2(100) Examine the code: SET SERVEROUTPUT ON DECLARE in_string VARCHAR2(25) := 'This is my test string'; out_string VARCHAR2(25); PROCEDURE double (original IN VARCHAR2, new_String OUT VARCHAR2) IS BEGIN new_string := original || ' + ' || original; EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Output buffer not long enough.'); Commit; END; BEGIN double(in_string, out_string); DBMS_OUTPUT.PUT_LINE(in_string || ' - ' || out_string); END; What is the outcome on execution? A. it displays 'Output buffer not long enough. This is my test string' B. it displays only 'Output buffer not long enough' and exits the anonymous block C. It displays only 'This is my test string' - because EXCEPTION should have been defined in the anonymous block to get the error message. D. It does not display any of the DBMS_OUTPUT messages and gives an error because a transaction control statement cannot be used in the exception section of a procedure

A. it displays 'Output buffer not long enough. This is my test string'

View the exhibit to examine the PL/SQL block: CREATE TABLE employees_temp (empid NUMBER(6) NOT NULL PRIMARY KEY, deptid NUMBER(6) CONSTRAINT c_employees_temp_deptid CHECK (deptid BETWEEN 100 AND 200), deptname VARCHAR2(30) DEFAULT 'Sales'); DECLARE emprec employees_temp%ROWTYPE; BEGIN emprec.empid := NULL; emprec.deptid := 50; DBMS_OUTPUT.PUT_LINE('emprec.deptname: ' || emprec.deptname); END; Which statement is true about the output of the PL/SQL block? A. it executes and the output is emprec.deptname B. it executes and the output is emprec.deptname:Sales C. it produces an error because NULL is assigned to the emprec.empid field in the record D. it produces and error because the CHECK constraint is violated while assigning a value to the emprec.deptid field in the record

A. it executes and the output is emprec.deptname

Examine the following block of code: DECLARE v_sal NUMBER; v_name VARCHAR2(30); v_tenure NUMBER; v_hire_date DATE; BEGIN SELECT AVG(salary) INTO v_Sal FROM employees; SELECT hire_date, DECODE(salary, v_sal, last_name, 'NA') INTO v_hire_date, v_name FROM employees WHERE employee_id = 195; v_tenure := MONTHS_BETWEEN(CURRENT_DATE, v_hire_date); END; What is the outcome? A. it executes successfully B. it gives an error because the decode cannot be used in a PL/SQL block C. it gives an error because the AVG function cannot be used in a PL/SQL block D. it gives an error because the MONTHS_BETWEEN function cannot be used in a PL/SQL block E. it gives an error because both the AVG and DECODE functions cannot be used in a PL/SQL block

A. it executes successfully

User SCOTT needs to generate a text report that contains the names of all employees and their salaries. Examine the following commands issued by the DBA: CREATE DIRECTORY my_dir AS '/temp/my_files*; GRANT WRITE ON DIRECTORY my_dir to SCOTT; Examine the procedure code: CREATE OR REPLACE PROCEDURE sal_status(p_dir IN VARCHAR2, p_filename IN VARCHAR2) IS f_file UTL_FILE.FILE_TYPE; CURSOR cur_emp IS SELECT last_name, salary FROM employees ORDER BY salary; BEGIN f_file := UTL_FILE.FOPEN(p_dir, p_filename, 'W'); UTL_FILE.PUT_LINE(f_file, 'REPORT: GENERATED ON ' || SYSDATE); FOR emp_rec IN cur_emp_LOOP UTL_FILE.PUT_LINE(f_file, ' EMPLOYEE: ' ||emp_rec.last_name || ' earns: ' || emp_rec.salary); END LOOP; UTL_FILE.FCLOSE(f_file); EXCEPTION WHEN UTL_FILE.INVALID_FILEHANDLE THEN RAISE_APPLICATION_ERROR(-20001, 'Invalid File.'); WHEN UTL_FILE.WRITE_ERROR THEN RAISE APPLICATION_ERROR(-20002, 'Unable to write to file.'); END; You issue the following command: SQL_EXEC sal_status ('my_dir', 'empreport.txt') What is the outcome? A. it executes successfully and creates the report B. it gives an error because the text file should be opened in append mode C. it gives an error because the 'no data found' condition is not handled to come out of the loop D. it gives an error because the user SCOTT should be granted both read and write privileges to the directory alias E. it executes but no data is written to the text file because the FFLUSH subprogram is not used to write all the data buffered in memory to a file

A. it executes successfully and creates the report

Examine the PL/SQL block: DECLARE TYPE population IS TABLE OF INTEGER INDEX BY VARCHAR2(64); city_population population; i VARCHAR2(64); BEGIN city_population('Smallville') := 2000; city_population('Midland') := 750000; city_population('Megalopolis') := 1000000; city_population('Smallville') := 2001; i := city_population.FIRST; WHILE i IS NOT NULL LOOP DBMS_OUTPUT.PUT_LINE('Population of ' || i || ' is ' || TO_CHAR(city_population(i))); i := city_population.NEXT(i); END LOOP; END; Which statement is true about the execution of the PL/SQL block? A. it executes successfully and gives the desired output B. it does not execute because the definition of type population is indexed by varchar2 C. it executes and the string keys of an associative array are not stored in creation order, but in sorted order D. it does not execute because the value that is once assigned to the element of the associative array cannot be changed

A. it executes successfully and gives the desired output C. it executes and the string keys of an associative array are not stored in creation order, but in sorted order (If only one answer, then C.)

Examine the following code: DECLARE TYPE dept_tab IS TABLE OF departments.department_id%TYPE; deptnums dept_tab; BEGIN SELECT department_id BULK COLLECT INTO deptnums FROM departments; FORALL i IN 1..deptnums.COUNT INSERT INTO emp_by_dept SELECT employee_id, department_id FROM employees WHERE department_id = deptnums(i); DBMS_OUTPUT.PUT_LINE(SQL%BULK_ROWCOUNT(deptnums.COUNT)); DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT); END; What is the outcome on execution of the above code? A. it executes successfully but the output statements show different values B. It executes successfully and both output statements show the same values C. It gives an error because the SQL%ROWCOUNT attribute cannot be used with BULK COLLECT D. It gives an error because the INSERT SELECT construct cannot be used with the FORALL

A. it executes successfully but the output statements show different values

Examine the following DECLARE section of the PL/SQL block: Line 1 DECLARE Line 2 v_job_type VARCHAR2 := 'TEMP'; Line 3 v_startdate DATE := SYSDATE; Line 4 v_enddate DATE := v_startdate + 10; Line 5 c_tax_rate CONSTANT NUMBER(2) := 8.25; Line 6 v_valid BOOLEAN NOT NULL DEFAULT TRUE; Which line in the declaration would generate an error? A. line 2 B. line 3 C. line 4 D. line 5 E. line 6

A. line 2

Which two statements are true about anonymous blocks and named subprograms? Choose two. A. subprograms are by default executed with definer's rights B. the declare section is optional for both anonymous blocks and subprograms C. both anonymous blocks and subprograms execute by default with invoker's rights D. the declare section is mandatory for anonymous blocks and optional for subprograms

A. subprograms are by default executed with definer's rights B. the declare section is optional for both anonymous blocks and subprograms

Which two statements are true about PL/SQL exception propagation? Choose two. A. the exception reproduces itself in successive enclosing blocks until a handler is found B. exceptions can propagate across the remote subprograms that are called through database links C. if you declare a local exception in a subblock and a global exception in the outer block, the local declaration overrides the global exception D. if you declare a local exception in a subblock and a global exception in the outer block, the global declaration overrides the local exception

A. the exception reproduces itself in successive enclosing blocks until a handler is found C. if you declare a local exception in a subblock and a global exception in the outer block, the local declaration overrides the global exception

Examine the following block of code: CREATE OR REPLACE FUNCTION del_rows (p_table_name VARCHAR2, p_empno NUMBER) RETURN NUMBER IS BEGIN EXECUTE IMMEDIATE 'DELETE FROM ' ||p_table_name|| 'WHERE empno = ' ||p_empno; RETURN SQL%ROWCOUNT; END; Which two statements are correct about the code above? Choose two. A. the function goes through only the parse and execute phases B. the function goes through the parse, bind, and execute phases C. the function goes through the parse, bind, execute, and fetch phases D. all the processing phases for the function are performed only at run time E. only the EXECUTE IMMEDIATE statement inside the function is parsed at run time

A. the function goes through only the parse and execute phases E. only the EXECUTE IMMEDIATE statement inside the function is parsed at run time

Identify two situations where the DBMS_SQL package should be used. Choose two. A. the select list is not known until run time B. the dynamic SQL statement retrieves rows into records C. you do not know how many columns a select statement will return, or what their data types will be D. you must see the %FOUND SQL cursor attribute after issuing a dynamic SQL statement that is an insert or update statement

A. the select list is not known until run time C. you do not know how many columns a select statement will return, or what their data types will be

Examine the following code: SET SERVEROUTPUT ON DECLARE Line 2 date1 DATE := 'January 10, 2008'; Line 3 date2 DATE := SYSDATE; Line 4 date_diff NUMBER; Line 5 BEGIN Line 6 date_diff := date2 - date1; Line 7 DBMS_OUTPUT.PUT_LINE('Difference in dates is ' || date_diff); Line 8 END; The above code generates an error on execution. What must you do to ensure that the code executes successfully? A. use the TO_DATE function in line 2 B. use the TO_DATE function in line 7 C. use the TO_NUMBER function in line 6 D. use both the TO_DATE function in line 2 and the TO_NUMBER function in line 6

A. use the TO_DATE function in line 2

In which of the following scenarios would you recommend using PL/SQL records? A. when you want to retrieve an entire row from a table and perform calculations B. when you know the number of elements in advance and the elements are usually accessed sequentially C. when you want to create a separate lookup table with multiple entries for each row of the main table, and access it through join queries D. when you want to create a relatively small lookup table, where the collection can be constructed in memory each time a subprogram is invoked

A. when you want to retrieve an entire row from a table and perform calculations

Which two statements correctly differentiate functions and procedures? (Choose two.) A. A function can be called only as part of a SQL statement, whereas a procedure can be called only as a PL/SQL statement. B. A function must return a value to the calling environment, whereas a procedure can return zero or more values to its calling environment. C. A function can be called as part of a SQL statement or PL/SQL expression, whereas a procedure can be called only as a PL/SQL statement. D. A function may return one or more values to the calling environment, whereas a procedure must return a single value to its calling environment.

B. A function must return a value to the calling environment, whereas a procedure can return zero or more values to its calling environment. C. A function can be called as part of a SQL statement or PL/SQL expression, whereas a procedure can be called only as a PL/SQL statement.

Which two statements are true about statement-level and row-level triggers? (Choose two.) A. A row trigger fires once even if no rows are affected. B. A statement trigger fires once even if no rows are affected. C. Row triggers are useful if the trigger action depends on the data of rows that are affected or on data that is provided by the triggering event itself. D. Statement triggers are useful if the trigger action depends on the data of rows that are affected or on data that is provided by the triggering event itself.

B. A statement trigger fires once even if no rows are affected. C. Row triggers are useful if the trigger action depends on the data of rows that are affected or on data that is provided by the triggering event itself.

Examine the following code: DECLARE emp_column VARCHAR2(30) := 'last_name'; table_name VARCHAR2(30) := 'emp'; temp_var VARCHAR2(30); BEGIN temp_var := emp_column; SELECT COLUMN_NAME INTO temp_var FROM USER_TAB_COLS WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = UPPER(emp_column); temp_var := table_name; SELECT OBJECT_NAME INTO temp_var FROM USER_OBJECTS WHERE OBJECT_NAME = UPPER(table_name) AND OBJECT_TYPE = 'TABLE'; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No data found for SELECT on ' || temp_var); END; Which statement is true about the exception handlers in the PL/SQL code? A. All the exceptions in the code are trapped by the exception handler. B. All the "no data found" errors in the code are trapped by the exception handler. C. The PL/SQL program does not execute because an exception is not declared in the declare section. D. An exception handler in the code traps the "no data found" error after executing the handler code and the program flow returns to the next line of code.

B. All the "no data found" errors in the code are trapped by the exception handler.

You want to maintain an audit of the date and time when each user of the database logs off. Examine the following code: CREATE TABLE log_trig_table (user_id VARCHAR2(30), log_date TIMESTAMP, action VARCHAR2(40)); CREATE OR REPLACE TRIGGER logoff_trig ________ ___________ ___________ BEGIN INSERT INTO log_trig_table(user_id, log_date, action) VALUES(user, sysdate, 'Logging Off'); ENd; Which two clauses should be used to fill in the blanks and complete the above code? Choose two. A. ON SCHEMA B. ON DATABASE C. AFTER LOGOFF D. BEFORE LOGOFF

B. ON DATABASE D. BEFORE LOGOFF

Examine the following package specification. CREATE OR REPLACE PACKAGE emp_pkf IS PROCEDURE search_emp (empdet NUMBER); PROCEDURE search_emp (empdet DATE); PROCEDURE search_emp (empdet NUMBER); RETURN VARCHAR2; PROCEDURE search_emp (empdet NUMBER); RETURN DATE; END emp_pkg; The package is compiled successfully. Why would it generate an error at run time? A. Because a function can not be overloaded B. Because a function cannot differ only in return type. C. Because all the functions and procedures in the package cannot have the same number of parameters with the same parameter name D. Because the search_emp(EMPDET NUMBER) procedure and the search_dept(EMPDET NUMBER) cannot have identical parameter names and data types

B. Because a function cannot differ only in return type.

Examine the following code: DECLARE CURSOR c1 IS SELECT last_name FROM employees ORDER BY last_name; name1 employees.last_name%TYPE; name2 employees.last_name%TYPE; name3 employees.last_name%TYPE; BEGIN OPEN c1; FETCH c1 INTO name1; FETCH c1 INTO name2; FETCH c1 INTO name3; CLOSE c1; END; Which statement is true about the fetch statements in the PL/SQL code? A. Each fetch retrieves the first row and assigns values to the target variables B. Each fetch retrieves the next consecutive row and assigns values to the target variables. C. They produce an error because you must close and reopen the cursor before each fetch-statement D. Only the first fetch retrieves the first row and assigns values to the target variables- the second produces an error.

B. Each fetch retrieves the next consecutive row and assigns values to the target variables.

Which two statements are correct about the usage of parameters in functions? Choose two. A. Functions can have only in mode parameters B. Functions called in SQL statements cannot have out or in out mode parameters. C. Functions having in, out, or in out parameters can be called only in named PL/SQL subprograms. D. Functions having in, out, or in out parameters can be called in PL/SQL procedures and anonymous blocks.

B. Functions called in SQL statements cannot have out or in out mode parameters. D. Functions having in, out, or in out parameters can be called in PL/SQL procedures and anonymous blocks.

Examine the following PL/SQL code: DECLARE stock_price NUMBER := 9.73; net_earnings NUMBER := 0; pe_ratio NUMBER; BEGIN pe_ratio := stock_price / net_earnings; DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio); END; Which statement is true about the execution of the code? A. It executes successfully. B. It generates a run time exception. C. It does not execute because of syntax error. D. It executes successfully and generates a warning.

B. It generates a run time exception.

You create the following table and execute the following code: CREATE TABLE emp_temp (deptno NUMBER(2), job VARCHAR2(18)); DECLARE TYPE numlist IS TABLE OF NUMBER; depts numlist := numlist(10,20,30); BEGIN INSERT INTO emp_temp VALUES(10, 'Clerk'); INSERT INTO emp_temp VALUES(20, 'Bookkeeper'); INSERT INTO emp_temp VALUES(30, 'Analyst'); FORALL j IN depts.FIRST..depts.LAST UPDATE emp_temp SET job = job || ' (Senior)' WHERE deptno = depts(j); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Problem in the FORALL statement'); COMMIT; END; Which statement is true about the outcome of the above code? A. It executes successfully and all the rows are updated. B. It gives an error but saves the inserted rows and the update to the first row. C. It gives an error but saves the inserted rows; however, no rows are updated. D. It gives an error and all the data manipulation language (DML) statements are rolled back

B. It gives an error but saves the inserted rows and the update to the first row.

ORDER_TOTAL is a column in the orders table with the data type and size as number(8,2) Examine the following code: SET SERVEROUTPUT ON DECLARE Line 2 v_order_id orders.order_id%TYPE; Line 3 v_order_total CONSTANT orders.order_total%TYPE := 1000; Line 4 v_all_order_total v_order_total%TYPE; Line 5 BEGIN Line 6 v_order_id := NULL; Line 7 DBMS_OUTPUT.PUT_LINE('Order total is ' || v_order_total); Line 8 END; Which statement is correct about the above code? A. It gives an error inline 3 B. It gives an error in line 4 C. It gives an error in line 6 D. It executes successfully and displays the output.

B. It gives an error in line 4

Examine the trigger code that is defined on the dept table to enforce the update and delete restrict referential actions on the primary key of the dept table. CREATE OR REPLACE TRIGGER dept_restrict BEFORE DELETE OR UPDATE OF deptno ON dept DECLARE dummy INTEGER; employees_present EXCEPTION; employees_not_present EXCEPTION; CURSOR dummy_cursor (dn NUMBER) IS SELECT deptno FROM emp WHERE deptno = dn; BEGIN OPEN dummy_cursor (:OLD.deptno); FETCH dummy_cursor INTO dummy; IF dummy_cursor%FOUND THEN RAISE employees_present; ELSE RAISE employees_not_present; END IF; CLOSE dummy_cursor; EXCEPTION WHEN employees_present THEN CLOSE dummy_cursor; What is the outcome on compilation? A. It compiles and executes successfully. B. It gives an error on compilation because it is not a row-level trigger. C. It gives an error on compilation because the exception section Is used in the trigger. D. It compiles successfully but gives an error on execution because it is not a row-level trigger.

B. It gives an error on compilation because it is not a row-level trigger.

What is the correct definition of the persistent state of a packaged variable? A. It is a private variable defined in a procedure or function within a package body whose value is consistent within a user session. B. It is a public variable in a package specification whose value is consistent within a user session. C. It is a private variable in a package body whose value is consistent across all current active sessions. D. It is a public variable in a package specification whose value is always consistent across all current active sessions.

B. It is a public variable in a package specification whose value is consistent within a user session.

Examine the following PL/SQL code: DECLARE emp_rec employees%ROWTYPE; BEGIN SELECT * INTO emp_rec FROM employees WHERE employee_id = 123; IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('Record not found'); ELSE DBMS_OUTPUT.PUT_LINE('Employee '||emp_rec.first_name||' '|| emp_rec.last_name||' Salary is '||emp_rec.salary); END IF; END; The server output is on for the session. Which statement is true about the execution of the code? A. It displays null if no employee with employee_id 123 exists. B. It produces the ora-01403: no data found error if no employee with employee_id 123 exists. C. It displays an error because the select into clause cannot be used to populate the PL/SQL record type. D. The code executes successfully even if no employee with employee_id 123 exists and displays Record Not Found.

B. It produces the ora-01403: no data found error if no employee with employee_id 123 exists.

DECLARE emp_job employee.job_id%TYPE := 'ST_CLERK'; emp_salary employees.salary%TYPE := 3000; my_record employees%ROWTYPE; CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS SELECT * FROM employees WHERE job_id = job AND salary > max_wage; BEGIN ......... Identify the open statement for opening the cursor that fetches the result as consisting of employees with JOB_ID as 'ST_CLERK' and salary greater than 3000. A. OPEN c1 (NULL, 3000); B. OPEN c1 (emp_job, 3000); C. OPEN c1 (3000, emp_salary); D. OPEN c1 ('ST_CLERK', 3000); E. OPEN c1 (emp_job, emp_salary);

B. OPEN c1 (emp_job, 3000); D. OPEN c1 ('ST_CLERK', 3000); E. OPEN c1 (emp_job, emp_salary);

Which three statements are true about anonymous blocks and subprograms? Choose three. A. Only subprograms can be parameterized. B. Only subprograms are persistent database objects. C. Both anonymous blocks and subprograms can be parameterized. D. Both anonymous blocks and subprograms are persistent database objects. E. Only subprograms can return values that persist after the execution of the subprogram. F. Both anonymous blocks and subprograms can return values that persist in SQL*Plus variables after their execution.

B. Only subprograms are persistent database objects. E. Only subprograms can return values that persist after the execution of the subprogram. F. Both anonymous blocks and subprograms can return values that persist in SQL*Plus variables after their execution.

View the Exhibit and examine the structure of the EMP table. DECLARE v_sal NUMBER; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = 130; INSERT INTO emp(empno, ename, sal) VALUES(185, 'Jones', v_sal+1000); END; Which stages are performed when the above block is executed? (Choose all that apply) A. Bind B. Parse C. Fetch D. Execute

B. Parse C. Fetch D. Execute

Which statements are true about PL/SQL procedures? (Choose all that apply.) A. Users with definer's rights who are granted access to a procedure that updates a table must be granted access to the table itself. B. Reuse of parsed PL/SQL code that becomes available in the shared SQL area of the server avoids the parsing overhead of SQL statements at run time. C. Depending on the number of calls, multiple copies of the procedure are loaded into memory for execution by multiple users to speed up performance. D. A PL/SQL procedure executing on the Oracle database can call an external procedure or function that is written in a different programming language, such as C or Java.

B. Reuse of parsed PL/SQL code that becomes available in the shared SQL area of the server avoids the parsing overhead of SQL statements at run time. D. A PL/SQL procedure executing on the Oracle database can call an external procedure or function that is written in a different programming language, such as C or Java.

Which two statements are true about the continue statement? (Choose two.) A. The PL/SQL block execution terminates immediately. B. The CONTINUE statement cannot appear outside a loop. C. The loop completes immediately and control passes to the statement after end loop. D. The statements after the continue statement in the iteration are executed before terminating the LOOP. E. The current iteration of the loop completes immediately and control passes to the next iteration of the loop

B. The CONTINUE statement cannot appear outside a loop. E. The current iteration of the loop completes immediately and control passes to the next iteration of the loop

Examine the following code: CREATE OR REPLACE FUNCTION job_chk (p_empno NUMBER) RETURN BOOLEAN IS v_job emp.job%TYPE; BEGIN SELECT job INTO v_job FROM emo WHERE empno = p_empno; IF v_job = 'SALESMAN' THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END job_chk; DECLARE Line 2 v_job BOOLEAN; Line 3 dyn_stmt VARCHAR2(200); Line 4 v_comm NUMBER := NULL: Line 5 v_empno emp.empno%TYPE; Line 6 BEGIN Line 7 dyn_stmt := 'BEGIN :v_job := job_chk(100); END'; Line 8 EXECUTE IMMEDIATE dyn_stmt USING OUT v_job; Line 9 IF v_job THEN Line 10 EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x WHERE empno = :y' Line 11 USING v_comm, v_empno; Line 12 END IF; Line 13 END; The anonymous block gives an error on execution. What Is the reason? A. The assignment in line 7 is not valid. B. The SQL does not support the Boolean data type. C. A null value cannot be applied to the bind arguments In the using clause in line 10 D. The names of bind variables must be the same as the using clause bind arguments in line 10

B. The SQL does not support the Boolean data type.

Which two statements are true about the usage of the cursor for loops? (Choose two.) A. The cursor needs to be closed after the iteration is complete. B. The implicit open, fetch, exit, and close of the cursor happen. C. The record type must be explicitly declared to control the loop. D. The PL/SQL creates a record variable with the fields corresponding to the columns of the cursor result set.

B. The implicit open, fetch, exit, and close of the cursor happen. D. The PL/SQL creates a record variable with the fields corresponding to the columns of the cursor result set.

Which two statements are true about the exit statement encountered in a loop? Choose two. A. The PL/SQL block execution terminates immediately after the exit statement. B. The loop completes immediately and control passes to the statement after end loop. C. The statements after the exit statement in the iteration are not executed before terminating the loop. D. The current iteration of the loop completes immediately and control passes to the next iteration of the loop.

B. The loop completes immediately and control passes to the statement after end loop. C. The statements after the exit statement in the iteration are not executed before terminating the loop.

View the Exhibit and examine the structure of the EMP table. You want to create two procedures using the overloading feature to search for employee details based on either the employee name or employee number. Which two rules should you apply to ensure that the overloading feature is used successfully? (Choose two.) A. The procedures can be either stand-alone or packaged. B. The procedures should be created only as packaged subprograms C. The procedures should be created only as stand-alone subprograms D. Each subprogram's formal parameters should differ in both name and data type. E. The formal parameters of each subprogram should differ in data type but can use the same names.

B. The procedures should be created only as packaged subprograms E. The formal parameters of each subprogram should differ in data type but can use the same names.

Which statements are true about database triggers? (Choose all that apply.) A. They can invoke only PL/SQL procedures B. They can include SQL and PL/SQL or calls to Java procedures. C. They are implicitly fired by an event that must occur within an application D. They are implicitly fired when a triggering event occurs, depending on which user is connected

B. They can include SQL and PL/SQL or calls to Java procedures. D. They are implicitly fired when a triggering event occurs, depending on which user is connected

Examine the following code: CREATE OR REPLACE FUNCTION f2 (p_p1 NUMBER) RETURN NUMBER PARALLEL_ENABLE IS BEGIN RETURN p_p1 * 2; END f2; Which two statements are true about the above function? Choose two. A. it can be used only in a parallelized query B. it can be used in both a parallelized query and a parallelized DML statement C. it can be used only in a parallelized DML statement D. it can have a separate copy run in each of the multiple processes when called from a SQL statement that is run in parallel E. it requires a PRAGMA RESTRICT_REFERENCES declaration with RNDS, WNDS, RNPS, WNPS specified in order to use parallel optimization

B. it can be used in both a parallelized query and a parallelized DML statement D. it can have a separate copy run in each of the multiple processes when called from a SQL statement that is run in parallel

Examine the following command: ALTER SESSION SET plsql_warnings * 'enable:severe', 'enable:performance', 'ERROR:05003'; What is the implication of the above command? A. it issues a warning whenever ERROR:05003 occurs during compilation B. it causes the compilation to fail whenever the warning ERROR:05003 occurs C. it issues warnings whenever the code causes an unexpected action or wrong results performance problems D. it causes the compilation to fail whenever the code gives wrong results or contains statements that are never executed

B. it causes the compilation to fail whenever the warning ERROR:05003 occurs

You have created a package and body manage_emp and package and body emp_det. CREATE OR REPLACE PACKAGE manage_emp IS v_empno NUMBER; PROCEDURE del_emp (p_empno NUMBER); END manage_emp; CREATE OR REPLACE PACKAGE BODY manage_emp IS PROCEDURe del_emp (p_empno NUMBER) IS BEGIN DELETE FROM emp WHERE empno = p_empno; END del_emp; END manage_emp; CREATE OR REPLACE PACKAGE emp_det IS PROCEDURE emp_chk (p_empno NUMBER); END emp_det; CREATE OR REPLACE PACKAGE BODY emp_det IS PROCEDURE emp_chk (p_empno NUMBER); END emp_det; CREATE OR REPLACE PACKAGE BODY emp_det IS PROCEDURE emp_chk (p_empno NUMBER) IS BEGIN manage_emp.del_emp(p_empno); END emp_chk; END emp_det; You issue the following command: DROP PACKAGE manage_emp; What is the outcome? A. it drops both the manage_emp and emp_det packages because of the cascading effect B. it drops the manage_emp package and invalidates only the body for the emp_det package C. it returns an error and does not drop the manage_emp package because of the cascading effect D. it drops the manage_emp package and invalidates both the specification and body for the emp_det package

B. it drops the manage_emp package and invalidates only the body for the emp_det package

Examine the following partial declare section from a block of PL/SQL code: DECLARE Line 2 v_wage NUMBER NOT NULL := 1000; Line 3 v_total_wages v_wage%TYPE; Line 4 work_complete CONSTANT BOOLEAN := TRUE; Line 5 all_work_complete work_complete%TYPE; Which line(s) in the above code are NOT valid? Choose all that apply. A. line 2 B. line 3 C. line 4 D. line 5

B. line 3 D. line 5

Examine the following block of code: 1 DECLARE 2 status VARCHAR2(10) NOT NULL DEFAULT 'TRUE'; 3 net_value NUMBER := 555; 4 done BOOLEAN; 5 valid_id BOOLEAN := TRUE; 6 BEGIN 7 done := (net_value > 100); 8 status := valid_id; 9 END; Which line in the above code would result in errors upon execution? A. line 5 B. line 8 C. line 2 D. line 7

B. line 8

Which tasks must be performed during the installation of the UTL_MAIL package? Choose all that apply. A. setting the UTL_FILE_DIR initialization parameter B. running the UTL_MAIL.SQL and prvtmail.plb scripts C. setting the SMTP_OUT_SERVER initialization parameter D. using the CREATE DIRECTORY statement to associate an alias with an operating system directory E. granting read and write privileges to control the type of access to files in the operating system

B. running the UTL_MAIL.SQL and prvtmail.plb scripts C. setting the SMTP_OUT_SERVER initialization parameter

Which statement is true about triggers on DDL statements? Choose two. A. they can be used to track changes only to a table or index B. the can be defined by all users in the database or only by a specific user C. they are fired only when the owner of the object issues the DDL statement D. they can be used to track changes to a table, table space, view, or synonym

B. the can be defined by all users in the database or only by a specific user D. they can be used to track changes to a table, table space, view, or synonym

Which two statements are true about the PL/SQL initialization parameters? A. to use native code compilation, PLSQL_OPTIMIZE_LEVEL should be set to a value less than or equal to 1 B. the default value of 2 for PLSQL_OPTIMIZE_LEVEL allows the compiler to rearrange code for better performance C. setting PLSQL_CODE_TYPE to native provides the greatest performance gains only for computation-intensive procedural operations D. changing the value of the PLSQL_CODE_TYPE parameter affects all the PL/SQL library units that have already been compiled

B. the default value of 2 for PLSQL_OPTIMIZE_LEVEL allows the compiler to rearrange code for better performance C. setting PLSQL_CODE_TYPE to native provides the greatest performance gains only for computation-intensive procedural operations

Which type of exceptions are qualified as non-predefined Oracle server errors? A. the exceptions that are explicitly raised by the program and can be caught by the exception handler B. the exceptions that are raised implicitly by the Oracle server and can be caught by the exception handler C. an exception that the developer determines as abnormal, are in the declarative section and raised explicitly D. an exception that is raised automatically when the PL/SQL program violates a database rule or exceeds a system-dependent limit

B. the exceptions that are raised implicitly by the Oracle server and can be caught by the exception handler

Identify situations in which the DBMS_SQL package is the only applicable method of processing dynamic SQL. Choose all that apply. A. when a query returns multiple rows B. when a column name in a where clause is unknown at compile time C. when the number of columns selected in a query is not known until run time D. when a table needs to be created based on an existing table structure at run time E. when privileges need to be granted to a new user to access an existing schema at run time

B. when a column name in a where clause is unknown at compile time C. when the number of columns selected in a query is not known until run time

Examine the following partial code: CREATE OR REPLACE PACKAGE calc_income IS v_taxrate NUMBER := 100; PROCEDURE calc_tax(p_empno NUMBER); PROCEDURE calc_sal(p_empno NUMBER); END calc_income; CREATE OR REPLACE PACKAGE BODY calc_income IS PROCEDURE calc_tax(p_empno NUMBER) .......... END calc_tax; PROCEDURE calc_sal(p_empno NUMBER) ........... END calc_sal; BEGIN SELECT rate_value INTO v_taxrate FROM tax_rates WHERE year = 2009; END calc_income; Which statement is correct about the unnamed block of code at the end of a package body? A. It generates an error because all the blocks of code in a package body must be named. B. It generates an error because V_TAXRATE is a public variable that is already initialized in the package specification. C. It acts as a package initialization block that executes once, when the package is first invoked within the user session. D. It acts as a package initialization block that executes each time a package subprogram is invoked within the user session and refreshes the initialized variable value.

C. It acts as a package initialization block that executes once, when the package is first invoked within the user session.

View the Exhibit to examine the PL/SQL code. DECLARE type t_rec IS RECORD (v_sal NUMBER(8), v_minsal NUMBER(8) DEFAULT 1000, v_hire_Date employees.hire_date%TYPE, v_rec1 employees.%ROWTYPE); v_myrec t_rec; BEGIN v_myrec.v_sal := v_myrec.v_minsal + 500; v_myrec.v_hire_date := sysdate; SELECT * INTO v_myrec.v_rec1 FROM employees WHERE employee_id = 100; DBMS_OUTPUT.PUT_LINE(v_myrec.v_rec1.last_name ||' '|| TO_CHAR(v_myrec.v_hire_date) ||' '|| TO_CHAR(v_myrec.v_sal)); END; Identify the correct output for the code. A. King 17-JUN-87 1500 B. King 17-JUN-87 24000 C. King current sysdate 1500 D. King current sysdate 24000

C. King current sysdate 1500

View the Exhibit and examine the code and its outcome on execution: CREATE PACKAGE my_debug IS debug CONSTANT BOOLEAN := TRUE; trace CONSTANT BOOLEAN := TRUE; END my_debug; CREATE PROCEDURE my_proc1 IS BEGIN $IF my_debug .debug $THEN DBMS_OUTPUT.PUT_LINE('Debugging ON'); $ELSE DBMS_OUTPUT.PUT_LINE('Debugging OFF'); $END END my_proc1; CREATE PROCEDURE my_proc2 IS BEGIN $IF my_debug.trace $THEN DBMS_OUTPUT.PUT_LINE('Tracing ON'); $ELSE DBMS_OUTPUT.PUT_LINE('Tracing OFF'); $END END my_proc2; What would be the effect on the two procedures if the value of debug is set to false? (Choose two.) A. MY_PROC2 is not recompiled. B. MY_PROC1 is recompiled but remains unchanged. C. MY_PROC2 is recompiled but remains unchanged. D. MY_PROC1 is recompiled without the debugging code.

C. MY_PROC2 is recompiled but remains unchanged. D. MY_PROC1 is recompiled without the debugging code.

Which two statements are true about triggers? Choose two. A. all the triggers that are created on a table cannot be disabled simultaneously B. any user who has the alter privilege on a table can create a trigger using that table C. Oracle provides a two-phase commit process whether a trigger updates tables in the local database or remote tables in a distributed database D. triggers become invalid if a dependent object, such as a stored subprogram that is invoked from the trigger body is modified, and have to be manually recompiled before the next invocation

C. Oracle provides a two-phase commit process whether a trigger updates tables in the local database or remote tables in a distributed database D. triggers become invalid if a dependent object, such as a stored subprogram that is invoked from the trigger body is modified, and have to be manually recompiled before the next invocation

You want to store values of different data types in a PL/SQL block and store one record at a time for processing the information. Which type of composite data type would you choose to fulfill the requirement? A. VARRAYS B. Nested table C. PL/SQL records D. Associative arrays

C. PL/SQL records

Examine the code created by the user SCOTT: CREATE OR REPLACE PACKAGE curs_pkg IS PROCEDURE open: PROCEDURE next(p_n NUMBER := 1); PROCEDURE close; END curs_pkg; CREATE OR REPLACE PACKAGE BODY curs_pkg IS CURSOR cur_c IS SELECT empno FROM emp; PROCEDURE open IS BEGIN IF NOT cur_c%ISOPEN THEN OPEN cur_c; END IF; END open; PROCEDURE next(p_n NUMBER := 1) IS v_emp_di emp.empno%TYPE; BEGIN FOR count IN 1..p_n LOOP FETCH cur_c INTO v_emp_id; EXIT WHEN cur_c%NOTFOUND; DBMS_OUTPUT.PUT_LINE('ID: ' ||(v_emp_id)); END LOOP; END next; PROCEDURE close IS BEGIN IF cur_c%ISOPEN THEN close cur_c; END IF; END close; END curs_pkg; SCOTT grants the necessary privileges to green to access the EMP table and execute the package. Examine the following sequence of activities: SCOTT starts a session and issues the SQL>EXEC CURS_PKG.OPEN command. SCOTT then issues the SQL>EXEC CURS_PKG.NEXT command. green starts a session while SCOTT's session is running and issues the SQL>EXEC CURS_PKG.NEXT command. SCOTT issues the SQI>EXEC SCOTT.CURS_PKG.NEXT command. The EMP table contains sequential EMPNOS from 100 through 108. Which statement correctly describes the output? A. SCOTT's session shows the EMPNO 100, GREEN'S session shows an error, and SCOTT's session shows an error. B. SCOTT's session shows the EMPNO 100, GREEN'S session shows EMPNO 100, and SCOTT's session shows the EMPNO 101. C. SCOTT's session shows the EMPNO 100, GREEN'S session shows an error, and SCOTT's session shows the second EMPNO 101. D. SCOTT's session shows the EMPNO 100, GREEN'S session shows EMPNO 101, and SCOTT's session shows the second EMPNO 102.

C. SCOTT's session shows the EMPNO 100, GREEN'S session shows an error, and SCOTT's session shows the second EMPNO 101.

Examine the following block of code: DECLARE v_sum_sal NUMBER; department_id employees.department_id%TYPE := 60; BEGIN SELECT SUM(salary) INTO v_sum_sal FROM employees WHERE department_id = department_id; DBMS_OUTPUT.PUT_LINE('The sum of salary is ' || v_sum_sal); END; What is the outcome? A. it gives an error because group functions cannot be used in anonymous blocks B. it executes successfully and correctly gives the result of the sum of salaries in department 60 C. it executes successfully and incorrectly gives the result of the sum of salaries in department 60 D. it gives an error because the variable name and column name are the same in the where clause of the select statement

C. it executes successfully and incorrectly gives the result of the sum of salaries in department 60

Examine the following PL/SQL code: DECLARE jobid employees.job_id%TYPE; empid employees.employee_id%TYPE := 115; sal employees.salary%TYPE; sal_raise NUMBER(3,2); BEGIN SELECT job_id, salary INTO jobid, sal FROM employees WHERE employee_id = empid; CASE WHEN jobid = 'PU_CLERK' THEN IF sal < 3000 THEN sal_raise := .12; ELSE sal_raise := .09; END IF; WHEN jobid = 'SH_CLERK' THEN IF sal < 4000 THEN sal_raise := .11; ELSE sal_raise := .08; END IF; WHEN jobid = 'ST_CLERK' THEN IF sal < 3500 THEN sal_raise := .10; ELSE sal_raise := .07; END IF; ELSE BEGIN DBMS_OUTPUT.PUT_LINE('No raise for this job: '|| jobid); END; END CASE; UPDATE employees SET salary = salary + salary * sal_raise WHERE employee_id = empid; COMMIT; END; Which statement is true about the execution of the code? A. The execution fails because of the misplaced else clause. B. The execution is successful even if there is no employee with employee id 115. C. The execution fails and throws exceptions if no employee with employee id 115 is found. D. The execution is successful, but it displays an incorrect output if no employee with employee id 115 is found.

C. The execution fails and throws exceptions if no employee with employee id 115 is found.

Which two statements are true about instead of triggers? (Choose two.) A. Delete operations cannot be performed using the instead of triggers. B. The instead or triggers must be created to add or modify data through any view. C. The instead of triggers can be written only for views, and the before and after timing options are not valid. D. The check option for views is not enforced when insertions or updates to the view are performed by using the instead of trigger.

C. The instead of triggers can be written only for views, and the before and after timing options are not valid. D. The check option for views is not enforced when insertions or updates to the view are performed by using the instead of trigger.

Examine the following procedure: SET SERVEROUTPUT ON VARIABLE n1 NUMBER VARIABLE n2 NUMBER CREATE OR REPLACE PROCEDURE proc1 (:n1 IN OUT NUMBER, :n2 IN OUT NUMBER) IS BEGIN :n1 := 20; DBMS_OUTPUT.PUT_LINE(:n1); :n2 := 30; DBMS_OUTPUT.PUT_LINE(:n2); END; What is the outcome? A. The procedure is created successfully and displays the values 20 and 30 when it is called. B. The procedure gives errors because the parameters should be in out mode. C. The procedure gives errors because the host variables cannot be referenced anywhere in the definition of a PL/SQL stored procedure. D. The procedure is created successfully but does not display any values when it is called because the host variables cannot be displayed inside the procedure.

C. The procedure gives errors because the host variables cannot be referenced anywhere in the definition of a PL/SQL stored procedure.

Which two statements are correct about PL/SQL package components? Choose two. A. a package must have both specification and body B. a package body can exist without the package specification C. a package specification can exist without the package body D. when a packaged public variable is called for the first time in a session, the entire package is loaded into memory

C. a package specification can exist without the package body D. when a packaged public variable is called for the first time in a session, the entire package is loaded into memory

Examine the procedure you created. The procedure uses the prod id to determine whether the list price is within a given range. CREATE OR REPLACE PROCEDURE check_price (p_prod_id NUMBER) IS v_price product.prod_list_price%TYPE; BEGIN SELECT prod_list_price INTO v_price FROM product WHERE prod_id = p_prod_id; IF v_price NOT BETWEEN 20 AND 30 THEN RAISE_APPLICATION_ERROR(-20100, 'Price not in range'); END IF; END; You then create the following trigger on the product table. CREATE OR REPLACE TRIGGER check_price__trg BEF0RE INSERT OR UPDATE OF prod_id,prod_list_price ON product FOR EACH ROW WHEN(NEW.prod_id <> NVL(OLD.prod_id,0) OR NEW.prod__list_price <> NVL(OLD.prod_list_price, 0) ) BEGIN check_price (:NEW.prod_id) ; END; Examine the following update command for an existing row in the product table. SQL> UPDATE produce SET prod_list_price = 10 WHERE prod_id=115; Why does it generate an error? A. because the procedure call in the trigger is not valid B. because the condition specified in the when clause is not valid C. because both the procedure and trigger access the same table D. because the WHEN clause cannot be used with a row-level trigger E. because the column list specified with UPDATE in the trigger is not valid

C. because both the procedure and trigger access the same table

Examine the following code: CREATE OR REPLACE PROCEDURE raise_salary (emp_id IN NUMBER, amount IN NUMBER, extra IN NUMBER DEFAULT 50) IS BEGIN UPDATE emp SET sal = sal + NVL(amount,0) + extra WHERE empno = emp_id; END raise_salary; Line 1DECLARE Line 2emp_num NUMBER(6) := 7900; Line 3 bonus NUMBER(6); Line 4 merit NUMBER(4); Line 5 BEGIN Line 6 raise_salary(7845); Line 7 raise_salary(emp_num, extra => 25); Line 8 raise_salary(7845, NULL, 25); Line 9 raise_salary(emp_num, extra => 25, amount => NULL; Line 10 END; EMPNOS 7845 and 7900 exist in the EMP table. Which two calls to the RAISE_SALARY procedure in the anonymous block execute successfully? (Choose two.) A. call in line 6 B. call in line 7 C. call in line 8 D. call in line 9

C. call in line 8 D. call in line 9

SET SERVEROUTPUT on DECLARE v_myage NUMBER; BEGIN IF v_myage < 11 THEN DBMS_OUTPUT.PUT_LINE(' I am a child'); ELSE DBMS_OUTPUT.PUT_LINE('I am not a child'); END IF; END; Which statement is true about the execution of the above code? A. it executes and displays null B. it executes and the condition returns true C. it executes and control goes to the else statement D. it fails because no value is assigned to the v_myage variable

C. it executes and control goes to the else statement

The salary of employee_id 195 is 2800. You execute the following code: SET SERVEROUTPUT ON DECLARE v_sal NUMBER(10,2) := 1000; BEGIN DBMS_OUTPUT.PUT_LINE('Salary is ' || v_sal); DECLARE v_sal NUMBER; BEGIN SELECT salary INTO v_sal FROM employees WHERE employee_id = 195; DBMS_OUTPUT.PUT_LINE('Salary is ' || v_Sal); DECLARE v_sal NUMBER := 50000; BEGIN <<b3>> DBMS_OUTPUT.PUT_LINE('Salary is ' || v_Sal); END b3; DBMS_OUTPUT.PUT_LINE('Salary is ' || v_Sal); END; END; What is the outcome? A. it gives an error because only the innermost block is labeled B. it gives an error because the same variable name cannot be used across all the nested blocks C. it executes successfully and displays the resultant values in the following sequence - 1000, 2800, 50000, 2800 D. it executes successfully and displays the resultant values in the following sequence: 1000, 2800, 50000, 1000

C. it executes successfully and displays the resultant values in the following sequence - 1000, 2800, 50000, 2800

VARIABLE min_sal NUMBER VARIABLE max_sal NUMBER CREATE OR REPLACE FUNCTION sal_ok(salary NUMBER, jobgrade NUMBER) RETURN BOOLEAN AS BEGIN SELECT losal, hisal INTO :min_sal, :max_sal FROM salgrade WHERE grade = jobgrade; RETURN (salary >= min_sal) AND (salary <= max_sal); END sal_ok' What is the outcome? A. it is created successfully B. it gives an error because the return clause condition is invalid C. it gives an error because the usage of the host variables in invalid D. it gives an error because the data type of the return clause is invalid

C. it gives an error because the usage of the host variables in invalid

Which two tasks should be created as functions instead of as procedures? (Choose two.) A. reference host or bind variables in a PL/SQL block of code B. tasks that compute and return multiple values to the calling environment C. tasks that compute a value that must be returned to the calling environment D. tasks performed in SQL that increase data independence by processing complex data analysis within the Oracle server, rather than by retrieving the data into an application

C. tasks that compute a value that must be returned to the calling environment D. tasks performed in SQL that increase data independence by processing complex data analysis within the Oracle server, rather than by retrieving the data into an application

You create a procedure to handle the processing of bank current accounts which rolls back payment transactions if the overdraft limit is exceeded. The procedure should return an 'error' condition to the caller in a manner consistent with other Oracle server errors. Which construct should be used to handle this requirement? A. the SQLERRM function B. the PRAGMA EXCEPTION_INIT procedure C. the RAISE_APPLICATION_ERROR procedure D. a user-defined exception used with a raise statement

C. the RAISE_APPLICATION_ERROR procedure

Which two statements are true about the %ROWTYPE attribute? Choose two. A. it is used to declare a record that can hold multiple rows of a table B. the attributes of fields in the record with the %ROWTYPE attribute can be modified manually C. the attributes of fields in the record take their names and data types from the columns of the table, view, cursor, or cursor variable D. it ensures that the data types of the variables that are declared with the %ROWTYPE attribute change dynamically when the underlying table is altered

C. the attributes of fields in the record take their names and data types from the columns of the table, view, cursor, or cursor variable D. it ensures that the data types of the variables that are declared with the %ROWTYPE attribute change dynamically when the underlying table is altered

CUST_ID and CUST_LIMIT are existing columns in the CUSTOMER table. Examine the following trigger code: CREATE OR REPLACE TRIGGER audit_cust AFTER UPDATE OF cust_credit_limit ON customer FOR EACH ROW BEGIN INSERT INTO audit_cust(user_name, change_time, cust_id, old_credit_limit, new_credit_limit) VALUES (USER, SYSDATE, :OLD.cust_id, :OLD.cust_credit_limit, :NEW.cust_credit_limit); COMMIT; END; What statement is true about the above trigger? A. It gives an error on compilation because it should be a statement-level trigger. B. It compiles and fires successfully when the credit limit is updated in the customer table. C. It gives an error on compilation because of the commit command in the trigger code. D. It compiles successfully, but gives an error when the credit limit is updated in the CUSTOMER table because the PRAGMA AUTONOMOUS_TRANSACTION statement should be introduced in the trigger.

D. It compiles successfully, but gives an error when the credit limit is updated in the CUSTOMER table because the PRAGMA AUTONOMOUS_TRANSACTION statement should be introduced in the trigger.

View the exhibit and examine the structure of the customer table. You need to create a trigger to ensure that customers in category 'A' and 'B' have a credit limit of more than 8000. Examine the following trigger. CREATE OR REPLACE TRIGGER verify_cust_category BEFORE INSERT ON customer BEGIN IF :NEW.cust_category IN ('A', 'B') and :NEW.cust_credit_limit < 8000 THEN RAISE_APPLICATION_ERROR (-20202, 'Credit limit cannot be less then 8000'); END IF; END; Which statement is correct about the outcome of this trigger? A. It compiles successfully and fires whenever the specified condition is met. B. It compiles successfully but does not fire even when the condition is met. C. It gives an error on compilation because the new qualifier is prefixed with a colon. D. It gives an error on compilation because the new qualifier can be used only in row-level triggers.

D. It gives an error on compilation because the new qualifier can be used only in row-level triggers.

View the Exhibit to examine the PL/SQL code. DECLARE past_due EXCEPTION; acct_num NUMBER; BEGIN DECLARE past_due EXCEPTION; acct_num NUMBER; due_date DATE := SYSDATE - 1; todays_date DATE := SYSDATE: BEGIN IF due_date < todays_date THEN RAISE past_due; END IF; END; EXCEPTION WHEN past_due THEN DBMS_OUTPUT.PUT_LINE('Handling PAST_DUE exception.'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Could not recognize exception.'); END; Which statement is true about the execution of the code? A. The exception raised in the code is handled by the exception handler for the PAST_DUE exception. B. It does not execute becauseyou cannot declare an exception with a similar name in the subblock. C. The PAST_DUE exception raised in the subblock causes the program to terminate abruptly because there is no exception handler in the subblock. D. The PAST_DUE exception raised by the enclosing block is not propagated to the outer block and it is handled by the WHEN OTHERS exception handler

D. The PAST_DUE exception raised by the enclosing block is not propagated to the outer block and it is handled by the WHEN OTHERS exception handler

The execution of the code produces errors. Identify the error in the code. DECLARE emp_name employee.last_name%TYPE; emp_job employee.job_id%TYPE; CURSOR c1 IS SELECT last_name, job_id FROM employees WHERE job_id LIKE '%CLERK%' AND manager_id > 120; BEGIN FOR emp_name, emp_job IN c1 LOOP DBMS_OUTPUT.PUT_LINE('Name = ' || emp_name || ', JOB = ' ||emp_job); END LOOP; END: A. The open cursor is missing. B. The fetch clause is missing. C. The exit when condition is missing. D. The emp_name and emp_job variables cannot be used in the for clause of the cursor FOR statement.

D. The emp_name and emp_job variables cannot be used in the for clause of the cursor FOR statement.

Examine the following snippet of code from the DECLARE section of PL/SQL: DECLARE cut_name VARCHAR2 (20) NOT NULL := 'tom jones': same_name cut_name%TYPE; A. The variable inherits only the data type from the CUT_NAME variable. B. The same_name variable inherits only the data type and default value from the CUT_NAME variable. C. The same_name variable inherits the data type, constraint, and default value from the CUT_NAME variable. D. The same_name variable inherits only the data type and constraint from the CUT_NAME variable resulting in an error

D. The same_name variable inherits only the data type and constraint from the CUT_NAME variable resulting in an error

/temp/my_files is an existing folder in the server, facultylist.txt is an existing text file in this folder Examine the following commands that are executed by the DBA: CREATE DIRECTORY my_dir AS ' /temp/my_files'; GRANT READ ON DIRECTORY my_dir TO public; View the exhibit and examine the procedure created by user SCOTT to read the list of faculty names from the text file. CREATE OR REPLACE PROCEDURE read_file (dirname VARCHAR2, txtfile VARCHAR2) IS f_file UTL_FILE.FILE_TYPE; v_buffer VARCHAR2(200); BEGIN f_file := UTL_FILE.FOPEN(dirname, txtfile, 'R'); LOOP UTL_FILE.GET_LINE(f_file, v_buffer); DBMS_OUTPUT.PUT_LINE(v_buffer); END LOOP; UTL_FILE.FCLOSE(f_file); END read_file; SCOTT executes the procedure as follows: SET SERVEROUTPUT ON EXEC read_file('my_dir', facultylist.txt'); What is the outcome? A. it goes into an infinite loop B. it executes successfully and displays only the list of faculty names C. it does not execute and displays an error message because the end-of-file condition is not taken care of D. it executes successfully and displays the list of faculty names followed by a "no data found" error message

D. it executes successfully and displays the list of faculty names followed by a "no data found" error message

Consider the following scenario: Local procedure A calls remote procedure B Procedure A was compiled at 8AM Procedure A was modified and recompiled at 9AM Remote procedure B was later modified and recompiled at 11AM The dependency mode is set to timestamp Which statement correctly describes what happens when procedure A is invoked at 1PM? A. procedure A is invalidated and recompiled immediately B. there is no effect on procedure A and it runs successfully C. procedure B is invalidated and recompiled again when invoked D. procedure A is invalidated and recompiles when invoked the next time

D. procedure A is invalidated and recompiles when invoked the next time

Examine the following PL/SQL code: (code is missing) SERVEROUTPUT is on for the session. Which statement is true about the output of the PL/SQL block? A. the output is x = y B. it produces an error C. the output is x != y D. the output is 'Can't tell if x and y are equal or not'

D. the output is 'Can't tell if x and y are equal or not'

In which of the following scenarios would you recommend using associative arrays? A. when you want to retrieve an entire row from a table and perform calculations B. when you know the number of elements in advance and the elements are usually accessed sequentially C. when you want to create a separate lookup table with multiple entries for each row of the main table and access it through join queries D. when you want to create a relatively small lookup table, where the collection can be constructed on memory each time a subprogram is invoked

D. when you want to create a relatively small lookup table, where the collection can be constructed on memory each time a subprogram is invoked

Identify the scenario in which you would use the current of clause for an update or delete statement to rows fetched from a cursor. A. when you want to lock the rows fetched by the cursor B. when you want to update or delete the result set without affecting the rows in the table C. when you want the database not to wait if the requested rows are locked by another user D. when you want to ensure that the current rows fetched by the cursor are updated or deleted

D. when you want to ensure that the current rows fetched by the cursor are updated or deleted

Examine the following code: DECLARE TYPE emprectyp IS RECORD (emp_name VARCHAR2(30), salary NUMBER(8,2)); FUNCTION highest_salary RETURN emprectyp IS emp_info emprectyp; CURSOR cur_emp_cursor IS SELECT ename, sal FROM employees WHERE sal = (SELECT MAX(sal) FROM emp); BEGIN FOR emp_info IN cur_emp_cursor LOOP RETURN emp_info; END LOOP: END highest_salary; BEGIN DBMS_OUTPUT.PUT_LINE('Emp: ' || highest_salary().emp_name || ' earns the highest salary of '|| highest_salary().salary); END; What is the outcome? A. It gives an error because the return type is not valid. B. It gives an error because the record type is not defined within the function. C. It gives an error because the function call in DBMS_OUTPUT.PUT_LINE is not valid. D. It executes successfully and displays the names and salaries of all employees who earn the highest salary. E. It executes successfully but does not display the names and salaries of all employees who earn the highest salary.

E. It executes successfully but does not display the names and salaries of all employees who earn the highest salary.

View the exhibit and examine the code: CREATE OR REPLACE PROCEDURE proc1 AS x CONSTANT BOOLEAN := TRUE; BEGIN IF x THEN DBMS_OUTPUT.PUT_LINE('TRUE'); ELSE DBMS_OUTPUT.PUT_LINE('FALSE'); END IF; END proc1; EXECUTE DBMS_WARNING_SETTING_STRING('DISABLE:ALL', 'SESSION'); CREATE OR REPLACE PROCEDURE compile_code (p_pkg_name VARCHAR2) IS v_warn_value VARCHAR2(200); v_compile_stmt VARCHAR2(200) := 'ALTER PACKAGE '|| p_pkg_name ||' COMPILE'; BEGIN v_warn_value := DBMS_WARNING.GET_WARNING_SETTING_STRING; EXECUTE IMMEDIATE v_compile_stmt; DBMS_WARNING.SET_WARNING_SETTING_STRING(v_warn_value, 'SESSION'); END; Which statement is true about the COMPILE_CODE procedure? A. It gives an error in line 6. B. It gives an error in line 8. C. It gives an error in line 5. D. It executes successfully, but displays a warning about the unreachable code when used for the proc1 procedure. E. It executes successfully, but a warning about the unreachable code is not displayed when used for the proc1 procedure.

E. It executes successfully, but a warning about the unreachable code is not displayed when used for the proc1 procedure.