BELAJAR 1-Z0 144 PART 4

Soal Sebelumnya

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

 

QUESTION 61
Which two statements are correct about the usage of parameters in functions? (Choose two.)

A. Functions can have only in mode parameters.
B. Functions called in SQL statements cannot have out or in out mode parameters.
C. Functions having in, out, or in out parameters can be called only in named PL/SQL subprograms
D. Functions having in, out, or in out parameters can be called In PL/SQL procedures and anonymous blocks.
Correct Answer#: BD
Section: (none)
Explanation

In SQL (not PL SQL),
Functions called cannot have out or in out mode parameters.

1) IN type parameter: These types of parameters are used to send values to stored procedures.
2) OUT type parameter: These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
3) IN OUT parameter: These types of parameters are used to send values and get values from stored procedures.

QUESTION 62
/temp/my_files is an existing folder in the server, facultylist.txt is an existing text file in this folder Examine the following commands that are executed by the DBA:

SQL>CREATE DIRECTION my_dir AS ` /temp/my_files’:
SQL>GRANT READ ON DIRECTORY my_dir To pubiic:

View the Exhibit and examine the procedure created by user SCOTT to read the list of faculty names from the text file.

q62

SCOTT executes the procedure as follows:

SQL>SET SERVEROUTPUT ON
SQL>EXEC read_file (`MY_DIR’, FACULTYLIST.TXT’)

What is the outcome?

A. It goes into an infinite loop.
B. It executes successfully and displays only the list of faculty names.
C. It does not execute and displays an error message because the end-of-file condition is not taken care of.
D. It executes successfully and displays the list of faculty names followed by a “no data found” error message.
Correct Answer#: B
Section: (none)
Explanation

QUESTION 63
Which two statements correctly differentiate functions and procedures? (Choose two.)

A. A function can be called only as part of a SQL statement, whereas a procedure can be called only as a PL7SQL statement.
B. A function must return a value to the calling environment, whereas a procedure can return zero or more values to its calling environment.
C. A function can be called as part of a SQL statement or PL/SQL expression, whereas a procedure can be called only as a PL/SQL statement.
D. A function may return one or more values to the calling environment, whereas a procedure must return a single value to its calling environment.
Correct Answer#: BC
Section: (none)
Explanation

QUESTION 64
View the Exhibit to examine the PL/SQL block.

q64

Which statement is true about the execution of the PL/SQL block?

A. It executes successfully and gives the desired output.
B. It does not execute because the definition of type population is indexed by varchar2.
C. It executes, and the string keys of an associative array are not stored in creation order, but in sorted order.
D. It does not execute because the value that is once assigned to the element of the associative array cannot be changed.
Correct Answer#: A
Section: (none)
Explanation

QUESTION 65
Which two statements are true about triggers? (Choose two.)

A. All the triggers that are created on a table cannot be disabled simultaneously.
B. Any user who has the alter privilege on a table can create a trigger using that table.
C. Oracle provides a two-phase commit process whether a trigger updates tables in the local database or remote tables in a distributed database.
D. Triggers become invalid if a dependent object, such as 3 stored subprogram that is invoked from the trigger body is modified, and have to be manually recompiled before the next invocation.
Correct Answer#: CD
Section: (none)
Explanation

C) two phase commit uses in distributed transactions
http://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_txns003.htm
D) after modifications of related sumbrogramms trigger became invalid
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#LNPLS20071

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

q66

View Exhibit2 and examine the code created by the user SCOTT:

SCOTT grants the necessary privileges to green to access the EMP table and execute the package.
Examine the following sequence of activities:

SCOTT starts a session and issues the SQL>EXEC CURS_PKG.OPEN command.
SCOTT then issues the SQL>EXEC CURS_PKG.NEXT command.
green starts a session while SCOTT’s session is running and issues THE SQL>EXEC CURS_PKG.NEXT command.
SCOTT issues the SQI>>EXEC SCOTT.CURS_PKG.NEXT command.

The EMP table contains sequential EMPNOS from 100 through 108.
Which statement correctly describes the output?

A. SCOTT’s session shows the EMPNO 100, GREEN’S session shows an error, and SCOTT’s session shows an error.
B. SCOTT’s session shows the EMPNO 100, GREEN’S session shows EMPNO 100, and SCOTT’s session shows the EMPNO 101.
C. SCOTT’s session shows the EMPNO 100, GREEN’S session shows an error, and SCOTT’s session shows the second EMPNO 101.
D. SCOTT’s session shows the EMPNO 100, GREEN’S session shows EMPNO 101, and SCOTT’s session shows the second EMPNO 102.
Correct Answer#: C
Section: (none)
Explanation
green will get this error: ORA-01001: invalid cursor, because Green didn’t call the open method.

QUESTION 67
View the Exhibits and examine the structure of the EMPLOYEES, DEPARTMENTS AND

q67

EMP_BY_DEPT tables.

q67-2

EMPLOYEES

q67-3

DEPAERTMENT

EMP_BY_DEPT

Examine the following code:

What is the outcome on execution of the above code?

A. It executes successfully but the output statements show different values.
B. It executes successfully and both output statements show the same values.
C. It gives an error because the SQL%ROWCOUNT attribute cannot be used with BULK COLLECT.
D. It gives an error because the INSERT SELECT construct cannot be used with the FORALL
Correct Answer#: A
Section: (none)
Explanation

For FORALL, %ROWCOUNT returns the total number of rows affected by all the FORALL statements executed, not simply the last statement.

C is incorrect.
SQL%BULK_ROWCOUNT attribute gives the number of rows affected by the FORALL statement.
SQL cursor has one more attribute %BULK_ROWCOUNT on top of its regular attributes SQL%ISOPEN, SQL%FOUND, SQL%ROWCOUNT

D is also incorrect.
The BULK COLLECT clause can appear in:
SELECT INTO statement, FETCH statement
RETURNING INTO clause of:
DELETE, INSERT, UPDATE, and EXECUTE IMMEDIATE statement

sql%Bulk_rowcount(deptnums.count) will return last batch of inserted rows and it is less than overall amount of inserted records, whicjh is sql%rowcount

QUESTION 68
Examine the following partial code:

q68

Which statement is correct about the unnamed block of code at the end of a package body?

A. It generates an error because all the blocks of code in a package body must be named.
B. It generates an error because V_TAXRATE is a public variable that is already initialized in the package specification.
C. It acts as a package initialization block that executes once, when the package is first invoked within the user session.
D. It acts as a package initialization block that executes each time a package subprogram is invoked within the user session and refreshes the initialized variable value.
Correct Answer#: C
Section: (none)
Explanation

A is incorrect. The CURSOR FOR LOOP will automatically terminate when all of the records in the cursor have been fetched. With each iteration, the cursor FOR LOOP statement fetches a row from the result set into the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor.

The body holds implementation details and private declarations, which are hidden from code outside the package. Following the declarative part of the package body is the optional initialization part, which holds statements that initialize package variables and do any other one-time setup steps.
the body can have an initialization part, whose statements initialize public variables and do other one-time setup steps. The initialization part runs ONLY THE FIRST time the package is referenced. The initialization part can include an exception handler.

QUESTION 69
You want to store values of different data types in a PL/SQL block and store one record at a time for processing the information.
Which type of composite data type would you choose to fulfill the requirement?

A. VARRAYS
B. Nested table
C. PL/SQL records
D. Associative arrays

Correct Answer#: C
Section: (none)
Explanation

Record types are composite data structures whose fields can have different datatypes. You can use records to hold related items and pass them to subprograms with a single parameter. When declaring records, you use the TYPE definition.
Usually you would use a record to hold data from an entire row of a database table. You can use the %ROWTYPE attribute to declare a record that represents a row in a table or a row from a query result set, without specifying the names and types for the fields. When using %ROWTYPE, the record type definition is implied, and the TYPE keyword is not necessary.

QUESTION 70
Which two statements are true about the %ROWTYPE attribute? (Choose two.)

A. It is used to declare a record that can hold multiple rows of a table.
B. The attributes of fields in the record with the %ROWTYPE attribute can be modified manually.
C. The attributes of fields in the record take their names and data types from the columns of the table, view, cursor, or cursor variable.
D. It ensures that the data types of the variables that are declared with the %ROWTYPE attribute change dynamically when the underlying table is altered.
Correct Answer#: CD
Section: (none)
Explanation

The %ROWTYPE attribute provides a record type that represents a row in a database table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. Variables declared using %ROWTYPE are treated like those declared using a datatype name. You can use the %ROWTYPE attribute in variable declarations as a datatype specifier.
Fields in a record and corresponding columns in a row have the same names and datatypes. However, fields in a %ROWTYPE record do not inherit constraints, such as the NOT NULL column or check constraint, or default values.

Declaring variables as the type table_name%ROWTYPE is a convenient way to transfer data between database tables and PL/SQL. You create a single variable rather than a separate variable for each column. You do not need to know the name of every column. You refer to the columns using their real names instead of made-up variable names. If columns are later added to or dropped from the table, your code can keep working without changes.

QUESTION 71
Which type of exceptions is qualified as no predefined Oracle server errors?

A. the exceptions that are explicitly raised by the program and can be caught by the exception handler
B. the exceptions that are raised implicitly by the Oracle server and can be caught by the exception handler
C. an exception that the developer determines as abnormal, are in the declarative section and raised explicitly
D. an exception that is raised automatically when the PL/SQL program violates a database rule or exceeds a system-dependent limit
Correct Answer#: C
Section: (none)
Explanation

