1 Answered Test Questions:
1. Which part of a database trigger determines the number of times a trigger body executes?
Answers:
• Trigger type
• Trigger body
• Trigger event
• Trigger timing
44 NOT Answered Yet Test Questions:
(hold on, will be updated soon)
2. Which cursor attribute returns the number of rows fetched from the active set in the case of an explicit cursor?
Answers:
• %ISOPEN
• %ROWCOUNT
• %FOUND
• %NOTFOUND
3. SQL%ISOPEN always evaluates to false in case of:
Answers:
• Explicit Cursor
• Implicit Cursor
• Parameterized Cursor
• Cursor with Arguments
4. You need to drop a table from within a stored procedure. How would you do this?
Answers:
• You cannot drop a table from a stored procedure
• By using the DROP command
• By using the DBMS_DDL packaged routines
• By using the DBMS_DROP packaged routines
5. An internal LOB is _____.
Answers:
• A table
• Stored in the database
• A file stored outside the database
• None of the above
6. Examine the Block given below:
declare
v_no number:=2;
v_msg varchar2(20):='Goodbye';
begin
case
When v_no=1 then
��������dbms_output.put_line('One!');
��������dbms_output.put_line('Another One!');
When v_no>1 then
��������dbms_output.put_line('>1!');
��������dbms_output.put_line('Still>1!');
when v_msg='Goodbye' then
��������dbms_output.put_line('Goodbye');
��������dbms_output.put_line('Adios');
else
��������dbms_output.put_line('No Match');
��������end case;
end;
/
What is the output of this block?
Answers:
• >1! Still>1!
• Goodbye Adios
• 1!
• Adios
7. Which of the following Triggers is fired each time a row in the table is affected by the triggering statement?
Answers:
• Statement Trigger
• Application Trigger
• Row
• Database Trigger
8. In Explicit lock, two database operations wait for each other to release a lock.
Answers:
• True
• False
9. Examine the following code:
CREATE OR REPLACE PROCEDURE
add_dept ( p_name dept.dname%TYPE DEFAULT 'unknown',
p_loc dept.locE DEFAULT 1700)
IS
BEGIN
INSERT INTO dept VALUES (dept_seq.NEXTVAL,p_name, p_loc);
END add_dept;
/
You created the add_dept procedure above. Now you want to invoke the procedure in SQL *Plus. Which of the following are the valid invocations?
Answers:
• EXECUTE add_dept(p_loc=>2500)
• EXECUTE add_dept( 'Education', 2500)
• EXECUTE add_dept( .2500 ,p_loc =>2500)
• EXECUTE add_dept(p_name=>'Education', 2500)
10. Which of the following commands is used to disable all the triggers on the EMPLOYEES table?
Answers:
• ALTER TRI GGERS ON TABLE employees DI SABLE;
• ALTER employees DISABLE ALL TRI GGERS;
• ALTER TABLE employees DISABLE ALL TRIGGERS;
• None of the above
11. Which of the following exceptions is raised when the data type or data size is invalid?
Answers:
• VALUE_ERROR
• NO_DATA_FOUND
• STORAGE_ERROR
• PROGRAM_ERROR
12. Which two programming constructs can be grouped within a package?
Answers:
• Cursor
• Constant
• Trigger
• Sequence
• View
13. Examine the code given below:
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;
/
What type of trigger is this?
Answers:
• DML trigger
• INSTEAD OF trigger
• Application trigger
• This is an invalid trigger
14. Which of the following types of argument passes a value from a procedure to the calling environment?
Answers:
• VARCHAR2
• BOOLEAN
• OUT
• IN
15. The oracle server implicitly opens a cursor to process:
Answers:
• Sql select statement
• PL/SQL Select statement
• DML Statements
• DDL Statements
16. Which table should be queried to determine when a procedure was last compiled?
Answers:
• USER_PROCEDURES
• USER_PROCS
• USER_OBJECTS
• USER_PLSQL_UNITS
17. Examine the following function:
CREATE OR REPLACE FUNCTION PLAYER_AVG
(V_ID in PLAYER.PLAYER_ID%TYPE)
RETURN NUMBER
IS
V_AVG NUMBER;
BEGIN
SELECT HITS INTO V_AVG
FROM PLAYER
WHERE PLAYER_ID = V_ID;
RETURN (V_AVG);
END;
Which of the following statements will successfully invoke this function in SQL *Plus?
Answers:
• SELECT PLAYER_AVG(PLAYER_ID) FROM PLAYER;
• EXECUTE PLAYER_AVG(31);
• PLAYER_AVG('RUTH');
• PLAYER_AVG(31);
18. A function CALTAX is given below:
CREATE OR REPLACE FUNCTION caltax
(sal NUMBER)
RETURN NUMBER IS
BEGIN
RETURN (sal * 0.05);
END CALTAX;
Which of the following statements is correct in case you want to run this function from the SQL *Plus prompt?
Answers:
• You need to execute the command CALTAX(1000);
• You need to execute the command EXECUTE FUNCTION caltax;
• You need to create a SQL *Plus environment variable X and issue the command :X := CALTAX(1000);
• You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALTAX(1000);
19. The OLD and NEW qualifiers can be used in Row level system trigger.
Answers:
• True
• False
20. Which of the following program declarations are correct for a stored program unit?
Answers:
• CREATE OR REPLACE FUNCTION tax(p_id NUMBER) RETURN NUMBER
• CREATE OR REPLACE PROCEDURE tax(p_id NUMBER) RETURN NUMBER
• CREATE OR REPLACE PROCEDURE tax(p_id NUMBER, p_amount OUT NUMBER)
• CREATE OR REPLACE FUNCTION tax(p_id NUMBER) RETURN NUMBER(10,2)
21. You need to create a trigger on the EMP table, which monitors every row that is changed and places this information in the AUDIT_TABLE. What type of trigger would you create?
Answers:
• FOR EACH ROW trigger on the EMP table
• Statement-level trigger on the EMP table
• FOR EACH ROW trigger on the AUDIT_TABLE table
• Statement-level trigger on the AUDIT_TABLE table
22. Examine the code given below:
CREATE OR REPLACE FUNCTION gen_email (first_name VARCHAR2, last_name VARCHAR2,
id NUMBER)
RETURN VARCHAR2 IS
email_name VARCHAR2(19);
BEGIN
email_name := SUBSTR(first_name, 1, 1) ||
SUBSTR(last_name, 1, 7) ||.@Oracle.com .;
UPDATE employees SET email = email_name
WHERE employee_id = id;
RETURN email_name;
END;
Which of the following statements removes the function?
Answers:
• DROP gen_email;
• REMOVE gen_email;
• DELETE gen_email;
• DROP FUNCTION gen_email;
23. Which procedure is called after a row has been fetched to transfer the value from the select list of the cursor to a local variable?
Answers:
• Row_value
• Column_value
• Raise_application
• Exception_init
24. The Technique employed by the Oracle engine to protect table data when several people are accessing is called:
Answers:
• Concurrency Control
• Program Control
• PL/SQL Control
• Locking
25. Examine the code given below:
CREATE OR REPLACE TRIGGER update_emp
AFTER UPDATE ON emp
BEGIN
INSERT INTO audit_table (who, dated) VALUES (USER, SYSDATE);
END;
/
You issue an UPDATE command in the EMP table, which results in changing 10 rows.
How many rows are inserted in the AUDIT_TABLE ?
Answers:
• 1
• 10
• None
• 2
26. CREATE OR REPLACE PACKAGE manage_emp IS
tax_rate CONSTANT NUMBER(5,2) := .28;
v_id NUMBER;
PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER);
PROCEDURE delete_emp;
PROCEDURE update_emp;
FUNCTION cal_tax (p_sal NUMBER) RETURN NUMBER;
END manage_emp;
/
CREATE OR REPLACE PACKAGE BODY manage_emp IS
PROCEDURE update_sal (p_raise_amt NUMBER) IS
BEGIN
UPDATE emp SET sal = (sal * p_raise_emt) + sal
WHERE empno = v_id;
END;
PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER) IS
BEGIN
INSERT INTO emp(empno, deptno, sal) VALUES
(v_id, p_depntno, p_sal);
END insert_emp;
PROCEDURE delete_emp IS
BEGIN
DELETE FROM emp WHERE empno = v_id;
END delete_emp;
PROCEDURE update_emp IS
v_sal NUMBER(10,2);
v_raise NUMBER(10, 2);
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno = v_id;
IF v_sal < 500 THEN v_raise := .05;
ELSIP v_sal < 1000 THEN v_raise := .07;
ELSE v_raise := .04;
END IF;
update_sal(v_raise);
END update_emp;
FUNCTION cal_tax (p_sal NUMBER)RETURN NUMBER IS
BEGIN
RETURN p_sal * tax_rate;
END cal_tax;
END manage_emp;
/
What is the name of the private procedure in this package?
Answers:
• CAL_TAX
• INSERT_EMP
• UPDATE_SAL
• DELETE_EMP
• UPDATE_EMP
27. Which cursor allows passing values dynamically to a cursor?
Answers:
• Implicit Cursor
• User Defined Cursor
• Parameterized Cursor
• Explicit Cursor
28. All the packages can be recompiled by using an Oracle utility called:
Answers:
• Dbms_Output
• Dbms_Lob
• Dbms_utility
• Dbms_Error
29. Which of the following commands is used to disable all the triggers on an EMP table?
Answers:
• ALTER TRIGGERS ON TABLE emp DISABLE;
• ALTER e mp l o y e e s DI SABLE ALL TRI GGERS;
• ALTER TABLE emp DISABLE ALL TRIGGERS;
• None of the above
30. Which of the following statements are correct with regard to packages?
Answers:
• The package specification is optional, but the package body is required
• The package specification is required, but the package body is optional
• The specification and body of the package are stored separately in the database
• None of the above
31. Database triggers are designed:
Answers:
• To duplicate the functionality of other triggers
• To guarantee that the related actions are performed when a specific operation is performed
• Both a and b
• None of the above
32. Examine the code given below:
CREATE OR REPLACE PACKAGE comm_package IS
g_comm NUMBER := 10;
PROCEDURE reset_comm(g_comm IN NUMBER);
END comm_package;
User Jones executes the following code at 9:01am:
EXECUTE comm_package.g_comm := 15
User Smith executes the following code at 9:05am:
EXECUTE comm_paclage.g_comm := 20
Which of the following statements is correct?
Answers:
• g_comm has a value of 15 at 9:06am for Smith
• g_comm has a value of 15 at 9:06am for Jones
• g_comm has a value of 20 at 9:06am for both Jones and Smith
• g_comm has a value of 15 at 9:03 am for both Jones and Smith
33. Examine the package given below:
CREATE OR REPLACE PACKAGE discounts IS
g_id NUMBER := 7829;
discount_rate NUMBER := 0.00;
PROCEDURE display_price (p_price NUMBER);
END discounts;
/
CREATE OR REPLACE PACKAGE BODY discounts IS
PROCEDURE display_price (p_price NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE( .Discounted .|| TO_CHAR(p_price*NVL(discount_rate, 1)));
END display_price;
BEGIN
discount_rate :=0.10;
END discounts;
/
Which of the following statements is correct?
Answers:
• The value of DISCOUNT_RATE always remains 0.00 in a session
• The value of DISCOUNT_RATE is set to 0.10 each time the package is invoked in a session
• The value of DISCOUNT_RATE is set to 1.00 each time the procedure DISPLAY_PRICE is invoked
• The value of DISCOUNT_RATE is set to 0.10 when the package is invoked for the first time in a session
34. The process of Breaking Sql sentence into words and then checking them for syntax and object privileges is called:
Answers:
• Binding
• Parsing
• Sequence
• Control Flow
35. How can you migrate from a LONG to a LOB data type for a column?
Answers:
• By using the DBMS_MANAGE_LOB.MIGRATE procedure
• By using the UTL_MANAGE_LOB.MIGRATE procedure
• By using the DBMS_LOB.MIGRATE procedure
• By using DBMS_REDEFINITION.START_REDEF_TABLE procedure
36. Which of the following statements is correct?
Answers:
• You can use the DBMS_LOB.WRITE procedure to write data to a BFILE
• You can use the DBMS_LOB.BFILENAME function to locate an external BFILE
• You can use the DBMS_LOB.FILEEXISTS function to find the location of a BFILE
• You can use the DBMS_LOB.FILECLOSE procedure to close the file being accessed
37. Which lock is used in Pl/Sql if the where clause evaluates to a set of data?
Answers:
• Row Level Lock
• Page Level lock
• Column Level lock
• Exclusive Lock
38. Which of the following statements is correct with regard to stored procedures?
Answers:
• A stored procedure uses the DECLARE keyword in the procedure specification to declare formal parameters
• A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification
• A stored procedure must have at least one executable statement in the procedure body
• A stored procedure uses the DECLARE keyword in the procedure body to declare formal parameters
39. __________ is present in Oralce9i only.
Answers:
• Timestamp
• Date
• Rowid
40. When the procedure or function is invoked, Oracle engine loads the compiled procedure or function in the memory area called:
Answers:
• PGA
• SGA
• Redo Log Buffer
• Data base buffer cache
41. Consider the following code:
declare
v_id number(3):=50;
V_message(30):='Product 10012';
begin
declare
v_id number(3):=40;
V_message(30):='welcome to world';
v_locn:='Europe;
begin
v_id:=v_id+20;
end;
end;
Determine the value of V_ID variable in the outer block.
Answers:
• 50
• 80
• 40
• 60
42. A CALL statement inside the trigger body enables you to call:
Answers:
• A package
• A stored function
• A stored procedure
• Another database trigger
43. Which of the following is not a legal declaration?
Answers:
• declare v_id number(4);
• declare v_x,V_y varchar2(10);
• declare V_birthdate date not null;
• declare V_Sex boolean:=1;
44. Examine the code given below:
Declare
cursor emps is
select Empno,ename,sal,deptno,job from emp;
begin
for rec in emps loop
open emps;
dbms_output.put_line(rec.empno||rec.ename||rec.sal||rec.deptno||rec.job);
end loop;
end;
/
What is wrong in above declaration?
Answers:
• Record Rec is not defined
• Fetch statement is not specified
• Close cursor in not specified
• There is no need to open cursor in the for loop
45. Examine the following code:
CREATE OR REPLACE PROCEDURE
add_dept ( p_name dept.dname%TYPE DEFAULT 'unknown',
p_loc dept.locE%TYPE DEFAULT 1700)
IS
BEGIN
INSERT INTO dept VALUES (dept_seq.NEXTVAL,p_name, p_loc);
END add_dept;
/
You created the add_dept procedure above. Now you want to invoke the procedure in SQL *Plus. Which of the following are the valid invocations?
Answers:
• EXECUTE add_dept(p_loc=>2500)
• EXECUTE add_dept( 'Education', 2500)
• EXECUTE add_dept( .2500 ,p_loc =>2500)
• EXECUTE add_dept(p_name=>'Education', 2500)
No comments:
Post a Comment