Online Access Free 1Z0-146 Exam Questions

Which three statements are true about hierarchical profiling? (Choose three.)

• A.It provides function-level summaries.
• B.It produces an aggregated report of the SQL and PL/SQL execution times.
• C.It generates trace information for PL/SQL programs in the PLSQL_TRACE_EVENTS table.
• D.It is useful for understanding the structure and control flow of complex programs.
• E.It can be used to identify hotspots and tuning opportunities in PL/SQL applications.

Examine the structure of the DEPARTMENTS table.

Name Null? Type
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
LOCATION_ID NUMBER(4)
View the Exhibit and examine the code that you plan to use for creating a package to obtain the
details of an employee using a host variable on the client side.
In SQL*Plus, you plan to use the following commands:
SQL> VARIABLE x REFCURSOR
SQL> EXECUTE emp_data.get_emp(195,:x)
SQL> PRINT x
Which statement is true about the above scenario?

• A.The package executes successfully, but does not pass the required data to the host variable because the cursor is closed before the PRINT statement runs.
• B.The package specification gives an error on compilation because cursor variable types cannot be defined in the specification.
• C.The package specification gives an error on compilation because the cursor variable parameter was specified before you defined it.
• D.The package executes successfully and passes the required data to the host variable.

You created a PL/SQL subprogram that successfully invokes an external C procedure. After a while, the database administrator (DBA) drops the alias library schema object. The shared library exists in the system. Which statement is true in this scenario?

• A.PL/SQL subprograms can be used to invoke the external C procedure.
• B.The existing extproc process is terminated and a new extproc is started.
• C.The corresponding shared library is also removed from the system.
• D.The PL/SQL subprogram that depends on the external C program becomes invalid.

You executed the following command:
SQL> ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';
You create a new package called PACK1. View Exhibit1 to examine the PL/SQL code for the
PACK1 package specification and body.
You issue the following query to see all unique identifiers with a name, such as %1:
SQL> SELECT NAME, SIGNATURE, TYPE
FROM USER_IDENTIFIERS
WHERE NAME LIKE '%1' AND USAGE='DECLARATION'
ORDER BY OBJECT_TYPE, USAGE_ID;
View Exhibit2 to examine the output of the query. Which two statements are true about the output
of the query? (Choose two.)

• A.The TYPE column has the value of packages, function or procedures, object types, PL/SQL types, triggers, or exceptions.
• B.The SIGNATURE column has a unique value for an identifier except for identifiers with the same name.
• C.The query shows the output for only those identifiers for PL/SQL objects, which are created by the user and are compiled after the ALTER SESSION command.
• D.The ALTER SESSION command automatically collects identifier data and the query shows the output for all the identifiers for PL/SQL objects, which are created by the user.

Examine the structure of the EMPLOYEES table in the SCOTT schema. Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
SALARY NOT NULL NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
DEPARTMENT_ID NUMBER(4)
View the Exhibit and examine the code for the EMP_TOTSAL procedure created by user SCOTT.
Which statement is true regarding the EMP_TOTSAL procedure?

• A.It generates an error because the %NOTFOUND attribute cannot be used in combination with a SELECT INTO statement.
• B.It is created successfully, but displays the correct output message only for existent employee IDs.
• C.It generates an error because a user-defined exception has to be included whenever the %NOTFOUND attribute is used in combination with a SELECT INTO statement.
• D.It is created successfully and displays the correct output message for both existent and nonexistent employee IDs.