BELAJAR 1-Z0 144 VERSI 2 PART 1

Q1
Anonymous blocks are used when building scripts to seed data or perform one-time processing activities. In which of the following sections of the anonymous-block of PL/SQL can anonymousblock programs be nested?

A. Exception
B. Declaration
C. Execution
D. Definition
Correct Answer: C
Section: (none)
Explanation/Reference:

Declaration, Exception and definition cant be nested

Q2
You work as an Application Developer for Tech-Mart Inc. The company uses an Oracle database. The database contains a function named Calc_Sal. You want to make some changes in the executable part of the function. What will you do to accomplish this? Each correct answer represents a complete solution. Choose two.

A. Drop the function and create a new one.
B. Use the MODIFY FUNCTION statement.
C. Use the CREATE OR REPLACE FUNCTION statement.
D. Use the ALTER FUNCTION statement.
Correct Answer: AC
Section: (none)
Explanation/Reference:

Theres no function cant be altered. And modify feature doesnt exists.

Q3
Which of the following evaluates to TRUE when an unsuccessful fetch occurs?

A. %ROWCOUNT
B. %ISOPEN
C. %FOUND
D. %NOTFOUND
Correct Answer: D
Section: (none)
Explanation/Reference:

%ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

%ISOPEN returns TRUE if its cursor or cursor variable is open; otherwise, %ISOPEN returns FALSE

%NOTFOUND is the logical opposite of %FOUND. %NOTFOUND yields FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row.

Q4
Which of the following packages will you use when you want an application to be notified whenever the values of interest in the database are changed?

A. DBMS_ HPROF
B. DBMS_ALERT
C. DBMS_LOCK
D. DBMS_ASSERT
Correct Answer: B
Section: (none)
Explanation/Reference:

The DBMS_HPROF package provides an interface for profiling the execution of PL/SQL applications. It provides services for collecting the hierarchical profiler data, analyzing the raw profiler output and profiling information generation.

DBMS_ALERT supports asynchronous notification of database events (alerts). By appropriate use of this package and database triggers, an application can notify itself whenever values of interest in the database are changed.

The DBMS_LOCK package provides an interface to Oracle Lock Management services. You can request a lock of a specific mode, give it a unique name recognizable in another procedure in the same or another instance, change the lock mode, and release it.

The DBMS_ASSERT package provides an interface to validate properties of the input value.

Q5
Which of the following functions is used to update or insert a reference to an externally stored binary large object?

A. BFILENAME
B. DUMP
C. COALESCE
D. DECODE
Correct Answer: A
Section: (none)

Explanation/Reference:

BFILENAME returns a BFILE locator that is associated with a physical LOB binary file on the server file system.

DUMP returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of expr. The returned result is always in the database character set.

COALESCE returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null.

DECODE compares expr to each search value one by one. If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null.

Q6
The following steps are taken to create a PL/SQL anonymous block and display the output:

1. Set the server output on by clicking the Enable DBMS Output icon.
2. Insert the PL/SQL code in the Enter SQL Statement box.
3. Display the output by clicking the DBMS Output tab.
4. Click the Execute Statement icon.
5. Click on the DBMS Output tab.

Which of the following is the correct sequence of these steps?

A. 2,5,1,3,4
B. 2,1,5,3,4
C. 2,5,1,4,3
D. 2,1,5,4,3
Correct Answer: C
Section: (none)

Explanation/Reference:

 

Q7
Which of the following statements is true about the cursor FOR loop?

A. Parameters cannot be passed to a cursor in a cursor FOR loop.
B. A cursor does not need to explicitly use the OPEN, FETCH, and CLOSE statements within a cursor FOR loop.
C. A cursor needs to explicitly use the OPEN, FETCH, and CLOSE statements within a cursor FOR loop.
D. A cursor variable cannot be opened for a type-compatible query in a cursor FOR loop.

Correct Answer: B
Section: (none)
Explanation/Reference:

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.

Q8
Andrew works as an Application Developer for Federal Inc. The company uses an Oracledatabase. The database contains a table named Orders. Andrew is required to create a database trigger on the Orders table. He executes the CREATE TRIGGER statement to accomplish this. However, during the compilation of the trigger, an error occurs. Which of the following statements regarding the creation of the trigger are true? Each correct answer represents a complete solution.
Choose two.

