BELAJAR 1-Z0 144 PART 3

Soal sebelumnya

BELAJAR 1-Z0 144 PART 2

QUESTION 41
Examine the following code:

Which statement is true about the execution of the above code?

A. It executes and displays null.
B. It executes and the condition returns true.
C. It executes and control goes to the else statement.
D. It fails because no value is assigned to the v_myage variable.
Correct Answer#: C
Section: (none)
Explanation

karena nilai v_myage bernilai null, jika diberi inisial variabel maka akan muncul ‘I am a child’ atau kondisi if diberi nvl()

QUESTION 42

View Exhibit1 and examine the structure of the DO table.

View Exhibit2 and examine the code.

The anonymous block gives an error on execution. What Is the reason?

A. The assignment in line 7 is not valid.
B. The SQL does not support the Boolean data type.
C. A null value cannot be applied to the bind arguments In the using clause in line 10
D. The names of bind variables must be the same as the using clause bind arguments in line 10
Correct Answer#: A
Section: (none)
Explanation

QUESTION 43
Which two statements are true about the handling of internally defined or user-defined PL7SQL exceptions? (Choose two.)

A. Add exception handlers whenever errors occur.
B. An exception handler should commit the transaction.
C. Handle named exceptions whenever possible instead of using when others in exception handlers
D. Instead of adding exception handlers to your PL/SQL block, check for errors at every point where they may occur.
Correct Answer#: CD
Section: (none)
Explanation
Explanation/Reference:

Guidelines for Avoiding and Handling PL/SQL Errors and Exceptions
Because reliability is crucial for database programs, use both error checking and exception handling to ensure your program can handle all possibilities:
Add exception handlers whenever there is any possibility of an error occurring. Errors are especially likely during arithmetic calculations, string manipulation, and database operations. Errors could also occur at other times, for example if a hardware failure with disk storage or memory causes a problem that has nothing to do with your code; but your code still needs to take corrective action.
Add error-checking code whenever you can predict that an error might occur if your code gets bad input data. Expect that at some time, your code will be passed incorrect or null parameters, that your queries will return no rows or more rows than you expect.
Make your programs robust enough to work even if the database is not in the state you expect. For example, perhaps a table you query will have columns added or deleted, or their types changed. You can avoid such problems by declaring individual variables with %TYPE qualifiers, and declaring records to hold query results with %ROWTYPE qualifiers.
Handle named exceptions whenever possible, instead of using WHEN OTHERS in exception handlers. Learn the names and causes of the predefined exceptions. If your database operations might cause particular ORA- errors, associate names with these errors so you can write handlers for them. (You will learn how to do that later in this chapter.)
Test your code with different combinations of bad data to see what potential errors arise.
Write out debugging information in your exception handlers. You might store such information in a separate table. If so, do it by making a call to a procedure declared with the PRAGMA AUTONOMOUS_TRANSACTION, so that you can commit your debugging information, even if you roll back the work that the main procedure was doing.
Carefully consider whether each exception handler should commit the transaction, roll it back, or let it continue. Remember, no matter how severe the error is, you want to leave the database in a consistent state and avoid storing any bad data.

QUESTION 44
View the Exhibit and examine the structure of the departments table in SCOTT’s schema.

Examine the following block of code:

CREATE OR REPLACE PROCEDURE add_dept(
p_id NUMBER, p_name VARCHAR2) IS
BEGIN
INSERT INTO departments VALUES

create table emp_temp(deptno number(2), job varchar2(18));
Table created
SQL> set serveroutput on;
SQL>
SQL> declare
2 type NumList IS TABLE OF NUMBER;
3 depts NumList := NumList(10,20,30);
4 Begin
5 insert into emp_temp values (10,’Clerk’);
6 insert into emp_temp values (20,’Bookkeeper’);
7 insert into emp_temp values (30,’Analyst’);
8 forall j in depts.First .. depts.last
9 update emp_temp set job=job||’ (senior)’
10 where deptno=depts(j);
11 exception
12 when others then
13 dbms_output.put_line(‘problems in the forall statement’);
14 commit;
15 end;
16 /
problems in the forall statement
PL/SQL procedure successfully completed
SQL> select * from emp_temp;
DEPTNO JOB
—— ——————
10 Clerk (senior)
20 Bookkeeper
30 Analyst
SQL>

QUESTION 48
You want to create a trigger that fires whenever rows are deleted from the customer table and that displays the number of rows remaining in the table.
Which two statements are correct about the trigger to be created for the above requirement? (Choose two.)

A. It should be an after trigger.
B. It should be a before trigger.
C. It should be a row-level trigger.
D. It should be a statement-level trigger.

E. It can be a before or an after trigger.
Correct Answer#: AC
Section: (none)
Explanation

because he is say “display the number of rows remaining in the table” , that mean can’t display the number when use before ,because he do not how rows will be deleted

because when it before how can collect remaining record and i don’t know which or how many records will be deleted
another reason
when it statement level that means not necessary using before or after
and in choices have not “without before or after ”

QUESTION 49
Examine the following code that you plan to execute:

What correction should be performed in the above code?

A. The PROC2 procedure code should be defined in the package body.
B. The PROC3 procedure should be declared in the package specification.
C. The PROC3 procedure header should be declared at the beginning of the package body.
D. The variable x must be declared in the package body and removed from the specification,
Correct Answer#: AB
Section: (none)
Explanation

A. For successful compilation of the code, proc2 which is declared in package specification must be defined in package body.
B. true the procedure is valid but it’s useless unless it’s either declared under the package specification or a publicly available procedure such as PROC1 is calling PROC3 from within the package body

QUESTION 50
Examine the following command:

SQL>ALTER SESSION
SET plsql_warnings *
‘enable:severe’,
‘enable:performance’,
‘ERROR:05003’;

What is the implication of the above command?

A. It issues a warning whenever ERROR: 05003 occur during compilation.
B. It causes the compilation to fail whenever the warning ERROR.05003 occurs.
C. It issues warnings whenever the code causes an unexpected action or wrong results performance problems.
D. It causes the compilation to fail whenever the code gives wrong results or contains statements that are never executed.
Correct Answer#: C
Section: (none)
Explanation

PL/SQL warning messages are divided into categories, so that you can suppress or display groups of similar warnings during compilation. The categories are:
SEVERE: Messages for conditions that might cause unexpected behavior or wrong results, such as aliasing problems with parameters.
PERFORMANCE: Messages for conditions that might cause performance problems, such as passing a VARCHAR2 value to a NUMBER column in an INSERT statement.
INFORMATIONAL: Messages for conditions that do not have an effect on performance or correctness, but that you might want to change to make the code more maintainable, such as unreachable code that can never be executed.

QUESTION 51
Which two tasks should be created as functions instead of as procedures? (Choose two.)

A. reference host or bind variables in a PL7SQL block of code
B. tasks that compute and return multiple values to the calling environment
C. tasks that compute a value that must be returned to the calling environment
D. tasks performed in SQL that increase data independence by processing complex data analysis within the Oracle server, rather than by retrieving the data into an application
Correct Answer#: AB
Section: (none)
Explanation

A and C are Correct
cuz in option D it says process complex data means it invlove DML and that is not acceptable in SQL query if ur using function within it.

QUESTION 52
What is the correct definition of the persistent state of a packaged variable?

A. It is a private variable defined in a procedure or function within a package body whose value is consistent within a user session.
B. It is a public variable in a package specification whose value is consistent within a user session.
C. It is a private variable in a package body whose value is consistent across all current active sessions.
D. It is a public variable in a package specification whose value is always consistent across all current active sessions.
Correct Answer#: B
Section: (none)
Explanation
Persistent states in package variables and cursors
o You can keep track of the state of a package variable or cursor, which persists throughout the user session, form the time the user first references the variable or cursor to the time the user disconnects.
o It persists across transactions within a session
o It does not persist from session to session for the same user.
o The value of the variable is released when the user disconnects.
o Each session will have its own value for the variables.
o It persists across the transactions and through the user session.
o Reusable package will last only for each database call.
o PRAGMA SERIALLY_REUSABLE;
o Serially reusable version resets the state (and thus the output) each time.
o By the first time you call a procedure, function, or reference a global variable within the package, the whole package will be loaded into the memory and stay there.

