View the Exhibit to examine the PL/SQL code.
SERVEROUTPUT is on for the session.
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 falls and throws exceptions if no employee with EMPLOYEE_ID us is found.
D.
The execution is successful, but it displays an incorrect output if no employee
withEMPLOYEE_ID 115 is found.
C
C
c
C.
Cause it will go to no_data_found exception.
select * from emp;
/
declare
a number:=1;
v_job emp.job%TYPE;
begin
SELECT job INTO v_job
FROM emp where empno =13;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘no data found!!’) ;
end;
/
Using Autonomous Triggers
Among other things, you can use database triggers to log events transparently.
Suppose you want to track all inserts into a table, even those that roll back. In the
example below, you use a trigger to insert duplicate rows into a shadow table. Because
it is autonomous, the trigger can commit changes to the shadow table whether or not
you commit changes to the main table.
— create a main table and its shadow table
CREATE TABLE parts (pnum NUMBER(4), pname VARCHAR2(15));
CREATE TABLE parts_log (pnum NUMBER(4), pname VARCHAR2(15));
— create an autonomous trigger that inserts into the
— shadow table before each insert into the main table
CREATE TRIGGER parts_trig
BEFORE INSERT ON parts FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO parts_log VALUES(:new.pnum, :new.pname);
COMMIT;
END;
— insert a row into the main table, and then commit the insert
INSERT INTO parts VALUES (1040, ‘Head Gasket’);
COMMIT;
— insert another row, but then roll back the insert
INSERT INTO parts VALUES (2075, ‘Oil Pan’);
Doing Independent Units of Work with Autonomous Transactions
Performing SQL Operations from PL/SQL 6-39
ROLLBACK;
— show that only committed inserts add rows to the main table
SELECT * FROM parts ORDER BY pnum;
PNUM PNAME
——- —————
1040 Head Gasket
— show that both committed and rolled-back inserts add rows
— to the shadow table
SELECT * FROM parts_log ORDER BY pnum;
PNUM PNAME
——- —————
1040 Head Gasket
2075 Oil Pan
Unlike regular triggers, autonomous triggers can execute DDL statements using native
dynamic SQL
Here is the code:
—————-
DECLARE
jobid employees.job_id%type;
empid EMPLOYEES.EMPLOYEE_ID%type;
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;
——————-
And here is re result:
Error report –
ORA-01403: no data found
ORA-06512: at line 7
01403. 00000 – "no data found"
*Cause: No data was found from the objects.
*Action: There was no data from the objects which may be due to end of fetch.
So C is the right answer.
.
C – tested the code also
C