Oracle Database 11g: Program with PL/SQL

QUESTION NO: 41

Examine the following snippet of PL/SQL code:

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

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);

Answer: B,D,E

Explanation:

QUESTION NO: 42

View the exhibit and examine the structure of the EMPLOYEES table.

The salary of EMPLOYEE_ID 195 is 2800.

You execute the following code

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.

Answer: C

Explanation:

QUESTION NO: 43

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.

Answer: B,D

Explanation:

QUESTION NO: 44

Examine the following PL/SQL code:

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.

Answer: A

Explanation:

QUESTION NO: 45

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.

Answer: D

Explanation:

QUESTION NO: 46

View the Exhibit to examine the PIVSQL block.

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.

Answer: A

Explanation:

QUESTION NO: 47

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

DECLARE

Cust_name VERCHAR2 (20) NOT NULL : = ‘Tom Jones’:

Same_name cust_name%TYPE:

Which statement is correct about the above snippets of code?

A. The SAME_NAME variable inherits only the data type from the CUST_NAME variable.

B. The SAME_NAME variable inherits only the data type and default value from the CUST_NAME variable.

C. The SAME_NAME variable inherits the data type, constraint, and default value from the CUST_NAME variable.

D. The SAME_NAME variable inherits only the data type and constraint from the CUST_NAME variable resulting in an error

Answer: D

Explanation:

QUESTION NO: 48

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 cannot be overload

B. Because function cannot 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) cannot have identical parameter names and data types

Answer: B

Explanation:

QUESTION NO: 49

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.

Answer: A,C

Explanation:

QUESTION NO: 50

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

Answer: B,C

Explanation:

QUESTION NO: 51

You want to maintain an audit of the date and time when each user of the database logs off.

Examine the following code:

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

(Choose two.)

A. ON SCHEMA

B. ON QRXABASE

C. AFTER LOGOFF

D. BEFORE LOGOFF

Answer: B,D

Explanation:

QUESTION NO: 52

View Exhibit1 and examine the structure of the product table.

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.

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

Answer: C

Explanation:

QUESTION NO: 53

View Exhibit1 and examine the structure of the employees table.

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_5tatus ('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.

Answer: A

Explanation:

QUESTION NO: 54

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.

Answer: A,C

Explanation:

References:

QUESTION NO: 55

View Exhibit1 and examine the structure of the EMP table.

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.

Answer: C

Explanation:

QUESTION NO: 56

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.

Answer: B,C

Explanation:

QUESTION NO: 57

View the Exhibits and examine the structure of the EMPLOYEES, DEPARTMENTS AND EMP_BY_DEPT tables.

EMPLOYEES

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

Answer: A

Explanation:

QUESTION NO: 58

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.

Answer: C,D

Explanation:

QUESTION NO: 59

Examine the following partial code:

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.

Answer: C

Explanation:

QUESTION NO: 60

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.

Answer: C,D

Explanation:

QUESTION NO: 61

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

Answer: C

Explanation:

QUESTION NO: 62

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

Answer: B

Explanation:

QUESTION NO: 63

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

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

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.

Answer: B

Explanation:

QUESTION NO: 64

Examine the following PL/SQL code:

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.

Answer: B

Explanation:

QUESTION NO: 65

View Exhibit1 and examine the structure of the employees table.

View Exhibit2 and examine the code.

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.

Answer: A

Explanation:

QUESTION NO: 66

Examine the following PL/SQL code:

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.

Answer: A

Explanation:

QUESTION NO: 67

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

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.

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.

Answer: B

Explanation:

QUESTION NO: 68

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

Answer: B,C

Explanation:

QUESTION NO: 69

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.

Answer: A,B

Explanation:

QUESTION NO: 70

View the Exhibit to examine the PL/SQL code.

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

Answer: D

Explanation:

QUESTION NO: 71

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

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

Answer: B,D

Explanation:

QUESTION NO: 72

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.

Answer: A,C

Explanation:

References:

QUESTION NO: 73

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

Examine the following code:

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.

Answer: C

Explanation:

QUESTION NO: 74

Examine the following code:

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.

Answer: A

Explanation:

QUESTION NO: 75

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

E. When privileges need to be granted to a new user to access an existing schema at run time

Answer: B,C

Explanation:

QUESTION NO: 76

Examine the following block of code:

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.

Answer: A,E

Explanation:

References:

QUESTION NO: 77

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

Answer: D

Explanation:

References: http://oraclexpert.com/basic-oracle-dba/sql/for-update-clause-in-a-select-statement/http://sql-plsql.blogspot.com/2007/05/oracle-plsql-where-current-of-for.html

QUESTION NO: 78

Examine the following code:

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.

Answer: C

Explanation:

QUESTION NO: 79

Examine the following PL/SQL code:

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 employee_id 123 exists.

B. It produces the ora-01403: no data found error if no employee with employee_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 employee_id 123 exists and displays Record Not Found.

Answer: B

Explanation:

QUESTION NO: 80

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.

Answer: A

Explanation:

QUESTION NO: 81

Which statements are true about the WHEN OTHERS exception handler? (Choose all that apply)

A. It can be the first exception handler.

B. It can be the only exception handler for the code.

C. It traps all the exceptions that are not already trapped.

D. You can have multiple OTHERS clauses to trap all the multiple unhandled exceptions.

Answer: B,C

Explanation:

References:

Site Search:

Close

Close
Download Free Demo of VCE
Exam Simulator

Experience Avanset VCE Exam Simulator for yourself.


Simply submit your e-mail address below to get started with our interactive software demo of your free trial.


Enter Your Email Address

Free Demo Limits: In the demo version you will be able to access only first 5 questions from exam.