Upwork Test Answers: Get all the correct answers of most recent and possible Upwork Tests A to Z (Updated on Jan, 2016)
Cover Letter Templates: These cover letter samples are not only for Upwork job, but also you will have some idea about your real life job
 
Freelance Profile Overviews: Different Profile samples and overviews of experts, advanced and intermediate level freelancers
For Newbie of Upwork: Upwork Help - How to apply for a job in Upwork with 10 most important articles about Upwork

A to Z View - All Upwork Test Answers

Oracle PL/Sql 10g Test Answers

Here you will find most recent Upwork's test questions and correct answers of Oracle PL/Sql 10g Test for Upwork Database category test.

This test answers will help you to get Upwork Database related job. Search your desired answers of the test questions by pressing Ctrl + F button.
--------------------------------------------------------------------------------------------------------------------------

1. Which two among the following programming constructs can be grouped within a package?
 Answers: • Constant or • Sequence

 2. Which two statements, among the following, describe the state of a package variable after executing the package in which it is declared?
 Answers: • It persists across transactions within a session or • It persists from session to session for the same user

 3. Which of the following is not a legal declaration?
 Answers: • declare x,y varchar2(10); or • declare Sex boolean:=1;

 4. The oracle server implicitly opens a cursor to process:
 Answers: • A Sql select statement or • DML Statements

 5. Which two statements out of the following regarding packages are true?
 Answers: • The package specification is required, but the package body is optional or • The specification and body of the package are stored separately in the database

 6. Examine the following trigger:

 CREATE OR REPLACE TRIGGER Emp_count
 AFTER DELETE ON Employee
 FOR EACH ROW
 DECLARE
 n INTEGER;
 BEGIN
 SELECT COUNT(*) INTO n FROM employee;
 DMBS_OUTPUT.PUT_LINE( 'There are now' || n || 'employees');
 END;

 This trigger results in an error after this SQL statement is entered: DELETE FROM Employee WHERE Empno = 7499;
 How should the error be corrected?
 Answers: • Change the trigger to a statement-level trigger by removing FOR EACH ROW

 7. Which Section deals with handling of errors that arise during execution of the data manipulation statements, which makeup the PL/SQL Block?
 Answers: • Exception

 8. Which of the following statements is true?
 Answers: • Stored functions can increase the efficiency of queries by performing functions in the query rather than in the application

 9. Examine the following code:
 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 FUNCTION gen_email;

 10. A table has to be dropped from within a stored procedure. How can this be implemented?
 Answers: • Use the DBMS_DDL packaged routines in the procedure to drop the table

 11. In Pl/Sql, if the where clause evaluates to a set of data, which lock is used?
 Answers: • Page Level lock

 12. If user defined error condition exists,Which of the following statements made a call to that exception?
 Answers: • Raise

 13. Which of the following are identified by the "INSTEAD OF" clause in a trigger?
 Answers: • The view associated with the trigger

 14. What type of trigger is created on the EMP table that monitors every row that is changed, and places this information into the AUDIT_TABLE?
 Answers: • FOR EACH ROW trigger on the EMP table

 15. 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: • UPDATE_SAL

 16. Which procedure is called after a row has been fetched to transfer the value, from the select list of the cursor into a local variable?
 Answers: • Row_value

 17. An internal LOB is _____.
 Answers: • Stored in the database

 18. What is the maximum number of handlers processed before the PL/SQL block is exited, when an exception occurs?
 Answers: • Only one

 19. The technique employed by the Oracle engine to protect table data, when several people are accessing it is called:
 Answers: • Concurrency Control

 20. Which table should be queried to determine when the procedure was last compiled?
 Answers: • USER_OBJECTS

 21. Which cursor dynamically allows passing values to a cursor while opening another cursor?
 Answers: • Implicit Cursor

 22. When the procedure or function is invoked, the Oracle engine loads the compiled procedure or function in the memory area called:
 Answers: • PGA

 23. Which precomplied word is called, which when encountered, immediately binds the numbered exception handler to a name?
 Answers: • Exception_init

 24. What happens during the execute phase with dynamic SQL for INSERT, UPDATE, and DELETE operations?
 Answers: • The area of memory established to process the SQL statement is released

 25. Examine the following package specification:
 CREATE OR REPLACE PACKAGE combine_all
 IS
 v_string VARCHAR2(100);
 PROCEDURE combine (p_num_val NUMBER);
 PROCEDURE combine (p_date_val DATE);
 PROCEDURE combine (p_char_val VARCHAR2, p_num_val NUMBER);
 END combine_all;
 /
 Which overloaded COMBINE procedure declaration can be added to this package specification?
 Answers: • PROCEDURE combine;

 26. Which part of a database trigger determines the number of times the trigger body executes?
 Answers: • Trigger type

 27. Which table should be queried to check the status of a function?
 Answers: • USER_OBJECTS

 28. Which of the following statements is true regarding stored procedures?
 Answers: • A stored procedure must have at least one executable statement in the procedure body

 29. Examine the following code:
 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 it?
 Answers: • This is an invalid trigger

 30. Which code is stored in the database when a procedure or function is created in SQL*PLUS?
 Answers: • Only P-CODE

 31. Evaluate the following PL/SQL block:
 DECLARE
 v_low NUMBER:=2;
 v_upp NUMBER:=100;
 v_count NUMBER:=1;
 BEGIN
 FOR i IN v_low..v_low LOOP
 INSERT INTO test(results)
 VALUES (v_count)
 v_count:=v_count+1;
 END LOOP;
 END;
 How many times will the executable statements inside the FOR LOOP execute?
 Answers: • 1

 32. What can be done with the DBMS_LOB package?
 Answers: • Use the DBMS_LOB.FILEEXISTS function to find the location of a BFILE

 33. Examine the following code:
 CREATE OR REPLACE TRIGGER UPD_SALARY
 FOR EACH ROW
 BEGIN
 UPDATE TEAM
 SET SALARY=SALARY+:NEW.SALARY
 WHERE ID=:NEW.TEAM_ID
 END;
 Which statement must be added to make this trigger executable after updating the SALARY column of the PLAYER table?
 Answers: • AFTER UPDATE ON PLAYER

 34. How can migration be done from a LONG to a LOB data type for a column?
 Answers: • Using ALTER TABLE statement

 35. Examine the following code:

 CREATE OR REPLACE PACKAGE comm_package IS
 g_comm NUMBER := 10;
 PROCEDURE reset_comm(p_comm IN NUMBER);
 END comm_package;

 User MILLER executes the following code at 9:01am:
 EXECUTE comm_package.g_comm := 15

 User Smith executes the following code at 9:05am:
 EXECUTE comm_package.g_comm := 20

 Which of the following statement is true?
 Answers: • g_comm has a value of 15 at 9:06am for Miller

 36. The CHECK_SAL procedure calls the UPD_SAL procedure. Both procedures are INVALID.Which command can be issued to recompile both procedures?
 Answers: • ALTER PROCEDURE CHECK_SAL compile

 37. Examine the following procedure:
 PROCEDURE emp_salary
 (v_bonus BOOLEAN,
 V_raise BOOLEAN,
 V_issue_check in out BOOEAN)
 is
 BEGIN
 v_issue_check:=v_bonus or v_raise;
 END;
 If v_bonus=TRUE and v_raise=NULL,which value is assigned to v_issue_check?
 Answers: • TRUE

 38. Which package construct must be declared and defined within the packages body?
 Answers: • Private Procedure

 39. What happens when rows are found using a FETCH statement?
 Answers: • The current row values are loaded into variables

 40. Evaluate the following PL/SQL block:
 DECLARE
 result BOOLEAN;
 BEGIN
 DELETE FROM EMPloyee
 WHERE dept_id IN (10,40,50);
 result:=SQL%ISOPEN;
 COMMIT:
 END;

 What will be the value of RESULT if three rows are deleted?
 Answers: • FALSE

 41. Which two statements among the following, regarding oracle database 10g PL/SQL support for LOB migration, are true?
 Answers: • Standard package functions accept LOBs as parameters

 42. Which command is used to disable all triggers on the EMPLOYEES table?
 Answers: • ALTER TABLE employees DISABLE ALL TRIGGERS;

 43. Which table and column can be queried to see all procedures and functions that have been marked invalid?
 Answers: • USER_OBJECTS table,STATUS column

 44. SQL%ISOPEN always evaluates to false in case of a/an:
 Answers: • Implicit Cursor

 45. Which datatype does the cursor attribute '%ISOPEN' return?
 Answers: • BOOLEAN

 46. Which of the following is a benefit of using procedures and functions?
 Answers: • Procedures and Function avoid reparsing for multiple users by exploiting shared SQL areas

 47. All packages can be recompiled by using an Oracle utility called:
 Answers: • Dbms_utility

 48. Which type of variable should be used to assign the value TRUE, FALSE?
 Answers: • Scalar

 49. In which type of trigger can the OLD and NEW qualifiers can be used?
 Answers: • Row level DML trigger

 50. Examine the following code:
 CREATE OR REPLACE TRIGGER update_emp
 AFTER UPDATE ON emp
 BEGIN
 INSERT INTO audit_table (who, dated) VALUES (USER, SYSDATE);
 END;
 /
 An UPDATE command is issued in the EMP table that results in changing 10 rows
 How many rows are inserted into the AUDIT_TABLE ?
 Answers: • 1