View the Exhibit and examine the code:
Which statement is true about the COMPILE_CODE procedure?
A.
It gives an error in line 6.
B.
It gives an error in line 8.
C.
It gives an error in line 5.
D.
It executes successfully, but displays a warning about the unreachable code when used for the
PROC1 procedure.
E.
It executes successfully, but a warning about the unreachable code is not displayed when used
for the PROC1 procedure.
C
This question contains mistake:
when you fire statement below you will get an error:
execute compile_code(‘proc1’);
BUT because of line 7:
EXECUTE IMMEDIATE ‘Alter Package PROC1 COMPILE’, Proc1 is PROCEDURE not package, package PROC1 is not established in this context
If to make compile statement correct, then answer will be E
Because all statements are correct, to be sure visit links below:
1) http://psoug.org/reference/dbms_warning.html
2) http://docs.oracle.com/cd/E18283_01/appdev.112/e17126/errors.htm#BABDGEJA
code for proc1 was taken fronm this link
You will not see warning about unreacheble code because:
a) You cannot see compiler warning firing execute immediate even if you use set ‘ENABLE:ALL’ flag.
b) In code was used category ‘PERFORMANCE’ it doesn’t indicate unreachable code.
Code that can never run indicated by category ‘INFORMATIONAL’
hello
you have been a good observer, indeed must, as you did, please read the questions to find errors.
Now having said that, and recognizing you merit,
I find myself in agreement with the response and how the right in the event that there was the error you mentioned.
I believe the answer
I believe that in this specific case, the answer would be D
simply because, even if after the establishment at session level, will be reset to their original state plsql warnings (disable: all), these have been changed before compilation, and therefore, in the compilation, it ended well, there would be their registration object filled (performance: enable), that you can see with the view user_plsql_object_settings
———————
instead,
remaining in the test as it is with all its mistakes, no other means,
the right answer, exactly as you wrote in the test, would occur to the line 7 as there is a package proc1 doesn’t exixst
but only in execution time, not in compilation time.
ORA-04043: object PROC1 does not exist
E
Why did you change your mind Piero?
Correct answer: E, if not for any other reason because the PERFORMANCE category does not catch this type of warning. For it to be catched you would have to use INFORMATIONAL.
And also in the code is “ALTER PACKAGE” were it should be “ALTER PROCEDURE”. When we invoke the compile_code procedure this way it gives an error.
the alter package/procedure mistake is irrilevant in this question. Put it’s just an error did by who project the question, I have to choose the E. No warning for these categories. An error yes, but no code unreachable error here.
So, imho it’s E.
are we suppose to know that unreachable code is informational warning?
some questions are silly
E
asnwer is E
create or replace procedure proc1 as
x constant boolean := true;
begin
if x then
dbms_output.put_line(‘TRUE’);
else
dbms_output.put_line(‘FALSE’);
end if;
end proc1;
execute dbms_warning.set_warning_setting_string(‘DISABLE:ALL’,’SESSION’);
create or replace procedure compile_code(p_pkg_name varchar2) IS
v_warn_value varchar2(200);
v_compile_stmt varchar2(200) := ‘ALTER PACKAGE ‘||p_pkg_name||’ COMPILE’;
BEGIN
v_warn_value := dbms_warning.get_warning_setting_string;
dbms_warning.add_warning_setting_cat(‘PERFORMANCE’,’ENABLE’,’SESSION’);
execute immediate v_compile_stmt;
dbms_warning.set_warning_setting_string(v_warn_value,’SESSION’);
end;
Answer is “E”
Test case:
SQL> call DBMS_WARNING.set_warning_setting_string (‘DISABLE:ALL’, ‘SESSION’);
Call completed.
SQL> call dbms_warning.add_warning_setting_cat(‘PERFORMANCE’,’ENABLE’,’SESSION’);
Call completed.
SQL> alter procedure proc1 compile;
Procedure altered.
===================================================
No any warnings! So, answer is “E”
We need to enable INFORMATIONAL to see warning:
===================================================
SQL> call dbms_warning.add_warning_setting_cat(‘INFORMATIONAL’,’ENABLE’,’SESSION’);
Call completed.
SQL> alter procedure proc1 compile;
SP2-0805: Procedure altered with compilation warnings
thanks for your nice explaination
E
Answer is D as “dbms_warning.add_warning_setting_cat(‘PERFORMANCE’,’ENABLE’,’SESSION’)” will not catch these type of warnings.
You should make it either ALL or INFORMATIONAL in order to work
Sorry Answer is E
E
E
??
E
I like this post, enjoyed this one thank you for posting. “It is well to give when asked but it is better to give unasked, through understanding.” by Kahlil Gibran.
CREATE OR REPLACE PROCEDURE proc1 AS
x CONSTANT BOOLEAN := TRUE;
BEGIN
IF x THEN
DBMS_OUTPUT.PUT_LINE(‘TRUE’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘FALSE’);
END IF;
END proc1;
/
EXECUTE DBMS_WARNING.SET_WARNING_SETTING_STRING(‘DISABLE:ALL’, ‘SESSION’);
CREATE OR REPLACE PROCEDURE compile_code(p_pkg_name VARCHAR2) IS
v_warn_value VARCHAR2(200);
v_compile_stmt VARCHAR2(200) := ‘ALTER PACKAGE ‘ || p_pkg_name || ‘ COMPILE ‘;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘TEST1’);
v_warn_value := DBMS_WARNING.GET_WARNING_SETTING_STRING;
DBMS_WARNING.ADD_WARNING_SETTING_CAT(‘PERFORMANCE’, ‘ENABLE’, ‘SESSION’);
EXECUTE IMMEDIATE v_compile_stmt;
DBMS_WARNING.SET_WARNING_SETTING_STRING(v_warn_value, ‘SESSION’);
DBMS_OUTPUT.PUT_LINE(‘TEST2’);
END;
/
EXEC compile_code(‘proc1’);
After execution:
ORA-04043: object PROC1 does not exist ORA-06512: at “SQL_JAXSSCAFHJTFYRALUDGZQDSJE.COMPILE_CODE”, line 8
ORA-06512: at line 1
ORA-06512: at “SYS.DBMS_SQL”, line 1721
So, it does show an error message, and not a warning…
Maybe this is the reason why the many before indicated the E option.