PRO - PL SQL Programming - 3 : Question 1 of 20

Examine the following snippet of PL/SQL code:

DECLARE
   
   emp_job    employees.job_id%TYPE := 'ST_CLERK';

   emp_salary employees.salary%TYPE :=  3000 ;

   my_record employees%ROWTYPE;

   CURSON c1 (job VARCHAR2,max_wage NUMBER) IS

      SELECT * FROM employees

         WHERE job_id = job

         AND salary > max_wage;


BEGIN
............


​
 Name             Null?           Type
     -------------    -------------   -----------

     EMPLOYEE_ID      NOT NULL        NUMBER(6)

     FIRST_NAME                       VARCHAR2(20)

     LAST_NAME        NOT NULL        VARCHAR2(25)

     EMAIL            NOT NULL        VARCHAR2(25)

     PHONE_NUMBER                     VARCHAR2(20)

     HIRE_DATE        NOT NULL        DATE

     JOB_ID           NOT NULL        VARCHAR2(10)

     SALARY                           NUMBER(8,2)

     COMISSION_PCT                    NUMBER(2,2)

     MANAGER_ID                       NUMBER(6)

     DEPARTMENT_ID                    NUMBER(4)


 
      

Identify open statement for opening the cursor that fetches the result as consisting of employees with JOB_ID as ‘ST_CLERK’ and salary greater than 3000.

Examine the following snippet of PL/SQL code:

DECLARE
   
   emp_job    employees.job_id%TYPE := 'ST_CLERK';

   emp_salary employees.salary%TYPE :=  3000 ;

   my_record employees%ROWTYPE;

   CURSON c1 (job VARCHAR2,max_wage NUMBER) IS

      SELECT * FROM employees

         WHERE job_id = job

         AND salary > max_wage;


BEGIN
............


​
 Name             Null?           Type
     -------------    -------------   -----------

     EMPLOYEE_ID      NOT NULL        NUMBER(6)

     FIRST_NAME                       VARCHAR2(20)

     LAST_NAME        NOT NULL        VARCHAR2(25)

     EMAIL            NOT NULL        VARCHAR2(25)

     PHONE_NUMBER                     VARCHAR2(20)

     HIRE_DATE        NOT NULL        DATE

     JOB_ID           NOT NULL        VARCHAR2(10)

     SALARY                           NUMBER(8,2)

     COMISSION_PCT                    NUMBER(2,2)

     MANAGER_ID                       NUMBER(6)

     DEPARTMENT_ID                    NUMBER(4)


 
      

Identify open statement for opening the cursor that fetches the result as consisting of employees with JOB_ID as ‘ST_CLERK’ and salary greater than 3000.

Which tasks must be performed during the installation of the UTL_MAIL package? Choose that to apply.

Which tasks must be performed during the installation of the UTL_MAIL package? Choose that to apply.

View Exhibit 1 and examine the structure of the employees table.

Name                      Null ?                    Type
---------------------     ---------------           ----------------
EMPLOYEE_ID               NOT NULL                  NUMBER(6)

FIRST_NAME                                          VARCHAR2(20)

LAST_NAME                 NOT NULL                  VARCHAR2(25)

HIRE_DATE                 NOT NULL                  DATE

JOB_ID                    NOT NULL                  VARCHAR2(10)

SALARY                                              NUMBER(8,2)

COMISSION_PCT                                       NUMBER(2,2)

MANAGER_ID                                          NUMBER(6)

DEPARTMENT_ID                                       NUMBER(4)

User SCOTT needs to generate a text report that contains the names of all employees and their salaries.

Examine the following commands issued by the DBA

SQL_CREATE DICTORY my_dir AS '/temp/my_files* ;

SQL_GRANT WRITE ON DIRECTORY my_dir TO SCOTT;

View Exhibit 2 and examine the procedure code. You issue the following command:

CREATE OR REPLACE PROCEDURE sal_status(p_dir IN VARCHAR2,p_filename IN VARCHAR2) IS

      f_file UTL_FILE,FILE_TYPE;

      CURSOR cur_emp IS

            SELECT last_name,salary FROM employees ORDER BY salary

      BEGIN

             f_file := UTL_FILE.FOPEN (p_dir,p_filename, 'W');

             UTL_FILE.PUT_LINE(f_file,'REPORT: GENERATED ON' || SYSDATE);

             FOR emp_rec IN cur_emp LOOP

                 UTL_FILE.PUT_LINE(f_file,'EMPLOYEE: ' || emp_rec.last_name || 
                          'earns:' || emp_rec.salart);

             END LOOP;

             UTL_FILE,FCLOSE(f_file);

             EXCEPTION

                  WHEN UTL_FILE.INVALID_FILEHANDLE THEN

                  RAISE_APPLICATION_ERROR(-20100,'Invalid File.');

                  WHEN UTL_FILE.WRITE_ERROR THEN

                  RAISE_APPLICATION_ERROR(-20002,'Unable to write to file');

        END sal_status;

You issue the following command:

SQL_EXEC sal_5tatus ('MY_DIR', ‘EMPREPORT.TXT')

What is the outcome ?

View Exhibit 1 and examine the structure of the employees table.

Name                      Null ?                    Type
---------------------     ---------------           ----------------
EMPLOYEE_ID               NOT NULL                  NUMBER(6)

FIRST_NAME                                          VARCHAR2(20)

LAST_NAME                 NOT NULL                  VARCHAR2(25)

HIRE_DATE                 NOT NULL                  DATE

JOB_ID                    NOT NULL                  VARCHAR2(10)

SALARY                                              NUMBER(8,2)

COMISSION_PCT                                       NUMBER(2,2)

MANAGER_ID                                          NUMBER(6)

DEPARTMENT_ID                                       NUMBER(4)

User SCOTT needs to generate a text report that contains the names of all employees and their salaries.

Examine the following commands issued by the DBA

SQL_CREATE DICTORY my_dir AS '/temp/my_files* ;

SQL_GRANT WRITE ON DIRECTORY my_dir TO SCOTT;

View Exhibit 2 and examine the procedure code. You issue the following command:

CREATE OR REPLACE PROCEDURE sal_status(p_dir IN VARCHAR2,p_filename IN VARCHAR2) IS

      f_file UTL_FILE,FILE_TYPE;

      CURSOR cur_emp IS

            SELECT last_name,salary FROM employees ORDER BY salary

      BEGIN

             f_file := UTL_FILE.FOPEN (p_dir,p_filename, 'W');

             UTL_FILE.PUT_LINE(f_file,'REPORT: GENERATED ON' || SYSDATE);

             FOR emp_rec IN cur_emp LOOP

                 UTL_FILE.PUT_LINE(f_file,'EMPLOYEE: ' || emp_rec.last_name || 
                          'earns:' || emp_rec.salart);

             END LOOP;

             UTL_FILE,FCLOSE(f_file);

             EXCEPTION

                  WHEN UTL_FILE.INVALID_FILEHANDLE THEN

                  RAISE_APPLICATION_ERROR(-20100,'Invalid File.');

                  WHEN UTL_FILE.WRITE_ERROR THEN

                  RAISE_APPLICATION_ERROR(-20002,'Unable to write to file');

        END sal_status;

You issue the following command:

SQL_EXEC sal_5tatus ('MY_DIR', ‘EMPREPORT.TXT')

What is the outcome ?

View the Exhibits and examine the structure of the EMPLOYEES, DEPARTMENTS AND EMP_BY_DEPT tables.

View the Exhibits and examine the structure of the EMPLOYEES, DEPARTMENTS AND EMP_BY_DEPT tables.

Which two statements are true about the handling of internally defined or user-defined PL/SQL exceptions? Choose that to apply.

Which two statements are true about the handling of internally defined or user-defined PL/SQL exceptions? Choose that to apply.

Which two statements are true about triggers? Choose that to apply.
 

Which two statements are true about triggers? Choose that to apply.
 

You want to maintain an audit of the date and time when each user of the database logs off.

Examine the following code:

SQL > CREATE TABLE log_trig_table(
user_id VARCHAR2(30),
log_date TIMESTAMP,
action VARCHAR2(40));


SQL > CREATE OR REPLACE TRIGGER logoff_trig

____________  ____________

BEGIN

INSERT INTO log_trig_table(user_id,log_date,action)

VALUES(USER,SYSDATE,'Logging off');

END;

Which two clauses should be used to fill in the blanks and complete the above code? Choose that to apply.

You want to maintain an audit of the date and time when each user of the database logs off.

Examine the following code:

SQL > CREATE TABLE log_trig_table(
user_id VARCHAR2(30),
log_date TIMESTAMP,
action VARCHAR2(40));


SQL > CREATE OR REPLACE TRIGGER logoff_trig

____________  ____________

BEGIN

INSERT INTO log_trig_table(user_id,log_date,action)

VALUES(USER,SYSDATE,'Logging off');

END;

Which two clauses should be used to fill in the blanks and complete the above code? Choose that to apply.

Consider the following scenario:

Local procedure a calls remote procedure B.

Procedure A was compiled at 8 AM.

Procedure A was modified and recompiled at 9 AM.

Remote procedure B was later modified and recompiled at 11 AM.

The dependency mode is set to timestamp.

Which statement correctly describes what happens when procedure A is invoked at 1 PM?

Consider the following scenario:

Local procedure a calls remote procedure B.

Procedure A was compiled at 8 AM.

Procedure A was modified and recompiled at 9 AM.

Remote procedure B was later modified and recompiled at 11 AM.

The dependency mode is set to timestamp.

Which statement correctly describes what happens when procedure A is invoked at 1 PM?

Which two statements are true about PL/SQL exception propagation? Choose that to apply.

Which two statements are true about PL/SQL exception propagation? Choose that to apply.

View Exhibit 1 and examine the structure of the EMP table.

SQL > desc emp

Name                     Null?               Type
--------------------     ---------------     ----------
EMPNO                    NOT NULL            NUMBER(4)

ENAME                                        VARCHAR2(10)

JOB                                          VARCHAR2(9)

MGR                                          NUMBER(4)

HIREDATE                                     DATE

SAL                                          NUMBER(7,2)

COMM                                         NUMBER(7,2)

DEPTNO                                       NUMBER(2)

View Exhibit 2 and examine the code created by  the user SCOTT

CREATE OR REPLACE PACKAGE curs_pkg IS

   PROCEDURE open;

  PROCEDURE next(p_n NUMBER := 1);

 PROCEDURE close;\

END curs_pkg;

/



CREATE OR REPLACE PACKAGE BODY curs_pkg IS
  
   CURSOR cur_c IS

        SELECT empno FROM emp;

   PROCEDURE open IS

   BEGIN

        IF NOT cur_c%ISOPEN THEN

            OPEN cur_c;

        END IF;

    END open;
    
    PROCEDURE next(p_n NUMBER :=1) IS

      v_emp_id emp.empno%TYPE

      BEGIN

         FOR count IN 1..P_n LOOP

             FETCH cur_c INTO v_emp_id;

             EXIT WHEN cur_c%NOTFOUND;

             DBMS_OUTPUT.PUT_LINE('Id:' || (v_emp_id));

          END LOOP;

     END next;

     PROCEDURE close IS

         BEGIN 

             IF cur_c%ISOPEN THEN

                 CLOSE cur_c;

             END IF;

      END Close;
     
     END cure_pkg;

     /

              

SCOTT grants the necessary privileges to green to access the EMP table and execute the package.

Examine the following sequence of activities:

SCOTT starts a session and issues the SQL>EXEC CURS_PKG.OPEN command.

SCOTT then issues the SQL>EXEC CURS_PKG.NEXT command.

green starts a session while SCOTT’s session is running and issues THE SQL>EXEC

CURS_PKG.NEXT command.

SCOTT issues the SQI>>EXEC SCOTT.CURS_PKG.NEXT command.

The EMP table contains sequential EMPNOS from 100 through 108.

Which statement correctly describes the output?

View Exhibit 1 and examine the structure of the EMP table.

SQL > desc emp

Name                     Null?               Type
--------------------     ---------------     ----------
EMPNO                    NOT NULL            NUMBER(4)

ENAME                                        VARCHAR2(10)

JOB                                          VARCHAR2(9)

MGR                                          NUMBER(4)

HIREDATE                                     DATE

SAL                                          NUMBER(7,2)

COMM                                         NUMBER(7,2)

DEPTNO                                       NUMBER(2)

View Exhibit 2 and examine the code created by  the user SCOTT

CREATE OR REPLACE PACKAGE curs_pkg IS

   PROCEDURE open;

  PROCEDURE next(p_n NUMBER := 1);

 PROCEDURE close;\

END curs_pkg;

/



CREATE OR REPLACE PACKAGE BODY curs_pkg IS
  
   CURSOR cur_c IS

        SELECT empno FROM emp;

   PROCEDURE open IS

   BEGIN

        IF NOT cur_c%ISOPEN THEN

            OPEN cur_c;

        END IF;

    END open;
    
    PROCEDURE next(p_n NUMBER :=1) IS

      v_emp_id emp.empno%TYPE

      BEGIN

         FOR count IN 1..P_n LOOP

             FETCH cur_c INTO v_emp_id;

             EXIT WHEN cur_c%NOTFOUND;

             DBMS_OUTPUT.PUT_LINE('Id:' || (v_emp_id));

          END LOOP;

     END next;

     PROCEDURE close IS

         BEGIN 

             IF cur_c%ISOPEN THEN

                 CLOSE cur_c;

             END IF;

      END Close;
     
     END cure_pkg;

     /

              

SCOTT grants the necessary privileges to green to access the EMP table and execute the package.

Examine the following sequence of activities:

SCOTT starts a session and issues the SQL>EXEC CURS_PKG.OPEN command.

SCOTT then issues the SQL>EXEC CURS_PKG.NEXT command.

green starts a session while SCOTT’s session is running and issues THE SQL>EXEC

CURS_PKG.NEXT command.

SCOTT issues the SQI>>EXEC SCOTT.CURS_PKG.NEXT command.

The EMP table contains sequential EMPNOS from 100 through 108.

Which statement correctly describes the output?

Examine the following partial code:

CREATE OR REPLACE PACKAGE calc_income IS

          v_taxrate NUMBER := 100;

          PROCEDURE cal_tax (p_empno NUMBER);

          PROCEDURE calc_sal (p_empno NUMBER);

END calc_income

/

CREATE OR REPLACE PACKAGE BODY cal_income IS

           
          PROCEDURE cal_tax (p_empno NUMBER)

          ..........

          END cal_tax;

          PROCEDURE calc_sal (p_empno NUMBER)

          ..........

          END cal_sal;

          BEGIN

               SELECT rate_value INTO v_taxrate

               FROM tax_rates

               WHERE year = 2009;

 END calc_income;

 /

Which statement is correct about the unnamed block of code at the end of a package body?

Examine the following partial code:

CREATE OR REPLACE PACKAGE calc_income IS

          v_taxrate NUMBER := 100;

          PROCEDURE cal_tax (p_empno NUMBER);

          PROCEDURE calc_sal (p_empno NUMBER);

END calc_income

/

CREATE OR REPLACE PACKAGE BODY cal_income IS

           
          PROCEDURE cal_tax (p_empno NUMBER)

          ..........

          END cal_tax;

          PROCEDURE calc_sal (p_empno NUMBER)

          ..........

          END cal_sal;

          BEGIN

               SELECT rate_value INTO v_taxrate

               FROM tax_rates

               WHERE year = 2009;

 END calc_income;

 /

Which statement is correct about the unnamed block of code at the end of a package body?

Which two statements correctly differentiate functions and procedures? Choose that to apply.

Which two statements correctly differentiate functions and procedures? Choose that to apply.

Which two statements are true about the %ROWTYPE attribute? Choose that to apply.
 

Which two statements are true about the %ROWTYPE attribute? Choose that to apply.
 

View the Exhibit to examine the PL/SQL block.

SQL > CREATE TABLE employees_temp(

       empid NUMBER(6) NOT NULL PRIMARY KEY,

       deptid NUMBER(6) CONSTRAINT c_employees_temp_deptid

        CHECK (deptid BETWEEN 100 AND 200),

       deptname VARCHAR2(30) DEFAULT 'Sales');

Table created.

SQL > DECLARE

         emprec employees_temp%ROWTYPE;

      BEGIN

          emprec.empid := NULL;

          emprec.deptid := 50;

           DBMS_OUTPUT.PUT_LINE('emprec.deptname:' || emprec.deptname);


       END;

Which statement is true about the output of the PL/SQL block?

View the Exhibit to examine the PL/SQL block.

SQL > CREATE TABLE employees_temp(

       empid NUMBER(6) NOT NULL PRIMARY KEY,

       deptid NUMBER(6) CONSTRAINT c_employees_temp_deptid

        CHECK (deptid BETWEEN 100 AND 200),

       deptname VARCHAR2(30) DEFAULT 'Sales');

Table created.

SQL > DECLARE

         emprec employees_temp%ROWTYPE;

      BEGIN

          emprec.empid := NULL;

          emprec.deptid := 50;

           DBMS_OUTPUT.PUT_LINE('emprec.deptname:' || emprec.deptname);


       END;

Which statement is true about the output of the PL/SQL block?

Examine the following snippet of code from the DECLARE section of PL/SQL

Cust_name VERCHAR2 (20) NOT NULL : = ‘Tom Jones’:

Same_name cust_name%TYPE:

Which statement is correct about the above snippets of code?

Examine the following snippet of code from the DECLARE section of PL/SQL

Cust_name VERCHAR2 (20) NOT NULL : = ‘Tom Jones’:

Same_name cust_name%TYPE:

Which statement is correct about the above snippets of code?

View Exhibit 1 and examine the structure of the product table.

Name                Null?           Type
-----------------   --------------  -------------
PROD_ID             NOT NULL        NUMBER(4)

PROD_NAME           NOT NULL        VARCHAR2(10)

PROD_LIST_PRICE     NOT NULL        NUMBER(0,2)

PROD_VALID                          VARCHAR2(1)

View Exhibit 2 and examine the procedure you created.

CREATE OR REPLACE PROCEDURE check_price (p_prod_id) NUMBER IS

      v_price product.prod_list_price%type;

BEGIN
 
      SELECT prd_list_price INTO v_price

      FROM product

      WHERE prod_id = p_prod_id;

      IF v_price NOT BETWEEN 20 AND 30 THEN

           RAISE_APPLICATION_ERROR(-20100,'Price not in range');

      END IF;

END;

/

You then create the following trigger on the product table.
 

CREATE OR REPLACE TRIGGER check_price__trg

BEFORE INSERT OR UPDATE OF prod_id, prod_list_price

ON product FOR EACH ROW

WHEN (nev.prod_id <> NVX(old.prod_id,0) OR

New.prod__list_price <> NVL(old.prod_list_price, 0) )

BEGIN

check_price (: new.prod_id) ;

END

/

Examine the following update command for an existing row in the product table.

SQL> UPDATE produce SET prod_list_price = 10 WHERE prod_id=115;

Why does it generate an error?

View Exhibit 1 and examine the structure of the product table.

Name                Null?           Type
-----------------   --------------  -------------
PROD_ID             NOT NULL        NUMBER(4)

PROD_NAME           NOT NULL        VARCHAR2(10)

PROD_LIST_PRICE     NOT NULL        NUMBER(0,2)

PROD_VALID                          VARCHAR2(1)

View Exhibit 2 and examine the procedure you created.

CREATE OR REPLACE PROCEDURE check_price (p_prod_id) NUMBER IS

      v_price product.prod_list_price%type;

BEGIN
 
      SELECT prd_list_price INTO v_price

      FROM product

      WHERE prod_id = p_prod_id;

      IF v_price NOT BETWEEN 20 AND 30 THEN

           RAISE_APPLICATION_ERROR(-20100,'Price not in range');

      END IF;

END;

/

You then create the following trigger on the product table.
 

CREATE OR REPLACE TRIGGER check_price__trg

BEFORE INSERT OR UPDATE OF prod_id, prod_list_price

ON product FOR EACH ROW

WHEN (nev.prod_id <> NVX(old.prod_id,0) OR

New.prod__list_price <> NVL(old.prod_list_price, 0) )

BEGIN

check_price (: new.prod_id) ;

END

/

Examine the following update command for an existing row in the product table.

SQL> UPDATE produce SET prod_list_price = 10 WHERE prod_id=115;

Why does it generate an error?

View the exhibit and examine the structure of the EMPLOYEES table.

Name              Null?        Type
----------------  -----------  -----------
EMPLOYEE_ID       NOT NULL     NUMBER(6)

FIRST_NAME                     VARCHAR2(20)

LAST_NAME         NOT NULL     VARCHAR2(25)

HIRE_DATE         NOT NULL      DATE

JOB_ID            NOT NULL      VARCHAR2(10)

SALARY                          NUMBER(8,2)

COMISSION_PCT                   NUMBER(2,2)

MANAGER_ID                      NUMBER(6)

DEPARTMENT_ID                   NUMBER(4)

The salary of EMPLOYEE_ID 195 is 2800.

You execute the following code:

SQL > SET SERVEROUTPUT ON

SQL > DECLARE
 
      v_sal NUMBER(10,2) := 1000;

      BEGIN
  
          DBMS_OUTPUT.PUT_LINE('Salart is' || v_sal);

          DECLARE

               v_sal NUMBER;

               BEGIN

                  SELECT salary INTO v_sal FROM employees WHER employee_id = 195;
 
                  DBMS_OUTPUT.PUT_LINE ('Salary is ' || v_sal);

                  DECLARE

                      v_sal NUMBER := 50000;
   
                      BEGIN <>

                       DBMS_OUTPUT.PUT_LINE('Salary is' || v_sal);

                      END b3;

                      DBMS_OUTPUT_PUT_LINE('Salary is' || v_sal);

                  END;

              END;

/

What is the outcome?

View the exhibit and examine the structure of the EMPLOYEES table.

Name              Null?        Type
----------------  -----------  -----------
EMPLOYEE_ID       NOT NULL     NUMBER(6)

FIRST_NAME                     VARCHAR2(20)

LAST_NAME         NOT NULL     VARCHAR2(25)

HIRE_DATE         NOT NULL      DATE

JOB_ID            NOT NULL      VARCHAR2(10)

SALARY                          NUMBER(8,2)

COMISSION_PCT                   NUMBER(2,2)

MANAGER_ID                      NUMBER(6)

DEPARTMENT_ID                   NUMBER(4)

The salary of EMPLOYEE_ID 195 is 2800.

You execute the following code:

SQL > SET SERVEROUTPUT ON

SQL > DECLARE
 
      v_sal NUMBER(10,2) := 1000;

      BEGIN
  
          DBMS_OUTPUT.PUT_LINE('Salart is' || v_sal);

          DECLARE

               v_sal NUMBER;

               BEGIN

                  SELECT salary INTO v_sal FROM employees WHER employee_id = 195;
 
                  DBMS_OUTPUT.PUT_LINE ('Salary is ' || v_sal);

                  DECLARE

                      v_sal NUMBER := 50000;
   
                      BEGIN <>

                       DBMS_OUTPUT.PUT_LINE('Salary is' || v_sal);

                      END b3;

                      DBMS_OUTPUT_PUT_LINE('Salary is' || v_sal);

                  END;

              END;

/

What is the outcome?

Which two statements are true about the usage of the cursor for loops? Choose that to apply.

Which two statements are true about the usage of the cursor for loops? Choose that to apply.

Examine the following package specification.

SQL>CREATE OR REPLACE PACKAGE emp_pkf IS

PROCEDURE search_emp (empdet NUMBER);

PROCEDURE search_emp (empdet DATE);

PROCEDURE search_emp (empdet NUMBER); RETURN VERCHAR2

PROCEDURE search_emp (empdet NUMBER); RETURN DATE

END emp_pkg

/

Why would it generate an error at run tune?

Examine the following package specification.

SQL>CREATE OR REPLACE PACKAGE emp_pkf IS

PROCEDURE search_emp (empdet NUMBER);

PROCEDURE search_emp (empdet DATE);

PROCEDURE search_emp (empdet NUMBER); RETURN VERCHAR2

PROCEDURE search_emp (empdet NUMBER); RETURN DATE

END emp_pkg

/

Why would it generate an error at run tune?

Examine the following PL/SQL code:

DECLARE

    v_lname VARCHAR2(15);

BEGIN

     SELECT last_name INTO v_lname

         FROM employees

         WHERE first_name='John';

     IF v_lname is NULL THEN

           DBMS_OUTPUT.PUT_LINE('No Rows found');

     ELSE
           DBMS_OUTPUT.PUT_LINE('John's last name is :' || v_lname);

      END IF;

END;
     

              

Which statement is true about the execution of the code if the query in the PL/SQL block returns no rows?

Examine the following PL/SQL code:

DECLARE

    v_lname VARCHAR2(15);

BEGIN

     SELECT last_name INTO v_lname

         FROM employees

         WHERE first_name='John';

     IF v_lname is NULL THEN

           DBMS_OUTPUT.PUT_LINE('No Rows found');

     ELSE
           DBMS_OUTPUT.PUT_LINE('John's last name is :' || v_lname);

      END IF;

END;
     

              

Which statement is true about the execution of the code if the query in the PL/SQL block returns no rows?