Actual of 1Z0-146 download materials and answers for Oracle certification for candidates, Real Success Guaranteed with Updated 1Z0-146 pdf dumps vce Materials. 100% PASS Oracle 11g: Advanced PL/SQL exam Today!
2021 Jun 1Z0-146 practice question
Q41. View Exhibit1 and examine the structure of the EMPLOYEES and DEPARTMENTS tables existing in your schema.
View Exhibit2 and examine the PL/SQL block that you execute to display the department-wise incremented salary for all the departments in your company.
The code generates an error on execution.
What correction should be done to ensure the code executes successfully?
A. The cursor variable parameter should be passed in IN OUT mode.
B. The cursor variable should be defined as a strong REF CURSOR type.
C. The cursor variable name passed as actual and formal parameters should be identical.
D. The %NOTFOUND cursor attribute cannot be used with the cursor variables and should be replaced with a user defined exception.
Q42. View the Exhibit and examine the PL/SQL code.
The code takes a long time to execute. What would you recommend to improve performance?
A. using NOT NULL constraint when declaring the variables
B. using the BULK COLLECT option for query instead of cursor
C. using WHILE.. END LOOP instead of FOR .. END LOOP
D. using the SIMPLE_INTEGER data type instead of the NUMBER data type
Q43. Which two conditions must be true for a PL/SQL function to be result cached? (Choose two.)
A. It must be part of a package.
B. It must be a pipelined table function.
C. It must not be defined in an anonymous block.
D. It must have at least one OUT or IN OUT parameter.
Q44. View the Exhibit to examine the PL/SQL code for the GET_METADATA function. Which statement is true about the metadata gathered by the function?
A. The end result is the creation of DDL for the TIMECARDS table with all instances of the HR schema changed to SCOTT.
B. The end result is the creation of an XML document for all tables with all physical, storage, logging, and other segment attributes.
C. The end result is the creation of DDL for all tables with all instances of the HR schema changed to SCOTT along with all physical, storage, logging, and other segment attributes.
D. The end result is the creation of DDL for all tables and associated indexes with all instances of the HR schema changed to SCOTT along with all physical, storage, logging, and other segment attributes.
Q45. To examine the dependencies between all PL/SQL objects and the tables and views they reference, you executed the following query as the user OE:
SQL> SELECT owner || '.' || NAME refs_table
, referenced_owner || '.' || referenced_name AS table_referenced
WHERE owner = USER
AND TYPE IN ('PACKAGE', 'PACKAGE BODY','PROCEDURE', 'FUNCTION')
AND referenced_type IN ('TABLE', 'VIEW')
AND referenced_owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, NAME, referenced_owner, referenced_name;
Which statement is true about the output of the query?
A. It displays all PL/SQL code objects created by user OE that reference any table or view owned by other users except SYS and SYSTEM..
B. It displays no rows because this query needs to be executed as the user SYS for required results.
C. It displays all PL/SQL code objects that reference a table or view directly for all the users in the database.
D. It displays only those PL/SQL code objects created by the user OE that reference a table or view created by the user SYS.
Refresh 1Z0-146 exam fees:
Q46. 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 is useful for understanding the structure and control flow of complex programs.
D. It can be used to identify hotspots and tuning opportunities in PL/SQL applications.
E. It generates trace information for PL/SQL programs in the PLSQL_TRACE_EVENTS table.
Q47. Which two statements are true about BFILES? (Choose two.)
A. BFILES support only sequential reads.
B. BFILES can be used as attributes in an object type.
C. When LOB is deleted, the corresponding physical file is automatically deleted.
D. The RMAN backup automatically backs up the BFILE locators and the corresponding physical files.
E. The physical file corresponding to a BFILE locator must reside on the file system that is accessible from the server where the database exists.
Q48. Which statements are true about temporary LOBs? (Choose all that apply.)
A. They can be created only for CLOB and NCLOB data.
B. They can be accessed only by the user who creates them.
C. They generate more redo information than persistent LOBs.
D. They exist for the duration of the session in which they are created.
E. They are stored temporarily in the default tablespace of the user who creates them.
Q49. The database instance was started up with the following initialization parameter values:
MEMORY_TARGET = 500M
RESULT_CACHE_MODE = FORCE
RESULT_CACHE_MAX_SIZE = 0
After the database startup, to enable the result cache, you issued the following command:
SQL> ALTER SYSTEM SET result_cache_max_size = 2M SCOPE = MEMORY;
Which is the effect of this command?
A. The query result cache is enabled and 2 MB of the memory target is allocated to the result cache.
B. The query result cache is enabled and 0.25% of the memory target is allocated to the result cache.
C. The command produces an error because the database instance is started up with the RESULT_CACHE_MAX_SIZE parameter set to 0.
D. The query result cache is not enabled because the database instance is started up with the RESULT_CACHE_MAX_SIZE parameter set to 0.
Q50. Examine the PL/SQL code for the GET_TABLE_MD function given below:
CREATE OR REPLACE FUNCTION get_table_md RETURN CLOB IS
h := DBMS_METADATA.OPEN('TABLE');
th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
doc := DBMS_METADATA.FETCH_CLOB(h);
Which statement is true about the compilation and execution of the function?
A. The function retrieves the metadata in Extensible Markup Language (XML) format for creating the TIMECARDS table in the HR schema.
B. The compilation produces an error because DBMS_METADATA.SET_FILTER(h,'SCHEMA','HR')is not placed in the correct order.
C. The function retrieves the metadata as a data definition language (DDL) statement for creating the TIMECARDS table in the HR schema.
D. The execution of the function produces an error because multiple objects are fetched and DBMS_METADATA.FETCH_CLOB is not called in a LOOP.
see more 1Z0-146 dumps