PLSQL Mid Term Exam Semester 1

1. Which of the following declarations is invalid?
v_count PLS_INTEGER:=0;
college_name VARCHAR2(20):='Harvard';
v_pages CONSTANT NUMBER; (*)
v_start_date DATE := sysdate+1;

2. Which of the following should NOT be used as the name ofa variable?
A table name.
A table column name. (*)
The database name.

3.
1. Null
2. False
3. True
4. 0
Which of the above can be assigned to a Boolean variable?

2 and 3
2, 3 and 4
1, 2 and 3 (*)
1, 2, 3 and 4

4. The following anonymous block of code is run:
BEGIN
INSERT INTO countries (id, name)
VALUES ('XA', 'Xanadu');
INSERT INTO countries (id, name)
VALUES ('NV','Neverland');
COMMIT;
COMMIT;
ROLLBACK;
END;
What happens when the block of code finishes?

You have nothing new; the last ROLLBACK undid the INSERTs.
You have the rows added twice; there are four new rows.
You have the two new rows added. (*)
You get an error; you cannot COMMIT twice in a row.

5. Which of the following best describes a databasetransaction?
All the DML statements in a single PL/SQL block
A related set of SQL DML statements which must be executed eithercompletely or not at all (*)
A single SQL statement that updates multiple rows of a table
A SELECT statement based on a join of two or more database tables

6. Which of the following are valid identifiers? (Choose two.) (Choose all correct answers)
Full Name
students_street_address (*)
v_code (*)
#hours
completion_%

7. Which of the following are valid identifiers? (Choosetwo.) (Choose all correct answers)
yesterday (*)
yesterday's date
number_of_students_in_the_class
v$testresult (*)
#students

8. Which of the following are PL/SQL lexical units? (Choosetwo.) (Choose all correct answers)
Identifiers (*)
Table Columns
Reserved Words (*)
Anonymous Blocks
SQL Workshop

9. What will be displayed when the following code isexecuted?
DECLARE
varA NUMBER := 12;
BEGIN
DECLARE
varB NUMBER := 8;
BEGIN
varA := varA + varB;
END;
DBMS_OUTPUT.PUT_LINE(varB);
END;

8
12
Nothing, the block will fail with an error (*)
20
VarB

10. In the following code, Line A causes an exception. Whatvalue will be displayed when the code is executed?
DECLARE
outer_var VARCHAR2(50) := 'My';
BEGIN
outer_var := outer_var || ' name';
DECLARE
inner_var NUMBER;
BEGIN
inner_var := 'Mehmet'; --Line A
outer_var := outer_var || ' is';
END;
outer_var := outer_var || ' Zeynep';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(outer_var);
END;

My
My name (*)
My name is
My name is Zeynep

11. When nested blocks are used, which blocks can or must be labeled?
The inner block must be labeled, the outer block can be labeled.
Both blocks must be labeled
Nested blocks cannot be labeled
The outer block must be labeled if it is to be referred to in the inner block. (*)

12. What will be displayed when the following code isexecuted?
DECLARE
x VARCHAR2(6) := 'Chang';
BEGIN
DECLARE
x VARCHAR2(12) := 'Susan';
BEGIN
x := x || x;
END;
DBMS_OUTPUT.PUT_LINE(x);
END;

Susan
Chang (*)
ChangChang
SusanChang
The code will fail with an error

13. Examine the following code. At Line A, we want to assigna value of 22 to the outer block's variable v_myvar. What code should wewrite at Line A?
<<outer_block>>
DECLARE
v_myvar NUMBER;
BEGIN
<<inner_block>>
DECLARE
v_myvar NUMBER := 15;
BEGIN
--Line A
END;
END;

outer_block.v_myvar := 22; (*)
v_myvar := 22;
<<outer_block>>.v_myvar := 22;
v_myvar(outer_block) := 22;
We cannot reference the outer block's variable because both variables have the same name

14. Which one of these SQL statements can be directlyincluded in a PL/SQL executable block?
DELETE FROM employeesWHERE department_id=60;(*)
SELECT salary FROM employeesWHERE department_id=60;
CREATE TABLE new_emps (last_name VARCHAR2(10), first_name VARCHAR2(10));
DROP TABLE locations;

15. Which of the following is NOT a good guideline forretrieving data in PL/SQL?
Declare the receiving variables using %TYPE
The WHERE clause is optional in nearly all cases. (*)
Specify the same number of variables in the INTO clause as databasecolumns in the SELECT clause.
THE SELECT statement should fetch exactly one row.

16. A variable is declared as:
DECLARE
v_salary employees.salary%TYPE;
BEGIN
Which of the following is a correct use of the INTO clause?
SELECT salaryINTO v_salaryFROM employeesWHERE employee_id=100;(*)
SELECT v_salaryINTO salaryFROM employeesWHERE employee_id=100;
SELECT salaryFROM employeesINTO v_salary;
SELECT salaryFROM employeesWHERE employee_id=100INTO v_salary;

17. Which rows will be deleted from the EMPLOYEES table when the following code is executed?
DECLARE
salary employees.salary%TYPE := 12000;
BEGIN
DELETE FROM employeesWHERE salary > salary;
END;

All rows whose SALARY column value is greater than 12000.
All rows in the table.
No rows. (*)
All rows whose SALARY column value is equal to 12000.

18. Which one of these SQL statements can be directlyincluded in a PL/SQL executable block?
SELECT last_name FROM employeesWHERE employee_id=100;
DESCRIBE employees;
UPDATE employeesSET last_name='Smith';(*)
DROP TABLE employees;

19. Evaluate the following declaration. Determine whether ornot it is legal.
DECLARE
maxsalary NUMBER(7) = 5000;

Correct.
Not correct. (*)

20. Assignment statements can continue over several lines inPL/SQL. True or False?
True (*)
False

21. When a variable is defined using the CONSTANT keyword, the value ofthe variable cannot change. True or False?
True (*)
False

22. Variables can be used in the following ways in a PL/SQLblock. (Choose two.) (Choose all correct answers)
To store data values. (*)
To rename tables and columns.
To refer to a single data value several times. (*)
To comment code.

23. When a variable is defined using the NOT NULL keywords, the variable must contain a value. True or False?
True (*)
False

24. PL/SQL is an Oracle proprietary, procedural, 4GLprogramming language. True or False?
True
False (*)

25. A program which specifies a list of operations to be performed sequentially to achieve the desired result can be called:

declarative
nondeclarative
procedural (*)
low level

26. Which of the following statements about PL/SQL and SQL is true?
PL/SQL and SQL are both ANSI-compliant.
PL/SQL and SQL can be used with many types of databases, including Oracle.
PL/SQL and SQL are both Oracle proprietary programming languages.
PL/SQL allows basic program logic and control flow to be combined
with SQL statements. (*)

27. Which statements are optional in a PL/SQL block? (Choosetwo.) (Choose all correct answers)
DECLARE (*)
BEGIN
EXCEPTION (*)
END;

28. What is the purpose of using DBMS_OUTPUT.PUT_LINE in aPL/SQL block?
To perform conditional tests
To allow a set of statements to be executed repeatedly
To display results to check if our code is working correctly (*)
To store new rows in the database

29. Which of the following tools can NOT be used to developand test PL/SQL code?
Oracle Jdeveloper
Oracle Application Express
Oracle JSQL (*)
Oracle iSQL*Plus

30. Given below are the parts of a PL/SQL block:
1. END;
2. EXCEPTION
3. DECLARE
4. BEGIN
Arrange the parts in order.

2,1,4,3
3,4,2,1 (*)
3,2,4,1
4,3,2,1

31. Every PL/SQL anonymous block must start with the keyword DECLARE.
True or False?
True
False (*)

32. Which lines of code will correctly display the message"The cat sat on the mat"? (Choose two.)
(Choose all correct answers)
DBMS_OUTPUT.PUT_LINE('The cat sat on the mat'); (*)
DBMS_OUTPUT.PUT_LINE(The cat sat on the mat);
DBMS_OUTPUT.PUT_LINE('The cat' || 'sat on the mat');
DBMS_OUTPUT.PUT_LINE('The cat sat ' || 'on the mat'); (*)

33. Errors are handled in the Exception part of the PL/SQLblock. True or False?
True (*)
False

34. Which component of Oracle Application Express is used toenter and run SQL statements and PL/SQL blocks?
Application Builder
SQL Workshop (*)
Utilities
Object Browser
Correct Correct

35. ______ are meant to store large amounts of data.
Variables
Scalar data types
LOBs (*)

36. What is the data type of the variable V_DEPT_TABLE in the followingdeclaration?
DECLARE
TYPE dept_table_type IS TABLE OF departments%ROWTYPE INDEX BYPLS_INTEGER; v_dept_table dept_table_type; ...

Scalar
Composite (*)
LOB

37. Which of these are PL/SQL data types? (Choose three.) (Choose all correct answers)
Scalar (*)
Identifier
Delimiter
Composite (*)
LOB (*)

38. What is the output when the following program isexecuted?
set serveroutput on
DECLARE
a VARCHAR2(10) := '333';
b VARCHAR2(10) := '444';
c PLS_INTEGER;
d VARCHAR2(10);
BEGIN
c := TO_NUMBER(a) + TO_NUMBER(b);
d := a || b;
DBMS_OUTPUT.PUT_LINE(c);
DBMS_OUTPUT.PUT_LINE(d);
END;

Nothing. The code will result in an error.
c=777 and d=333444 (*=777 and d=333444 (*)
c=777 and d=777
c=333444 and d=777

39. The implicit data type conversion at Point A may notwork correctly. Why not?
DECLARE
v_mydate DATE;
BEGIN
V_MYDATE := '29-Feb-04'; --Point A
END;

There are only 28 days in February
Oracle cannot implicitly convert a character string to a date, even
if the string contains a valid date value
If the database language is not English, 'Feb' has no meaning. (*)
V_MYDATE has been entered in uppercase

40. If today's date is 14th June 2007, which statement willcorrectly convert today's date to the value: June 14, 2007 ?
TO_CHAR(sysdate)
TO_DATE(sysdate)
TO_DATE(sysdate,'Month DD, YYYY')
TO_CHAR(sysdate, 'Month DD, YYYY') (*)

Incorrect Incorrect. Refer to Section 2.
Previous Page 8 of 10 Next Summary

41. PL/SQL can convert a VARCHAR2 value containing alphabeticcharacters to a NUMBER value. True or False?
True
False (*)

42. What is wrong with this assignment statement?
myvar :=
'To be or not to be';
'That is the question';

An assignment statement must be a single line of code
Nothing is wrong, the statement is fine
An assignment statement must have a single semicolon at the end (*)
"myvar" is not a valid name for a variable
Character literals should not be enclosed in quotes

43. Examine the following code. What is the final value ofV_MYVAR ?
DECLARE
v_myvar NUMBER;
BEGIN
v_myvar := 1 + 2 * 3;
v_myvar := v_myvar * 2;
END;

81
49
14 (*)
18

44. Which of the following are valid assignment statements?
(Choose two.) (Choose all correct answers)
v_string = 'Hello';
v_string := Hello;
v_number := 17 + 34; (*)
v_string := 'Hello'; (*)
v_date := 28-DEC-06;

45. Examine the following code. What is the final value of V_MYBOOL ?
DECLARE
v_mynumber NUMBER;
v_mybool BOOLEAN ;
BEGIN
v_mynumber := 6;
v_mybool := (v_mynumber BETWEEN 10 AND 20);
v_mybool := NOT (v_mybool);
END;

True (*)
False

46. Examine the following code:
1 DECLARE
2 x NUMBER;
3 BEGIN
4 x:= '300';
5 END;
After line 4, what is the value of x?

'300'
300 (*)
NULL

47. TO_NUMBER, TO_CHAR, and TO_DATE are all examples of:

Implicit conversion functions
Explicit conversion functions (*)
Character functions
Operators

48. Assume there are 5 employees in Department 10. Whathappens when the following statement is executed?
UPDATE employeesSET salary=salary*1.1;

All employees get a 10% salary increase. (*)
No rows are modified because you did not specify "WHEREdepartment_id=10"
A TOO_MANY_ROWS exception is raised.
An error message is displayed because you must use the INTO clauseto hold the new salary.

49. You declare an implicit cursor in the DECLARE section of
a PL/SQL block. True or False?
True
False (*)

50. A PL/SQL block includes the following statement:
SELECT last_name INTO v_last_name
FROM employeesWHERE employee_id=100;
What is the value of SQL%ISOPEN immediately after the SELECT statement isexecuted?
True
False (*)
Null
Error. That attribute does not apply for implicit cursors.

PLSQL Mid Term Exam Semester 2

1. Examine the following code:
CREATE TRIGGER emp_triggAFTER UPDATE OF salary ON employeesFOR EACH ROW
DECLARE
v_count NUMBER;
BEGIN
--Line A
END;
Which of the following statements is NOT allowed at Line A?

SELECT count(*) INTO v_count FROMdepartments;
UPDATE employees SET job_id = 'IT_PROG' WHEREemployee_id = :OLD.employee_id;
SELECT count(*) INTO v_count FROM employees;(*)
DBMS_OUTPUT.PUT_LINE('A salary was updated');
None. All of the above are allowed.

2. Which of the following statements could cause a DDLtrigger to fire?
DROP TABLE employees;
ALTER TABLE departments ADD (budgetNUMBER(8,2));
CREATE TABLE newemp AS SELECT * FROMemployees;
TRUNCATE TABLE locations;
All of the above (*)

3. The database administrator wants to write a log recordevery time an Oracle Server error occurs in any user's session. The DBAcreates the following trigger:

CREATE TRIGGER log_errs_trigg--Line A
BEGIN
--Line A
BEGIN
INSERT INTO errlog_table VALUES (...);
END;
What should the DBA code at Line A ?

AFTER ERROR ON DATABASE
AFTER SERVER ERROR ON DATABASE
AFTER SERVERERROR ON SCHEMA
AFTER SERVERERROR ON DATABASE (*)
AFTER ORACLE ERROR ON SCHEMA

4. You want to prevent any objects in your schema frombeing altered or dropped. You decide to create the following trigger:
CREATE TRIGGER stop_ad_trigg--Line A
BEGIN
RAISE_APPLICATION_ERROR(-20203,'Invalid Operation');
END;
What should you code at Line A ?

AFTER ALTER OR DROP ON SCHEMA
INSTEAD OF ALTER OR DROP ON SCHEMA
BEFORE ALTER OR DROP ON SCHEMA (*)
BEFORE ALTER, DROP ON SCHEMA
AFTER ALTER, DROP ON SCHEMA

5. Which kinds of trigger can cause a mutating tableproblem? (Choose two.) (Choose all correct answers)
BEFORE UPDATE row triggers (*)
DDL triggers
AFTER DELETE row triggers (*)
Database Event triggers
INSTEAD OF triggers
Database Event triggers
INSTEAD OF triggers

6. Examine this code:
CREATE TRIGGER new_triggAFTER CREATE ON reserved_word
BEGIN ...
Which of the following can be used in place of reserved_word? (Choosetwo.) (Choose all correct answers)
TABLE
SCHEMA (*)
USER
DATABASE (*)
TABLE employees

7. Examine this code:
CREATE TRIGGER de_trigg--Line A
BEGIN ...
Which of the following are NOT valid at Line A ? (Choose two.) (Choose all correct answers)
AFTER LOGOFF ON SCHEMA (*)
AFTER LOGON ON SCHEMA
BEFORE LOGOFF ON SCHEMA
BEFORE DISCONNECT ON SCHEMA (*)
AFTER SERVERERROR ON SCHEMA

8. You need to disable all triggers that are associatedwith DML statements on the DEPARTMENTS table. Which of the following commands should you use?

ALTER TABLE departments DISABLE ALL TRIGGERS;(*)
ALTER TRIGGER DISABLE ALL ON departments;
ALTER TABLE departments DISABLE TRIGGERS;
DISABLE ALL TRIGGERS ON departments;
ALTER TABLE departments DROP ALL TRIGGERS;

9. After the following SQL statement is executed, all thetriggers on the DEPARTMENTS table will no longer fire, but will remain inthe database. True or False?
ALTER TABLE departments DISABLE ALL TRIGGERS;
True (*)
False

10. User AYSEGUL successfully creates the following trigger:
CREATE TRIGGER loc_triggBEFORE UPDATE ON aysegul.locationsBEGIN ....
AYSEGUL now tries to drop the LOCATIONS table. What happens?

An error message is displayed because youcannot drop a table that is associated with a trigger.
The table is dropped and the trigger isdisabled.
The trigger is dropped but the table is notdropped.
Both the table and the trigger are dropped.(*)
None of the above.

11. A SQL statement canpass through several stages. Which of the following is NOT one of thesestages?

BIND
FETCH
PARSE
RETURN(*)
EXECUTE

12. Examine the following code:
CREATE OR REPLACE PROCEDURE myproc ISCURSOR c_curs IS SELECT view_name FROM user_views;
BEGIN
FOR v_curs_rec IN c_curs LOOP
EXECUTE IMMEDIATE 'DROP VIEW ' || v_curs_rec.view_name;
END LOOP;
END;
What will happen when this procedure is invoked?
All views in the user's schema will be dropped. (*)
The procedure will not compile successfullybecause the syntax of EXECUTE IMMEDIATE is incorrect.
The procedure will raise an exception becauseDynamic SQL can drop tables but cannot drop views.
The procedure will raise an exception becauseone of the views is a complex view.

13. Which of the following SQL statements can be included ina PL/SQL block only by using Dynamic SQL? (Choose two.) (Choose all correct answers)

DELETE
SAVEPOINT
ALTER (*)
SELECT ..... FOR UPDATE NOWAIT
GRANT (*)
SAVEPOINT
ALTER (*)
SELECT ..... FOR UPDATE NOWAIT
GRANT (*)

14. MARY wants HENRY to be able to query her EMPLOYEEStable. Mary executes the following code:
DECLARE
v_grant_stmt VARCHAR2(50);
BEGIN
v_grant_stmt := 'GRANT SELECT ON employees TO henry';
DBMS_SQL.EXECUTE(v_grant_stmt);
END;

Mary has successfully granted the privilege to Henry. True or False?

True
False (*)

15. Package MULTIPACK declares the following global
variable:
g_myvar NUMBER;
User DICK executes the following:
multipack.g_myvar := 45;
User HAZEL now connects to the database. Both users immediately execute:
BEGIN
DBMS_OUTPUT.PUT_LINE(multipack.g_myvar);
END;
What values will Dick and Hazel see?

Dick: 45, Hazel: 45
Dick: 45, Hazel: 0
Dick: 45, Hazel: null (*)
Dick: 0, Hazel: 0
Both queries will fail because the syntax ofDBMS_OUTPUT.PUT_LINE is incorrect

16. Package CURSPACKdeclares a global cursor in the package specification. The packagecontains three public procedures: OPENPROC opens the cursor; FETCHPROCfetches 5 rows from the cursor's active set; CLOSEPROC closes the cursor.
What will happen when a user session executes the following commands in the order shown?
curspack.openproc; --line 1
curspack.fetchproc; --line 2
curspack.fetchproc; --line 3
curspack.openproc; --line 4
curspack.fetchproc; --line 5
curspack.closeproc; --line 6

The first 15 rows will be fetched.
The first 10 rows will be fetched, then thefirst 5 rows will be fetched again.
The first 5 rows will be fetched three times.
An error will occur at line 2.
An error will occur at line 4. (*)

17. Which of the following statements about a packageinitialization block is true?
It cannot contain any SQL statements.
It is an anonymous block at the end of apackage body. (*)
It is a procedure in a package that must beinvoked before the rest of the package can be used.
It is an anonymous block in the packagespecification.
It is executed automatically every time anyglobal variable in the package is referenced.

18. A public function in a package is invoked from within aSQL statement. The function's code can include a COMMIT statement. True or False?
True
False (*)

19. Package TAXPACK declares a global variable G_TAXRATENUMBER(2,2). The value of the tax rate is stored in table TAXTAB in the database. You want to read this value automatically into G_TAXRATE eachtime a user session makes its first call to TAXPACK. How would you dothis?
Declare the global variable as:
g_taxrate NUMBER(2,2) := SELECT tax_rate FROM taxtab;

Create a database trigger that includes thefollowing code:
SELECT tax_rate INTO taxpack.g_taxrate FROM taxtab;

Add a private function to the package body ofTAXPACK, and invoke the function from the user session.

Add a package initialization block to thepackage body of TAXPACK.(*)

20. Which two of these declarations cannot be in the same package specification?
PROCEDURE myproc (p1 NUMBER, p2 VARCHAR2);
PROCEDURE myproc (p1 VARCHAR2, p2 NUMBER);
PROCEDURE myproc (p1 NUMBER, p2 CHAR);
PROCEDURE myproc (p1 NUMBER);

1 and 2
1 and 3 (*)
2 and 3
3 and 4
1 and 4

21. Which of the followingare NOT stored inside the database? (Choose two.) (Choose all correct answers)
A PL/SQL package specification
A database trigger
An anonymous block (*)
An application trigger (*)
A sequence

22. You can use a trigger to prevent rows from being deletedfrom the EMPLOYEES table on Mondays. True or False?
True (*)
False

23. What type of database object would you create to writean auditing record automatically every time a user connects to the
database?
A procedure
A complex view
A trigger (*)
A function
A package

24. A business rule states that an employee's salary cannotbe greater than 99,999.99 or less than 0. The best way to enforce thisrule is by using:

A datatype of NUMBER(7,2) for the SALARYcolumn
A database trigger
A check constraint (*)
An application trigger
A view

25. The following objects have been created in a user'sschema:
-a function FUNC1
-A package PACK1 which contains a public procedure PACKPROC and aprivate function PACKFUNC
-a trigger TRIGG1.
The procedure and functions each accept a single IN parameter of typeNUMBER, and the functions return BOOLEANs. Which of the following callsto these objects (from an anonymous block) are correct? (Choose two.) (Choose all correct answers)

pack1.packproc(25); (*)
SELECT func1(100) FROM dual;
trigg1;
IF pack1.packfunc(40) THEN ...
IF func1(75) THEN ... (*)

26. You can code COMMIT and ROLLBACK statements in a trigger body. True or False?
True
False (*)

27. A trigger can be created in the database or within anapplication. True or False?
True (*)
False

28. An Oracle directory called FILESDIR has been created byexecuting:
CREATE OR REPLACE DIRECTORY filesdir AS 'C:\NEWFILES';
Which of the following will create a new text file calledC:\NEWFILES\EMP_REPORT.TXT ?

UTL_FILE.CREATE('FILESDIR','EMP_REPORT.TXT');
UTL_FILE.FOPEN('C:\NEWFILES\EMP_REPORT.TXT','w');
UTL_FILE.FOPEN('FILESDIR','EMP_REPORT.TXT','w'); (*)
UTL_FILE.OPEN('FILESDIR','EMP_REPORT.TXT','c');

29. Why is it better to use DBMS_OUTPUT only in anonymousblocks, not inside stored subprograms such as procedures?

Because DBMS_OUTPUT cannot be used inside procedures

Because anonymous blocks display messageswhile the block is executing, while procedures do not display anythinguntil their execution has finished

Because DBMS_OUTPUT should be used only fortesting and debugging PL/SQL code (*)

Because DBMS_OUTPUT can raise a NO_DATA_FOUND exception if used inside a packaged procedure

30. Which of the following best describes the purpose of theUTL_FILE package?

It is used to load binary files such asemployees' photos into the database. employees' photos into the database.

It is used to read and write text files
stored outside the database. (*)

It is used to find out how much free space isleft on an operating system disk.

It is used to query CHAR and VARCHAR2 columnsin tables.

31. What will be displayedwhen the following code is executed?

BEGIN
DBMS_OUTPUT.PUT('I do like');
DBMS_OUTPUT.PUT_LINE('to be');
DBMS_OUTPUT.PUT('beside the seaside');
END;

I do like to be
beside the seaside

I do like
to be
beside the seaside

I do like to be

I do liketo be(*)

I do like to be beside the seaside

32. Every subprogram which has been declared in a package
specification must also be included in the package body. Triue or False?
True (*)
False

33. The following package specification has been created:
CREATE OR REPLACE PACKAGE mypack ISFUNCTION myfunc(p_funcparam DATE) RETURN BOOLEAN;
PROCEDURE myproc(p_procparam IN NUMBER);
END mypack;
Which of the following will correctly invoke the package subprograms? (Choose two.) (Choose all correct answers)
mypack.myfunc('22-JAN-07');
mypack.myproc(35);(*)
IF NOT mypack.myfunc(SYSDATE) THEN DBMS_OUTPUT.PUT_LINE('Message'); END IF;(*)
myproc(40);
v_num := mypack.myproc(22);

34. Which one of the following can NOT be part of a Package?
Procedures
Explicit cursors
Triggers (*)
Functions
Global variables

35. What is wrong with the following syntax for creating apackage specification?

CREATE OR REPLACE PACKAGE mypack ISg_constant1 NUMBER(6) := 100;
FUNCTION func1 (p_param1 IN VARCHAR2);
FUNCTION func2;
g_constant1 NUMBER(6) := 100;
FUNCTION func1 (p_param1 IN VARCHAR2);
FUNCTION func2;
END mypack;

You cannot declare constants in the specification.
A package must contain at least oneprocedure.
The RETURN datatype of the functions must be specified.(*)
The first line should be: CREATE OR REPLACE PACKAGE SPECIFICATION mypack IS Nothing is wrong, this code contains noerrors.

36. What is wrong with the following code?

CREATE OR REPLACE TRIGGER loc_triggBEFORE DELETE ON locations
BEGIN
RAISE_APPLICATION_ERROR(-20201,'Invalid delete');
ROLLBACK;
END;

END loc_trigg;
a trigger.

BEFORE DELETE OF locations (*) and execute successfully.
The last line should be: You cannot use RAISE_APPLICATION_ERROR inside
The second line should be: You cannot use ROLLBACK inside a trigger.
Nothing is wrong, this trigger will compile

37. What is wrong with the following code?
CREATE OR REPLACE TRIGGER emp_dept_triggBEFORE UPDATE OR DELETE ON employees, departmentsBEGIN
...

One trigger can be associated with only onetable(*)
The second line should be: BEFORE (UPDATE,DELETE) ON employees, departments
DML triggers must be row triggers, so FOREACH ROW is missing
The second line should be: BEFORE UPDATE OR DELETE ON employees OR departments

38. A DML statement trigger fires only once for eachtriggering DML statement, while a row trigger fires once for each rowprocessed by the triggering statement. True or False?
True (*)
False

39. The following code will successfully create emp_trigg: True or False?
CREATE OR REPLACE TRIGGER emp_triggBEFORE DELETE OF salary ON employeesBEGIN
RAISE_APPLICATION_ERROR(-20202,'Deleting salary is not allowed');
END;

True
False (*)

40. In a package, public components are declared in thespecification but private components are not. True or False?
True (*)
False

41. Package NEWPACKcontains several procedures and functions, including private functionPRIVFUNC. From where can PRIVFUNC be invoked? (Choose two.) (Choose all correct answers)

From an anonymous block
From any procedure in NEWPACK (*)
From any private function in another package
From any function in NEWPACK (*)
From any public procedure in another package

42. Which of the following will display the detailed code of the subprograms in package DEPTPACK in your schema ?
SELECT text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'PACKAGE'ORDER BY line;

SELECT text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'PACKAGE BODY'ORDER BY line;(*)

SELECT text FROM USER_SOURCE
WHERE object_name = 'DEPTPACK'AND object_type = 'PACKAGE BODY'ORDER BY line;
AND object_type = 'PACKAGE BODY'ORDER BY line;

SELECT text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'BODY'ORDER BY line;

43. Package OLDPACK is in your schema. What will happen whenthe following statement is executed?
DROP PACKAGE oldpack;

The body will be dropped but thespecification will be retained.
The specification will be dropped but thebody will be retained.
Both the specification and the body will bedropped. (*)
The statement will fail because you must dropthe body before you can drop the specification.

44. When a change is made to the detailed code of a publicprocedure in a package (but not to the procedure's name or parameters), both the specification and the body must be recompiled. True or False?

True
False (*)

45. Your schema contains four packages, each having aspecification and a body. You have also been granted privileges to accessthree packages (and their bodies) in other users' schemas. What will bedisplayed by the following query?
SELECT COUNT(*) FROM ALL_OBJECTSWHERE object_type LIKE 'PACK%'
AND owner <> USER;

14
7
3
6 (*)
0

46. We want to remove the specification (but not the body) of package BIGPACK from the database.
Which of the following commands will do this?

DROP PACKAGE bigpack;
DROP PACKAGE SPECIFICATION bigpack;
DROP PACKAGE bigpack SPECIFICATION;
DROP PACKAGE HEADER bigpack;
None of the above (*)

47. Examine the following code. To create a row trigger, what code should be included at Line A?
CREATE TRIGGER dept_triggAFTER UPDATE OR DELETE ON departments--Line A
BEGIN ...

AFTER EACH ROW
FOR EVERY ROW
FOR EACH ROW (*)
ON EACH ROW
ON EVERY ROW

48. With which kind of trigger can the :OLD and :NEWqualifiers be used?
DDL triggers
Database Event triggers
Statement triggers
Row triggers (*)
AFTER triggers

49. In the following code:
CREATE TRIGGER mytriggINSTEAD OF INSERT OR UPDATE ON my_object_nameFOR EACH ROW
BEGIN ...
my_object_name can be the name of a table. True or False?
True
False (*)

50. Examine the following trigger. It should raise anapplication error if a user tries to update an employee's last name. Itshould allow updates to all other columns of the EMPLOYEES table. Whatshould be coded at line A?
CREATE TRIGGER stop_ln_triggBEFORE UPDATE ON employeesBEGIN
--Line A
RAISE_APPLICATION_ERROR(-20201,'Updating last name not allowed');
END IF;
END;

IF UPDATING LAST_NAME THEN
IF UPDATING('LAST_NAME') THEN (*)
IF UPDATE('LAST_NAME') THEN
IF UPDATING THEN

PLSQL Mid Term Exam Semester 2

1. Examine the following code:
CREATE TRIGGER emp_triggAFTER UPDATE OF salary ON employeesFOR EACH ROW
DECLARE
v_count NUMBER;
BEGIN
--Line A
END;
Which of the following statements is NOT allowed at Line A?

SELECT count(*) INTO v_count FROMdepartments;
UPDATE employees SET job_id = 'IT_PROG' WHEREemployee_id = :OLD.employee_id;
SELECT count(*) INTO v_count FROM employees;(*)
DBMS_OUTPUT.PUT_LINE('A salary was updated');
None. All of the above are allowed.

2. Which of the following statements could cause a DDLtrigger to fire?
DROP TABLE employees;
ALTER TABLE departments ADD (budgetNUMBER(8,2));
CREATE TABLE newemp AS SELECT * FROMemployees;
TRUNCATE TABLE locations;
All of the above (*)

3. The database administrator wants to write a log recordevery time an Oracle Server error occurs in any user's session. The DBAcreates the following trigger:

CREATE TRIGGER log_errs_trigg--Line A
BEGIN
--Line A
BEGIN
INSERT INTO errlog_table VALUES (...);
END;
What should the DBA code at Line A ?

AFTER ERROR ON DATABASE
AFTER SERVER ERROR ON DATABASE
AFTER SERVERERROR ON SCHEMA
AFTER SERVERERROR ON DATABASE (*)
AFTER ORACLE ERROR ON SCHEMA

4. You want to prevent any objects in your schema frombeing altered or dropped. You decide to create the following trigger:
CREATE TRIGGER stop_ad_trigg--Line A
BEGIN
RAISE_APPLICATION_ERROR(-20203,'Invalid Operation');
END;
What should you code at Line A ?

AFTER ALTER OR DROP ON SCHEMA
INSTEAD OF ALTER OR DROP ON SCHEMA
BEFORE ALTER OR DROP ON SCHEMA (*)
BEFORE ALTER, DROP ON SCHEMA
AFTER ALTER, DROP ON SCHEMA

5. Which kinds of trigger can cause a mutating tableproblem? (Choose two.)
(Choose all correct answers)
BEFORE UPDATE row triggers (*)
DDL triggers
AFTER DELETE row triggers (*)
Database Event triggers
INSTEAD OF triggers
Database Event triggers
INSTEAD OF triggers

6. Examine this code:
CREATE TRIGGER new_triggAFTER CREATE ON reserved_word
BEGIN ...
Which of the following can be used in place of reserved_word? (Choosetwo.) (Choose all correct answers)
TABLE
SCHEMA (*)
USER
DATABASE (*)
TABLE employees

7. Examine this code:
CREATE TRIGGER de_trigg--Line A
BEGIN ...
Which of the following are NOT valid at Line A ? (Choose two.) (Choose all correct answers)
AFTER LOGOFF ON SCHEMA (*)
AFTER LOGON ON SCHEMA
BEFORE LOGOFF ON SCHEMA
BEFORE DISCONNECT ON SCHEMA (*)
AFTER SERVERERROR ON SCHEMA


8. You need to disable all triggers that are associatedwith DML statements on the DEPARTMENTS table. Which of the following commands should you use?

ALTER TABLE departments DISABLE ALL TRIGGERS; (*)
ALTER TRIGGER DISABLE ALL ON departments;
ALTER TABLE departments DISABLE TRIGGERS;
DISABLE ALL TRIGGERS ON departments;
ALTER TABLE departments DROP ALL TRIGGERS;

9. After the following SQL statement is executed, all thetriggers on the DEPARTMENTS table will no longer fire, but will remain inthe database. True or False?
ALTER TABLE departments DISABLE ALL TRIGGERS;

True (*)
False

10. User AYSEGUL successfully creates the following trigger:
CREATE TRIGGER loc_triggBEFORE UPDATE ON aysegul.locationsBEGIN ....
AYSEGUL now tries to drop the LOCATIONS table. What happens?

An error message is displayed because youcannot drop a table that is associated with a trigger.
The table is dropped and the trigger isdisabled.
The trigger is dropped but the table is notdropped.
Both the table and the trigger are dropped.(*)
None of the above.

11. A SQL statement canpass through several stages. Which of the following is NOT one of thesestages?
BIND
FETCH
PARSE
RETURN (*)
EXECUTE

12. Examine the following code:
CREATE OR REPLACE PROCEDURE myproc ISCURSOR c_curs IS SELECT view_name FROM user_views;
BEGIN
FOR v_curs_rec IN c_curs LOOP
EXECUTE IMMEDIATE 'DROP VIEW ' || v_curs_rec.view_name;
END LOOP;
END;
What will happen when this procedure is invoked?

All views in the user's schema will be dropped. (*)
The procedure will not compile successfullybecause the syntax of EXECUTE IMMEDIATE is incorrect.
The procedure will raise an exception becauseDynamic SQL can drop tables but cannot drop views.
The procedure will raise an exception becauseone of the views is a complex view.

13. Which of the following SQL statements can be included ina PL/SQL block only by using Dynamic SQL? (Choose two.) (Choose all correct answers)
DELETE
SAVEPOINT
ALTER (*)
SELECT ..... FOR UPDATE NOWAIT
GRANT (*)
SAVEPOINT
ALTER (*)
SELECT ..... FOR UPDATE NOWAIT
GRANT (*)

14. MARY wants HENRY to be able to query her EMPLOYEEStable. Mary executes the following code:
DECLARE v_grant_stmt VARCHAR2(50);
BEGIN
v_grant_stmt := 'GRANT SELECT ON employees TO henry';
DBMS_SQL.EXECUTE(v_grant_stmt);
END;
Mary has successfully granted the privilege to Henry. True or False?

True
False (*)

15. Package MULTIPACK declares the following global
variable:
g_myvar NUMBER;
User DICK executes the following:
multipack.g_myvar := 45;
User HAZEL now connects to the database. Both users immediately execute:
BEGIN
DBMS_OUTPUT.PUT_LINE(multipack.g_myvar);
END;
What values will Dick and Hazel see?

Dick: 45, Hazel: 45
Dick: 45, Hazel: 0
Dick: 45, Hazel: null (*)

Dick: 0, Hazel: 0
Both queries will fail because the syntax ofDBMS_OUTPUT.PUT_LINE is incorrect

16. Package CURSPACKdeclares a global cursor in the package specification. The packagecontains three public procedures: OPENPROC opens the cursor; FETCHPROCfetches 5 rows from the cursor's active set; CLOSEPROC closes the cursor.
What will happen when a user session executes the following commands in the order shown?
curspack.openproc; --line 1
curspack.fetchproc; --line 2
curspack.fetchproc; --line 3
curspack.openproc; --line 4
curspack.fetchproc; --line 5
curspack.closeproc; --line 6

The first 15 rows will be fetched.
The first 10 rows will be fetched, then thefirst 5 rows will be fetched again.
The first 5 rows will be fetched three times.
An error will occur at line 2.
An error will occur at line 4. (*)

17. Which of the following statements about a packageinitialization block is true?

It cannot contain any SQL statements.
It is an anonymous block at the end of apackage body. (*)
It is a procedure in a package that must beinvoked before the rest of the package can be used.
It is an anonymous block in the package specification.
It is executed automatically every time anyglobal variable in the package is referenced.

18. A public function in a package is invoked from within aSQL statement. The function's code can include a COMMIT statement. True or False?
True
False (*)

19. Package TAXPACK declares a global variable G_TAXRATENUMBER(2,2). The value of the tax rate is stored in table TAXTAB in the database. You want to read this value automatically into G_TAXRATE eachtime a user session makes its first call to TAXPACK. How would you dothis?

Declare the global variable as: g_taxrate NUMBER(2,2) := SELECT tax_rate FROM taxtab;

Create a database trigger that includes thefollowing code: SELECT tax_rate INTO taxpack.g_taxrate FROM taxtab;

Add a private function to the package body ofTAXPACK, and invoke the function from the user session.
Add a package initialization block to thepackage body of TAXPACK.(*)

20. Which two of these declarations cannot be in the same package specification?
PROCEDURE myproc (p1 NUMBER, p2 VARCHAR2);
PROCEDURE myproc (p1 VARCHAR2, p2 NUMBER);
PROCEDURE myproc (p1 NUMBER, p2 CHAR);
PROCEDURE myproc (p1 NUMBER);

1 and 2
1 and 3 (*)
2 and 3
3 and 4
1 and 4

21. Which of the followingare NOT stored inside the database? (Choose two.) (Choose all correct answers)
A PL/SQL package specification
A database trigger
An anonymous block (*)
An application trigger (*)
A sequence

22. You can use a trigger to prevent rows from being deletedfrom the EMPLOYEES table on Mondays. True or False?
True (*)
False

23. What type of database object would you create to writean auditing record automatically every time a user connects to the database?
A procedure
A complex view
A trigger (*)
A function
A package

24. A business rule states that an employee's salary cannotbe greater than 99,999.99 or less than 0. The best way to enforce thisrule is by using:
A datatype of NUMBER(7,2) for the SALARYcolumn
A database trigger
A check constraint (*)
An application trigger
A view

25. The following objects have been created in a user's schema:
-a function FUNC1
-A package PACK1 which contains a public procedure PACKPROC and aprivate function PACKFUNC
-a trigger TRIGG1.
The procedure and functions each accept a single IN parameter of typeNUMBER, and the functions return BOOLEANs. Which of the following callsto these objects (from an anonymous block) are correct? (Choose two.) (Choose all correct answers)

pack1.packproc(25); (*)
SELECT func1(100) FROM dual;
trigg1;
IF pack1.packfunc(40) THEN ...
IF func1(75) THEN ... (*)

26. You can code COMMIT and ROLLBACK statements in a trigger body. True or False?
True
False (*)

27. A trigger can be created in the database or within anapplication. True or False?

True (*)
False

28. An Oracle directory called FILESDIR has been created byexecuting:
CREATE OR REPLACE DIRECTORY filesdir AS 'C:\NEWFILES';
Which of the following will create a new text file calledC:\NEWFILES\EMP_REPORT.TXT ?
UTL_FILE.CREATE('FILESDIR','EMP_REPORT.TXT');
UTL_FILE.FOPEN('C:\NEWFILES\EMP_REPORT.TXT','w');
UTL_FILE.FOPEN('FILESDIR','EMP_REPORT.TXT','w'); (*)
UTL_FILE.OPEN('FILESDIR','EMP_REPORT.TXT','c');

29. Why is it better to use DBMS_OUTPUT only in anonymousblocks, not inside stored subprograms such as procedures?
Because DBMS_OUTPUT cannot be used inside procedures
Because anonymous blocks display messageswhile the block is executing, while procedures do not display anythinguntil their execution has finished
Because DBMS_OUTPUT should be used only fortesting and debugging PL/SQL code (*)
Because DBMS_OUTPUT can raise a NO_DATA_FOUND exception if used inside a packaged procedure

30. Which of the following best describes the purpose of theUTL_FILE package?

It is used to load binary files such asemployees' photos into the database. employees' photos into the database.
It is used to read and write text files stored outside the database. (*)
It is used to find out how much free space isleft on an operating system disk.
It is used to query CHAR and VARCHAR2 columnsin tables.

31. What will be displayedwhen the following code is executed?
BEGIN
DBMS_OUTPUT.PUT('I do like');
DBMS_OUTPUT.PUT_LINE('to be');
DBMS_OUTPUT.PUT('beside the seaside');
END;

I do like to be beside the seaside
I do like to be beside the seaside
I do like to be (*)
I do like to be beside the seaside

32. Every subprogram which has been declared in a package specification must also be included in the package body. Triue or False?
True (*)
False

33. The following package specification has been created:
CREATE OR REPLACE PACKAGE mypack ISFUNCTION myfunc(p_funcparam DATE) RETURN BOOLEAN;
PROCEDURE myproc(p_procparam IN NUMBER);
END mypack;
Which of the following will correctly invoke the package subprograms? (Choose two.) (Choose all correct answers)
mypack.myfunc('22-JAN-07');
mypack.myproc(35); (*)
IF NOT mypack.myfunc(SYSDATE) THEN
DBMS_OUTPUT.PUT_LINE('Message');
END IF; (*)
myproc(40);
v_num := mypack.myproc(22);

34. Which one of the following can NOT be part of a Package?
Procedures
Explicit cursors
Triggers (*)
Functions
Global variables

35. What is wrong with the following syntax for creating apackage specification?

CREATE OR REPLACE PACKAGE mypack ISg_constant1 NUMBER(6) := 100;
FUNCTION func1 (p_param1 IN VARCHAR2);
FUNCTION func2;
g_constant1 NUMBER(6) := 100;
FUNCTION func1 (p_param1 IN VARCHAR2);
FUNCTION func2;
END mypack;

You cannot declare constants in the specification.
A package must contain at least oneprocedure.
The RETURN datatype of the functions must be specified.(*)
The first line should be: CREATE OR REPLACE PACKAGE SPECIFICATION mypack IS
Nothing is wrong, this code contains noerrors.

36. What is wrong with the following code?
CREATE OR REPLACE TRIGGER loc_triggBEFORE DELETE ON locations
BEGIN
RAISE_APPLICATION_ERROR(-20201,'Invalid delete');
ROLLBACK;
END;

END loc_trigg;
a trigger.
BEFORE DELETE OF locations (*)
 and execute successfully.
The last line should be: You cannot use RAISE_APPLICATION_ERROR inside
The second line should be: You cannot use ROLLBACK inside a trigger.
Nothing is wrong, this trigger will compile

37. What is wrong with the following code?
CREATE OR REPLACE TRIGGER emp_dept_triggBEFORE UPDATE OR DELETE ON employees, departmentsBEGIN
...
One trigger can be associated with only onetable(*)
The second line should be: BEFORE (UPDATE,DELETE) ON employees, departments
DML triggers must be row triggers, so FOREACH ROW is missing
The second line should be: BEFORE UPDATE OR DELETE ON employees OR departments

38. A DML statement trigger fires only once for eachtriggering DML statement, while a row trigger fires once for each rowprocessed by the triggering statement. True or False?
True (*)
False

39. The following code will successfully create emp_trigg: True or False?
CREATE OR REPLACE TRIGGER emp_triggBEFORE DELETE OF salary ON employeesBEGIN
RAISE_APPLICATION_ERROR(-20202,'Deleting salary is not allowed');
END;


True
False (*)

40. In a package, public components are declared in thespecification but private components are not. True or False?
True (*)
False

41. Package NEWPACKcontains several procedures and functions, including private functionPRIVFUNC. From where can PRIVFUNC be invoked? (Choose two.) (Choose all correct answers)

From an anonymous block
From any procedure in NEWPACK (*)
From any private function in another package
From any function in NEWPACK (*)
From any public procedure in another package

42. Which of the following will display the detailed code of the subprograms in package DEPTPACK in your schema ?

SELECT text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'PACKAGE'ORDER BY line;

SELECT text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'PACKAGE BODY'ORDER BY line; (*)

SELECT text FROM USER_SOURCE

WHERE object_name = 'DEPTPACK'AND object_type = 'PACKAGE BODY'ORDER BY line;
AND object_type = 'PACKAGE BODY'ORDER BY line;

SELECT text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'BODY'ORDER BY line;

43. Package OLDPACK is in your schema. What will happen whenthe following statement is executed?
DROP PACKAGE oldpack;
The body will be dropped but thespecification will be retained.
The specification will be dropped but thebody will be retained.
Both the specification and the body will bedropped. (*)
The statement will fail because you must dropthe body before you can drop the specification.

44. When a change is made to the detailed code of a publicprocedure in a package (but not to the procedure's name or parameters),
both the specification and the body must be recompiled. True or False?
True
False (*)

45. Your schema contains four packages, each having aspecification and a body. You have also been granted privileges to accessthree packages (and their bodies) in other users' schemas. What will bedisplayed by the following query?
SELECT COUNT(*) FROM ALL_OBJECTSWHERE object_type LIKE 'PACK%'
AND owner <> USER;

14
7
3
6 (*)
0

46. We want to remove the specification (but not the body) of package BIGPACK from the database.
Which of the following commands will do this?

DROP PACKAGE bigpack;
DROP PACKAGE SPECIFICATION bigpack;
DROP PACKAGE bigpack SPECIFICATION;
DROP PACKAGE HEADER bigpack;
None of the above (*)

47. Examine the following code. To create a row trigger,
what code should be included at Line A?
CREATE TRIGGER dept_triggAFTER UPDATE OR DELETE ON departments--Line A
BEGIN ...

AFTER EACH ROW
FOR EVERY ROW
FOR EACH ROW (*)
ON EACH ROW
ON EVERY ROW

48. With which kind of trigger can the :OLD and :NEWqualifiers be used?
DDL triggers
Database Event triggers
Statement triggers
Row triggers (*)
AFTER triggers

49. In the following code: CREATE TRIGGER mytrigg INSTEAD OF INSERT OR UPDATE ON my_object_name FOR EACH ROW
BEGIN ...
my_object_name can be the name of a table. True or False?

True
False (*)

50. Examine the following trigger. It should raise anapplication error if a user tries to update an employee's last name. It should allow updates to all other columns of the EMPLOYEES table. Whats hould be coded at line A?
CREATE TRIGGER stop_ln_triggBEFORE UPDATE ON employeesBEGIN
--Line A
RAISE_APPLICATION_ERROR(-20201,'Updating last name not allowed');
END IF;
END;

IF UPDATING LAST_NAME THEN
IF UPDATING('LAST_NAME') THEN (*)
IF UPDATE('LAST_NAME') THEN
IF UPDATING THEN

PLSQL Mid Term Exam Semester 1 - Part II

1. A cursor is declared as:
CURSOR c IS SELECT * FROM departments FOR UPDATE;
After opening the cursor and fetching some rows, you want to delete the most recently
fetched row. Which of the following will do this successfully?

DELETE FROM c WHERE CURRENT OF c;
DELETE FROM departments WHERE CURRENT OF c; (*)
DELETE FROM c WHERE CURRENT OF departments;
DELETE FROM departments WHERE c%ROWCOUNT = 1;
None of the above.

2. Examine the following code:
DECLARE
CURSOR c IS SELECT * FROM employees FOR UPDATE;
c_rec c%ROWTYPE;
BEGIN
OPEN c;
FOR i IN 1..20 LOOP
FETCH c INTO c_rec;
IF i = 6 THEN
UPDATE employees SET first_name = 'Joe'
WHERE CURRENT OF c;
END IF;
END LOOP;
CLOSE c;
END;
Which employee row or rows will be updated when this block is executed?

The first 6 fetched rows will be updated.
No rows will be updated because you locked the rows when the cursor was opened.
The 6th fetched row will be updated. (*)
The block will not compile because the cursor should have been declared .... FOR
UPDATE WAIT 5;
None of the above.


3. You want to declare a cursor which locks each row fetched by the cursor.
Examine the following code:
DECLARE
CURSOR emp_curs IS
SELECT * FROM employees
FOR --Point A
Which of the following can NOT be coded at Point A?

UPDATE;
UPDATE OF salary;
UPDATE OF employees; (*)
UPDATE NOWAIT;

4. Which of the following cursor attributes is set to the total number of rows returned so far?
Mark for Review
(1) Points
%ISOPEN
%NOTFOUND
%FOUND
%ROWCOUNT (*)

5. Assume that you have declared a cursor called C_EMP. Which of the following
statements about C_EMP is correct? (Choose two.) (Choose all correct answers)
You can use c_emp%NOTFOUND to exit a loop. (*)
You can fetch rows when c_emp%ISOPEN evaluates to FALSE.
You can use c_emp%ROWCOUNT to return the number of rows returned by the cursor so far. (*)
You can use c_emp%FOUND after the cursor is closed.

6. The DEPARTMENTS table contains four columns. Examine the following code:

DECLARE
CURSOR dept_curs IS
SELECT * FROM departments;
v_dept_rec dept_curs%ROWTYPE;
CURSOR dept_curs IS
SELECT * FROM departments;
v_dept_rec dept_curs%ROWTYPE;
BEGIN
OPEN dept_curs;
FETCH dept_curs INTO v_dept_rec;
...
Which one of the following statements is true?

v_dept_rec contains the first four rows of the departments table.
The FETCH will fail because the structure of v_dept_rec does not match the structure of  the cursor.
v_dept_rec contains the first row of the departments table. (*)
The block will fail because the declaration of v_dept_rec is invalid.

7. The following cursor has been declared:
CURSOR emp_curs IS
SELECT first_name, last_name, job_id, salary
FROM employees;
Which of the following correctly declares a composite record with the same structure as the cursor?

emp_rec emp_rec%ROWTYPE;
emp_rec emp_curs%TYPE;
emp_rec emp_curs%ROWTYPE; (*)
emp_rec cursor%ROWTYPE;

8. Which of the following cursor attributes evaluates to TRUE if the cursor is open?
%ISOPEN (*)
%NOTFOUND
%FOUND
%ROWCOUNT

9. Examine the following code fragment:
DECLARE
CURSOR emp_curs IS
SELECT first_name, last_name FROM employees;
v_emp_rec emp_curs%ROWTYPE;
BEGIN
...
FETCH emp_curs INTO v_emp_rec;
DBMS_OUTPUT.PUT_LINE(... Point A ...);
&nbsp...
To display the fetched last name, what should you code at Point A?

v_emp_rec.last_name (*)
v_emp_rec(last_name)
v_emp_rec
last_name
None of the above

10. Which statement best describes when a WHILE loop shouild be used?
When the number of iterations is known
When repeating a sequence of statements until the controlling condition is no longer true (*)
When assigning a value to a Boolean variable
When testing whether a variable is null

11. Which of the following blocks produces the same output as this block?
BEGIN
FOR i in 1 .. 3 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;

DECLARE
i PLS_INTEGER := 0;
BEGIN
WHILE i<3 LOOP
DBMS_OUTPUT.PUT_LINE(i);
i := i + 1;
END LOOP;
END;

DECLARE
i PLS_INTEGER := 0;
BEGIN
WHILE i<3 LOOP
i := i + 1;
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END; (*)

DECLARE
i PLS_INTEGER := 0;
BEGIN
WHILE i<3 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
i := i+ 1;
END;

12. What will happen when the following code is executed?
BEGIN
FOR i in 1 ..3 LOOP
DBMS_OUTPUT.PUT_LINE (i);
i := i + 1;
END LOOP;
END; END;

It will display 1, 2, 3.
It will display 2, 3, 4.
It will result in an error because you cannot modify the counter in a FOR loop. (*)
It will result in an error because the counter was not explicitly declared.

13. In a WHILE loop, the statements inside the loop must execute at least once. True or False?
True
False (*)

14. In a FOR loop, an implicitly declared counter automatically increases or
decreases with each iteration. True or False?
True (*)
False

15. Examine the following code:
DECLARE
v_bool BOOLEAN := FALSE;
v_counter NUMBER(4) := 0;
BEGIN
... Line A
END;
Which of the following is NOT valid at line A?

WHILE NOT v_boolean LOOP

WHILE v_boolean AND v_counter < 6 LOOP
WHILE v_counter > 8 LOOP
WHILE v_counter IN 1..5 LOOP (*)

16. You want to assign a value to v_result which depends on the value of v_grade: if v_grade
= 'A' set v_result to 'Very Good' and so on.
DECLARE
v_grade CHAR(1);
v_result VARCHAR2(10);
BEGIN
v_result :=
CASE v_grade
The next line should be

WHEN v_grade = 'A' THEN 'Very Good'
WHEN 'A' THEN 'Very Good';
WHEN 'A' THEN v_result := 'Very Good';
WHEN 'A' THEN 'Very Good' (*)

17. What value will v_answer contain after the following code is executed?
DECLARE
v_age NUMBER:= 18;
v_answer VARCHAR2(10);
BEGIN
v_answer :=
CASE
WHEN v_age < 25 THEN 'Young'
WHEN v_age = 18 THEN 'Exactly 18'
ELSE 'Older'
END CASE;
END;

Exactly 18

Young (*)
Null
Older
Correct

18. What will be the value of v_result after the following code is executed?
DECLARE
v_grade CHAR(1) := NULL;
v_result VARCHAR2(10);
BEGIN
CASE v_grade
WHEN 'A' THEN v_result := 'Very Good';
WHEN 'F' THEN v_result := 'Poor';
ELSE v_result := 'In Between';
END;
END;

Poor
In Between (*)
Null
Very Good

19. What will be the value of v_sal_desc after the following code is executed?
DECLARE
v_salary NUMBER(6,2) := NULL;
v_sal_desc VARCHAR2(10);
BEGIN
CASE
WHEN v_salary < 10000 THEN v_sal_desc := 'Low Paid';
WHEN v_salary >= 10000 THEN v_sal_desc := 'High Paid';
END CASE;
END;

High Paid

Low Paid
Null
The code will fail and return an exception (*)

20. You want to display a message which depends on the value of v_grade: if
v_grade = 'A' display 'Very Good', if v_grade = 'B' then display 'Good', and so on.
DECLARE
v_grade CHAR(1);
BEGIN
CASE v_grade
The next line should be

WHEN 'A' THEN (*)
WHEN v_grade = 'A' THEN
WHEN 'A' THEN;
IF 'A' THEN

21. Which of these constructs can be used to fetch multiple rows from a cursor's active set?
A CASE statement
An IF .... ELSE statement
A basic loop which includes FETCH and EXIT WHEN statements (*)
A basic loop which includes OPEN, FETCH and CLOSE statements

22. An explicit cursor must always be declared, opened and closed by the PL/SQL
programmer. True or False?
True

False (*)

23. Examine the following code:
DECLARE
CURSOR emp_curs IS
SELECT last_name, salary
FROM employees
ORDER BY salary;
v_last_name employees.last_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
...
Which of the following statements successfully opens the cursor and fetches the first row of
the active set?

OPEN emp_curs;
FETCH emp_curs INTO v_last_name, v_salary; (*)

OPEN emp_curs;
FETCH emp_curs INTO v_salary, v_last_name;

OPEN emp_curs;
FETCH FIRST emp_curs INTO v_last_name, v_salary;

OPEN emp_curs;
FETCH emp_curs;

24. Which of these statements about implicit cursors is NOT true?
They are declared automatically by Oracle for single-row SELECT statements.
They are declared automatically by Oracle for all DML statements.
They are declared by the PL/SQL programmer. (*)
They are opened and closed automatically by Oracle.


25. After a cursor has been closed, it can be opened again in the same PL/SQL block. True or False?
True (*)
False

26. The employees table contains 20 rows. What will happen when the following code is executed?
DECLARE
&nbspCURSOR emp_curs IS
&nbspSELECT job_id FROM employees;
v_job_id employees.job_id%TYPE;
BEGIN
OPEN emp_curs;
LOOP
FETCH emp_curs INTO v_job_id;
DBMS_OUTPUT.PUT_LINE(v_job_id);
EXIT WHEN emp_curs%NOTFOUND;
END LOOP;
CLOSE emp_curs;
END;

20 job_ids will be displayed.
The block will fail and an error message will be displayed.
21 rows of output will be displayed; the first job_id will be displayed twice.
21 rows of output will be displayed; the last job_id will be displayed twice. (*)

27. What will happen when the following code is executed?
DECLARE
CURSOR emp_curs IS
SELECT salary FROM employees;
v_salary employees.salary%TYPE;
BEGIN
FETCH emp_curs INTO v_salary;
DBMS_OUTPUT.PUT_LINE(v_salary);
CLOSE emp_curs;
END;

The first employee's salary will be fetched and displayed.
All employees' salaries will be fetched and displayed.
The execution will fail and an error message will be displayed. (*)
The lowest salary value will be fetched and displayed.

28. For which type of SQL statement must you use an explicit cursor?
DML statements that process more than one row.
Queries that return more than one row. (*)
Data Definition Language (DDL) statements.
Queries that return a single row.

29. When using multiple nested cursors, what kinds of loops can you use?
Cursor FOR loops only.
Basic loops only.
WHILE loops only.
None of the above.
All of the above. (*)

30. How many explicit cursors can be declared and used in a single PL/SQL block?
One or two.
Only one.
As many as needed. (*)
Up to eight cursors.
None of the above.

31. What is wrong with the following code?
DECLARE
CURSOR emp_curs(p_dept_id NUMBER) IS
SELECT * FROM employees WHERE department_id = p_dept_id;
BEGIN
FOR dept_rec IN (SELECT * FROM departments) LOOP
DBMS_OUTPUT.PUT_LINE(dept_rec.department_name);
FOR emp_rec IN emp_curs(dept_rec.department_id) LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.last_name);
END LOOP;
END LOOP;
END;

The DEPARTMENTS cursor must be declared with a parameter.
You cannot use a cursor with a subquery in nested loops.
You cannot use two different kinds of loop in a single PL/SQL block.
EMP_CURS should not be DECLAREd explicitly; it should be coded as a subquery in a cursor FOR loop.
Nothing is wrong. The block will execute successfully and display all departments and the employees in those departments. (*)

32. Examine the following code:
BEGIN
FOR i IN 1..5 LOOP
FOR j IN 1..8 LOOP
EXIT WHEN j = 7;
DBMS_OUTPUT.PUT_LINE(i || j);
END LOOP;
END LOOP;
END;
How many lines of output will be displayed when this code is executed?

35

6
30 (*)
40

33. What kinds of loops can be nested?
BASIC loops
WHILE loops
FOR loops
All of the above (*)

34. A PL/SQL block contains the following code:
v_counter := 1;
LOOP
EXIT WHEN v_counter = 5;
v_counter := v_counter + 1;
END LOOP;
What is the value of V_COUNTER after the loop is finished?

5 (*)
6
1
This is an infinite loop; the loop will never finish.

35. What kind of statement is best suited for displaying the multiplication table for "sixes": 6x1=6, 6x2=12 ... 6x12=72?

CASE expression

IF statement
CASE statement
LOOP statement (*)

36. Which one of these is NOT a kind of loop?
ASCENDING loop (*)
FOR loop
Basic loop
WHILE loop

37. A PL/SQL block contains the following code:
v_counter := 1;
LOOP
EXIT WHEN v_counter=5;
END LOOP;
v_counter := v_counter + 1;
What is the value of V_COUNTER after the loop is finished?

5
6
1
This is an infinite loop; the loop will never finish. (*)

38. Which one of these tasks is best done using a LOOP statement?
Assigning a letter grade to a numerical score
Calculating and displaying the sum of all integers from 1 to 100 (*)
Testing if a condition is true, false or null
Fetching and displaying an employee's last name from the database

39. What is the correct form of a compound IF statement?
IF condition
THEN statement1
ELSE statement 2;
IF condition
THEN statement1
ELSE statement 2;
END IF;
IF condition;
THEN statement1;
ELSE statement2;
END IF;
IF condition THEN statement1;
ELSE statement2;
END IF; (*)

40. Examine the following code:
DECLARE
v_salary NUMBER(6);
v_constant NUMBER(6) := 15000;
v_result VARCHAR(6); := 'MIDDLE';
BEGIN
IF v_salary != v_constant THEN
v_result := 'HIGH';
ELSE
v_result := 'LOW';
END IF;
END;
What is the final value of v_result?

HIGH

LOW (*)
MIDDLE
Null

41. What is the correct form of a simple IF statement?
IF condition THEN statement;
IF condition THEN statement;
END IF; (*)

IF condition;
THEN statement;
END IF;

IF condition
THEN statement
ENDIF;

42. What is the correct name for CASE, LOOP, WHILE, and IF-THEN-ELSE
structures ?
Control structures (*)
Array structures
Memory structures
Cursor structures

43. There are 12 distinct JOB_IDs in the EMPLOYEES table. You need to write
some PL.SQL code to fetch and display all the employees with a specific JOB_ID. The
chosen JOB_ID can be different each time the code is executed.
What is the best way to do this?

Write 12 separate PL/SQL blocks, each declaring a cursor with a different JOB_ID in the WHERE clause.
Write a single PL/SQL block which declares 12 cursors, one for each distinct value of JOB_ID.
Write a single PL/SQL block which declares one cursor using a parameter for the JOB_ID. (*)
Write a single PL/SQL block which uses a cursor to fetch all the employee rows, with an IF statement to decide which of the fetched rows to display.

44. Examine the following declaration of a cursor with a parameter. What should be coded at Point A? DECLARE
CURSOR emp_curs(--Point A --) IS
SELECT * FROM employees
WHERE job_id = p_job_id;

p_job_id
ST_CLERK'
p_job_id VARCHAR2(25)
p_job_id VARCHAR2 (*)
job_id VARCHAR2

45. Examine the following code. To display the salary of an employee, what must be coded at Point A?
DECLARE
CURSOR emp_curs IS SELECT * FROM employees;
BEGIN
FOR emp_rec IN emp_curs LOOP
DBMS_OUTPUT.PUT_LINE( --what goes here ? );
END LOOP;
END;

salary
emp_curs.salary
emp_rec.salary (*)
employees.salary
emp_rec.salary IN emp_curs

46. When using a cursor FOR loop, OPEN, CLOSE and FETCH statements should not be explicitly coded. True or False?
True (*)
False

47. What is wrong with the following code?
DECLARE
CURSOR dept_curs IS SELECT * FROM departments;
BEGIN
FOR dept_rec IN dept_curs LOOP
DBMS_OUTPUT.PUT_LINE(dept_curs%ROWCOUNT || dept_rec.department_name);
END LOOP;
DBMS_OUTPUT.PUT_LINE(dept_rec.department_id);
END;

The cursor DEPT_CURS has not been opened.
The implicitly declared record DEPT_REC cannot be referenced outside the cursor FOR loop. (*)
You cannot use %ROWCOUNT with a cursor FOR loop.
The cursor DEPT_CURS has not been closed.
Nothing is wrong, this code will execute successfully.

PLSQL Semester 1 Mid Term Exam - Part I

1.  PL/SQL is an Oracle proprietary, procedural, 4GL programming language. True or False?    
    True
    False (*)
     
  2.  PL/SQL extends SQL by including all of the following except:
    variables
    conditional statements
    reusable program units
    constants
    nonprocedural constructs (*)
     
  3.  Which of the following statements about PL/SQL and SQL is true?
    PL/SQL and SQL are both ANSI-compliant.
    PL/SQL and SQL can be used with many types of databases, including Oracle.
    PL/SQL and SQL are both Oracle proprietary programming languages.
    PL/SQL allows basic program logic and control flow to be combined with SQL statements. (*)
     
  4.  Using Oracle Application Express, you can create Web applications that include PL/SQL. True or False?    
    True (*)
    False
     
  5.  The fact that PL/SQL is portable is a good thing because:
    Exceptions can be ported to different operating systems
    Blocks can be sent to the operating system.
    PL/SQL code can be developed on one platform and deployed on another (*)
    PL/SQL code can be run on any operating system without a database
     
  6.  Which of the following can you use PL/SQL to do?
    Update data (DML)
    Develop Web applications using the Web Application Toolkit
    Manage database security
    Create customized reports
    All of the above (*)
     
  7.  Which of the following tools can NOT be used to develop and test PL/SQL code?
    Oracle Jdeveloper
    Oracle Application Express
    Oracle JSQL (*)
    Oracle iSQL*Plus
     
  8.  What kind of block is defined by the following PL/SQL code?
BEGIN
    DBMS_OUTPUT.PUT_LINE('My first quiz');
END;      
    procedure
    subroutine
    function
    anonymous (*)
     
  9.  Given below are the parts of a PL/SQL block:
1. END;
2. EXCEPTION
3. DECLARE
4. BEGIN
Arrange the parts in order.
   
    2,1,4,3
    3,4,2,1 (*)
    3,2,4,1
    4,3,2,1
   
  10.  Which lines of code will correctly display the message "The cat sat on the mat"? (Choose two.)
   (Choose all correct answers)
    DBMS_OUTPUT.PUT_LINE('The cat sat on the mat'); (*)
    DBMS_OUTPUT.PUT_LINE(The cat sat on the mat);
    DBMS_OUTPUT.PUT_LINE('The cat' || 'sat on the mat');
    DBMS_OUTPUT.PUT_LINE('The cat sat ' || 'on the mat'); (*)
   
  11.  What is the purpose of using DBMS_OUTPUT.PUT_LINE in a PL/SQL block?
    To perform conditional tests
    To allow a set of statements to be executed repeatedly
    To display results to check if our code is working correctly (*)
    To store new rows in the database
     
  12.  Errors are handled in the Exception part of the PL/SQL block. True or False?
    True (*)
    False
     
  13.  Type of a variable determines the range of values the variable can have and the set of operations that are defined for values of the type.    
    True (*)
    False
     
  14.  Which of these are PL/SQL data types? (Choose three.) (Choose all correct answers)
    Scalar (*)
    Identifier
    Delimiter
    Composite (*)
    LOB (*)
     
  15.  A movie is an example of which category of data type?    
    Scalar
    Composite
    Reference
    LOB (*)
     
  16.  Which of the following are valid identifiers? (Choose two.) (Choose all correct answers)
    yesterday (*)
    yesterday's date
    number_of_students_in_the_class
    v$testresult (*)
    #students
     
  17.  Which of the following are PL/SQL lexical units? (Choose two.) (Choose all correct answers)
    Identifiers (*)
    Table Columns
    Reserved Words (*)
    Anonymous Blocks
    SQL Workshop
     
  18.  Delimiters are _____ that have special meaning to the Oracle database.    
    identifiers
    variables
    symbols (*)

  19.  When nested blocks are used, which blocks can or must be labeled?    
    The inner block must be labeled, the outer block can be labeled.
    Both blocks must be labeled
    Nested blocks cannot be labeled
    The outer block must be labeled if it is to be referred to in the inner block. (*)
     
  20.  What will be displayed when the following block is executed?
<<outer>>
DECLARE
   v_myvar VARCHAR2(10) := 'Hello' ;
BEGIN
   <<inner>>    DECLARE
     v_myvar VARCHAR2(10) := 'World';
     BEGIN
     v_myvar := v_myvar || ' ' || outer.v_myvar;
   END;
   DBMS_OUTPUT.PUT_LINE(inner.v_myvar);
END;
 
    HelloWorld
    Hello World
    World
    The code will fail since the inner variable is not within the scope of the outer block. (*)
     
  21.  In the following code, Line A causes an exception. What value will be displayed when the code is executed?
DECLARE
    outer_var VARCHAR2(50) := 'My';
BEGIN
    outer_var := outer_var || ' name';
    DECLARE
       inner_var NUMBER;
    BEGIN
       inner_var := 'Mehmet'; -- Line A
       outer_var := outer_var || ' is';
    END;
    outer_var := outer_var || ' Zeynep';
EXCEPTION
    WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(outer_var);
END;
   
    My
    My name (*)
    My name is
    My name is Zeynep

  22.  What will be displayed when the following code is executed?
DECLARE
    x VARCHAR2(6) := 'Chang';
BEGIN
    DECLARE
       x VARCHAR2(12) := 'Susan';
    BEGIN
       x := x || x;
    END;
    DBMS_OUTPUT.PUT_LINE(x);
END;
   
    Susan
    Chang (*)
    ChangChang
    SusanChang
    The code will fail with an error
     
  23.  Examine the following code. Line A causes an exception. What will be displayed when the block is executed?
DECLARE
    var_a NUMBER := 6;
    var_b DATE;
BEGIN
    var_a := var_a * 2;
    var_b := '28 December 2006'; -- Line A
    var_a := var_a * 2;
EXCEPTION
    WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(var_a);
END;
   
    12 (*)
    24
    6
    Nothing will be displayed
     
  24.  To comment a single line of code, use two dashes after the comment. True or False?    
    True
    False (*)
   
  25.  Which of the following will help to make code easier to read?    
    Naming variables.
    Using %Type.
    Including comments in the code. (*)
     
  26.  Using standards for naming conventions is recommended. True or False?    
    True (*)
    False

  27.  If today's date is 14th June 2007, which statement will correctly convert today's date to the value: June 14, 2007 ?    
    TO_CHAR(sysdate)
    TO_DATE(sysdate)
    TO_DATE(sysdate,'Month DD, YYYY')
    TO_CHAR(sysdate, 'Month DD, YYYY') (*)
     
  28.  Examine the following code. What is the final value of V_MYBOOL ?
DECLARE
    v_mynumber NUMBER;
    v_mybool BOOLEAN ;
BEGIN
    v_mynumber := 6;
    v_mybool := (v_mynumber BETWEEN 10 AND 20);
    v_mybool := NOT (v_mybool);
END;
   
    True (*)
    False
     
  29.  Examine the following code. What is the final value of V_MYVAR ?
DECLARE
    v_myvar NUMBER;
BEGIN
    v_myvar := 1 + 2 * 3;
    v_myvar := v_myvar * 2;
END;
   
    81
    49
    14 (*)
    18
     
  30.  Single row character functions are valid SQL functions in PL/SQL. True or False?    
    True (*)
     False
     
  31.  The implicit data type conversion at Point A may not work correctly. Why not?
DECLARE
    v_mydate DATE;
BEGIN
    V_MYDATE := '29-Feb-04'; -- Point A
END;
   
    There are only 28 days in February
    Oracle cannot implicitly convert a character string to a date, even if the string contains a valid date value
     If the database language is not English, 'Feb' has no meaning. (*)
     V_MYDATE has been entered in uppercase

  32.  Examine the following code:
1 DECLARE
2 x NUMBER;
3 BEGIN
4 x:= '300';
5 END;
After line 4, what is the value of x?
   
    '300'
    300 (*)
    NULL

  33.  When you use a function to convert data types in a PL/SQL program, it is called ______ conversion.      
    Explicit (*)
    Implicit
    TO_CHAR
     
  34.  TO_NUMBER, TO_CHAR, and TO_DATE are all examples of:    
    Implicit conversion functions
    Explicit conversion functions (*)
    Character functions
    Operators
     
  35.  Assignment statements can continue over several lines in PL/SQL. True or False?    
    True (*)
    False
     
  36.  When a variable is defined using the NOT NULL keywords, the variable must contain a value. True or False?    
    True (*)
    False
     
  37.  Evaluate the following declaration. Determine whether or not it is legal.
DECLARE
maxsalary NUMBER(7) = 5000;
   
    Correct.
    Not correct. (*)
     
  38.  Variables can be used in the following ways in a PL/SQL block. (Choose two.) (Choose all correct answers)
     
    To store data values. (*)
    To rename tables and columns.
    To refer to a single data value several times. (*)
    To comment code.
     
  39.  A variable must have a value if NOT NULL is specified. True or False?    
    True (*)
    False
     
  40.  You need to declare a variable to hold a value which has been read from the SALARY column of the EMPLOYEES table. Which of the following is an advantage of declaring the variable as: employees.salary%TYPE ?    
    It is shorter than coding NUMBER(8,2)
    If the SALARY column is ALTERed later, the PL/SQL code need not be changed. (*)
    It executes much faster than using NUMBER(8,2)
    It allows the software to perform implicit data type conversions.
     
  41.  Which of the following declarations is invalid?    
    v_count PLS_INTEGER:=0;
    college_name VARCHAR2(20):='Harvard';
    v_pages CONSTANT NUMBER; (*)
    v_start_date DATE := sysdate+1;
   
  42.  Given this first section of code:
DECLARE
    v_result employees.salary%TYPE;
BEGIN
Which statement will always return exactly one value?
     
    SELECT salary
INTO v_result
FROM employees;
 
    SELECT salary
INTO v_result
FROM employees
WHERE last_name ='Smith';
 
    SELECT salary
INTO v_result
FROM employees
WHERE department_id = 80;
 
    SELECT SUM(salary)
INTO v_result
FROM employees; (*)

     
  43.  The following code will return the last name of the employee whose employee id is equal to 100: True or False?
DECLARE
    v_last_name employees.last_name%TYPE;
    employee_id employees.employee_id%TYPE := 100;
BEGIN
    SELECT last_name INTO v_last_name
    FROM employees
    WHERE employee_id = employee_id;
END;
   
    True
    False (*)
     
  44.  Which one of these SQL statements can be directly included in a PL/SQL executable block?
     
    SELECT last_name FROM employees
WHERE employee_id=100;
 
    DESCRIBE employees;
 
    UPDATE employees
SET last_name='Smith'; (*)

     DROP TABLE employees;
   
  45.  Which of the following is NOT a good guideline for retrieving data in PL/SQL?    
    Declare the receiving variables using %TYPE
    The WHERE clause is optional in nearly all cases. (*)
    Specify the same number of variables in the INTO clause as database columns in the SELECT clause.
    THE SELECT statement should fetch exactly one row.
     
  46.  The following anonymous block of code is run:
BEGIN
    INSERT INTO countries (id, name)
    VALUES ('XA', 'Xanadu');
    SAVEPOINT XA;
    INSERT INTO countries (id, name)
    VALUES ('NV','Neverland');
    COMMIT;
    ROLLBACK TO XA;
END;
What happens when the block of code finishes?
     
    No data is inserted and no errors occur.
    No data is inserted and an error occurs
    Two rows are inserted and no errors occur.
    Two rows are inserted and an error occurs. (*)
     
  47.  The following anonymous block of code is run:
BEGIN
    INSERT INTO countries (id, name)
    VALUES ('XA', 'Xanadu');
    INSERT INTO countries (id, name)
    VALUES ('NV','Neverland');
    COMMIT;
    COMMIT;
    ROLLBACK;
END;
What happens when the block of code finishes?
     
    You have nothing new; the last ROLLBACK undid the INSERTs.
    You have the rows added twice; there are four new rows.
    You have the two new rows added. (*)
    You get an error; you cannot COMMIT twice in a row.
     
  48.  Which SQL statement can NOT use an implicit cursor?    
    A DELETE statement
    An UPDATE statement
    A SELECT statement that returns multiple rows (*)
    A SELECT statement that returns one row
     
  49.  A PL/SQL block includes the following statement:
SELECT last_name INTO v_last_name
FROM employees
WHERE employee_id=100;
What is the value of SQL%ISOPEN immediately after the SELECT statement is executed?
   
    True
    False (*)
    Null
    Error. That attribute does not apply for implicit cursors.
     
  50.  There are no employees in Department 77. What will happen when the following block is executed?
BEGIN
DELETE FROM employees
WHERE department_id=77;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
END;
   
    A NO_DATA_FOUND exception is raised.
    A NULL is displayed.
    A zero (0) is displayed. (*)
    An exception is raised because the block does not contain a COMMIT statement.

PLSQL Semester 1 Mid Term Exam - Part I

 1.  PL/SQL is an Oracle proprietary, procedural, 4GL programming language. True or False?
    True
    False (*)
     
  2.  PL/SQL extends SQL by including all of the following except:
    variables
    conditional statements
    reusable program units
    constants
    nonprocedural constructs (*)
     
  3.  Which of the following statements about PL/SQL and SQL is true?
    PL/SQL and SQL are both ANSI-compliant.
    PL/SQL and SQL can be used with many types of databases, including Oracle.
    PL/SQL and SQL are both Oracle proprietary programming languages.
    PL/SQL allows basic program logic and control flow to be combined with SQL statements. (*)
     
  4.  Using Oracle Application Express, you can create Web applications that include PL/SQL. True or False?
    True (*)
    False
     
  5.  The fact that PL/SQL is portable is a good thing because:
    Exceptions can be ported to different operating systems
    Blocks can be sent to the operating system.
    PL/SQL code can be developed on one platform and deployed on another (*)
    PL/SQL code can be run on any operating system without a database
     
  6.  Which of the following can you use PL/SQL to do?
    Update data (DML)
    Develop Web applications using the Web Application Toolkit
    Manage database security
    Create customized reports
    All of the above (*)
     
  7.  Which of the following tools can NOT be used to develop and test PL/SQL code?
    Oracle Jdeveloper
    Oracle Application Express
    Oracle JSQL (*)
    Oracle iSQL*Plus
     
  8.  What kind of block is defined by the following PL/SQL code?
BEGIN
    DBMS_OUTPUT.PUT_LINE('My first quiz');
END;

    procedure
    subroutine
    function
    anonymous (*)
     
  9.  Given below are the parts of a PL/SQL block:
1. END;
2. EXCEPTION
3. DECLARE
4. BEGIN
Arrange the parts in order.
     
    2,1,4,3
    3,4,2,1 (*)
    3,2,4,1
    4,3,2,1

     
  10.  Which lines of code will correctly display the message "The cat sat on the mat"? (Choose two.)      
   (Choose all correct answers)
     
    DBMS_OUTPUT.PUT_LINE('The cat sat on the mat'); (*)
    DBMS_OUTPUT.PUT_LINE(The cat sat on the mat);
    DBMS_OUTPUT.PUT_LINE('The cat' || 'sat on the mat');
    DBMS_OUTPUT.PUT_LINE('The cat sat ' || 'on the mat'); (*)
     
  11.  What is the purpose of using DBMS_OUTPUT.PUT_LINE in a PL/SQL block?
     
    To perform conditional tests
    To allow a set of statements to be executed repeatedly
    To display results to check if our code is working correctly (*)
    To store new rows in the database
     
  12.  Errors are handled in the Exception part of the PL/SQL block. True or False?    
    True (*)
     False
     
  13.  Type of a variable determines the range of values the variable can have and the set of operations that are defined for values of the type.
    True (*)
    False
     
  14.  Which of these are PL/SQL data types? (Choose three.) (Choose all correct answers)
    Scalar (*)
    Identifier
    Delimiter
    Composite (*)
    LOB (*)
     
  15.  A movie is an example of which category of data type?
    Scalar
    Composite
    Reference
    LOB (*)
 
 16.  Which of the following are valid identifiers? (Choose two.) (Choose all correct answers)
    yesterday (*)
    yesterday's date
    number_of_students_in_the_class
    v$testresult (*)
    #students
 
 17.  Which of the following are PL/SQL lexical units? (Choose two.) (Choose all correct answers)
    Identifiers (*)
    Table Columns
    Reserved Words (*)
    Anonymous Blocks
    SQL Workshop
     
  18.  Delimiters are _____ that have special meaning to the Oracle database.    
    identifiers
    variables
    symbols (*)
   
  19.  When nested blocks are used, which blocks can or must be labeled?
    The inner block must be labeled, the outer block can be labeled.
    Both blocks must be labeled
    Nested blocks cannot be labeled
    The outer block must be labeled if it is to be referred to in the inner block. (*)
     
  20.  What will be displayed when the following block is executed?
<<outer>>
DECLARE
   v_myvar VARCHAR2(10) := 'Hello' ;
BEGIN
   <<inner>>    DECLARE
     v_myvar VARCHAR2(10) := 'World';
     BEGIN
     v_myvar := v_myvar || ' ' || outer.v_myvar;
   END;
   DBMS_OUTPUT.PUT_LINE(inner.v_myvar);
END;
   
    HelloWorld
    Hello World
    World
    The code will fail since the inner variable is not within the scope of the outer block. (*)
     
  21.  In the following code, Line A causes an exception. What value will be displayed when the code is executed?
DECLARE
    outer_var VARCHAR2(50) := 'My';
BEGIN
    outer_var := outer_var || ' name';
    DECLARE
       inner_var NUMBER;
    BEGIN
       inner_var := 'Mehmet'; -- Line A
       outer_var := outer_var || ' is';
    END;
    outer_var := outer_var || ' Zeynep';
EXCEPTION
    WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(outer_var);
END;
   
    My
    My name (*)
    My name is
    My name is Zeynep
     
  22.  What will be displayed when the following code is executed?
DECLARE
    x VARCHAR2(6) := 'Chang';
BEGIN
    DECLARE
       x VARCHAR2(12) := 'Susan';
    BEGIN
       x := x || x;
    END;
    DBMS_OUTPUT.PUT_LINE(x);
END;
   
    Susan
    Chang (*)
    ChangChang
    SusanChang
    The code will fail with an error
     
  23.  Examine the following code. Line A causes an exception. What will be displayed when the block is executed?
DECLARE
    var_a NUMBER := 6;
    var_b DATE;
BEGIN
    var_a := var_a * 2;
    var_b := '28 December 2006'; -- Line A
    var_a := var_a * 2;
EXCEPTION
    WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(var_a);
END;
   
    12 (*)
    24
    6
    Nothing will be displayed
     
  24.  To comment a single line of code, use two dashes after the comment. True or False?
    True
    False (*)
     
  25.  Which of the following will help to make code easier to read?
    Naming variables.
    Using %Type.
    Including comments in the code. (*)
     
  26.  Using standards for naming conventions is recommended. True or False?    
    True (*)
    False
 
  27.  If today's date is 14th June 2007, which statement will correctly convert today's date to the value: June 14, 2007 ?
    TO_CHAR(sysdate)
    TO_DATE(sysdate)
    TO_DATE(sysdate,'Month DD, YYYY')
    TO_CHAR(sysdate, 'Month DD, YYYY') (*)

  28.  Examine the following code. What is the final value of V_MYBOOL ?
DECLARE
    v_mynumber NUMBER;
    v_mybool BOOLEAN ;
BEGIN
    v_mynumber := 6;
    v_mybool := (v_mynumber BETWEEN 10 AND 20);
    v_mybool := NOT (v_mybool);
END;
   
    True (*)
    False
     
  29.  Examine the following code. What is the final value of V_MYVAR ?
DECLARE
    v_myvar NUMBER;
BEGIN
    v_myvar := 1 + 2 * 3;
    v_myvar := v_myvar * 2;
END;
   
    81
    49
    14 (*)
    18
 
 30.  Single row character functions are valid SQL functions in PL/SQL. True or False?
    True (*)
    False
   
  31.  The implicit data type conversion at Point A may not work correctly. Why not?
DECLARE
    v_mydate DATE;
BEGIN
    V_MYDATE := '29-Feb-04'; -- Point A
END;

    There are only 28 days in February
    Oracle cannot implicitly convert a character string to a date, even if the string contains a valid date value
    If the database language is not English, 'Feb' has no meaning. (*)
    V_MYDATE has been entered in uppercase
     
  32.  Examine the following code:
1 DECLARE
2 x NUMBER;
3 BEGIN
4 x:= '300';
5 END;
After line 4, what is the value of x?
   
    '300'
    300 (*)
    NULL
   
  33.  When you use a function to convert data types in a PL/SQL program, it is called ______ conversion.      
    Explicit (*)
    Implicit
    TO_CHAR
     
  34.  TO_NUMBER, TO_CHAR, and TO_DATE are all examples of:
    Implicit conversion functions
    Explicit conversion functions (*)
    Character functions
    Operators
     
  35.  Assignment statements can continue over several lines in PL/SQL. True or False?    
    True (*)
    False
   
  36.  When a variable is defined using the NOT NULL keywords, the variable must contain a value. True or False?    
    True (*)
    False
     
  37.  Evaluate the following declaration. Determine whether or not it is legal.
DECLARE
maxsalary NUMBER(7) = 5000;
    Correct.
    Not correct. (*)
     
  38.  Variables can be used in the following ways in a PL/SQL block. (Choose two.) (Choose all correct answers)
    To store data values. (*)
    To rename tables and columns.
    To refer to a single data value several times. (*)
    To comment code.
     
  39.  A variable must have a value if NOT NULL is specified. True or False?    
    True (*)
    False
     
  40.  You need to declare a variable to hold a value which has been read from the SALARY column of the EMPLOYEES table. Which of the following is an advantage of declaring the variable as: employees.salary%TYPE ?

    It is shorter than coding NUMBER(8,2)
    If the SALARY column is ALTERed later, the PL/SQL code need not be changed. (*)
    It executes much faster than using NUMBER(8,2)
    It allows the software to perform implicit data type conversions.