BELAJAR 1-Z0 144 PART 2

Soal sebelumnya.

https://javanewbie.wordpress.com/database/oracle/belajar-1-z0-144-part-1/

 

QUESTION 20
Which two guidelines are recommended by Oracle to reduce invalidation of dependent objects? (Choose two.)

A. Reference tables indirectly by using views.
B. Reference tables directly avoid using views.
C. When adding new items to a package, add them to the end of the package.
D. When adding new items to a package, add them to the beginning of the package.
Correct Answer#: AC
Section: (none)
Explanation
Explanation/Reference:

Guidelines for Reducing Invalidation
To reduce invalidation of dependent objects, follow these guidelines:
Add New Items to End of Package
Reference Each Table Through a View

QUESTION 21
Examine the following partial declare section from a block of PL/SQL code

q21

Which line(s) in the above code are NOT valid? (Choose all that apply.)

A. line 2
B. line 3
C. line 4
D. line 5
Correct Answer#: BD
Section: (none)
Explanation

Type tidak bisa digunakan untuk menurunkan variabel. Hanya bisa menurunkan dari field suatu table.

QUESTION 22
View Exhibit 1 and examine the structure of the EMP and dept tables.

q22

View Exhibit2 and examine the trigger code that is defined on the dept table to enforce the update and delete restrict referential actions on the primary key of the dept table.

q22-2

What is the outcome on compilation?

A. It compiles and executes successfully.
B. It gives an error on compilation because it is not a row-level trigger.
C. It gives an error on compilation because the exception section Is used in the trigger.
D. It compiles successfully but gives an error on execution because it is not a row-level trigger.
Correct Answer#: B
Section: (none)
Explanation

:new atau :old identifier hanya bisa digunakan pada row-level trigger.

QUESTION 23
Identify situations in which the DBMS_SQL package is the only applicable method of processing dynamic SQL.
(Choose all that apply.)

A. When a query returns multiple rows
B. When a column name in a where clause is unknown at compile time.
C. When the number of columns selected in a query is not known until run time
D. When a table needs to be created based on an existing table structure at run time “First Test, E. When privileges need to be granted to a new user to access an existing schema at run time
Correct Answer#: BC
Section: (none)
Explanation

 

QUESTION 24
View the Exhibit and examine the structure of the SALGRADE table.

q24

Examine the following code:

q24-2

What is the outcome?

A. It is created successfully.
B. It gives an error because the return clause condition is invalid.
C. It gives an error because the usage of the host variables is invalid.
D. It gives an error because the data type of the return clause is invalid.
Correct Answer#: C
Section: (none)
Explanation

<unexplain/bingung nih, berubah dari B jadi C karena pendapat di forum -_- >

QUESTION 25
Identify the scenario in which you would use the current of clause for an update or delete statement to rows fetched from a cursor.

A. when you want to lock the rows fetched by the cursor
B. when you want to update or delete the result set without affecting the rows in the table ‘
C. when you want the database not to wait if the requested rows are locked by another user
D. when you want to ensure that the current rows fetched by the cursor are updated or deleted
Correct Answer#: B
Section: (none)
Explanation

If you plan on updating or deleting records that have been referenced by a SELECT FOR UPDATE statement, you can use the WHERE CURRENT OF statement.

The WHERE CURRENT OF statement allows you to update or delete the record that was last fetched by the cursor.

QUESTION 26
View the Exhibit to examine the PL/SQL code.

q26

Which statement is true about the execution of the code?

A. The exception raised in the code is handled by the exception handler for the PAST_DUE exception.
B. It does not execute because you cannot declare an exception with a similar name in the subblock.
C. The PAST_DUE exception raised in the subblock causes the program to terminate abruptly because there is no exception handler in the subblock.
D. The PAST_DUE exception raised by the enclosing block is not propagated to the outer block and it is handled by the WHEN OTHERS exception handler
Correct Answer#: D
Section: (none)
Explanation

