PLSQL 1

1. PL/SQL can be used not only with an Oracle database, but also with any kind of relational database. True or False? (1) Points
True
False (*)

2. Which of the following tools can NOT be used to develop and test PL/SQL code? (1) Points
Oracle Jdeveloper
Oracle Application Express
Oracle JSQL (*)
Oracle iSQL*Plus

3. Which of the following statements about PL/SQL and SQL is true? (1) Points
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. Which of the following are PL/SQL lexical units? (Choose two.) (1) Points
Identifiers (*)
Table Columns
Reserved Words (*)
Anonymous Blocks
SQL Workshop

5. 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;
(1) Points

81
49
14 (*)
18

6. Which of the following should NOT be used as the name of a variable? (1) Points
A table name.
A table column name. (*)
The database name.

7. When nested blocks are used, which blocks can or must be labeled? (1) Points
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. (*)

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

9. A collection is a composite data type. True or False? (1) Points
True (*)
False

10. 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;
(1) Points

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.

Section 3

11. 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? (1) Points 

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.

12. A variable is declared as: 

DECLARE 

    v_salary employees.salary%TYPE; 

BEGIN 

Which of the following is a correct use of the INTO clause? (1) Points 

SELECT salary 

INTO v_salary 

FROM employees 

WHERE employee_id=100; 

(*)

SELECT v_salary 

INTO salary 

FROM employees 

WHERE employee_id=100; 

SELECT salary 

FROM employees 

INTO v_salary; 

SELECT salary 

FROM employees 

WHERE employee_id=100 

INTO v_salary; 

Section 4

13. What is the correct name for CASE, LOOP, WHILE, and IF-THEN-ELSE structures ? (1) Points 

Control structures (*)

Array structures

Memory structures

Cursor structures

14. 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;

(1) Points 

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.

15. You want to display multiplication tables for numbers up to 12. The display should look like this: 

1 x 1 = 1 

1 x 2 = 2 

..... 

1 x 12 = 12 

2 x 1 = 2 

2 x 2 = 4 

..... 

2 x 12 = 24 

3 x 1 = 3 

..... 

..... 

12 x 12 = 144 

Which of the following is an efficient way to do this in PL/SQL? (1) Points 

Use two nested FOR loops. (*)

Store all the numbers from 1 to 144 in a table, then fetch and display them using a cursor.

Create a function which accepts two numbers as IN parameters and returns their product. Invoke the function 144 times.

Write an anonymous block which contains 144 calls to DBMS_OUTPUT, each looking like: DBMS_OUTPUT.PUT_LINE('7 x 9 = 63');

16. 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; 

(1) Points 

High Paid

Low Paid

Null

The code will fail and return an exception (*)

17. 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? 

(1) Points 

5 (*)

6

1

This is an infinite loop; the loop will never finish.

Section 5

18. An implicit cursor can be used for a multiple-row SELECT statement. True or False? (1) Points 

True

False (*)

19. What is one of the advantages of using parameters with a cursor? (1) Points 

You can use a cursor FOR loop.

You can declare the cursor FOR UPDATE.

You do not need to DECLARE the cursor at all.

You can use a single cursor to fetch a different set of rows each time the cursor is opened. (*)

It will execute much faster than a cursor without parameters.

20. What is wrong with the following code? 

BEGIN 

    FOR emp_rec IN 

       (SELECT * FROM employees WHERE ROWNUM < 10 

          FOR UPDATE NOWAIT) LOOP 

        DBMS_OUTPUT.PUT_LINE(emp_rec%ROWCOUNT || emp_rec.last_name): 

    END LOOP; 

END; 

(1) Points 

You cannot use FOR UPDATE NOWAIT with a cursor FOR loop using a subquery.

You cannot reference %ROWCOUNT with a cursor FOR loop using a subquery. (*)

The field EMP_REC.LAST_NAME does not exist.

You cannot use ROWNUM with a cursor FOR loop.

The cursor has not been opened.

Section 5

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

22. 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? (1) Points
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.

23. 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;
(1) Points

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. (*)

Section 6

24. What will happen when the following code is executed?
BEGIN -- outer block
    DECLARE -- inner block
       CURSOR emp_curs IS SELECT * FROM employees;
       v_emp_rec emp_curs%ROWTYPE;
    BEGIN
       OPEN emp_curs;
       LOOP
          FETCH emp_curs INTO v_emp_rec;
          DBMS_OUTPUT.PUT_LINE(v_emp_rec.salary);
       END LOOP;
    END;
    CLOSE emp_curs;
END;
(1) Points

The code will fail because you cannot declare a cursor in an inner block.
The code will fail because the cursor is declared in the inner block but is referenced in the outer block. (*)
The code will execute successfully and display all the employees' salaries.
The code will execute forever because there is no statement to EXIT from the loop.

25. An attempt to insert a null value into a NOT NULL table column raises an ORA-01400 exception. How can you code an exception handler to trap this exception? (1) Points
Test for WHEN ORA-1400 in the exception section.
Declare a variable e_null_excep of type EXCEPTION, associate it with ORA-01400 using a PRAGMA directive, and test for WHEN e_null_excep in the exception section. (*)
Declare a variable e_null_excep of type VARCHAR2, associate it with ORA-01400 using a PRAGMA directive, and test for WHEN e_null_excep in the exception section.
Declare a variable as follows: e_null_excep EXCEPTION := -01400; Then test for WHEN e_null_excep in the exception section.

26. Department-id 99 does not exist. What will be displayed when the following code is executed?
DECLARE
    v_deptname departments.department_name%TYPE;
BEGIN
    SELECT department_name INTO v_deptname
       FROM departments WHERE department_id = 99;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
       RAISE_APPLICATION_ERROR(-20201,'Department does not exist');
END;
(1) Points

ORA-01403: No Data Found ORA-20201: Department does not exist
ORA-01403: No Data Found
ORA-20201: Department does not exist (*)
None of the above

27. Which of the following are good practice guidelines for exception handling? (Choose three.) (1) Points
(Choose all correct answers)
Test your code with different combinations of data to see what potential errors can happen. (*)
Use an exception handler whenever there is any possibility of an error occurring. (*)
Include a WHEN OTHERS handler as the first handler in the exception section.
Allow exceptions to propagate back to the calling environment.
Handle specific named exceptions where possible, instead of relying on WHEN OTHERS. (*)

Section 7

28. Suppose you set up a parameter with an explicit IN mode. What is true about that parameter? (1) Points
It must have a DEFAULT value.
It cannot have a DEFAULT value.
It acts like a constant (its value cannot be changed inside the subprogram). (*)
It must be the same type as the matching OUT parameter.
It inherits its type from the matching OUT parameter.

29. The following are the steps involved in creating, and later modifying and re-creating, a PL/SQL procedure in Application Express. In what sequence should these steps be performed?
A.Retrieve the saved code from "Saved SQL" in SQL Commands
B.Execute the code to create the procedure
C.Execute the code to re-create the procedure
D.Click on the "Save" button and save the procedure code
E.Modify the code in the SQL Commands window
F.Type the procedure code in the SQL Commands window
(1) Points

F,C,A,B,E,D
F,B,D,A,E,C (*)
E,D,F,C,A,B
F,B,D,E,A,C
F,B,C,D,E,A

30. Which of the following is NOT correct coding for a procedure parameter? (1) Points
(p_param IN VARCHAR2)
(p_param VARCHAR2)
(p_param VARCHAR2(50)) (*)
(p_param employees.last_name%TYPE)
(p_param IN OUT VARCHAR2)


31. Consider the following function:
CREATE FUNCTION ADD_EM
    (a NUMBER := 1,
    b NUMBER := 2 )
    RETURN NUMBER
IS BEGIN
    RETURN (a+b);
END ADD_EM;
Which one of the following blocks will NOT work correctly?
(1) Points

DECLARE
    x NUMBER;
BEGIN
    x:= add_em(b=4);
END;
(*)

DECLARE
    x NUMBER;
BEGIN
    x:= add_em(4);
END;

DECLARE
    x NUMBER;
BEGIN
    x:= add_em(4,5);
END;






DECLARE
    x NUMBER;
BEGIN
    x:= add_em;
END;

None of them will work.

32. How do you specify that you want a procedure MYPROCA to use "Definer's Rights"? (1) Points

CREATE OR REPLACE PROCEDURE myproca
AUTHID CURRENT_USER IS...

CREATE OR REPLACE PROCEDURE myproca
AUTHID OWNER IS...

GRANT DEFINER TO myprocA;

ALTER PROCEDURE myproca TO DEFINER;

Definer's Rights are the default, therefore no extra code or commands are needed. (*)

33. Which of the following are NOT allowed in a function which is used inside a SQL statement which updates the EMPLOYEES table? (Choose two). (1) Points
(Choose all correct answers)
SELECT .... FROM departments ....;
COMMIT; (*)
A RETURN statement.
DDL statements such as CREATE or ALTER. (*)
A WHEN OTHERS exception handler.

34. You want to remove the procedure NO_NEED from your schema. You execute:
DROP PROCEDURE no_need;
Which Data Dictionary views are updated automatically?
(1) Points

USER_PROCEDURES
USER_OBJECTS
USER_SOURCE
All of the above. (*)
None of the above.

Section 9

35. Examine the following package code:
CREATE OR REPLACE PACKAGE ol_pack IS
    PROCEDURE subprog (p1 IN VARCHAR2, p2 IN NUMBER);
    PROCEDURE subprog (param1 IN CHAR, param2 IN NUMBER);
    FUNCTION subprog (param1 IN VARCHAR2, param2 IN NUMBER) RETURN DATE;
END ol_pack;
Which of the following calls will be successful? (Choose two.) (1) Points
(Choose all correct answers)
ol_pack.subprog('Jane',30);
ol_pack.subprog(param1=>'Jane',param2=>30); (*)
v_number := ol_pack.subprog(p1=>'Jane');
v_date := ol_pack.subprog('Jane',30); (*)

36. Package CURSPACK declares a global cursor in the package specification. The package contains three public procedures: OPENPROC opens the cursor; FETCHPROC fetches 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
(1) Points
The first 15 rows will be fetched.
The first 10 rows will be fetched, then the first 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. (*)

37. Which of the following will display the detailed code of the subprograms in package DEPTPACK in your schema ? (1) Points

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;

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

38. Package MYPACK contains procedure MYPROC. You can see which parameters MYPROC uses by executing: DESCRIBE mypack.myproc. True or False? (1) Points
True
False (*)

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

40. The UTL_FILE package can be used to create binary files such as JPEGs as well as text files. True or False? (1) Points
True
False (*)


Section 10

41. Examine the following code:
CREATE TRIGGER emp_trigg
AFTER UPDATE OF salary ON employees
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN
    -- Line A
END;
Which of the following statements is NOT allowed at Line A?
(1) Points

SELECT count(*) INTO v_count FROM departments;
UPDATE employees SET job_id = 'IT_PROG' WHERE employee_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.

42. Examine the following trigger. It should raise an application error if a user tries to update an employee's last name. It should allow updates to all other columns of the EMPLOYEES table. What should be coded at line A?
CREATE TRIGGER stop_ln_trigg
BEFORE UPDATE ON employees
BEGIN
       -- Line A
    RAISE_APPLICATION_ERROR(-20201,'Updating last name not allowed');
    END IF;
END;
(1) Points

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

43. Which dictionary view shows the detailed code of a trigger body? (1) Points
USER_SOURCE
USER_TRIGGERS (*)
USER_OBJECTS
USER_DML_TRIGGERS
USER_SUBPROGRAMS

44. A trigger can be a public subprogram within a PL/SQL package. True or False? (1) Points
True
False (*)

45. Examine the following code:
CREATE TRIGGER emp_trigg
    -- Line A
BEGIN
    INSERT INTO log_table VALUES (USER, SYSDATE);
END;
Which of the following can NOT be coded at Line A?
(1) Points

BEFORE UPDATE ON employees
AFTER INSERT OR DELETE ON employees
AFTER SELECT ON employees (*)
BEFORE DELETE ON employees
AFTER UPDATE OF last_name ON employees

Section 11

46. Which of the following PL/SQL data structures can store a collection? (Choose two.) (1) Points
(Choose all correct answers)
An INDEX BY table (*)
A record
%ROWTYPE
An INDEX BY table of records (*)
A BLOB

47. The JOB_APPLICANTS table contains two columns:
(applicant_id NUMBER PRIMARY KEY,
resume CLOB)
For applicant_id 100, we want to modify the value of the RESUME column value from "I worked for Oracle" to "I worked for Oracle for five years".
Which of the following will do this successfully? (Choose two.) (1) Points
(Choose all correct answers)

UPDATE job_applicants
SET SUBSTR(resume, 21,14) = 'for five years'
WHERE candidate_id = 100;

UPDATE job_applicants
SET resume = 'I worked for Oracle for five years'
WHERE candidate_id = 100; (*)

DECLARE
    v_locator CLOB;
BEGIN
    v_locator := 'I worked for Oracle for five years';
    UPDATE job_applicants
    SET resume = DBMS_LOB.WRITE(v_locator)
    WHERE candidate_id = 100;
END;

DECLARE
    v_lobloc CLOB;
BEGIN
    SELECT resume INTO v_lobloc FROM job_applicants
       WHERE applicant_id = 100;
    DBMS_LOB.WRITE(v_lobloc,14,21,'for five years');
END;(*)

48. Package ED_PACK has declared a record type named ED_TYPE in the package specification. Which of the following anonymous blocks successfully declares a variable whose datatype is ED_TYPE? (1) Points

DECLARE
    v_ed_rec IS RECORD ed_pack.ed_type;
BEGIN ...

DECLARE
    v_ed_rec ed_pack.ed_type;
BEGIN ...
(*)

DECLARE
    v_ed_rec ed_pack.ed_type%ROWTYPE;
BEGIN...

DECLARE
    v_ed_rec ed_pack.ed_type%TYPE;
BEGIN ...

None of the above. Variables of datatype ED_TYPE can be declared only within ED_PACK, not in separate subprograms or anonymous blocks.

49. BFILEs are stored outside the database and can be queried and updated by using procedures in DBMS_LOB. True or False? (1) Points
True
False (*)

Section 12

50. A single procedure can be both a referenced object and a dependent object. True or False? (1) Points
True (*)
False