QUESTION 1
Examine this function:
CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG (V_ID in PLAYER_BAT_STAT.PLAYER_ID%
TYPE) RETURN NUMBER IS V_AVG NUMBER;
BEGIN
SELECT HITS / AT_BATS
INTO V_AVG
FROM PLAYER_BAT_STAT
WHERE PLAYER_ID = V_ID; RETURN (V_AVG);
END
Which statement will successfully invoke this function in SQL *Plus?
A. SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;
B. EXECUTE CALC_PLAYER_AVG(31);
C. CALC_PLAYER('RUTH');
D. CALC_PLAYER_AVG(31);
E. START CALC_PLAYER_AVG(31)
Answer: A Incorrect Answers
B. You can't call a function in this way, in this way you can call a procedure, because function must return a value, to call a function using EXECUTE command you should declare a bind variable using the VARIABLE command then assign the value returned from the function to this variable, in the following way:
SQL> VARIABLE v_get_value NUMBER
SQL> EXECUTE :v_get_value := CALC_PLAYER_AVG(31)
PL/SQL procedure successfully completed.
SQL> PRINT v_get_value V_GET_VALUE -----------
C. Again this way can't be use for calling a function in PL/SQL block because the function return a value and this values must be assigned to PL/SQL variable or to bind variable. Like this
DECLARE v_get_from_fn NUMBER;
BEGIN v_get_from := CALC_PLAYER_AVG(31);
END; /
D. Same as C. E. START is use to execute a script.
QUESTION 2
Which three are true statements about dependent objects? (Choose three)
A. Invalid objects cannot be described.
B. An object with status of invalid cannot be a referenced object.
C. The Oracle server automatically records dependencies among objects.
D. All schema objects have a status that is recorded in the data dictionary.
E. You can view whether an object is valid or invalid in the USER_STATUS data dictionary view.
F. You can view whether an object is valid or invalid in the USER_OBJECTS data dictionary view.
Answer: A,C,F Incorrect answers: B, D, E
QUESTION 3
You have created a stored procedure DELETE_TEMP_TABLE that uses dynamic SQL to remove a table in your schema.
You have granted the EXECUTE privilege to user A on this procedure.
When user A executes the DELETE_TEMP_TABLE procedure, under whose privileges are the operations performed by default?
A. SYS privileges
B. Your privileges
C. Public privileges
D. User A's privileges
E. User A cannot execute your procedure that has dynamic SQL.
Answer: B When you create a procedure, it will be executed under the privileges of the creator, unless the procedure has the following statement AUTHID CURRENT_USER.
If you specify AUTHID CURRENT_USER, the privileges of the current user are checked at run time, and external references are resolved in the schema of the current user. Like this example
SQL> CREATE OR REPLACE PROCEDURE delete_temp_table(v_table varchar2)
2 AUTHID CURRENT_USER
3 IS
4 BEGIN
5 EXECUTE IMMEDIATE 'DROP TABLE '||V_TABLE;
6 END;
7 / Procedure created.
If the procedure is create in this way then the
EXECUTE IMMEDIATE statement will be execute under the privilege of the user who executes the procedure, but if we skip line 2 then the procedure will be executed under the privilege of the owner of the procedure.
Incorrect Answers
A: SYS privilege has nothing with is.
C: What is the public privileges? There is nothing called public privileges.
D: This will be true if the procedure contains the AUTHID CURRENT_USER.
E: There is no problem in having a dynamic SQL statement in Procedure.
QUESTION 4 Examine this code: CREATE OR REPLACE PRODECURE add_dept (p_dept_name VARCHAR2 DEFAULT 'placeholder', p_location VARCHAR2 DEFAULT 'Boston') IS BEGIN INSERT INTO departments VALUES (dept_id_seq.NEXTVAL, p_dept_name, p_location);
END add_dept; /
Which three are valid calls to the add_dep procedure? (Choose three)
A. add_dept;
B. add_dept('Accounting');
C. add_dept(, 'New York');
D. add_dept(p_location=>'New York');.
Answer: A,B,D A is correct because both of the parameter have a default values.
B is correct because here we call the procedure using position notation, and the first parameter for the procedure will have the value 'Accounting', and since the second parameter has a default value then we can skip it, and in this case it will take the default value.
D is correct because here we are calling the procedure using naming notation, the value 'New York' will go to the parameter p_location, and the parameter p_dept_name will have the default value. The following table list the for passing parameters to a procedure: Incorrect Answer
C: You can't use this way and assume that the PL/SQL will understand that he should assign the default value for the first parameter. This is incorrect way for calling.
QUESTION 5 Which two statements about packages are true? (Choose two)
a. You can pass parameters to packages.
b. A package is loaded into memory each time it is invoked.
c. The contents of packages can be shared by many applications.
d You can achieve information hiding by making package constructs private.
Answer: c,d,actually theses are some of the advantages of the package, sharing the package among applications and hide the logic of the procedures and function that are inside the package by declaring them in the package header and write the code of these procedures and functions inside the package body.
Incorrect Answers
a: Parameters can't be passed to a package; parameters can be passed to procedures and functions only. C: By the first time you call a procedure, function, or reference a global variable within the package, the whole package will be loaded into the memory and stay there, so when ever you need to reference any of the package's constructs again you will find it in the memory.
QUESTION 6 Which two programming constructs can be grouped within a package? (Choose two)
A. Cursor B. Constant C. Trigger D. Sequence E. View
Answer: A,
B Incorrect Answers
C: Triggers are objects that we create are created on the tables.
D: Sequences can't be grouped inside the packages, but we can reference then inside the package.
E: Views are created and they are database objects, and they can't be grouped inside the packages.
QUESTION 7 Which two statements describe the state of a package variable after executing the package in which it is declared? (Choose two)
A. It persists across transactions within a session.
B. It persists from session to session for the same user.
C. It does not persist across transaction within a session.
D. It persists from user to user when the package is invoked.
E. It does not persist from session to session for the same user.
Answer: A,E You can keep track of the state of a package variable or cursor, which persists throughout the user session, from the time the user first references the variable or cursor to the time the user disconnects.
1. Initialize the variable within its declaration or within an automatic, one-time-only procedure.
2. Change the value of the variable by means of package procedures.
3. The value of the variable is released when the user disconnects. Incorrect
Answers B: Each session will have its own value for the variables
C: It persists across the transactions and through the user session.
D: Each user has his own values and results, because each user has his own users.
QUESTION 8 Which code can you use to ensure that the salary is not increased by more than 10% at a time nor is it ever decreased?
A. ALTER TABLE emp ADD CONSTRAINT ck_sal CHECK (sal BETWEEN sal AND sal*1.1);
B. CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary not increase by more than 10%'); END;
C. CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary not increase by more than 10%'); END;
D. CREATE OR REPLACE TRIGGER check_sal AFTER UPDATE OR sal ON emp WHEN (new.sal < old.sal OR -new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary not increase by more than 10%'); END;
Answer: B
Row triggers are the correct chose for solving the problem.
A row trigger fires each time the table is affected by the triggering event. If the triggering event affects no rows, a row trigger is not executed. Row triggers are useful if the trigger action depends on data of rows that are affected or on data provided by the triggering event itself. You can create a BEFORE row trigger in order to prevent the triggering operation from succeeding if a certain condition is violated. Within a ROW trigger, reference the value of a column before and after the data change by prefixing it with the OLD and NEW qualifier.
Incorrect Answers: A: Check constaint can't do this job lets take a look:
SQL> ALTER TABLE emp ADD 2 CONSTRAINT ck_sal CHECK (sal BETWEEN sal AND sal*1.1) 3 / Table altered. SQL> select ename, sal 2 from emp 3 where ename = 'Bill'; ENAME SAL ---------- ---------- Bill 5000 Now let's issue an update statement SQL> update emp 2 set sal = 10 3 where ename = 'Bill'; 1 row updated. As you can see the check constraint can't compare the old value with the new value.
D,C: You can use NEW and OLD qualifier with row level triggers, If in the CREATE TRIGGER statement you didn't say FOR EACH ROW then the trigger will be statement level trigger
QUESTION 9
Examine this code: CREATE OR REPLACE PACKAGE BODY bonus IS v_salary employees.salary%TYPE;
v_bonus employees.commission_pct%TYPE;
FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER IS BEGIN SELECT salary,
commission_pct
INTO v_salary, v_bonus
FROM employees
WHERE employee_id = p_emp_id; RETURN v_bonus * v_salary;
END calc_bonus FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER IS
BEGIN
SELECT salary, commission_pct
INTO v_salary, v_bonus
FROM employees
WHERE employees RETURN v_bonus * v_salary + v_salary;
END cacl_salary;
END bonus; /
Which statement is true?
A. You can call the BONUS.CALC_SALARY packaged function from an INSERT command against the EMPLOYEES table.
B. You can call the BONUS.CALC_SALARY packaged function from a SELECT command against the EMPLOYEES table.
C. You can call the BONUS.CALC_SALARY packaged function form a DELETE command against the EMPLOYEES table.
D. You can call the BONUS.CALC_SALARY packaged function from an UPDATE command against the EMPLOYEES table.
Answer: B For the Oracle server to execute a SQL statement that calls a stored function, it must know the purity level of a stored functions, that is, whether the functions are free of side effects.
Side effects are changes to database tables or public packaged variables (those declared in a package specification). Side effects could delay the execution of a query, yield order-dependent (therefore indeterminate) results, or require that the package state variables be maintained across user sessions. Various side effects are not allowed when a function is called from a SQL query or DML statement. Therefore, the following restrictions apply to stored functions called from SQL expressions:
• A function called from a query or DML statement may not end the current transaction, create or roll back to a savepoint, or alter the system or session
• A function called from a query statement or from a parallelized DML statement may not execute a DML statement or otherwise modify the database
• A function called from a DML statement may not read or modify the particular table being modified by that DML statement
QUESTION 10
Which statement is valid when removing procedures?
A. Use a drop procedure statement to drop a standalone procedure.
B. Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package specification.
C. Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package body.
D. For faster removal and re-creation, do not use a drop procedure statement. Instead, recompile the procedure using the alter procedure statement with the REUSE SETTINGS clause.
Answer: A The DROP DROCEDURE statement is used to drop a stand alone procedure Incorrect Answers: B: You can't drop a procedure that's inside a package, you have to drop the package, and in this case the whole procedures, functions,... that are inside the packages will be droped.
C: Same as B.
D: REUSE SETTINGS is used to to prevent Oracle from dropping and reacquiring compiler switch settings.With this clause, Oracle preserves the existing settings and uses them for the recompilation.
Examine this function:
CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG (V_ID in PLAYER_BAT_STAT.PLAYER_ID%
TYPE) RETURN NUMBER IS V_AVG NUMBER;
BEGIN
SELECT HITS / AT_BATS
INTO V_AVG
FROM PLAYER_BAT_STAT
WHERE PLAYER_ID = V_ID; RETURN (V_AVG);
END
Which statement will successfully invoke this function in SQL *Plus?
A. SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;
B. EXECUTE CALC_PLAYER_AVG(31);
C. CALC_PLAYER('RUTH');
D. CALC_PLAYER_AVG(31);
E. START CALC_PLAYER_AVG(31)
Answer: A Incorrect Answers
B. You can't call a function in this way, in this way you can call a procedure, because function must return a value, to call a function using EXECUTE command you should declare a bind variable using the VARIABLE command then assign the value returned from the function to this variable, in the following way:
SQL> VARIABLE v_get_value NUMBER
SQL> EXECUTE :v_get_value := CALC_PLAYER_AVG(31)
PL/SQL procedure successfully completed.
SQL> PRINT v_get_value V_GET_VALUE -----------
C. Again this way can't be use for calling a function in PL/SQL block because the function return a value and this values must be assigned to PL/SQL variable or to bind variable. Like this
DECLARE v_get_from_fn NUMBER;
BEGIN v_get_from := CALC_PLAYER_AVG(31);
END; /
D. Same as C. E. START is use to execute a script.
QUESTION 2
Which three are true statements about dependent objects? (Choose three)
A. Invalid objects cannot be described.
B. An object with status of invalid cannot be a referenced object.
C. The Oracle server automatically records dependencies among objects.
D. All schema objects have a status that is recorded in the data dictionary.
E. You can view whether an object is valid or invalid in the USER_STATUS data dictionary view.
F. You can view whether an object is valid or invalid in the USER_OBJECTS data dictionary view.
Answer: A,C,F Incorrect answers: B, D, E
QUESTION 3
You have created a stored procedure DELETE_TEMP_TABLE that uses dynamic SQL to remove a table in your schema.
You have granted the EXECUTE privilege to user A on this procedure.
When user A executes the DELETE_TEMP_TABLE procedure, under whose privileges are the operations performed by default?
A. SYS privileges
B. Your privileges
C. Public privileges
D. User A's privileges
E. User A cannot execute your procedure that has dynamic SQL.
Answer: B When you create a procedure, it will be executed under the privileges of the creator, unless the procedure has the following statement AUTHID CURRENT_USER.
If you specify AUTHID CURRENT_USER, the privileges of the current user are checked at run time, and external references are resolved in the schema of the current user. Like this example
SQL> CREATE OR REPLACE PROCEDURE delete_temp_table(v_table varchar2)
2 AUTHID CURRENT_USER
3 IS
4 BEGIN
5 EXECUTE IMMEDIATE 'DROP TABLE '||V_TABLE;
6 END;
7 / Procedure created.
If the procedure is create in this way then the
EXECUTE IMMEDIATE statement will be execute under the privilege of the user who executes the procedure, but if we skip line 2 then the procedure will be executed under the privilege of the owner of the procedure.
Incorrect Answers
A: SYS privilege has nothing with is.
C: What is the public privileges? There is nothing called public privileges.
D: This will be true if the procedure contains the AUTHID CURRENT_USER.
E: There is no problem in having a dynamic SQL statement in Procedure.
QUESTION 4 Examine this code: CREATE OR REPLACE PRODECURE add_dept (p_dept_name VARCHAR2 DEFAULT 'placeholder', p_location VARCHAR2 DEFAULT 'Boston') IS BEGIN INSERT INTO departments VALUES (dept_id_seq.NEXTVAL, p_dept_name, p_location);
END add_dept; /
Which three are valid calls to the add_dep procedure? (Choose three)
A. add_dept;
B. add_dept('Accounting');
C. add_dept(, 'New York');
D. add_dept(p_location=>'New York');.
Answer: A,B,D A is correct because both of the parameter have a default values.
B is correct because here we call the procedure using position notation, and the first parameter for the procedure will have the value 'Accounting', and since the second parameter has a default value then we can skip it, and in this case it will take the default value.
D is correct because here we are calling the procedure using naming notation, the value 'New York' will go to the parameter p_location, and the parameter p_dept_name will have the default value. The following table list the for passing parameters to a procedure: Incorrect Answer
C: You can't use this way and assume that the PL/SQL will understand that he should assign the default value for the first parameter. This is incorrect way for calling.
QUESTION 5 Which two statements about packages are true? (Choose two)
a. You can pass parameters to packages.
b. A package is loaded into memory each time it is invoked.
c. The contents of packages can be shared by many applications.
d You can achieve information hiding by making package constructs private.
Answer: c,d,actually theses are some of the advantages of the package, sharing the package among applications and hide the logic of the procedures and function that are inside the package by declaring them in the package header and write the code of these procedures and functions inside the package body.
Incorrect Answers
a: Parameters can't be passed to a package; parameters can be passed to procedures and functions only. C: By the first time you call a procedure, function, or reference a global variable within the package, the whole package will be loaded into the memory and stay there, so when ever you need to reference any of the package's constructs again you will find it in the memory.
QUESTION 6 Which two programming constructs can be grouped within a package? (Choose two)
A. Cursor B. Constant C. Trigger D. Sequence E. View
Answer: A,
B Incorrect Answers
C: Triggers are objects that we create are created on the tables.
D: Sequences can't be grouped inside the packages, but we can reference then inside the package.
E: Views are created and they are database objects, and they can't be grouped inside the packages.
QUESTION 7 Which two statements describe the state of a package variable after executing the package in which it is declared? (Choose two)
A. It persists across transactions within a session.
B. It persists from session to session for the same user.
C. It does not persist across transaction within a session.
D. It persists from user to user when the package is invoked.
E. It does not persist from session to session for the same user.
Answer: A,E You can keep track of the state of a package variable or cursor, which persists throughout the user session, from the time the user first references the variable or cursor to the time the user disconnects.
1. Initialize the variable within its declaration or within an automatic, one-time-only procedure.
2. Change the value of the variable by means of package procedures.
3. The value of the variable is released when the user disconnects. Incorrect
Answers B: Each session will have its own value for the variables
C: It persists across the transactions and through the user session.
D: Each user has his own values and results, because each user has his own users.
QUESTION 8 Which code can you use to ensure that the salary is not increased by more than 10% at a time nor is it ever decreased?
A. ALTER TABLE emp ADD CONSTRAINT ck_sal CHECK (sal BETWEEN sal AND sal*1.1);
B. CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary not increase by more than 10%'); END;
C. CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary not increase by more than 10%'); END;
D. CREATE OR REPLACE TRIGGER check_sal AFTER UPDATE OR sal ON emp WHEN (new.sal < old.sal OR -new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary not increase by more than 10%'); END;
Answer: B
Row triggers are the correct chose for solving the problem.
A row trigger fires each time the table is affected by the triggering event. If the triggering event affects no rows, a row trigger is not executed. Row triggers are useful if the trigger action depends on data of rows that are affected or on data provided by the triggering event itself. You can create a BEFORE row trigger in order to prevent the triggering operation from succeeding if a certain condition is violated. Within a ROW trigger, reference the value of a column before and after the data change by prefixing it with the OLD and NEW qualifier.
Incorrect Answers: A: Check constaint can't do this job lets take a look:
SQL> ALTER TABLE emp ADD 2 CONSTRAINT ck_sal CHECK (sal BETWEEN sal AND sal*1.1) 3 / Table altered. SQL> select ename, sal 2 from emp 3 where ename = 'Bill'; ENAME SAL ---------- ---------- Bill 5000 Now let's issue an update statement SQL> update emp 2 set sal = 10 3 where ename = 'Bill'; 1 row updated. As you can see the check constraint can't compare the old value with the new value.
D,C: You can use NEW and OLD qualifier with row level triggers, If in the CREATE TRIGGER statement you didn't say FOR EACH ROW then the trigger will be statement level trigger
QUESTION 9
Examine this code: CREATE OR REPLACE PACKAGE BODY bonus IS v_salary employees.salary%TYPE;
v_bonus employees.commission_pct%TYPE;
FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER IS BEGIN SELECT salary,
commission_pct
INTO v_salary, v_bonus
FROM employees
WHERE employee_id = p_emp_id; RETURN v_bonus * v_salary;
END calc_bonus FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER IS
BEGIN
SELECT salary, commission_pct
INTO v_salary, v_bonus
FROM employees
WHERE employees RETURN v_bonus * v_salary + v_salary;
END cacl_salary;
END bonus; /
Which statement is true?
A. You can call the BONUS.CALC_SALARY packaged function from an INSERT command against the EMPLOYEES table.
B. You can call the BONUS.CALC_SALARY packaged function from a SELECT command against the EMPLOYEES table.
C. You can call the BONUS.CALC_SALARY packaged function form a DELETE command against the EMPLOYEES table.
D. You can call the BONUS.CALC_SALARY packaged function from an UPDATE command against the EMPLOYEES table.
Answer: B For the Oracle server to execute a SQL statement that calls a stored function, it must know the purity level of a stored functions, that is, whether the functions are free of side effects.
Side effects are changes to database tables or public packaged variables (those declared in a package specification). Side effects could delay the execution of a query, yield order-dependent (therefore indeterminate) results, or require that the package state variables be maintained across user sessions. Various side effects are not allowed when a function is called from a SQL query or DML statement. Therefore, the following restrictions apply to stored functions called from SQL expressions:
• A function called from a query or DML statement may not end the current transaction, create or roll back to a savepoint, or alter the system or session
• A function called from a query statement or from a parallelized DML statement may not execute a DML statement or otherwise modify the database
• A function called from a DML statement may not read or modify the particular table being modified by that DML statement
QUESTION 10
Which statement is valid when removing procedures?
A. Use a drop procedure statement to drop a standalone procedure.
B. Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package specification.
C. Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package body.
D. For faster removal and re-creation, do not use a drop procedure statement. Instead, recompile the procedure using the alter procedure statement with the REUSE SETTINGS clause.
Answer: A The DROP DROCEDURE statement is used to drop a stand alone procedure Incorrect Answers: B: You can't drop a procedure that's inside a package, you have to drop the package, and in this case the whole procedures, functions,... that are inside the packages will be droped.
C: Same as B.
D: REUSE SETTINGS is used to to prevent Oracle from dropping and reacquiring compiler switch settings.With this clause, Oracle preserves the existing settings and uses them for the recompilation.