A. The trigger will be created. However, it will need to be recompiled after the compilation error is rectified.
B. The trigger will not be created unless the compilation error is rectified.
C. The trigger will be created, and it will be fired successfully by DML statements.
D. The trigger will be created. However, when a DML statement fires the trigger, the DML statement will fail.
Correct Answer: AD
Section: (none)

Explanation/Reference:

If an error occurs during the compilation of a trigger, the trigger is still created. Therefore, if a DML statement fires the trigger, the DML statement fails (unless the trigger was created in the disabled state). To see trigger compilation errors, either use the SHOW ERRORS statement in SQL*Plus or Enterprise Manager, or SELECT the errors from the USER_ERRORS view.

Q9
Martha works as a Database Administrator for MarcoTech Inc. She writes the following PL/SQL block:

DECLARE
Low number;
High number;
BEGIN
Low := 4;
High := 4;
For i in Low..High LOOP
dbms_output.put_line(i);
END LOOP;
END;
How many times will the loop iterate when this PL/SQL block is executed?

A. 1
B. 8
C. 0
D. 4
Correct Answer: A
Section: (none)
Explanation/Reference:

 

Q10
Which of the following PL/SQL functions is used to find the position where a substring starts in a string?

A. LENGTH
B. INSTR
C. CONCAT
D. INITCAP
Correct Answer: B
Section: (none)
Explanation/Reference:

The LENGTH functions return the length of char. LENGTH calculates length using characters as defined by the input character set. LENGTHB uses bytes instead of characters. LENGTHC uses Unicode complete characters. LENGTH2 uses UCS2 code points. LENGTH4 uses UCS4 code points

The INSTR functions search string for substring. The function returns an integer indicating the position of the character in string that is the first character of this occurrence. INSTR calculates strings using characters as defined by the input character set. INSTRB uses bytes instead of characters.INSTRC uses Unicode complete characters. INSTR2 uses UCS2 code points. INSTR4 uses UCS4 code points.

CONCAT returns char1 concatenated with char2. Both char1 and char2 can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, orNCLOB. The string returned is in the same character set as char1. Its datatype depends on the datatypes of the arguments.

INITCAP returns char, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric.

Q11
You work as an Application Developer for Gentech Inc. The company uses an Oracle database. The database contains a table named Employees. You are required to create a procedure named Add_Emp that will be used to insert new records in the Employees table. What precautions will you take while creating the procedure in order to minimize the dependency failure in case the Employees table undergoes any change? Each correct answer represents a complete solution.
Choose all that apply.

A. Declare variables by using the %TYPE attribute.
B. Include a column list with the INSERT statement.
C. Specify schema names when referencing objects in the procedure.
D. Use the SELECT *notation when performing queries through the procedure.
Correct Answer: ABD
Section: (none)

Explanation/Reference:

Use the SELECT * notation. This will minimize recompilation failures because SELECT *
will select all the columns of the table. If you add or remove any column from the table, the SELECT statement will not be impacted by the change in the table and will require no
changes to the program unit.

Use the %ROWTYPE to declare records and %TYPE to declare variables. This allows you to
declare records and variables that inherit the data types of the underlying columns of the base
tables, rather than defining the data type of each variable in the program unit. The %ROWTYPE
and %TYPE attributes provide you the flexibility to change the data types of the columns of the
table without having to modify the data types specified in the program unit.

Q12
Which of the following statements can be used to create a package? Each correct answer represents a part of the solution. Choose two.

A. CREATE PACKAGE
B. CREATE BODY/SPECIFICATION
C. CREATE PACKAGE SPECIFICATION
D. CREATE PACKAGE BODY
Correct Answer: AD
Section: (none)

Explanation/Reference:

The CREATE PACKAGE statement creates or replaces the specification for a stored package, which is an encapsulated collection of related procedures, functions, and other program objects stored together in the database. The package specification declares these objects. The package body, specified subsequently, defines these objects.
The CREATE PACKAGE BODY statement creates or replaces the body of a stored package, which is an encapsulated collection of related procedures, stored functions, and other program objects stored together in the database. The package body defines these objects. The package specification, defined in an earlier CREATE PACKAGE statement, declares these objects.

