Exam Code: 1Z0-144 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database 11g: Program with PL/SQL
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1Z0-144 Exam.
♥♥ 2021 NEW RECOMMEND ♥♥
Free VCE & PDF File for Oracle 1Z0-144 Real Exam (Full Version!)
★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions
Q1. User SCOTT has been granted CREATE ANY TRIGGER AND ALTER ANY TABLE by the DBA. HR is an existing schema in the database.
SCOTT creates the following trigger:
CREATE OR REPLACE TRIGGER drop_trigger BEFORE DROP ON hr.SCHEMA
RAISE_APPLICATION_ERROR (-20000, ‘Cannot drop object');
SCOTT does not grant the execute privilege on this trigger to any other users.
For which user(s) would this trigger fire by default when they drop an object in the hr schema?
A. Only HR
B. SCOTT and HR
C. Only SCOTT
D. SCOTT, HR, and SYS
Q2. In which of the following scenarios would you recommend using PL/SQL records?
A. when you want to retrieve an entire row from a table and perform calculations
B. when you know the number of elements in advance and the elements are usually accessed sequentially
C. when you want to create a separate lookup table with multiple entries for each row of the main table, and access it through join queries
D. when you want to create a relatively small lookup table, where the collection can be constructed in memory each time a subprogram is invoked
Q3. Examine the following PL/SQL code:
Which statement is true about the execution of the PL/SQL code?
A. It executes successfully.
B. It generates a run-time exception.
C. It does not execute because of syntax error
D. It executes successfully and generates a warning.
Explanation: Reference: http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/errors.htm
Q4. Which two statements are correct about the usage of parameters in functions? (Choose two.)
A. Functions can have only in mode parameters.
B. Functions called in SQL statements cannot have out or in out mode parameters.
C. Functions having in, out, or in out parameters can be called only in named PL/SQL subprograms
D. Functions having in, out, or in out parameters can be called In PL/SQL procedures and anonymous blocks.
Q5. 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.
Q6. 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.
Q7. Which two statements are true about statement-level and row-level triggers? (Choose two.)
A. A row trigger fires once even if no rows are affected.
B. A statement trigger fires once even if no rows are affected.
C. Row triggers are useful if the trigger action depends on the data of rows that are affected or on data that is provided by the triggering event itself.
D. Statement triggers are useful if the trigger action depends on the data of rows that are affected or on data that is provided by the triggering event itself.
Q8. 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.
Q9. 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
Q10. 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.