Do you know that? 180+ countries that oDesk has freelancers from next

Contact Email: teammmrem@yahoo.com

<< All Upwork (oDesk) and Elance Tests << Upwork (oDesk) test answers << Databases category

Test answers for Oracle PL/SQL 9i Test 2016

(1 / 44, CL) Last updated: May 14
Upwork (oDesk) • Databases
Questions and answers are regularly updated. Answered and not answered tests questions.

This helps getting job: Hundreds of (cover letter examples , interview questions , profile samples ) • Earn on Upwork (oDesk)
Job assistance: jobs popularityfreelance rates

Popular test answers: HTML, .Net, CSS, English, SEO, Photoshop, iOS, Java, Android, Ruby, Python, JavaScript

See all 6 tests answers updated

Find Upwork (oDesk) and Elance test answers on this website:



Collapse | Expand

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)