Q13
Which of the following procedures of the DBMS_OUTPUT package has two overloaded forms?

A. DBMS_OUTPUT.PUT_LINE
B. DBMS_OUTPUT.PUT
C. DBMS_OUTPUT.GET_LINES
D. DBMS_OUTPUT.GET_LINE
Correct Answer: C
Section: (none)

Explanation/Reference:

 

Q14
Which of the following statements are true about the DBMSOUTPUT.ENABLE procedure? Each correct answer represents a complete solution. Choose all that apply.

A. The minimum and maximum values of the buffer_size that the user can specify are 20,000 and 1,000,000 respectively.
B. The calls to the DBMS_OUTPUT.PUT, DBMS_OUTPUT.PUT_LINE, DBMS_OUTPUT.NEWLINE, DBMS_OUTPUT.GET_LINE and DBMS_OUTPUT.GET_LINES procedures are acknowledged only when the DBMS_OUTPUT is activated.
C. It is not necessary to call DBMS_OUTPUT.ENABLE procedure when a user uses the SET SERVEROUTPUT option of SQL*Plus.
D. When the DBMS_OUTPUT. ENABLE procedure is called more than once, the buffer_size is set to the value passed in the first call.
E. When the DBMSOUTPUT. ENABLE procedure is called and the buffer_size is set to NULL, the buffer_size takes 20,000 as its default value.
F. The minimum and maximum values of the buffer_size that the user can specify are 2,000 and1,000,000 respectively.
Correct Answer: BCEF
Section: (none)

Explanation/Reference:

The default buffer size is 20000 bytes. The minimum size is 2000 bytes and the maximum is unlimited.

This procedure enables calls to PUT, PUT_LINE, NEW_LINE, GET_LINE, and GET_LINES. Calls to these procedures are ignored if the DBMS_OUTPUTpackage is not activated.

It is not necessary to call DBMS_OUTPUT.ENABLE procedure when you use the SET SERVEROUTPUT option of SQL*Plus.

Q15
Martha designs a database for Oliver Inc. She wants to retrieve the error number for the current exception. Which of the following functions will she use?

A. SQLNUM
B. SQLCODE
C. ERRORNUM
D. SQLERRM
Correct Answer: B
Section: (none)

Explanation/Reference:

SQLNUM, ERRORNUM  not exist

The function SQLCODE returns the number code of the most recent exception.
For internal exceptions, SQLCODE returns the number of the associated Oracle error. The number that SQLCODE returns is negative unless the Oracle error is no data found, in which case SQLCODE returns +100. For user-defined exceptions, SQLCODE returns +1, or a value you assign if the exception is associated with an Oracle error number through pragma EXCEPTION_INIT.

The function SQLERRM returns the error message associated with its error-number argument. If the argument is omitted, it returns the error message associated with the current value of SQLCODE. SQLERRM with no argument is useful only in an exception handler. Outside a handler, SQLERRM with no argument always returns the normal, successful completion message. For internal exceptions, SQLERRM returns the message associated with the Oracle error that occurred. The message begins with the Oracle error code.

Q16
Which of the following packages will you use to obtain information about the PL/SQL objects?

A. DBMS_STATS
B. DBMS_UTILITY
C. DBMS_DESCRIBE
D. DBMS_METADATA
Correct Answer: C
Section: (none)

Explanation/Reference:

With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.

The DBMS_UTILITY package provides various utility subprograms.

You can use the DBMS_DESCRIBE package to get information about a PL/SQL object. When you specify an object name, DBMS_DESCRIBE returns a set of indexed tables with the results. Full name translation is performed and security checking is also checked on the final object.

The DBMS_METADATA package provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object.

Q17
You work as an Application Developer for Blue-Well Inc. The company uses an Oracle database. The database contains a table named Employees that contains employee records. You are required to create subprograms (procedures and functions) that perform different operations on the Employees table. You want to group the subprograms in a package named Manage_Employees. In order to accomplish the task, you first create the package specification by executing the following statement:

CREATE OR REPLACE PACKAGE Manage_Employees IS
tax_rate CONSTANT NUMBER(5, 2) := .28;
v_id NUMBER;
PROCEDURE Insert_Emp (p_deptno NUMBER, p_sal NUMBER);
PROCEDURE Delete_Emp;
PROCEDURE Update_Emp;
FUNCTION Calc_Tax (p_sal NUMBER) RETURN NUMBER;
END Manage_Employees;

