Re: Stored Procedure

"rajii" <raji_v_at_nospam.mailcity.com> wrote in message news:e81a9e08c1cbb897edb7183a8a1231fc_at_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?
>
> 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.
>
>
>
>
>
>

raji,

if you are going to ask others for answers, then you should put their names on your resume next to your OCA credentials ;-)

you should research and test out these questions yourself -- that way you will really learn the answers, and your OCA will mean something

++ mcs Received on Fri Feb 18 2005 - 05:55:33 CST