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 AUTONOMOUSJTRANSACTION 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.
Why A, I think – B
Because, DML statements don’t raise NO_DATA_FOUND exception implicitly in this case.
Good answer!
I would be a good answer if the only NO_DATA_FOUND wold be catched.
I had run this script but it didnt get any error message get recorded
i think answer is A
I think B wants to say that: though error is not raised now, if any error message occured, it would appear in the debug table.
Srinivas is true about the NO_DATA_FOUND but the exception we are catching there is WHEN others, which actually could be many other things in a complex database environment and I this is where answer B becomes interesting.
Bala: what you say is true but the question here is not whether you get error message or not but rather if you get error whether if will be recorded in the table or not. So try to raise an error and you will see.
The story gets even more interesting if we have a look at the SUBSTR call. It does not actually takes the first 100 characters but the all characters from the position 100. It means most of the time no message gets recorded even if exception is raised. Saying that the right answer is probably A and has nothing to do with the AUTONOMOUS transaction but rather with the wrong usage of SUBSTR. I wonder what Oracle’s answer is to the question, wheteher the SUBSTR is a mistake?
A is correct answer.
B seems to be right, but not full, because sqlerrm is a tricky thing. lez test the code:
CREATE TABLE PRODUCTS(PROD_ID NUMBER(4) NOT NULL PRIMARY KEY,
PROD_NAME VARCHAR2(10) NOT NULL,
PROD_LIST_PRICE NUMBER(9,2) NOT NULL,
prod_valid varchar2(1));
CREATE TABLE SALE(PROD_ID NUMBER(4),
AMOUNT NUMBER(4),
constraint fk_sale_prod foreign key(prod_id) references products(prod_id));
INSERT INTO PRODUCTS VALUES(100,’apple’, 10,’Y’);
insert into sale values(100,20);
CREATE TABLE DEBUG_OUTPUT(MSG VARCHAR2(100));
SELECT * FROM PRODUCTS;
select * from sale;
create or replace procedure debugging(msg varchar2)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
dbms_output.put_line(msg);
INSERT INTO DEBUG_OUTPUT VALUES(msg);
COMMIT;
END DEBUGGING;
/
create or replace procedure delete_details(p_id number)
AS
MSG VARCHAR2(100);
BEGIN
DELETE FROM PRODUCTS WHERE PROD_ID=P_ID;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
MSG:=SUBSTR(SQLERRM, 100);
DBMS_OUTPUT.PUT_LINE(MSG);
dbms_output.put_line(sqlcode);
DEBUGGING(MSG);
debugging(sqlcode);
END DELETE_DETAILS;
/
EXEC DELETE_DETAILS(100);
select * from DEBUG_OUTPUT;
only the null are in each row!!
i must say nothing.
and then i add the sqlcode in the debuging, it shows something.
Thus i always use sqlcode in exception.
if not substr it would be B
I marked option A which is wrong 🙁 .. This means although it seems to appear that in most of the runs of above code, there will be no message logged in table.. but there can still be some scenario that if some error occurs within block.. it will definitely log the error message if it happens to be > 100 in length.
Right
There is no way you can say A. Since you have no info about the products table. What if products table has a trigger that raises an error when you delete the record? What if data file for the table is corrupt or unavailable at the time of the DELETE_DETAILS call? Those are just 2 ideas that come to mind.
And regarding Srinivas NO_DATA_FOUND – this has never been an issue or a question here!
Sure there are not errors now, but when there will be it will be in the table.
I’m not oracle, but I would say its B.
If prod_id is PK (or unique) delete can rise error.
Desc of product table haven’t setted prod_id as PK or unique, this is why i choose A. Even if by the name it seems to be a primary key.
In that case, the correct reply is B.
B !
In rare cases also a delete can raise an exception:
If the table has a foreign-key-constraint without “on delete cascade” or “on delete set null”
or if a DML-trigger or DDL-trigger raises an exception.
IS EVERYONE SURE THAT ITS NOT c AND d?
B
A is correct answer.
please All A or B
I think strongly B.
That’s why.
A => no error messages get recorded in the DEBUG_OUTPUT table.
WRONG!! Any error will get recorded in the DEBUG_OUTPUT table.
What error? Think about you’re deleting a record referenced by another table! Or if there’s a deadlock on the table?? You got a wonderful exception…
B => any error messages get recorded in the DEBUG_OUTPUT table.
That’s true. But we can say always “WRONG! What about there’s a problem on the DEBUG_OUTPUT table?”…
this question is very very bad. 🙂
BTW, I vote for B.
Answer should be A.
Even if we consider an error can occur, B says – ANY ERROR messages get recorded. This is certainly not true since if sqlerrm < 100 then the error is not recorded. Thus by elimination A looks correct.
there is not a correct answer.
A-> no error –> is triggers reise a reise_application_error , and the msg a huge text ….. then, A is worng
B-> any error … is wrong if the error is smaller as 100.
A is correct, in DEBUG_OUTPUT table will be inserted a new records with NULL message, because msg should be substr(sqlerrm, 1, 100), not msg := substr(sqlerrm, 100).