View the exhibit and examine the structure of the products table.
Examine the following code
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.
Explanation:
In this case, the debug output will only occur if there is an exception.
A
A
Because of SUBSTR(sqlerrm, 100)
Error Message is cutted off and this why “no error message was recorded”
It’s A because the delete will not raises any exception, so debug_output will never be invoked…
Still there is a possibility of recording error messages where sqlerrm> 100
Hence B is the correct answer
a
B
A
Correct Answer is A.
A. If there is an exception, then the debugging procedure will be called.
The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction.
A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction.
An autonomous transaction is an independent transaction that is initiated by another transaction, and executes without interfering with the parent transaction. When an autonomous transaction is called, the originating transaction gets suspended. Control is returned when the autonomous transaction does a COMMIT or ROLLBACK.
A trigger or procedure can be marked as autonomous by declaring it as PRAGMA AUTONOMOUS_TRANSACTION;. You may need to increase the TRANSACTIONS parameter to allow for the extra concurrent transactions.
C is incorrect.
A trigger or procedure can be marked as autonomous by declaring it as PRAGMA AUTONOMOUS_TRANSACTION
D is incorrect.
If an autonomous transaction attempts to access a resource held by the main transaction (which cannot resume until the autonomous routine exits), a deadlock can occur. Oracle raises an exception in the autonomous transaction, which is rolled back if the exception goes unhandled.
C is incorrect bc this pragma can be applied to stand alone procedures, and not just packaged procedures
The syntax for the SUBSTR function in Oracle/PLSQL is:
SUBSTR( string, start_position [, length ] )
SUBSTR(‘This is a test’, 6, 2)
Result: ‘is’
SUBSTR(‘This is a test’, 6)
Result: ‘is a test’
SUBSTR(‘TechOnTheNet’, 1, 4)
Result: ‘Tech’
SUBSTR(‘TechOnTheNet’, -3, 3)
Result: ‘Net’
SUBSTR(‘TechOnTheNet’, -6, 3)
Result: ‘The’
SUBSTR(‘TechOnTheNet’, -8, 2)
Result: ‘On’
DECLARE
v_error VARCHAR2(10);
BEGIN
SELECT ID INTO v_error
FROM employee;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(‘SQLERRM: ‘|| SUBSTR(SQLERRM, 100));
DBMS_OUTPUT.PUT_LINE(‘SQLCODE: ‘||SQLCODE);
END;
/
OUTPUT:
========================================
PL/SQL procedure successfully completed.
SQLERRM:
SQLCODE: -1422
Are these questions still valid for the 1z0-144 certification exam? cause i plan on taking it and would like to know please. WOuld really appreciate the help.
thanks
hi,
Are this dumps still valid ? if not then can someone please give me a valid link for recent dumps for FEB,2016
The answer will Probably be B
there is no data inserted in to the table, in the question.
So B will be right answer.
Tested it. It is B.
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;
/
show error
create or replace procedure delete_details (p_id number)
as
msg varchar2(100);
begin
delete from product where prod_id = p_id;
commit;
exception
when others then
msg:= substr(msg, 100);
debugging(msg);
end delete_details;
/
show error
execute delete_details(-100);
select * from debug_output
I don’t have any error message in your case
A
msg:= substr(sqlerrm, 100); is the tricky part.
sqlerrm is of length 24, and we are chosing 100 as starting postion which makes no sence as it will store null values in to debugging table.
So answer is A(tested and verified)
A