Category: 1Z0-146

Exam 1Z0-146: Oracle Database 11g: Advanced PL/SQL Exam Preparation Material

What is the outcome?

View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.

Examine the PL/SQL block that you execute to find the average salary for employees in the
‘Sales’ department:

DECLARE
TYPE emp_sal IS TABLE OF employees.salary%TYPE INDEX BY VARCHAR2(20);
v_emp_sal emp_sal;

PROCEDURE get_sal(p_dept_name VARCHAR2, p_arr OUT emp_sal) IS
BEGIN
SELECT AVG(salary) INTO p_arr(p_dept_name)
FROM employees WHERE department_id=
(SELECT department_id FROM departments
WHERE department_name=p_dept_name);
END get_sal;

BEGIN
get_sal(‘Sales’,v_emp_sal);
DBMS_OUTPUT.PUT_LINE( v_emp_sal(‘Sales’));
END;
/
What is the outcome?

Identify two scenarios in which information is stored in the USAGE column.

Examine the following structure:
SQL> DESCRIBE user_identifiers
Name Null? Type
—————————————– ——– ———————–
NAME VARCHAR2(30)
SIGNATURE VARCHAR2(32)
TYPE VARCHAR2(18)
OBJECT_NAME NOT NULL VARCHAR2(30)
OBJECT_TYPE VARCHAR2(13)
USAGE VARCHAR2(11)
USAGE_ID NUMBER
LINE NUMBER
COL NUMBER
USAGE_CONTEXT_ID NUMBER

Identify two scenarios in which information is stored in the USAGE column. (Choose two.)(Exhibit)

What correction should you do to achieve the required result?

Examine the structure of the TEST_DETAILS table:
Name Null? Type
——————- ——– ————-
TEST_ID NUMBER
DESCRIPTION CLOB

DESCRIPTION data was entered earlier and saved for TEST_ID 12.
You execute this PL/SQL block to add data to the end of the existing data in the DESCRIPTION
column for TEST_ID 12:

DECLARE
clob_loc CLOB;
buf CHAR(12);
BEGIN
SELECT description INTO clob_loc FROM test_details WHERE test_id = 12 ;
buf := ‘0123456789’;
DBMS_LOB.WRITEAPPEND(clob_loc,DBMS_LOB.GETLENGTH(buf), buf);
COMMIT;
END;
/
It generates an error on execution.
What correction should you do to achieve the required result?

What is the outcome of executing the block of PL/SQL code?

Examine the structure of the EMPLOYEES table that exists in your schema.
Name Null? Type
————————– ————— ———————
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
DEPARTMENT_ID NUMBER(4)
You successfully create a GET_MAX procedure to find the maximum salary in the department of a specified employee.
You then code a PL/SQL block to display the maximum salary in the departments of the first five employees in the EMPLOYEES table.
View the Exhibit. Examine the procedure and the block of PL/SQL code.
What is the outcome of executing the block of PL/SQL code?


Page 1 of 1212345...10...Last »