QUESTION 53
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.
Correct Answer#: B
Section: (none)
Explanation

QUESTION 54
Which statements are true about PL/SQL procedures? (Choose all that apply.)

A. Users with definer’s rights who are granted access to a procedure that updates a table must be granted access to the table itself.
B. Reuse of parsed PL/SQL code that becomes available in the shared SQL area of the server avoids the parsing overhead of SQL statements at run time.
C. Depending on the number of calls, multiple copies of the procedure are loaded into memory for execution by multiple users to speed up performance.
D. A PL/SQL procedure executing on the Oracle database can call an external procedure or function that is written in a different programming language, such as C or Java.

Correct Answer#: BD
Section: (none)
Explanation

B is advantage of pl sql
c) is for parallel_enable function

QUESTION 55
Examine the following block of code:

Which line in the above code would result in errors upon execution?

A. line 5
B. line 8
C. line 2
D. line 7
Correct Answer#: B
Section: (none)
Explanation

in D can use condition in assign variable but can’t in return like in function
in B can’t assign data type with another data type (varchar2 := boolean)

QUESTION 56
Identify two situations where the DBMS_SQL package should be used. (Choose two.)

A. The select list is not known until run time.
B. The dynamic SQL statement retrieves rows into records.
C. You do not know how many columns a select statement will return, or what their data types will
D. You must use the %found SQL cursor attribute after issuing a dynamic SQL statement that is an insert or update statement.
Correct Answer#: AC
Section: (none)
Explanation

Oracle lets you write stored procedures and anonymous PL/SQL blocks that use dynamic SQL. Dynamic SQL statements are not embedded in your source program; rather, they are stored in character strings that are input to, or built by, the program at runtime. This enables you to create more general-purpose procedures. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.

With DBMS_SQL you must call VARIABLE_VALUE to retrieve the value of an OUT parameter for an anonymous block, and you must callCOLUMN_VALUE after fetching rows to actually retrieve the values of the columns in the rows into your program.

QUESTION 57
View Exhibit1 and examine the structure of the employees table.

View Exhibit2 and examine the code.

What would be the outcome when the code is executed?

A. It executes successfully.
B. It gives an error because the SAL variable is not visible in the increase function.
C. It gives an error because the increase function cannot be called from the RAISE_SALARY procedure.
D. It gives an error because the increase function and the RAISE_SALARY procedure should be declared at the beginning of the declare section before all the other declarations.
Correct Answer#: A
Section: (none)
Explanation

SAL IS GLOBAL TO THE ENTIRE BLOCK
AND FUNCTION AND PROC ARE DECLARED IN DECLARE SESSION

QUESTION 58
View the Exhibit and examine the structure of the customer table.

Examine the following trigger code:

What is the outcome when the above trigger is compiled?

A. It compiles successfully.
B. It gives an error because the when condition is not valid.
C. It gives an error because when cannot be used for row-level triggers.
D. It gives an error because the statements under updating are not valid.
E. It gives an error because the new qualifier in the when clause requires a colon prefix.
Correct Answer#: A
Section: (none)
Explanation

option C is wrong, when can not be sed in Stmt triggers

QUESTION 59
The STRING_TAB table has the following structure:

View the Exhibit and examine the code.

What is the outcome on execution?

A. It displays
Out put buffer not long enough.
This is my test string.-.
B. It displays only
Output buffer not long enough, and exits the anonymous block.
C. It displays only
This is my test string. – Because EXCEPTION should have been defined in the anonymous block to get the error message.
D. It does not display any of the MEMS_PUTPUT messages and gives an error because a transaction control statement cannot be used in the exception section of a procedure.
Correct Answer#: A
Section: (none)
Explanation
Explanation/Reference:

QUESTION 60
View the Exhibit and examine the code.

Why does the code give an error on execution?

A. because the WORD_LIST variable is not visible in procedure wording
B. because the lexicon variable is not visible in procedure ADD_ENTRY
C. because the lexicon variable is not initialized in procedure wording
D. because the WORD_LIST parameter in out mode cannot be of a record data type
Correct Answer#: A
Section: (none)
Explanation

variable word-list is local to the add_entry procedure.

Soal selanjutnya

BELAJAR 1-Z0 144 PART 4