After creating the package specification, you execute the following statement in order to create the package body: Which of the following subprograms in the Manage_Employees package is a private subprogram?

A. Update_Sal
B. Update_Emp
C. Calc_Tax
D. lnsert_Emp
E. Delete_Emp
Correct Answer: A
Section: (none)

Explanation/Reference:

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.

Q18
Which of the following PL/SQL packages is used to support the SQL plan management feature?

A. UTL_MAIL
B. Utl_Compress
C. DBMS_Warning
D. DBMS_SPM
Correct Answer: D
Section: (none)

Explanation/Reference:

The UTL_MAIL package is a utility for managing email which includes commonly used email features, such as attachments, CC, and BCC.

The UTL_COMPRESS package provides a set of data compression utilities.

The DBMS_WARNING package provides a way to manipulate the behavior of PL/SQL warning messages, in particular by reading and changing the setting of the PLSQL_WARNINGS initialization parameter to control what kinds of warnings are suppressed, displayed, or treated as errors. This package provides the interface to query, modify and delete current system or session settings.
The DBMS_SPM package supports the SQL plan management feature by providing an interface for the DBA or other user to perform controlled manipulation of plan history and SQL plan baselines maintained for various SQL statements.

Q19
You work as an Application Developer for Federal Inc. The company uses an Oracle database. The database contains a table named Employees. You are required to create a procedure named Sal_Raise that increases the salary of the specified employee. In order to accomplish this, you execute the following statement:

CREATE OR REPLACE PROCEDURE Sal_Raise (Id NUMBER, Sal NUMBER) IS BEGIN
UPDATE Employees
SET Salary = Salary Sal
WHERE EmpID = Id;
COMMIT;
END;

You want to invoke the procedure from SQL*Plus. What will you do to accomplish the task? Each correct answer represents a complete solution. Choose two.

A. You cannot invoke the procedure from SQL*Plus.
B. Use the EXECUTE statement.
C. Use the RUN statement.
D. Use an anonymous PL/SQL block.
Correct Answer: BD
Section: (none)

Explanation/Reference:

To execute a PL/SQL statement that references a stored procedure, you can use the SQL*Plus EXECUTE command. EXECUTE runs the PL/SQL statement that you enter immediately after the command.

Q20
Which of the following is NOT an advantage of a package?

A. Stored packages allow users to encapsulate or group stored procedures, datatypes, variables, etc. in a single named, stored unit in the database.
B. The methods of package definition allow users to specify which variables, cursors, and procedures are private and public.
C. Packages implement data consistency.
D. Packages provide better performance.
Correct Answer: C
Section: (none)

Explanation/Reference:

Packages offer several advantages: modularity, easier application design, information hiding, added functionality, and better performance.
Modularity
Packages let you encapsulate logically related types, items, and subprograms in a named PL/SQL module. Each package is easy to understand, and the interfaces between packages are simple, clear, and well defined. This aids application development.
Easier Application Design
When designing an application, all you need initially is the interface information in the package specs. You can code and compile a spec without its body. Then, stored subprograms that reference the package can be compiled as well. You need not define the package bodies fully until you are ready to complete the application.
Information Hiding
With packages, you can specify which types, items, and subprograms are public (visible and accessible) or private (hidden and inaccessible). For example, if a package contains four subprograms, three might be public and one private. The package hides the implementation of the private subprogram so that only the package (not your application) is affected if the implementation changes. This simplifies maintenance and enhancement. Also, by hiding implementation details from users, you protect the integrity of the package.
Added Functionality
Packaged public variables and cursors persist for the duration of a session. So, they can be shared by all subprograms that execute in the environment. Also, they allow you to maintain data across transactions without having to store it in the database.
Better Performance
When you call a packaged subprogram for the first time, the whole package is loaded into memory. So, later calls to related subprograms in the package require no disk I/O. Also, packages stop cascading dependencies and thereby avoid unnecessary recompiling. For example, if you change the implementation of a packaged function, Oracle need not recompile the calling subprograms because they do not depend on the package body.

%d bloggers like this: