BELAJAR 1-Z0 144 PART 1

QUESTION 1
View the Exhibit and examine the structure of the EMP table. Which stages are performed when the above block is executed? (Choose all that apply)

declare
v_sal number;
begin
select sal into v_sal from emp where empno = 130;
insert into emp(empno,ename,sal) values (185,’Jones’,v_sal+1000);
end;
/

A. Bind
B. Parse
C. Fetch
D. Execute
Correct Answer#: BCD
Section: (none)
Explanation

parse ==> merubah native sintax maupun string dalam variabel menjadi bahasa yang dapat dieksekusi.
Fetch ==> mengambil nilai dari suatu souce dan memasukan nya kedalama variabel
execute ==> mengeksekusi statement yang telah diparsing.

QUESTION 2
Which system events can be used to create triggers that fire both at database and schema levels? (Choose all that apply)

A. AFTER LOGON
B. AFTER STARTUP
C. BEFORE SHUTDOWN
D. AFTER SERVERERROR
Correct Answer#: D
Section: (none)
Explanation

After logon ==> user event
AFTER STARTUP ==> Causes the database to fire the trigger whenever the database is opened. This event is valid only with DATABASE, not with SCHEMA.
BEFORE SHUTDOWN ==> Causes the database to fire the trigger whenever an instance of the database is shut down. This event is valid only with DATABASE, not with SCHEMA.

QUESTION 3
In which of the following scenarios would you recommend using PL/SQL records?

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
D. when you want to create a relatively small lookup table, where the collection can be constructed in memory each time a subprogram is invoked
Correct Answer#: CD
Section: (none)
Explanation

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

q4-1

Execute the following block of code:

 

q4-2

What is the outcome?

A. It gives an error because group functions cannot be used in anonymous blocks
B. It executes successfully and correctly gives the result of the sum of salaries in department 60.
C. It executes successfully and incorrectly gives the result of the sum of salaries in department 60.
D. It gives an error because the variable name and column name are the same in the where clause of the select statement.
Correct Answer#: C
Section: (none)
Explanation

ini terjadi karena variabel pada baris 3, yang digunakan menjadi variabel sebagai kondisi pada baris 7,bernama sama. Sehingga kondisi tidak valid,walaupun tidak menyebabkan eror.

QUESTION 5
Examine the following snippet of PL/SQL code:

q5-2

View the exhibit for table description of EMPLOYEES table. The EMPLOYEES table has 200 rows.

q5-1

Identify open statement for opening the cursor that fetches the result as consisting of employees with JOB_ID as `ST_CLERK’ and salary greater than 3000.

A. OPEN c1 (NULL, 3000);
B. OPEN c1 (emp_job, 3000);
C. OPEN c1 (3000, emp_salary);
D. OPEN c1 (`ST_CLERK’, 3000)
E. OPEN c1 (EMP_job, emp_salary);
Correct Answer#: D
Section: (none)
Explanation

sebaiknya untuk pengiriman parameter dalam bentuk literal, jangan dalam bentuk variabel.

QUESTION 6
View the exhibit and examine the structure of the EMPLOYEES table

q6-1

The salary of EMPLOYEE_ID 195 is 2800.
You execute the following code

q6-2

What is the outcome?

A. It gives an error because only the innermost block is labeled
B. It gives an error because the same variable name cannot be used across all the nested blocks.
C. It executes successfully and displays the resultant values in the following sequence- 1000, 2800 50000, 2800.
D. It executes successfully and displays the resultant values in the following sequence: 1000, 2800, 50000, 1000.
Correct Answer#: C
Section: (none)
Explanation

QUESTION 7
Which two statements are true about the usage of the cursor for loops? (Choose two.)

A. The cursor needs to be closed after the iteration is complete.
B. The implicit open, fetch, exit, and close of the cursor happen.
C. The record type must be explicitly declared to control the loop.
D. The PL/SQL creates a record variable with the fields corresponding to the columns of the cursor result set.
Correct Answer#: BD
Section: (none)
Explanation
Explanation/Reference:

record
Name for the loop index that the cursor FOR LOOP statement implicitly declares as a %ROWTYPE record variable of the type that cursor orselect_statement returns.
record is local to the cursor FOR LOOP statement. Statements inside the loop can reference record and its fields. They can reference virtual columns only by aliases. Statements outside the loop cannot reference record. After the cursor FOR LOOP statement runs, record is undefined.
cursor
Name of an explicit cursor that is not open when the cursor FOR LOOP is entered.
actual_cursor_parameter
Actual parameter that corresponds to a formal parameter of cursor.
select_statement
SQL SELECT statement (not PL/SQL SELECT INTO statement). For select_statement, PL/SQL declares, opens, fetches from, and closes an implicit cursor. However, because select_statement is not an independent statement, the implicit cursor is internal—you cannot reference it with the name SQL.

QUESTION 8
Examine the following PL/SQL code:

q8-1

Which statement is true about the execution of the code if the query in the PL/SQL block returns no rows?

A. The program abruptly terminates and an exception is raised.
B. The program executes successfully and the output is No ROWS_FOUND.
C. The program executes successfully and the query fetches a null value in the V_LNAME variable.
D. Program executes successfully, fetches a NULL value in the V_LNAME variable and an exception is raised.
Correct Answer#: A
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 9
Consider the following scenario:

Local procedure a calls remote procedure B
Procedure A was compiled at 8 AM.
Procedure A was modified and recompiled at 9 AM.

Remote procedure B was later modified and recompiled at 11 AM.
The dependency mode is set to timestamp.

Which statement correctly describes what happens when procedure A is invoked at 1 PM?

A. Procedure A is invalidated and recompiled immediately.
B. There is no effect on procedure A and it runs successfully.
C. Procedure B is invalidated and recompiled again when invoked.
D. Procedure A is invalidated and recompiles when invoked the next time.
Correct Answer#: D
Section: (none)
Explanation

karena procedure B yang terakhir di modifikasi dan direkompile, makan procedure a lah yang akan invalid dan akan dicompile ulang saat dipanggil.

QUESTION 10
View the Exhibit to examine the PIVSQL block.

q10

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

A. It executes and the Output is emprec.deptname:.
B. It executes and the Output is emprec.deptname:Sales.
C. It produces an error because NULL is assigned to the emprec.empid field in the record.
D. It produces an error because the CHECK constraint is violated while assigning a value to the emprec.
deptid field in the record.
Correct Answer#: A
Section: (none)
Explanation

variabel turunan hanya akan membawa tipe data dan nilai yang diassign pada variabel tersebut.

QUESTION 11
Examine the following snippet of code from the DECLARE section of PL/SQL

DECLARE

Cut_name VERCHAR2 (20) NOT NULL := `tom jones’:
Same_name cut_name%TYPE:

Which statement is correct about the above snippets of code?

A. The variable inherits only the data type from the CUT_NAME variable.
B. The same_name variable inherits only the data type and default value from the CUT_NAME variable.
C. The same_name variable inherits the data type, constraint, and default value from the CUT_NAME variable.
D. The same_name variable inherits only the data type and constraint from the CUT_NAME variable resulting in an error
Correct Answer#: AB
Section: (none)
Explanation

variabel turunan hanya akan membawa tipe data dan nilai yang diassign pada variabel tersebut.

QUESTION 12
Examine the following package specification.

SQL>CREATE OR REPLACE PACKAGE emp_pkf IS
PROCEDURE search_emp (empdet NUMBER);
PROCEDURE search_emp (empdet DATE);
PROCEDURE search_emp (empdet NUMBER); RETURN VERCHAR2
PROCEDURE search_emp (empdet NUMBER); RETURN DATE
END emp_pkg
The package is compiled successfully

Why would it generate an error at run tune?

A. Because function can not be overload
B. Because function can not differ only in return type.
C. Because all the functions and procedures In the package cannot have the same number of parameters with the same parameter name
D. Because the search EMP (EMPDET NUMBER) procedure and the SEARCH_DEPT (EMPDET NUMBER) can not have identical parameter names and data types
Correct Answer#: B
Section: (none)
Explanation

syarat overloading :
1. harus didalam package
2. nama stored procedure/function boleh sama, nama variabel parameter boleh sama, tapi tidak boleh tipe data sama.
3. jika ingin tipe data sama, bedakan jumlah variabel parameter.

QUESTION 13
Which two statements are true about PL/SQL exception propagation? (Choose two.)

A. The exception reproduces Itself In successive enclosing blocks until a handler is found.
B. Exception- can propagate across the remote subprograms that are called through database links.
C. If you declare a local exception in a subblock and a global exception in the outer block, the local declaration overrides the global exception.
D. If you declare a local exception in a subblock and a global exception in the outer block, the global declaration overrides the local exception.
Correct Answer#: AC
Section: (none)
Explanation

QUESTION 14
Which tasks must be performed during the installation of the UTL_MAIL package?

(Choose all that apply.)

A. setting the UTL_FILE_DIR initialization parameter
B. running the UTLMAIL.SQL and prvtmail.plb scripts
C. setting the SMTP_OUT_SERVER initialization parameter
D. using the CREATE DIRECTORY statement to associate an alias with an operating system directory
E. granting read and WRITE privileges to control the type of access to files in the operating system
Correct Answer#: BC
Section: (none)
Explanation

You must both install UTL_MAIL and define the SMTP_OUT_SERVER.
* To install UTL_MAIL:
sqlplus sys/
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

You define the SMTP_OUT_SERVER parameter in the init.ora rdbms initialization file. However, if SMTP_OUT_SERVER is not defined, this invokes a default of DB_DOMAIN which is guaranteed to be defined to perform appropriately.

QUESTION 15
You want to maintain an audit of the date and time when each user of the database logs off.
Examine the following code:

q15

Which two clauses should be used to fill in the blanks and complete the above code? (Choose two.)

A. ON SCHEMA
B. ON DATABASE
C. AFTER LOGOFF
D. BEFORE LOGOFF
Correct Answer#: AD
Section: (none)
Explanation

A system trigger fires at exactly one of these timing points:
Before the triggering statement runs
(The trigger is called a BEFORE statement trigger or statement-level BEFORE trigger.)
After the triggering statement runs
(The trigger is called a AFTER statement trigger or statement-level AFTER trigger.)
Instead of the triggering CREATE statement
(The trigger is called an INSTEAD OF CREATE trigger.)

A SCHEMA trigger is created on a schema and fires whenever the user who owns it is the current user and initiates the triggering event.
Suppose that both user1 and user2 own schema triggers, and user1 invokes a DR unit owned by user2. Inside the DR unit, user2 is the current user. Therefore, if the DR unit initiates the triggering event of a schema trigger that user2 owns, then that trigger fires. However, if the DR unit initiates the triggering event of a schema trigger that user1 owns, then that trigger does not fire.

A DATABASE trigger is created on the database and fires whenever any database user initiates the triggering event.

QUESTION 16
Which two statements are true about the PL/SQL initialization parameters? (Choose two.)

A. To use native code compilation, PLSQL_OPTIMIZE_I.EVEL should be set to a value less than or equal to l.
B. The default value of 2 for PLSQL_OPTIMI2E_LEVEL allows the compiler to rearrange code for better performance.
C. Setting PLSQL_CODE_TYPE to native provides the greatest performance gains only for computation- intensive procedural operations.
D. Changing the value of the PLSQL_CODE_TYPE parameter affects all the PL/SQL library units that have already been compiled
Correct Answer#: BC
Section: (none)
Explanation :

PLSQL_CODE_TYPE = { INTERPRETED | NATIVE } used to native compilation
A) wrong
The optimizer is enabled by default. In rare cases, if the overhead of the optimizer makes compilation of very large applications too slow, you can lower the optimization by setting the compilation parameter PLSQL_OPTIMIZE_LEVEL=1 instead of its default value 2. In even rarer cases, PL/SQL might raise an exception earlier than expected or not at all. Setting PLSQL_OPTIMIZE_LEVEL=1 prevents the code from being rearranged.
B) true
C) true
Dependencies, Invalidation, and Revalidation
Recompilation is automatic with invalidated PL/SQL modules. For example, if an object on which a natively compiled PL/SQL subprogram depends changes, the subprogram is invalidated. The next time the same subprogram is called, the database recompiles the subprogram automatically. Because the PLSQL_CODE_TYPE setting is stored inside the library unit for each subprogram, the automatic recompilation uses this stored setting for code type.
Explicit recompilation does not necessarily use the stored PLSQL_CODE_TYPE setting. For the conditions under which explicit recompilation uses stored settings, see “PL/SQL Units and Compilation Parameters”.
D) false

QUESTION 17
View Exhibit1 and examine the structure of the product table.

q17

View Exhiblt2 and examine the procedure you created. The procedure uses the prod id to determine whether the list price is within a given range.

q17-2

You then create the following trigger on the product table.

CREATE OR REPLACE TRIGGER check_price__trg
BEF0RE INSERT OR UPDATE OF prod_id, prod_list_price
ON product FOR EACH ROW
WHEN (nev.prod_id NVX(old.prod_id,0) OR
New.prod__list_price NVL(old.prod_list_price, 0) )
BEGIN
check_price (:new.prod_id) ;
END
/

Examine the following update command for an existing row in the product table.

SQL> UPDATE produce SET prod_list_price = 10 WHERE prod_id=115;

Why does it generate an error?

A. because the procedure call in the trigger is not valid ”
B. because the condition specified in the when clause is not valid
C. because both the procedure and trigger access the same table
D. because the WHEN clause cannot be used with a row-level trigger
E. because the column list specified with UPDATE in the trigger is not valid
Correct Answer#: C
Section: (none)
Explanation

Akan muncul error tabel produce is mutating saat trigger aktif.

QUESTION 18
Which two statements are true about anonymous blocks and named subprograms? (Choose two)

A. Subprograms are by default executed with definer’s rights.
B. The declare section is optional for both anonymous blocks and subprograms.
C. Both anonymous blocks and subprograms execute by default with invoker’s rights.
D. The declare section is mandatory for anonymous blocks and optional for subprograms.
Correct Answer#: AB
Section: (none)
Explanation

By default, stored procedures and SQL methods execute with the privileges of their owner, not their current user. Such definer’s rights subprograms are bound to the schema in which they reside, allowing you to refer to objects in the same schema without qualifying their names.

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

q19

User SCOTT needs to generate a text report that contains the names of all employees and their salaries.
Examine the following commands issued by the DBA:

SQL_CREATE DICTORY my_dir AS ‘/temp/my_files* ;
SQL_GRANT WRITE ON DIRECTORY my_dir TO SCOTT;
View Exhibit2 and examine the procedure code. You issue the following command:

You issue the following command:

SQL_EXEC sal_status (‘MY_DIR’, `EMPREPORT.TXT’)

What is the outcome?

A. It executes successfully and creates the report.
B. It gives an error because the text file should be opened in append mode.
C. It gives an error because the “no data found” condition is not handled to come out of the loop.
D. It gives an error because user SCOTT should be granted both read and write privileges to the directory alias.
E. It executes but no data is written to the text file because the FFLUSH subprogram is not used to write all the data buffered in memory to a file.
Correct Answer#: A
Section: (none)
Explanation

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

Soal selanjutnya..

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

%d bloggers like this: