Braindumps for "1Z0-101" Exam

Develop PL/SQL Program Units

 Question 1.
Examine this procedure:
CREATE OR REPLACE PROCEDURE update_employee
	(v_emp_id IN NUMBER)
IS
	v_comm NUMBER;
	PROCEDURE calc_comm
	IS
	v_total NUMBER;
	BEGIN
		SELECT SUM(ord.total)
		INTO v_total
		FROM ord,customer
		WHERE ord.custid = customer.custid
		AND customer.repid = v_emp_id;
		v_comm := v_total * .20;
	END calc_comm;
	v_percentage NUMBER;
BEGIN
	SELECT percentage
	INTO v_percentage
	FROM daily_figures
	WHERE TRUNC(figure_date) = TRUNC(SYSDATE);
	IF v_percentage > 33 THEN
		calc_comm;
	END IF;
END;

Why does this code cause an error when compiled?

A.	The SUBPROGRAM keyword does not exist.
B.	CALC_COMM must be invoked using the EXECUTE command.
C.	CALC_COMM must be declared after all local variable declarations.
D.	CALC_COMM must be declared before all local variable declaration.

Answer:  C

Explanation:
Subprograms must be declared after all local variables.

CREATE OR REPLACE PROCEDURE update_employee
(v_emp_id IN NUMBER)
IS
	v_comm   NUMBER;
	v_percentage NUMBER;
	PROCEDURE calc_comm
	IS
		v_total NUMBER;
	BEGIN
		SELECT SUM (ord.total)
		  INTO v_total
		  FROM ord,customer
		  WHERE ord.custid = customer.custid
		  AND   customer.repid = v_emp_id;
		  v_comm := v_tota * .20;
	END calc_comm;
BEGIN
	SELECT percentage
	INTO  v_percentage
	FROM daily_figures
	WHERE TRUNC(figure_date) = TRUNC(SYSDATE);

	IF v_percentage > 33 THEN
		calc_comm
	END IF;

END;

Question 2.
In order for you to create run a package MAINTAIN_DATA which privilege do you need?

A.	EXECUTE privilege on the MAINTAIN_DATA package.
B.	INVOKE privilege on the MAINTAIN_DATA package.
C.	EXECUTE privilege on the program units in the MAINTAIN_DATA package.
D.	Object privilege on all of the objects that the MAINTAIN_DATA package is accessing.
E.	Execute privilege on the program units inside the MAINTAIN_DATA package and execute privilege on the MAINTAIN_DATA package.

Answer:   A

Question 3.
You have created a script file EMP_PROC.SQL that holds the text to create a procedure PROCESS_EMP. You have compiled the procedure for SQL Plus environment by running the script file EMP_PROC.SQL. What happens if there are syntax errors in the procedure PROCESS_EMP?

A.	The errors are stored in the EMP_PROC.ERR file.
B.	The errors are displayed to the screen when the script file is run.
C.	The errors are stored in the procedure_errors data dictionary view.
D.	YOU need to issue the SHOWERRORS command in the SQL Plus environment to see the errors.
E.	YOU need to issue the display errors command in the SQL Plus environment to see the errors.

Answer:   D

Question 4.
Which statement about the local dependent object is TRUE?

A. They are on different nodes.
B. They are in a different database.
C. They are on the same node in the same database.
D. They are on the same node in a different database.

Answer:   C

Question 5.
You need to create a stored procedure, which deletes rows from a table. The name of the table from which the rows are to be deleted is unknown until run time.

Which method do you implement while creating such a procedure?

A.	Use SQL command delete in the procedure to delete the rows.
B.	Use DBMS_SQL packaged routines in the procedure to delete the rows.
C.	Use DBMS_DML packaged routines in the procedure to delete the rows.
D.	Use DBMSDELETE packaged routines in the procedure to delete the rows.
E.	You cannot have a delete statement without providing a table name before compile time.

Answer:   B

Question 6.
Under which situation do you create a server side procedure?

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

Answer:   D

Question 7.
Examine this procedure 

CREATE OR REPLACE PROCEDURE ADD_PLAYER 
(V_ID IN NUMBER, V_LAST_NAME VARCHER2) 
IS 
BEGIN 
INSERT INTO PLAYER(ID,LAST_NAME). 
VALUES(V_ID,V_LAST_NAME); 
COMMIT; 
END; 

This procedure must invoke the UPD-STAT procedure and pass a parameter. 
Which statement will successfully invoke this procedure?

A. EXECUTE UPD_BAT_STAT(V_ID);
B. UPD_BAT_STAT(V_ID);
C. RUN UPD_BAT_STAT(V_ID);
D. START UPD_BAT_STAT(V_ID);

Answer:   B

Question 8.
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;
SELECTS HITS/AT_BATS
INTO V_AVG
FROM PLAYER_BAT_STAT
WHERE PLAYER_ID_V_ID;
RETURN(V_AVG);
END;

This function must be moved to a package. Which additional statement must be added to the function to allow you to continue using the function in the group by the clause of a select statement?

A.	PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, WNDS, WNPS);
B.	PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, WNPS);
C.	PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, RNPS, WNPS);
D.	PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, ALLOW_GROUP_BY);

Answer:   A

Question 9.
Examine this procedure:

CREATE OR REPLACE PROCEDURE find_cpt
	(v_movie_id {argument mode} NUMBER, v_cost_per_ticket {argument mode}
NUMBER)
IS
BEGIN
	IF v_cost_per_ticket > 8.50 THEN
		SELECT cost_per_ticket
		INTO v_cost_per_ticket
		FROM gross _receipt
		WHERE movie_id = v_movie_id;
	END IF;
