Stored Procedure

Mark C. Stock · *nix forums Guru Joined: 05 May 2005 Posts: 730

"rajii" <raji_v@nospam.mailcity.com> wrote in message
news:e81a9e08c1cbb897edb7183a8a1231fc@localhost.talkaboutdatabases.com...

David Aldridge · *nix forums beginner Joined: 03 Feb 2005 Posts: 46

So what do you think are the answers?

rajii · *nix forums beginner Joined: 18 Feb 2005 Posts: 1

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?

A. You cannot drop a table from a stored procedure.
B. Use the drop command in the procedure to drop the table.
C. Use the dbms_ddl packaged routines in the procedure to drop the
table.
D. Use the dbms_sql packaged routines in the procedure to drop the
table.
E. 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?

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: I am having doubt on whether B or D.

3. 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?
A. DML trigger
B. INSTEAD OF trigger
C. Application trigger
D. System event trigger
E. 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.

Google