PL/SQL

Which of the following is a good reason to use two cursors in a single PL/SQL block?

N the following example, what statement belongs in Line A? ALTER SESSION SET PLSQL_CCFLAGS = ‘debug:true’; CREATE OR REPLACE PROCEDURE testproc IS BEGIN … $IF $$debug $THEN DBMS_OUTPUT.PUT_LINE(‘This code was executed’); — Line A … END testproc; ALTER SESSION SET PLSQL_CCFLAGS = ‘debug:false’;

Which of the following statements is true?

Nquiry directives are used to selectively include or exclude PL/SQL code based on values of pre-defined variables that are set using the PLSQL_CCFLAGS parameter.

What is wrong with this assignment statement? myvar := 'To be or not to be'; 'That is the question';

DECLARE v_age1 NUMBER(3); v_age2 NUMBER(3); v_message VARCHAR2(20); BEGIN CASE WHEN v_age1 = v_age2 THEN v_message := 'Equal'; WHEN v_age1 <> v_age2 THEN v_message := 'Unequal'; ELSE v_message := 'Undefined'; END CASE; DBMS_OUTPUT.PUT_LINE(v_message); END;

What would you code at Line A?

What kind of block is defined by the following PL/SQL code? BEGIN DBMS_OUTPUT.PUT_LINE('My first quiz'); END;

You have declared a cursor as follows: CURSOR loc_curs IS SELECT * FROM locations; How should you code a FOR loop to use this cursor?

What will happen when the following procedure is called as format_phone(8005551234)?CREATE OR REPLACE PROCEDURE format_phone(p_phone_no IN OUT VARCHAR2) ISBEGINp_phone_no := SUBSTR(p_phone_no,1,3) ||'.' || SUBSTR(p_phone_no,4,3) ||'.' || SUBSTR(p_phone_no,7);END format_phone;

Why is it better to use DBMS_OUTPUT only in anonymous blocks, not inside stored subprograms such as procedures?

Which of the following will display the number of invalid package bodies in your schema?

You can create a trigger which prevents DDL statements on an individual table, while still allowing DDL on other tables in the same schema.

Which of the following can be done using PL/SQL

Look at the following code:DECLARECURSOR emp_cursor IS SELECT * FROM employees;BEGINFOR emp_record IN emp_cursor LOOPDBMS_OUTPUT.PUT_LINE( --Point A -- );END LOOP;END; To display the salary of an employee, what code should you write at Point A?

Which data dictionary view allows you to see the setting for PLSQL_OPTIMIZE_LEVEL?

Which of the following will delete all employees who work in the Sales department?

Variables can be assigned a value in both the Executable and Declaration sections of a PL/SQL program. True or False?

When creating a procedure, where in the code must the parameters be listed?

What type of database object would you create to write an auditing record automatically every time a user connects to the database?

Examine the following code. What is the scope of variable v_myvar? DECLARE v_myvar NUMBER; BEGIN v_myvar := 6; DECLARE v_hervar NUMBER; BEGIN v_hervar := 4; END; END;

Which of the following statements about a package initialization block is true?

You want to calculate and display the multiplication table for "sevens": 7x1=7, 7x2=14, 7x3=21 and so on. Which kind of PL/SQL construct is best for this?

You want to create a function which drops a table. You write the following code: CREATE OR REPLACE FUNCTION droptab (p_tab_name IN VARCHAR2) RETURN BOOLEAN IS BEGIN DROP TABLE p_tab_name; RETURN TRUE; EXCEPTION WHEN OTHERS THEN RETURN FALSE; END; Why will this procedure not compile successfully?

In an INDEX BY table of records the record can be______

Which of the following is a PL/SQL programming environment?

Which of the following database objects are created when the utldtree.sql script is run?

The following table has been created: CREATE TABLE student_table (stud_id NUMBER(6), last_name VARCHAR2(20), first_name VARCHAR2(20), lunch_num NUMBER(4)); Which one of the following INSERT statements will fail?

Package NEWPACK contains several procedures and functions, including private function PRIVFUNC. From where can PRIVFUNC be invoked? (Choose two.)

A DML statement trigger fires only once for each triggering DML statement, while a row trigger fires once for each row processed by the triggering statement. True or False?

Which PL/SQL warning message identifies code that can cause unexpected behavior or wrong results when executed?

Package emp_pack contains two public procedures: get_emps and upd_emps. A separate procedure emp_proc invokes emp.pack.get_emps. The upd_emps package body code is now altered, and the package body (but not the package specification) is recreated. emp_proc will be marked invalid and needs to be recompiled. True or False?

You can use a database trigger to prevent invalid transactions from being committed. True or False?

Package OLDPACK is in your schema. What will happen when the following statement is executed? DROP PACKAGE oldpack;

A warning in PL/SQL is the same as an error in PL/SQL, but can only be viewed through the USER_ERRORS data dictionary view. True or False?

What value will v_answer contain after the following code is executed? DECLARE v_age NUMBER:= 18; v_answer VARCHAR2(10); BEGIN v_answer := CASE WHEN v_age < 25 THEN 'Young' WHEN v_age = 18 THEN 'Exactly 18' ELSE 'Older' END CASE; END;

In the following example, where do you place the phrase BULK COLLECT? … BEGIN SELECT — Position A salary — Position B INTO v_saltab — Position C FROM employees WHERE department_id = 20 ORDER BY salary — Position D ;

You have declared a cursor as follows: CURSOR loc_curs IS SELECT * FROM locations; How should you code a FOR loop to use this cursor?

The DBMS_OUTPUT package is useful for which of the following activities? (Choose two)

Which of the following are NOT allowed within a database trigger?

What is wrong with the following code? CREATE OR REPLACE TRIGGER emp_dept_trigg BEFORE UPDATE OR DELETE ON employees, departments BEGIN

Which of the following statements about implicit conversions is NOT true?

Examine the following code: DECLARE CURSOR region_cur IS SELECT * FROM wf_world_regions; v_region_rec region_cur%ROWTYPE; CURSOR country_cur (p_region_id NUMBER) IS SELECT * FROM wf_countries WHERE region_id = p_region_id; v_country_rec country_cur%ROWTYPE; BEGIN OPEN region_cur; LOOP FETCH region_cur INTO v_region_rec; EXIT WHEN region_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE (v_region_rec.region_name); -- Line A -- LOOP FETCH country_cur INTO v_country_rec; EXIT WHEN country_cur%NOTFOUND; ...... What would you code at Line A?

Which of the following keywords MUST be included in every PL/SQL procedure definition? (Choose three.) (Choose all correct answers)

Which of the following statements is true?

There are three employees in department 90. What will be displayed when this code is executed? DECLARE v_last_name employees.last_name%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE(‘Message 1’); BEGIN SELECT last_name INTO v_last_name FROM employees WHERE department_id = 90; DBMS_OUTPUT.PUT_LINE(‘Message 2’); END; DBMS_OUTPUT.PUT_LINE(‘Message 3’); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘Message 4’); END;

A trigger can be a public subprogram within a PL/SQL package. True or False?

What will be displayed when the following block is executed? DECLARE v_age NUMBER(3); v_gender VARCHAR2(6) := 'Female'; v_status VARCHAR2(20); BEGIN CASE WHEN v_age >= 18 AND v_gender = 'Male' THEN v_status := 'Adult Male'; WHEN v_age >= 18 AND v_gender = 'Female' THEN v_status := 'Adult Female'; WHEN v_age < 18 AND v_gender = 'Male' THEN v_status := 'Junior Male'; WHEN v_age < 18 AND v_gender = 'Female' THEN v_status := 'Junior Female'; ELSE v_status := 'Other Value'; END CASE; DBMS_OUTPUT.PUT_LINE(v_status); END;

While editing a document in Microsoft Word, you go to the FILE menu and SAVE your work. To do this, Microsoft Word has executed an application trigger. True or False?

An INDEX BY TABLE primary key cannot be negative.

Which good programming practice guideline would make this code easier to read? DECLARE v_sal NUMBER(8,2); BEGIN SELECT salary INTO v_sal FROM employees WHERE employee_id = 100; UPDATE employees SET salary = v_sal; END;

What is wrong with the following trivial IF statement: IF (v_job=’President’) THEN v_salary := 10000;