END;

Which argument mode should be used for V_MOVIE_ID?

A.	IN
B.	OUT
C.	IN OUT
D.	IN RETURN

Answer:   A

Explanation:
The value of V_MOVIE_ID is used in the WHERE clause to determine which row to return. Since it is only being read and not modified, it should be declared as an IN argument.

Question 10.
Which statement about procedure is true?

A.	They promote reusability and maintainability.
B.	They add functionality to SQL DML statements.
C.	They perform actions and always return a value.
D.	They add functionality to SQL SELECT statements.

Answer:   A

Explanation:
Procedure usually contains code that is executed from more than one application. Storing code in one location makes it ideally suitable for reusability and maintainability.

Procedures cannot be used in SQL statements and do not have to return a value.

Question 11.
The MODIFY_PAYROLL procedure contains many SQL statements and will be executed from multiple client applications. Where should this procedure be stored?

A.	server only
B.	system global area
C.	client applications only 
D.	server and client applications

Answer:  A

Explanation: 
A procedure that contains multiple SQL statements should be stored on the server to dramatically reduce the amount of network traffic when executed from a client machine.

If the procedure is stored in an Oracle Developer application, each SQL statement must be sent separately to the server to be processed. If the procedure is stored on the server, the application simply execute it with one call.

Question 12.
When invoking a procedure you can specify the arguments using the positional method by listing the values in the order of the argument list. Which method would you use to list values in an arbitrary order?

A.	FIFO
B.	List
C.	Type
D.	Named

Answer:  D

Explanation: 
You can specify argument values using the positional or named method. The named method requires the use of the “=>” operator to specify a value for each argument and allows for an arbitrary assignment of values.

The named method:
EXECUTE find_seats_sold (v_theater_id => 500, v_movie_id => 34);

The positional method:
EXECUTE find_seats_sold (500, 34);

The value of 500 is assigned to the first argument listed in the procedure header and 34 is assigned to the second argument.

Question 13.
The UPDATE_EMPLOYEE procedure contains an algorithm that calculates an employee’s commission multiple times throughout the program. If a change is made to the algorithm, the change must be made multiple times. How can this procedure be modified to simplify the code and reduce duplicated code?

A.	Add an algorithm exception handler.
B.	Create a library containing the algorithm.
C.	Add a local subprogram containing the algorithm.
D.	Create multiple anonymous blocks containing the alogrithm.

Answer:  C

Explanation:
Subprograms allow you to create just one occurrence of a piece of code that must be executed in different locations of a procedure.

Use local subprograms when the code is only executed within the procedure. If the code will be executed from outside the procedure, then the subprogram should be written as a packaged or stand-alone procedure instead.

Example: (calc_comm is the subprogram)

CREATE OR REPLACE PROCEDURE update_employee
	(v_emp_id IN NUMBER)
IS
	v_comm NUMBER;

	PROCEDURE calc_comm
	IS
		v_total NUMBER;
	BEGINS
		SELECT SUM(ord.total)
		INTO v_total
		FROM ord,customer
		WHERE ord.custid = customer.custid
		AND customer.repid = v_emp_id;
		V_comm := v_total * .20;
	END calc_comm;

BEGIN
	…
	calc_comm;
	…
	calc_comm;
	…
	calc_comm;
END;

Question 14.
You have just successfully dropped the CALC_COMM procedure and deleted the script file containing the source code. Which command can you execute to recover this procedure?

A.	ROLLBACK;
B.	ROLLBACK TO PROCEDURE calc_comm;
C.	ALTER PROCEDURE calc_comm COMPILE;
D.	Only the database administrator can recover this procedure using backups.

Answer:   D

Explanation:
The DROP PROCEDURE command is a DDL command and is therefore, auto-committing. A committed Transaction cannot be rolled back. Without a script file containing the source code, only the DBA can recover this procedure.

Question 15.
Examine this procedure:

CREATE OR REPLACE PROCEDURE find_seats_sold
	(v_movie_id IN NUMBER)
IS
	v_seats_sold    gross_receipt.seats_sold%TYPE;
BEGIN
	SELECT seats_sold
	INTO v_seats_sold
	FROM gross_receipt
	WHERE movie_id = v_movie_id;
END;

The value of V_SEATS_SOLD must be returned to the calling environment. Which change should you make to the code.

A.	Declare V_SEATS_SOLD as an OUT argument.
B.	Declare V_SEATS_SOLD as a RETURN argument.
C.	Add RETURN V_SEATS_SOLD immediately before the IS keyword.
D.	Add RETURN V_SEATS_SOLD immediately before the END keyword.

Answer:  A

Explanation:
Procedure can return values to the calling environment using OUT arguments. Arguments are declared after the IS keyword and before the BEGIN keyword.
The procedure after adding the OUT argument:
CREATE OR REPLACE PROCEDURE find_seats_sold
	(v_movie_id IN NUMBER, v_seats_sold OUT NUMBER)
IS
	v_seats_sold gross_receipt.seats_sold%TYPE;
BEGIN
	SELECT seats_sold
	INTO v_seats_sold
	FROM gross_receipt
	WHERE MOVIE_ID = v_movie_id;
END;

	Web www.certsbraindumps.com

Advertise

Submit Braindumps

Forum

Tell A Friend

Contact Us

Privacy Policy                   Disclaimer                    Feedback                    Term & Conditions

ITCertKeys.com

Copyright © 2004 CertsBraindumps.com Inc. All rights reserved.