Oracle Program with PL/SQL (1Z0-149) Exam Dumps 2023
Oracle Program with PL/SQL (1Z0-149) Exam Dumps 2023
Visit Official SkillCertPro Website :-
Oracle Program with PL/SQL (1Z0-149) Practice Tests 2023. Contains 1050+ exam questions to pass the exam in first attempt.
For a full set of 1050+ questions. Go to
https://skillcertpro.com/product/oracle-database-1z0-149-exam-questions/
SkillCertPro offers detailed explanations to each question which helps to understand the concepts better.
It is recommended to score above 85% in SkillCertPro exams before attempting a real exam.
SkillCertPro updates exam questions every 2 weeks.
You will get life time access and life time free updates
SkillCertPro assures 100% pass guarantee in first attempt.
Question 1:
Examine this block:
1 DECLARE
2 TYPE va$ IS VARRAY(200) OF NUMBER);
3 va va$ :)= va$();
4 BEGIN
5 va.EXTEND(100);
6 END;
Which two will be correct after line 5?
A. va.LAST and va.LIMIT will return the same value.
B. va.LAST and va.COUNT will return the same value.
C. va.LIMIT and va.COUNT will return the same value.
D. va.LIMIT and va.NEXT (199) will return the same value.
E. va.LAST will return 200.
F. va.NEXT (199) will return NULL.
Answer: B, F
Explanation:
Limit will be 200, count will be 100 and last will be 100 and next(199) will return null.
Question 2:
Identify the two correct scenarios where a function can be optimized using the function result cache feature.
A. A function which inserts multiple records into a DEPARTMENTS table as part of one-time data setup for an HR application.
B. A function which accesses multiple tables and calculates the commission to be given to a sales representative based on the number of products sold by that representative.
C. A function which deletes all the records from an EMPLOYEES_AUDIT table based on their LOG_DATE.
D. A function which updates the SALARY of all the employees in an EMPLOYEES table by a fixed percentage based on their DESIGNATION.
E. A function which calculates the factorial of a given number without accessing any table.
Answer: B, D
Explanation:
D has a data integrity problem when execute the update
Question 3:
Which two statements are true about the DBMS_ LOB package?
A. DBMS_LOB.COMPARE can compare parts of two LOBs.
B. DBMS_LOB.COMPARE returns the size difference of the compared LOBs.
C. DBMS_LOB.COMPARE is overloaded and can compare CLOBs with BLOBs.
D. If the destination LOB is a temporary LOB, the row must be locked before calling DBMS_LOB.CONVERTTOBLOB.
E. Before calling DBMS_LOB.CONVERTTOBLOB, both the source and destination LOB instances must exist.
Answer: A, E
Explanation:
D is false because it says temporary instead of persistent.
https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_lob.htm#BABEAJAD
https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_lob.htm#i1020355
Question 4:
Examine this code:
CREATE FUNCTION invoice_date RETURN VARCHAR2
RESULT_CACHE AUTHID DEFINER IS
l_date VARCHAR2(50);
BEGIN
l_date := SYSDATE;
RETURN l_date;
END;
Users of this function may set different date formats in their sessions.
Which two modifications must be made to allow the use of your sessions date format when outputting the cached result of this function?
A. Change the RETURN type to DATE.
B. Change AUTHID to CURRENT_USER.
C. Use the TO_CHAR function around SYSDATE, that is, 1_date := TO_CHAR (SYSDATE).
D. Change the data type of 1_date to DATE.
E. Set NLS_DATE_FORMAT to DD-MM-YY at the instance level.
F. Set the RESULT_CACHE_MODE parameter to FORCE.
Answer: A, D
Question 5:
Which statement is true about internal and external LOBs?
A. An external LOB can be loaded into an internal LOB variable using the DBMS_LOB package.
B. A NOEXIST_DIRECTORY exception can be raised when using internal and external LOBs.
C. Internal and external LOBs can be written using DBMS_LOB.
D. After an exception transfers program control outside a PL/SQL block, all references to open external LOBs are lost.
E. When using DBMS_LOB.INSTR for internal and external LOBs, DBMS_LOB.OPEN should be called for each LOB.
Answer: A
Explanation:
https://docs.oracle.com/database/121/ARPLS/d_lob.htm#ARPLS66728
LOADCLOBFROMFILE Procedure:
This procedure loads data from a BFILE to an internal CLOB/NCLOB with necessary character set conversion and returns the new offsets.
D. https://docs.oracle.com/database/121/ARPLS/d_lob.htm#ARPLS600
After the exception has occurred, the BFILE locator variable file goes out of scope, and no further operations on the file can be done using that variable. Therefore, the solution is to use an exception handler
For a full set of 1050+ questions. Go to
https://skillcertpro.com/product/oracle-database-1z0-149-exam-questions/
SkillCertPro offers detailed explanations to each question which helps to understand the concepts better.
It is recommended to score above 85% in SkillCertPro exams before attempting a real exam.
SkillCertPro updates exam questions every 2 weeks.
You will get life time access and life time free updates
SkillCertPro assures 100% pass guarantee in first attempt.
Question 6:
You created a PL/SQL function with the RESULT_CACHE clause, which calculates a percentage of total marks for each student by querying the MARKS table.
A. Under which two circumstances will the cache for this function not be used and the function body be executed instead?
B. Under which two circumstances will the cache for this function not be used and the function body be executed instead?
C. When a user fixes incorrect marks for a student, with an update to the MARKS table, and then executes the function in the same session
D. When the amount of memory allocated for the result cache is increased
E. When the function is executed in a session frequently with the same parameter value
F. When the database administrator disables the result cache during ongoing application patching
Answer: B, E
Explanation:
RESULT_CACHE_MAX_RESULT(Specifies the maximum amount of server result cache memory (in percent) that can be used for a single result. Valid values are between 1 and 100. The default value is 5%. You can set this parameter at the system or session level.) cannot be set to 0.
Question 7:
You work as a Database Administrator for Tech-Mart Inc. The company uses an Oracle database. Andrew is an employee in the company. He owns a table named Customers. He is required to create a procedure named Add_Customer that will be used to add new customers to the Customers table. You grant the CREATE ANY PROCEDURE system privilege to Andrew so that he is able to create the procedure. You also want to ensure that Andrew is capable of executing the Add_Customer procedure as well as other procedures in the database. Which other privilege will you grant to Andrew?
A. EXECUTE ANY PROCEDURE system privilege
B. EXECUTE object privilege
C. Andrew does not need any additional privilege.
D. RUN ANY PROCEDURE system privilege
Answer: C
Question 8:
Which of the following datatypes makes PL/SQL code to perform better under Real Native Compilation?
A. NATIVE_INTEGER
B. BINARY_ INTEGER
C. PLS_INTEGER
D. SIMPLE_ INTEGER
Answer: D
Question 9:
You work as an Application Developer for Dolliver Inc. The company uses an Oracle database. You own subprograms that reference to other subprograms on remote locations. Oracle server uses the signature mode of remote dependency in order to manage remote dependencies among the subprograms. Which of the following statements about the signature mode of remote dependency are true? . Choose two.
A. Signature mode is the default mode of remote dependency.
B. Oracle server records both the timestamp and the signature for each PL/SQL program unit.
C. Oracle server records only the signature for each PL/SQL program unit.
D. Using the signature mode prevents the unnecessary recompilation of dependent local procedures, as itallows remote procedures to be recompiled without affecting the dependent local procedures.
Answer: B, D
Question 10:
Samantha designs a database for Oliver Inc. She wants to retrieve the text associated with the current exception. Which of the following functions will she use?
A. SQLERRMSG
B. ERRORTEXT
C. ERRTEXT
D. SQLERRM
Answer: D
For a full set of 1050+ questions. Go to
https://skillcertpro.com/product/oracle-database-1z0-149-exam-questions/
SkillCertPro offers detailed explanations to each question which helps to understand the concepts better.
It is recommended to score above 85% in SkillCertPro exams before attempting a real exam.
SkillCertPro updates exam questions every 2 weeks.
You will get life time access and life time free updates
SkillCertPro assures 100% pass guarantee in first attempt.