EXCEPTION TYPE : User Defined Errors
DESCRIPTION : It is any condition that the developer determines, which is abnormal.
HANDLING TIP : Declare with in the Declarative Section and Raise Explicitly.

The central issue here is how the question is written. I think the phrase “no predefined Oracle server errors” could mean basically what in the list is not any type of predefined error, in other words a User-defined error. It tricky wording and I think it would confuse most people.

QUESTION 72
View Exhibit 1 and examine the structure of the EMP table.

q72

View Exhlbit2 and examine the code of the packages that you have created.

q72-2

You issue the following command:

SQL> DROP PACKAGE manage_emp;

What is the outcome?

A. It drops both the MANAGE_EMP AND EMP__DET packages because of the cascading effect.
B. It drops the MANAGE_EMP package and invalidates only the body for the EMP_DET package.
C. It returns an error and does not drop the MAMAGE_EMP package because of the cascading effect.
D. It drops the MANAGE_EMP package and invalidates both the specification and body for the EMP_DET package.
Correct Answer#: B
Section: (none)
Explanation

QUESTION 73
Examine the following block of code:

q73

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

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 74
View Exhibit 1 and examine the structure of the EMP table.

q74

View Exhlbit2 and examine the code of the packages that you have created.

q74-2

You issue the following command:

SQL> DROP PACKAGE manage_emp;

What is the outcome?

A. It drops both the MANAGE_EMP AND EMP__DET packages because of the cascading effect.
B. It drops the MANAGE_EMP package and invalidates only the body for the EMP_DET package.
C. It returns an error and does not drop the MAMAGE_EMP package because of the cascading effect.
D. It drops the MANAGE_EMP package and invalidates both the specification and body for the EMP_DET package.
Correct Answer#: B
Section: (none)
Explanation

QUESTION 75
Examine the following PL/SQL code:

q75

Which statement is true about the fetch statements in the PL/SQL code?

A. Each fetch retrieves the first row and assigns values to the target variables.
B. Each fetch retrieves the next consecutive row and assigns values to the target variables.
C. They produce an error because you must close and reopen the cursor before each fetch – statement
D. Only the first fetch retrieves the first row and assigns values to the target variables- the second produces an error.
Correct Answer#: B
Section: (none)
Explanation

Each fetch on the cursor acts like each iteration of the loop.
like…
for rec in c1
loop
dbms_output.put_line(rec.last_name);
end loop;

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

q76

View Exhibit2 and examine the code.

q76-2

What is the outcome when the code is executed?

A. Both blocks compile and execute successfully when called.
B. Both blocks compile successfully but the CALC_SAL procedure gives an error on execution.
C. The CALC_SAL procedure gives an error on compilation because the amt variable should be declared in the RAISE_SALARY procedure.
D. The CALC_SAL procedure gives an error on compilation because the RAISE_SALARY procedure cannot call the stand-alone increase function.
Correct Answer#: A
Section: (none)
Explanation

QUESTION 77
Which two statements are correct about the usage of parameters in functions? (Choose two.)

A. Functions can have only in mode parameters.
B. Functions called in SQL statements cannot have out or in out mode parameters.
C. Functions having in, out, or in out parameters can be called only in named PL/SQL subprograms
D. Functions having in, out, or in out parameters can be called In PL/SQL procedures and anonymous blocks.

Correct Answer#: BD
Section: (none)
Explanation

In SQL (not PL SQL),
Functions called cannot have out or in out mode parameters.

1) IN type parameter: These types of parameters are used to send values to stored procedures.
2) OUT type parameter: These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
3) IN OUT parameter: These types of parameters are used to send values and get values from stored procedures.

QUESTION 78
View the Exhibit and examine the code and its outcome on execution:

q78

What would be the effect on the two procedures if the value of debug is set to false? (Choose two.)

A. MY_PROC2 is not recompiled.
B. MY_PROC1 is recompiled but remains unchanged.
C. MY_PROC2 is recompiled but remains unchanged.
D. MY_PROC1 is recompiled without the debugging code.
Correct Answer#: CD
Section: (none)
Explanation

Changing the value of one of the constants forces all the dependent units of the package to recompile with the new value. For example, changing the value of debug to FALSE causes my_proc1 to be recompiled without the debugging code. my_proc2 is also recompiled, but my_proc2 is unchanged because the value of trace did not change.

QUESTION 79
Which two statements art true about the instead of triggers’ (choose two.)

