Stored Procedure

From: rajii <raji_v_at_nospam.mailcity.com>
Date: Thu, 17 Feb 2005 23:54:06 -0500
Message-ID: <e81a9e08c1cbb897edb7183a8a1231fc@localhost.talkaboutdatabases.com>

Hi,
I am going to take-up OCA exam of pl/sql this month. I have some doubts in couple of question h so I will send the question to below. I would really appreciate anyone can tell the answer which so similar.

Thanks & Regards,
Raji

1.You need to drop a table from within a stored procedure. How do you implement this?

You cannot drop a table from a stored procedure.
Use the drop command in the procedure to drop the table.
Use the dbms_ddl packaged routines in the procedure to drop the table.
Use the dbms_sql packaged routines in the procedure to drop the table.
Use the dbms_drop packaged routines in the procedure to drop the table.

ANSWER: 2. 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?

SYS privileges
Your privileges
Public privileges
User A’s privileges
User A cannot execute your procedure that has dynamic SQL. ANSWER: I am having doubt on whether B or D.
CREATE OR REPLACE TRIGGER secure_emp BEFORE LOGON ON employees BEGIN IF (TO_CHAR(SYSDATE, ‘DY’) IN (‘SAT’, ‘SUN’)) OR (TO_CHAR(SYSDATE, ‘HH24:MI’) NOT BETWEEN ’08:00’ AND ’18:00’) THEN RAISE_APPLICATION_ERROR (-20500, ‘You may insert into the EMPLOYEES table only during business hours.’); END IF; END;
/
4.What type of trigger is it?
DML trigger
INSTEAD OF trigger
Application trigger
System event trigger
This is an invalid trigger.

ANSWER: 5.You create a DML trigger. For the timing information, which is valid with a DML trigger?

A. DURING
B. INSTEAD
C. ON SHUTDOWN
D. BEFORE
E. ON STATEMENT EXECUTION

ANSWER:
6.All users are 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 2 actions must you take?(choose 2)

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.

ANSWER: 7.Create package pack_cur is
Cursor c1 is select * from emp;
Procedure proc1_3rows;
Procedure proc4_6rows;
End;
/

create package body pack_cur is
v_rec emp%rowtype;
procedure proc1_3rows is
begin

	open c1;
	loop
 	fetch c1 into v_rec;

dbms_output.put_line(‘Row: ‘||c1%rowcount); exit when c1%rowcount>3;
end loop;

end;
end proc1_3rows;
procedure proc4_6rows is

	begin
	 loop
			etch  c1 into v_rec;
		dbms_output.put_line(‘ Row : ‘||c1%rowcount);
			exit when c1%rowcount>6;
	end loop;
	close c1;
	end;

end proc4_6rows;
end pack_cur;
/

the table emp as 1000 recorda and set serveroutput in on. If u execute pack_cur.proc1_3 what will be output: a)error
b)row:

row:
row:
c)row:1

row:2

  row:3
d)row:3
  row:3

row:3
e)row:4

row:4
row:4

ANSWER: 8. Local procedure A calls remote procedure B. Procedure B was compiled at 8 A.M. Procedure A was modified and recompiled at 9 A.M. Remote procedure B was later modified and recompiled at 11 A.M. The dependency mode is set to TI MESTAMP. What happens when procedure A is invoked at 1 P.M?

A. There is no affect on procedure A and it runs successfully. 
B. Procedure B is invalidated and recompiles when invoked. 
C. Procedure A is invalidated and recompiles for the first time it is

invoked.
D. Procedure A is invalidated and recompiles for the second time it is invoked.
Answer:

9. Examine this package: CREATE OR REPLACE PACKAGE BB_PACK IS V_MAX_TEAM_SALARY NUMBER(12,2); PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBER); END BB_PACK; / CREATE OR REPLACE PACKAGE BODY BB_PACK IS V_PLAYER_AVG NUMBER(4,3); PROCEDURE UPD_PLAYER_STAT V_ID IN NUMBER, V_AB IN NUMBER DEFAULT 4, V_HITS IN NUMBER) IS BEGIN UPDATE PLAYER_BAT_STAT SET AT_BATS = AT_BATS + V_AB, HITS = HITS + V_HITS WHERE PLAYER_ID = V_ID; COMMIT; VALIDATE_PLAYER_STAT(V_ID); END UPD_PLAYER_STAT; PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBER) IS BEGIN INSERT INTO PLAYER(ID,LAST_NAME,SALARY) VALUES (V_ID, V_LAST_NAME, V_SALARY); UPD_PLAYER_STAT(V_ID,0,0); END ADD_PLAYER; END BB_PACK / Which statement will successfully assign .333 to the V_PLAYER_AVG variable from a procedure outside the package?

A. V_PLAYER_AVG := .333; 
B. BB_PACK.UPD_PLAYER_STAT.V_PLAYER_AVG := .333; 
C. BB_PACK.V_PLAYER_AVG := .333; 
D. This variable cannot be assigned a value from outside of the package.

Answer:

10.Examine this package: CREATE OR REPLACE PACKAGE BB:PACK IS V_MAX_TEAM:SALAR NUMBER(12,2); PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBER); END BB_PACK; / CREATE OR REPLACE PACKAGE BODY BB_PACK IS PROCEDURE UPD_PLAYER_STAT (V_ID IN NUMBER, V_AB IN NUMBER DEFAULT 4, V_HITS IN NUMBER) IS BEGIN UPDATE PLAYER_BAT_STAT SET AT_BATS = AT_BATS + V_AB, HITS = HITS + V_HITS WHERE PLAYER_ID = V_ID; COMMIT; END UPD_PLAYER_STAT; PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBER) IS BEGIN INSERT INTO PLAYER(ID,LAST_NAME,SALARY) VALUES (V_ID, V_LAST_NAME, V_SALARY); UPD_PLAYER_STAT(V_ID,0,0); END ADD_PLAYER; END BB_PACK; You make a change to the body of the BB_PACK package. The BB_PACK body is recompiled. What happens if the stand alone procedure VALIDATE_PLAYER_STAT references this package?

A. VALIDATE_PLAYER_STAT cannot recompile and must be recreated. 
B. VALIDATE_PLAYER_STAT is not invalidated. 
C. VALDIATE_PLAYER_STAT is invalidated. 
D. VALIDATE_PLAYER_STAT and BB_PACK are invalidated.

Answer: c or b

11.What happens during the execute phase with dynamic SQL for INSERT, UPDATE, and DELETE operations?

A. The rows are selected and ordered. 
B. The validity of the SQL statement is established. 
C. An area of memory is established to process the SQL statement. 
D. The SQL statement is run and the number of rows processed is returned.

E. The area of memory established to process the SQL statement is released.
Answer: b or d. Received on Thu Feb 17 2005 - 22:54:06 CST