Examine this procedure:
CREATE OR REPLACE PROCEDURE INSERT_TEAM
(V_ID in NUMBER, V_CITY in VARCHAR2 DEFAULT 'AUSTIN', V_NAME in VARCHAR2)
IS
BEGIN
INSERT INTO TEAM (id, city, name)
VALUES (v_id, v_city, v_name);
COMMIT;
END
Which two statements will successfully invoke this procedure in SQL *Plus? (Choose two)

• A. EXECUTE INSERT_TEAM(3, 'AUSTIN','LONGHORNS');
• B. EXECUTE INSERT_TEAM(3, V_NAME=>'LONGHORNS', V_CITY=>'AUSTIN');
• C. EXECUTE INSERT_TEAM (3, 'LONGHORNS');
• D. EXECUTE INSERT_TEAM (V_ID := V_NAME := 'LONGHORNS', V_CITY := 'AUSTIN');
• E. EXECUTE INSERT_TEAM;

Explanation: Only visible for UpdateDumps members. You can sign-up / login (it's free).

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. EXECUTE CALC_PLAYER_AVG(31);
• B. CALC_PLAYER_AVG(31);
• C. START CALC_PLAYER_AVG(31)
• D. CALC_PLAYER('RUTH');
• E. SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;

Explanation: Only visible for UpdateDumps members. You can sign-up / login (it's free).

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. 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.
• D. Use a drop procedure statement to drop a procedure that is part of a package.
  Then recompile the package body.

Explanation: Only visible for UpdateDumps members. You can sign-up / login (it's free).

Examine this code CREATE OR REPLACE FUNCTION change_dept (p_old_id NUMBER, p_deptname VARCHAR2) RETURN NUMBER IS V_new_id NUMBER; BEGIN SELECT departments_seq.nextval INTO v_new_id FROM dual; UPDATE departments SET departmenet_id = v_new_id, Department_name = p_deptname WHERE department_id = p_old_id; Return v_new_id; END; /
There are no foreign key integrity constraints on the EMPLOYEES and DEPARTMENTS tables. Which statement performs a successful update to the EMPLOYEES table?

• A. UPDATE departments
  change_dept(270, 'Outsource')
  Where department_name = 'payroll';
• B. UPDATE employees
  SET department_id = change_dept(10, 'Finance')
  Where department_id = DEPARTMENTS:CURRVAL;
• C. UPDATE employees
  SET department_id = change_dept(10, 'Finance')
  Where department_id = 10;
• D. UPDATE departments
  SET department_id = change_dept(10, 'Finance')
  Where department_id = 10;

Explanation: Only visible for UpdateDumps members. You can sign-up / login (it's free).

Which two describe a stored procedure? (Choose two)

• A. A stored procedure has three parts: the specification, the body, and the exception handler part.
• B. The executable section of a stored procedure contains statements that assigns values, control execution, and return values to the calling environment.
• C. A stored procedure is a type of PL/SQL subprogram that performs an action.
• D. A stored procedure is typically written in SQL.
• E. A stored procedure is a named PL/SQL block that can accept parameters.

Explanation: Only visible for UpdateDumps members. You can sign-up / login (it's free).