A. Delete operations cannot be performed using the instead of triggers.
B. The instead or triggers must be created to add or modify data through any view.
C. The instead of triggers can be written only for views, and the before and after timing options are not valid.
D. The check option for views Is not enforced when Insertions or updates to the view are performed by using the instead of trigger.
Correct Answer#: BD
Section: (none)
Explanation

are Data events and System events. Data events consist of DML statements (INSERT, UPDATE
& DELETE) and DDL statements, such as CREATE or DROP . System Events Comprised of
actions that are performed either at the schema level or database level.
Incorrect Answers:
A: An Application trigger fires when a particular event occurs in the application. Application
triggersare developed using Oracle client-side tools, such as Oracle Forms Developer.C. The
events that cause triggers created on system events to fire are: DDL statement on an object,Users
logging on or off the database, Database shutdown or startup & Server errors.

QUESTION 80
View the Exhibit and examine the structure of the EMP table.

q80

You want to create two procedures using the overloading feature to search for employee details based on either the employee name or employee number. Which two rules should you apply to ensure that the overloading feature is used successfully? (Choose two.)

A. The procedures can be either stand-alone or packaged.
B. The procedures should be created only as packaged subprograms
C. The procedures should be created only as stand-alone subprograms
D. Each subprogram’s formal parameters should differ in both name and data type.
E. The formal parameters of each subprogram should differ in data type but can use the same names.
Correct Answer#: BE
Section: (none)
Explanation

“….that the overloading feature is used successfully?”
You cannot overload procedure in schema scope, therefore A is not correct.

Only local or packaged subprograms, or type methods, can be overloaded. Therefore, you cannot overload standalone subprograms. Also, you cannot overload two subprograms if their formal parameters differ only in name or parameter mode.

QUESTION 81
Which two statements are correct about the usage of parameters in functions? (Choose two.)

A. Functions can have only in mode parameters.
B. Functions called in SQL statements cannot have out or in out mode parameters.
C. Functions having in, out, or in out parameters can be called only in named PL/SQL subprograms
D. Functions having in, out, or in out parameters can be called In PL/SQL procedures and anonymous blocks.
Correct Answer#: BD
Section: (none)
Explanation

In SQL (not PL SQL),
Functions called cannot have out or in out mode parameters.

1) IN type parameter: These types of parameters are used to send values to stored procedures.
2) OUT type parameter: These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
3) IN OUT parameter: These types of parameters are used to send values and get values from stored procedures.

QUESTION 82
View the Exhibit and examine the structure of the employees table.

q82

Examine the following block of code:

q82-2

What is the outcome when the above code is executed?

A. It executes successfully.
B. It gives an error because decode cannot be used in a PL/SQL block.
C. It gives an error because the AVG function cannot be used in a PL/SQL block
D. It gives an error because the MONTHS_BETWEEN function cannot be used in a PL/SQL block.
E. It gives an error because both the AVG and decode functions cannot be used in a PL/SQL block.
Correct Answer#: A
Section: (none)
Explanation

QUESTION 83
Examine the following PL/SQL code:

q83

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

A. The code executes successfully and gives the desired output.
B. The code generates an error because the EMP_RECORD variable is not declared.
C. The code generates an error because the cursor is not opened before the FOR loop.
D. The code generates an error because the loop does not have the exit when clause.
Correct Answer#: A
Section: (none)
Explanation

B,C & D will be OK if change to a different loop, not a for.

QUESTION 84
Examine the following code:

q84

Which two statements are true about the above function? (Choose two.)

A. It can be used only in a parallelized query.
B. It can be used in both a parallelized query and a parallelized DML statement.
C. It can be used only in a parallelized data manipulation language (DML) statement.
D. It can have a separate copy run in each of the multiple processes when called from a SQL statement that is run in parallel.
E. It requires a PRAGMA RESTRICT_REFERENCES declaration with RNDS, WNDS, RNPS, and WNPS specified in order to use parallel optimization.
Correct Answer#: BE
Section: (none)
Explanation
Explanation/Reference:

1_ The PARALLEL_ENABLE keyword can be used in the syntax for declaring a function. It is an optimization hint that indicates that the function can be used in a parallelized query or parallelized DML statement.
2_ For DML statements, before Oracle8i, the parallelization optimization looked to see whether a function was noted as having all four of RNDS, WNDS, RNPS, and WNPS specified in a PRAGMA RESTRICT_REFERENCES declaration;

In a SELECT statement or a subquery in a DML or DDL statement, a user-written function may be executed in parallel in any of the following cases:
* If it has been declared with the PARALLEL_ENABLE keyword
* If it is declared in a package or type and has a PRAGMA RESTRICT_REFERENCES clause that indicates all of WNDS, RNPS, and WNPS
* If it is declared with CREATE FUNCTION and the system can analyze the body of the PL/SQL code and determine that the code neither writes to the database nor reads or modifies package variables

%d bloggers like this: