Free Oracle 1Z0-147 Exam Dumps Questions & Answers

The creation of which database objects will cause a DDL trigger to fire? (Choose all that apply)

• A.  Cluster
• B.  Database links
• C.  Function
• D.  Dimensions
• E.  Index
• F.  Package
• G.  Synonyms

Given a function CALCTAX:
CREATE OR REPLACE FUNCTION calctax (sal NUMBER) RETURN NUMBER
IS
BEGIN
RETURN (sal * 0.05);
END;
If you want to run the above function from the SQL *Plus prompt, which statement is true?

• A.  You need to create a SQL *Plus environment variable X and issue the command :X := CALCTAX(1000);.
• B.  You need to execute the command CALCTAX(1000);.
• C.  You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX;
• D.  You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX(1000);
• E.  You need to execute the command EXECUTE FUNCTION calctax;.

Examine this code:
CREATE OR REPLACE PROCEDURE set_bonus
(p_cutoff IN VARCHAR2 DEFAULT 'WEEKLY'
p_employee_id IN employees_employee_id%TYPE
p_salary IN employees_salary%TYPE,
p_bonus_percent IN OUT NUMBER DEFAULT 1.5,
p_margin OUT NUMBER DEFAULT 2,
p_bonus_value OUT NUMBER)
IS
BEGIN UPDATE emp_bonus SET bonus_amount =(p_salary * p_bonus_percent)/p_margin WHERE employee_id = p_employee_id; END set_bonus; /
You execute the CREATE PROCEDURE statement above and notice that it fails. What are two reasons why it fails? (Choose two)

• A.  The declaration of the format parameter p_margin cannot have a DEFAULT clause.
• B.  The declaration of the format parameter p_bonus_percent cannot have a DEFAULT clause.
• C.  The formal parameter p_cutoff cannot have a DEFAULT clause.
• D.  The format parameter p_bonus_value is declared but is not used anywhere.
• E.  You cannot update a table using a stored procedure.
• F.  The syntax of the UPDATE statement is incorrect.

When using a packaged function in a query, what is true?

• A.  The COMMIT and ROLLBACK commands are allowed in the packaged function.
• B.  The packaged function can execute and INSERT, UPDATE, or DELETE statement against the table that is being queried if it is used in a subquery.
• C.  The packaged function can execute an INSERT, UPDATEM or DELETE statement against the table that is being queried if the pragma RESTRICT REFERENCE is used.
• D.  The packaged function cannot execute an INSERT, UPDATE, or DELETE statement against the table that is being queried.
• E.  You can not use packaged functions in a query statement.

You have a row level BEFORE UPDATE trigger on the EMP table. This trigger contains a SELECT statement on the EMP table to ensure that the new salary value falls within the minimum and maximum salary for a given job title.
What happens when you try to update a salary value in the EMP table?

• A.  The trigger fails because you cannot use the minimum and maximum functions in a BEFORE UPDATE trigger.
• B.  The trigger fires successfully.
• C.  The trigger fails because it needs to be a row level AFTER UPDATE trigger.
• D.  The trigger fails because a SELECT statement on the table being updated is not allowed.