Examine this code: CREATE OR REPLACE FUNCTION gen_email_name (p_first_name VARCHAR2, p_last_name VARCHAR2, p_id NUMBER) RETURN VARCHAR2 is v_email_name VARCHAR2(19); BEGIN v_email_home := SUBSTR(p_first_name, 1, 1) || SUBSTR(p_last_name, 1, 7) || .@Oracle.com .; UPDATE employees SET email = v_email_name WHERE employee_id = p_id; RETURN v_email_name; END; You run this SELECT statement: SELECT first_name, last_name gen_email_name(first_name, last_name, 108) EMAIL FROM employees; What occurs?

The statement fails because functions called from SQL expressions cannot perform DML.

Oracle PL/SQL Stored Procedures and Functions

Question 1 Multiple Choice (Single Answer)

Which type of argument passes a value from a procedure to the calling environment?

VARCHAR2
BOOLEAN
OUT
IN

Question 2 Multiple Choice (Single Answer)

Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHAR2) IS BEGIN INSERT INTO PLAYER (ID,LAST_NAME) VALUES (V_ID, V_LAST_NAME); COMMIT; END; This procedure must invoke the APD_BAT_STAT procedure and pass a parameter. Which statement, when added to the above procedure will successfully invoke the UPD_BAT_STAT procedure?

EXECUTE UPD_BAT_STAT(V_ID);
UPD_BAT_STAT(V_ID);
RUN UPD_BAT_STAT(V_ID);
START UPD_BAT_STAT(V_ID);

Question 3 Multiple Choice (Single Answer)

When creating a function in SQL *Plus, you receive this message: .Warning: Function created with compilation errors.. Which command can you issue to see the actual error message?

SHOW FUNCTION_ERROR
SHOW USER_ERRORS
SHOW ERRORS
SHOW ALL_ERRORS

Question 4 Multiple Choice (Single Answer)

Examine this code: CREATE OR REPLACE FUNCTION gen_email_name (p_first_name VARCHAR2, p_last_name VARCHAR2, p_id NUMBER) RETURN VARCHAR2 is v_email_name VARCHAR2(19); BEGIN v_email_home := SUBSTR(p_first_name, 1, 1) || SUBSTR(p_last_name, 1, 7) || [email protected] .; UPDATE employees SET email = v_email_name WHERE employee_id = p_id; RETURN v_email_name; END; You run this SELECT statement: SELECT first_name, last_name gen_email_name(first_name, last_name, 108) EMAIL FROM employees; What occurs?

Employee 108 has his email name updated based on the return result of the function.
The statement fails because functions called from SQL expressions cannot perform DML.
The statement fails because the functions does not contain code to end the transaction.
The SQL statement executes successfully, because UPDATE and DELETE statements are ignoring in stored functions called from SQL expressions.
The SQL statement executes successfully and control is passed to the calling environment.

Question 5 Multiple Choice (Single Answer)

Given a function CALCTAX : CREATE OR REPLACE FUNCTION calc tax (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?

You need to execute the command CALCTAX(1000); .
You need to execute the command EXECUTE FUNCTION calc tax; .
You need to create a SQL *Plus environment variable X and issue the command :X := CALCTAX(1000); .
You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX;
You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX(1000);

Question 6 Multiple Choice (Single Answer)

Which statement is valid when removing procedures?

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

Question 7 Multiple Choice (Multiple Answers)

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)

add_dept;
add_dept( .Accounting .);
add_dept(, .New York .);
add_dept(p_location=> .New York .);

Question 8 Multiple Choice (Single Answer)

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?

SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;
EXECUTE CALC_PLAYER_AVG (31);
CALC_PLAYER (.RUTH.);
CALC_PLAYER_AVG(31);
START CALC_PLAYER_AVG(31)

Question 9 Multiple Choice (Single Answer)

When creating store procedures and functions which construct allows you to transfer values to and from the calling environment?

Local variables.
Arguments.
Boolean variables.
Substitution variables.

Question 10 Multiple Choice (Single Answer)

Which type of argument passes a value from a calling environment?

VARCHAR2.
BOOLEAN.
OUT.
IN.

Question 11 Multiple Choice (Single Answer)

Under which situation do you create a server side procedure?

When the procedure contains no SQL statements.
When the procedure contains no PL/SQL commands.
When the procedure needs to be used by many client applications accessing several remote databases.
When the procedure needs to be used by many users accessing the same schema objects on a local database.

Question 12 Multiple Choice (Single Answer)

Examine this package body:CREATE OR REPLACE PACKAGE BODY forward_packISV_sum NUMBER;- 44 -PROCEDURE calc_ord(. . . );PROCEDURE generate_summary(. . . )ISBEGINCalc_ord(. . . );. . .END calc_ord;END forward_pack;/ Which construct has a forward declaration?

V_SUM
CALC_ORD.
FORWARD_PACK
GENERATE_SUMMARY.

Question 13 Multiple Choice (Single Answer)

Examine this procedure:CREATE OR REPLACE PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME VARCHER2(30))ISBEGININSERT INTO PLAYER(ID, LAST_NAME)VALUES(V_ID, V_LAST_NAME);COMMIT;END; Why does this command fail when executed?

When declaring arguments length is not allowed.
When declaring arguments each argument must have a mode specified.
When declaring arguments each argument must have a length specified.
When declaring a VARCHAR2 argument it must be specified.

Question 14 Multiple Choice (Multiple Answers)

All users currently have the INSERT privileges on the PLAYER table. You want only your users to insert into this table using the ADD_PLAYER procedure. Which two actions must you take? (Choose two)

a) GRANT SELECT ON ADD_PLAYER TO PUBLIC;
b) GRANT EXECUTE ON ADD_PLAYER TO PUBLIC;
c) GRANT INSERT ON PLAYER TO PUBLIC;
d) GRANT EXECUTE, INSERT ON ADD_PLAYER TO PUBLIC;
e) REVOKE INSERT ON PLAYER FROM PUBLIC;

Question 15 Multiple Choice (Single Answer)

The PROCEDURE_ADD_PRODUCT is defined within a package specifications as follows: PROCEDURE_ADD_PRODUCT (P_PRODNO NUMBER,P_PRODNAME VARCHER2); Which procedure declaration can’t be added to package specifications?

a) PROCEDURE add_product (p_order_date DATE);
b) PROCEDURE add_product (p_name VARCHER2, P_ORDERED DATE);
c) PROCEDURE add_product (p_prodname VARCHER2, P_PRISE NUMBER);
d) PROCEDURE add_product (p_prize NUMBER, P_DESCRIPTION VARCHER2);

Question 16 Multiple Choice (Single Answer)

Command to see the current user ?

show users;
display user;
show name;
show user;

Question 17 Multiple Choice (Single Answer)

Command to change SQL prompt name ?

SQL> set sqlprompt “Qwest > “
SQL> set prompt “Qwest > “
SQL> sqlprompt set “Qwest > “
SQL> prompt “Qwest > “

Question 18 Multiple Choice (Single Answer)

Command to switch to UNIX prompt ?

bye
end
host
None of above

Question 19 Multiple Choice (Single Answer)

Which one of the following is not an Explicit Cursor attributes

cursor_name%Found
cursor_name%NOTFOUND
cursor_name%OPEN
cursor_name%ROWCOUNT

Question 20 Multiple Choice (Single Answer)

Which one of the following is not an Implicit Cursor attributes

SQL%Found
SQL%ISOPEN
SQL%NOTFOUND
SQL%ROWCOUNT

Oracle PL/SQL Stored Procedures and Functions

Questions