View Exhibit 1 and examine the structure of the EMP table.
SQL > desc emp
Name Null? Type
--------------- ------------ ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
View Exhibit 2 and examine the code of the packages that you have created.
CREATE OR REPLACE PACKAGE manage_emp IS
v_empno NUMBER;
PROCEDURE del_emp (p_empno NUMBER);
END manage_emp
/
CREATE OR REPLACE PACKAGE BODY mange_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_dat;
/
CREATE OR REPLACE PACKAGE BODY emp_dat 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:
SQL> DROP PACKAGE manage_emp;
What is the outcome?
View Exhibit 1 and examine the structure of the EMP table.
SQL > desc emp
Name Null? Type
--------------- ------------ ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
View Exhibit 2 and examine the code of the packages that you have created.
CREATE OR REPLACE PACKAGE manage_emp IS
v_empno NUMBER;
PROCEDURE del_emp (p_empno NUMBER);
END manage_emp
/
CREATE OR REPLACE PACKAGE BODY mange_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_dat;
/
CREATE OR REPLACE PACKAGE BODY emp_dat 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:
SQL> DROP PACKAGE manage_emp;
What is the outcome?
Examine the following partial declare section from a block of PL/SQL code
SQL > DECLARE
v_wage NUMBER NOT NULL := 1000;
v_total_wages v_wage%TYPE;
work_complete work_complete%TYPE;
Which line(s) in the above code are NOT valid? Choose that to apply.
Examine the following partial declare section from a block of PL/SQL code
SQL > DECLARE
v_wage NUMBER NOT NULL := 1000;
v_total_wages v_wage%TYPE;
work_complete work_complete%TYPE;
Which line(s) in the above code are NOT valid? Choose that to apply.
Which two guidelines are recommended by Oracle to reduce invalidation of dependent objects? Choose that to apply.
Which two guidelines are recommended by Oracle to reduce invalidation of dependent objects? Choose that to apply.
Which statement is true about transactions in PL/SQL?
Which statement is true about transactions in PL/SQL?
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?
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?
Identify the scenario in which you would use the current of clause for an update or delete statement to rows fetched from a cursor.
Identify the scenario in which you would use the current of clause for an update or delete statement to rows fetched from a cursor.
View the Exhibit and examine the structure of the SALGRADE table.
SQL > desc salgrade
Name Null? Type
----------- ---------- ----------
GRADE NOT NULL NUMBER
LOSAL NUMBER
HISAL NUMBER
Examine the following code:
SQL > VARIABLE min_sal NUMBER
SQL > VARIABLE max_sal NUMBER
SQL > CREATE OR REPLACE FUNCTION sal ok(salary NUMBER,jobgrade NUMBER0
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?
View the Exhibit and examine the structure of the SALGRADE table.
SQL > desc salgrade
Name Null? Type
----------- ---------- ----------
GRADE NOT NULL NUMBER
LOSAL NUMBER
HISAL NUMBER
Examine the following code:
SQL > VARIABLE min_sal NUMBER
SQL > VARIABLE max_sal NUMBER
SQL > CREATE OR REPLACE FUNCTION sal ok(salary NUMBER,jobgrade NUMBER0
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?
Examine the following block of code:
CREATE OR REPLACE FUNCTION del_rows
(p_table_name VARCHAR2,p_empno NUMBER);
RETURN NUUMBER IS
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM' || p_table_name || 'WHERE empno = ' || p_empno;
END;
/
Function created.
Which two statements are correct about the code above? Choose that to apply.
Examine the following block of code:
CREATE OR REPLACE FUNCTION del_rows
(p_table_name VARCHAR2,p_empno NUMBER);
RETURN NUUMBER IS
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM' || p_table_name || 'WHERE empno = ' || p_empno;
END;
/
Function created.
Which two statements are correct about the code above? Choose that to apply.
Which two statements are true about the PL/SQL initialization parameters? Choose that to apply.
Which two statements are true about the PL/SQL initialization parameters? Choose that to apply.
Examine the following code:
SQL > SET SERVEROUTPUT ON
SQL > VARIABLE n1 NUMBER
SQL > VARIABLE n2 NUMBER
SQL > VARIABLE n2 NUMBER
SQL > 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?
Examine the following code:
SQL > SET SERVEROUTPUT ON
SQL > VARIABLE n1 NUMBER
SQL > VARIABLE n2 NUMBER
SQL > VARIABLE n2 NUMBER
SQL > 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?
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?
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?
View Exhibit 1 and examine the structure of the employees table.
Name Null? Type
------------------- ----------- -------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMISSION_PCT NUMBER(6)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
View Exhibit 2 and examine the 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 increse;
/
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_salary(emp_num);
END cal_sal;
/
What is the outcome when the code is executed?
View Exhibit 1 and examine the structure of the employees table.
Name Null? Type
------------------- ----------- -------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMISSION_PCT NUMBER(6)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
View Exhibit 2 and examine the 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 increse;
/
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_salary(emp_num);
END cal_sal;
/
What is the outcome when the code is executed?
Which two statements are true about anonymous blocks and named subprograms? Choose that to apply.
Which two statements are true about anonymous blocks and named subprograms? Choose that to apply.
View Exhibit 1 and examine the structure of the EMP and DEPT tables.
SQL > DESC emp
Name Null? Type
----------------- ------------ -------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL > DESC dept
Name Null? Type
------------- ------------ -----------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
View Exhibit 2 and 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;
RAISE_APPLICATION_ERROR(-20001,'Employees Present in' || 'Department' || TO_CHAR(:OLD.DEPTNO));
WHEN employees_not_present THEN
CLOSE Dummy_cursor;
END;
/
What is the outcome on compilation?
View Exhibit 1 and examine the structure of the EMP and DEPT tables.
SQL > DESC emp
Name Null? Type
----------------- ------------ -------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL > DESC dept
Name Null? Type
------------- ------------ -----------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
View Exhibit 2 and 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;
RAISE_APPLICATION_ERROR(-20001,'Employees Present in' || 'Department' || TO_CHAR(:OLD.DEPTNO));
WHEN employees_not_present THEN
CLOSE Dummy_cursor;
END;
/
What is the outcome on compilation?
Examine the following PL/SQL code:
DECLARE
CURSOR cl 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?
Examine the following PL/SQL code:
DECLARE
CURSOR cl 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?
Which type of exceptions is qualified as nonpredefined Oracle server errors?
Which type of exceptions is qualified as nonpredefined Oracle server errors?
Identify situations in which the DBMS_SQL package is the only applicable method of processing
dynamic SQL. Choose that to apply.
Identify situations in which the DBMS_SQL package is the only applicable method of processing
dynamic SQL. Choose that to apply.
Examine the following code:
SQL > SET SERVEROUTPUT ON
SQL > DECLARE
date1 DATE := 'January 10, 2008';
date2 DATE := SYSDATE;
date_diff NUMBER;
BEGIN
date_diff := data2 - date1;
DBMS_OUTPUT.PUT_LINE('Differences in dates is' || date_diff);
END
/
The above code generates an error on execution.
What must you do to ensure that the code executes successfully?
Examine the following code:
SQL > SET SERVEROUTPUT ON
SQL > DECLARE
date1 DATE := 'January 10, 2008';
date2 DATE := SYSDATE;
date_diff NUMBER;
BEGIN
date_diff := data2 - date1;
DBMS_OUTPUT.PUT_LINE('Differences in dates is' || date_diff);
END
/
The above code generates an error on execution.
What must you do to ensure that the code executes successfully?
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 ?
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 ?
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?
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?