The enclosing block does not handle the raised exception because the declaration of past_due in the sub-block prevails. Though they share the same name, the two past_due exceptions are different, just as the two acct_num variables share the same name but are different variables. Thus, the RAISE statement and the WHEN clause refer to different exceptions. To have the enclosing block handle the raised exception, you must remove its declaration from the sub-block or define an OTHERS handler.

QUESTION 27
Examine the following code:

q27

The above code generates an error on execution.
What must you do to ensure that the code executes successfully?

A. Use the TO_DATE function in line 2.
B. Use the TO_DATE function in line 7.
C. Use the TO_NUMBER function in line 6.
D. Use both the TO_DATE function in line 2 and the TO_NUMBER function in line 6.
Correct Answer#: A
Section: (none)
Explanation

variable date1 masih dalam bentuk format string yang tidak bisa langsung dilakukan konversi implicit, jadi harus menggunakan function todate.

QUESTION 28
Examine the following block of code:

q28

Which two statements are correct about the code above? (Choose two.)

A. The function goes through only the parse and executes phases.
B. The function goes through the parse, bind, and execute phases.
C. The function goes through the parse, bind, execute, and fetch phases.
D. All the processing phases for the function are performed only at run time.
E. Only the EXECUTE IMMEDIATE statement inside the function is parsed at run time.
Correct Answer#: DE
Section: (none)
Explanation
D,E is answer
A,B,C is not correct – function doesn’t go through the parse. Only the EXECUTE IMMEDIATE statement inside the function is parsed at run time. So “E” excludes “A”.
Benefits of Stored Procedures and Functions:
Improved performance
– Avoid reparsing for multiple users by exploiting the shared SQL area
– Avoid PL/SQL parsing at run time by parsing at compile time

QUESTION 29
Examine the following code:

q29

What is the outcome?

A. The procedure is created successfully and displays the values 20 and 30 when it is called.
B. The procedure gives errors because the parameters should be in out mode.
C. The procedure gives errors because the host variables cannot be referenced anywhere in the definition of a PL/SQL stored procedure.
D. The procedure is created successfully but does not display any values when it is called because the host variables cannot be displayed inside the procedure.
Correct Answer#: A
Section: (none)
Explanation

 

QUESTION 30
Examine the following PL/SQL code:

q30

The server output is on for the session. Which statement is true about the execution of the code?

A. It displays null if no employee with empioyee_id 123 exists.
B. It produces the ora-01403: no data found error if no employee with empioyee_id 123 exists.
C. It displays an error because the select into clause cannot be used to populate the PL/SQL record type.
D. The code executes successfully even if no employee with empioyee_id 123 exists and displays Record Not Found.
Correct Answer#: B
Section: (none)
Explanation

select into jika tidak mendapatkan hasil dari querynya akan menimbulkan exception no data found, kecuali melalui cursor ataupun delete/update select statement.

QUESTION 31
Which statement is true about transactions in PL/SQL?

A. A transaction can span multiple blocks.
B. A block can contain only a single transaction.
C. SERVERPOINTS cannot be created in a PL/SQL block.
D. The END keyword signals the end of a PL/SQL block and automatically commits the transaction in the block.
Correct Answer#: A
Section: (none)
Explanation

 

QUESTION 32
View the Exhibit to examine the PL/SQL code:

q32

SREVROUPUT is on for the session. Which statement Is true about the output of the PL/SQL block?

A. The output is x = y.
B. It produces an error.
C. The output Is x != y.
D. The output Is Can’t tell if x and y are equal or not.
Correct Answer#: A
Section: (none)
Explanation

 

QUESTION 33
User SCOTT has been granted CREATE ANY TRIGGER AND ALTER ANY TABLE by the DBA.
HR is an existing schema in the database.
SCOTT creates the following trigger:

CREATE OR REPLACE TRIGGER drop_trigger
BEFORE DROP ON hr.SCHEMA
BEGIN
RAISE_APPLICATION_ERROR (-20000, `Cannot drop object’);
END:

SCOTT does not grant the execute privilege on this trigger to any other users. For which user(s) would this trigger fire by default when they drop an object in the hr schema?

A. Only HR
B. SCOTT and HR
C. Only SCOTT
D. SCOTT, HR, and SYS
Correct Answer#: A
Section: (none)
Explanation
Explanation/Reference:
A is correct ans bcs Scott has only create trigger and alter table rights.
And even if drop table/object right is given to scott then also the object gets dropped but the trigger does not get fire.
It is fired only when HR drops the object.

DATABASE — The trigger fires whenever any database user initiates
the triggering event.
SCHEMA — The trigger is created on a schema and fires whenever the
user who owns it is the current user and initiates the triggering event.
For Schema, only “the schema owner intiates the trigger event” can fire the trigger. Even though Scott has the drop permission on HR schema, “Scott drop one HR table” cannot fire the trigger.

QUESTION 34
ORDER_TOTAL is a column in the orders table with the data type and size as number(8, 2) Examine the following code:

q34

Which statement is correct about the above code?

A. It gives an error in line 3
B. It gives an error in line 4
C. It gives an error in line 6
D. It executes successfully and displays the output.
Correct Answer#: B
Section: (none)
Explanation

Type tidak bisa digunakan untuk menurunkan variabel. Hanya bisa menurunkan dari field suatu table.

QUESTION 35
Which two statements are true about the continue statement? (Choose two.)

A. The PL/SQL block execution terminates immediately.
B. The CONTINUE statement cannot appear outside a loop.
C. The loop completes immediately and control passes to the statement after end loop.
D. The statements after the continue statement in the iteration are executed before terminating the LOOP.
E. The current iteration of the loop completes immediately and control passes to the next iteration of the loop
Correct Answer#: BE
Section: (none)
Explanation

The CONTINUE statement exits the current iteration of a loop, either conditionally or unconditionally, and transfer control to the next iteration. You can name the loop to be exited.

If and only if the value of this expression is TRUE, the current iteration of the loop (or the iteration of the loop identified by label_name) is exited immediately.

An unconditional CONTINUE statement (that is, one without a WHEN clause) exits the current iteration of the loop immediately. Execution resumes with the next iteration of the loop.

A CONTINUE statement can appear anywhere inside a loop, but not outside a loop.
If you use a CONTINUE statement to exit a cursor FOR loop prematurely (for example, to exit an inner loop and transfer control to the next iteration of an outer loop), the cursor is closed automatically (in this context, CONTINUE works like GOTO). The cursor is also closed automatically if an exception is raised inside the loop.

QUESTION 36
View the Exhibit and examine the blocks of code that you plan to execute.

q36

Which statement is true about the blocks of code?

A. All the blocks execute successfully and the anonymous block displays cant: 45 cnt: 45
B. All the blocks execute successfully and the anonymous block displays cut: 0 cart: 1
C. The anonymous block gives an error because the function invocation in line 2 is not valid.
D. The procedure creation gives an error because the function invocation in line 1 is not valid.
Correct Answer#: A
Section: (none)
Explanation

create or replace function dflt RETURN NUMBER IS
cnt NUMBER := 0;
BEGIN
CNT := CNT + 1;
RETURN 45;
END DFLT;
CREATE OR REPLACE PROCEDURE P(I IN NUMBER DEFAULT DFLT()) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(I);
END P;
DECLARE
CNT NUMBER := DFLT();
BEGIN
FOR J IN 1..3 LOOP
P(J);
END LOOP;
DBMS_OUTPUT.PUT_LINE (‘CNT ‘ || CNT);
P();
DBMS_OUTPUT.PUT_LINE(‘CNT ‘ || CNT);
END;

the output is :
1
2
3
CNT 45
45
CNT 45

QUESTION 37
Which statement is true about triggers on data definition language (DDL) statements?

A. They can be used to track changes only to a table or Index.
B. They can be defined by all users in the database or only by a specific user.
C. They are fired only when the owner of the object Issues the DDL statement.
D. They can be used to track changes to a table, table space, view, or synonym.
Correct Answer#: AB
Section: (none)
Explanation

Data Manipulation Language (DML) statements
DML triggers are available to fire whenever a record is inserted into, updated in, or deleted from a table. These triggers can be used to perform validation, set default values, audit changes, and even disallow certain DML operations.

Data Definition Language (DDL) statements
DDL triggers fire whenever DDL is executed for example, whenever a table is created. These triggers can perform auditing and prevent certain DDL statements from occurring.
Oracle allows you to define triggers that will fire when Data Definition Language (DDL) statements are executed. Simply put, DDL is any SQL statement used to create or modify a database object such as a table or an index. Here are some examples of DDL statements:
CREATE TABLE
ALTER INDEX
DROP TRIGGER
Each of these statements results in the creation, alteration, or removal of a database object.
The syntax for creating these triggers is remarkably similar to that of DML triggers, except that the firing events differ and they are not applied to individual tables.
Database events
Database event triggers fire whenever the database starts up or is shut down, whenever a user logs on or off, and whenever an Oracle error occurs. For Oracle8i Database and above, these triggers provide a means of tracking activity in the database.
INSTEAD OF
INSTEAD OF triggers are essentially alternatives to DML triggers . They fire when inserts, updates, and deletes are about to occur; your code specifies what to do in place of these DML operations. INSTEAD OF triggers control operations on views, not tables. They can be used to make non-updateable views updateable and to override the behavior of views that are updateable. These triggers are not covered further in this section, as they are a specialized topic that requires thorough coverage to be useful.

QUESTION 38
Which two statements are correct about PL/SQL package components? (Choose two)

A. A package must have both specification and body.
B. A package body can exist without the package specification.
C. A package specification can exist without the package body.
D. When a packaged public variable is called for the first time in a session, the entire package is loaded into memory.
Correct Answer#: CD
Section: (none)
Explanation

A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. A package is compiled and stored in the database, where many applications can share its contents.
A package always has a specification, which declares the public items that can be referenced from outside the package. For more information about the package specification, see “Package Specification”.
If the public items include cursors or subprograms, then the package must also have a body. The body must define queries for public cursors and code for public subprograms. The body can also declare and define private items that cannot be referenced from outside the package, but are necessary for the internal workings of the package. Finally, the body can have an initialization part, whose statements initialize variables and do other one-time setup steps, and an exception-handling part. You can change the body without changing the specification or the references to the public items; therefore, you can think of the package body as a black box. For more information about the package body, see “Package Body”.

Package public variables and cursors can persist for the life of a session. They can be shared by all subprograms that run in the environment. They let you maintain data across transactions without storing it in the database. (For the situations in which package public variables and cursors do not persist for the life of a session, see “Package State”.)

QUESTION 39
View Exhibit1 and examine the structure of the EMP table.

q39

View Exhibit2 and examine the code.

q39-2

EKPNOS 7845 and 7900 exist in the EMP table.
Which two calls to the RAISE_SALABY procedure in the anonymous block execute successfully? (Choose two.)

A. call in line 6
B. call in line 7
C. call in line 8
D. call in line 9
Correct Answer#: CD
Section: (none)
Explanation

jumlah parameter pemanggil harus sama dengan jumlah parameter yang dideklarasikan. Untuk urutan dapat dirubah dengan menambahkan nama pada saat pemanggilan. Jika jumlah paramater kurang, pastikan yang kurang adalah variabel yang memiliki nilai default.

QUESTION 40
In which of the following scenarios would you recommend using associative arrays?

A. when you want to retrieve an entire row from a table and perform calculations
B. when you know the number of elements in advance and the elements are usually accessed sequentially
C. when you want to create a separate lookup table with multiple entries for each row of the main table, and access it through join queries (Nested tables)
D. When you want to create a relatively small lookup table, where the collection can be constructed on memory each time a subprogram is invoked(Associative arrays).
Correct Answer#: D
Section: (none)
Explanation

Associative arrays are appropriate for relatively small lookup tables where the collection can be constructed in memory each time a procedure is called or a package is initialized. They are good for collecting information whose volume is unknown beforehand, because there is no fixed limit on their size. Their index values are more flexible, because associative array subscripts can be negative, can be nonsequential, and can use string values instead of numbers.

 

Soal selanjutnya..

https://javanewbie.wordpress.com/database/oracle/belajar-1-z0-144-part-3/

%d bloggers like this: