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

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

EMP


Name               Null?            Type
-------------      -----------      ---------
EMP_ID                              NUMBER(3)

EMP_NAME                            VARCHAR2(10)

SALARY                              NUMBER(10,2)

View Exhibit 2 and examine the PLSQL block of code.

SQL > SET SERVEROUTPUT ON

SQL > DECLARE

             TYPE EmpRectType IS RECORD (

             emp_name  VARCHAR2(30),

             salary NUMBER(8,2),

             FUNCTION highest_salary RETURN EmpRectType IS

                emp_info EmpRectType;

                CURSOR cur_emp_cursor IS

                            SELECT ename,sal

                            FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);

              BEGIN

                 FOR emp_info IN cur_emp_cursor

                 LOOP

                         RETURN emp_info;

                 END LOOP;

              END highest_salary;

         BEGIN

              DBMS_OUTPUT.PUT_LINE('Emp: ' || highest_salary().emp_name ||

              'earns the highest salary of ' || highest_salary().salary);

         END;

 /

What is the outcome?

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

EMP


Name               Null?            Type
-------------      -----------      ---------
EMP_ID                              NUMBER(3)

EMP_NAME                            VARCHAR2(10)

SALARY                              NUMBER(10,2)

View Exhibit 2 and examine the PLSQL block of code.

SQL > SET SERVEROUTPUT ON

SQL > DECLARE

             TYPE EmpRectType IS RECORD (

             emp_name  VARCHAR2(30),

             salary NUMBER(8,2),

             FUNCTION highest_salary RETURN EmpRectType IS

                emp_info EmpRectType;

                CURSOR cur_emp_cursor IS

                            SELECT ename,sal

                            FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);

              BEGIN

                 FOR emp_info IN cur_emp_cursor

                 LOOP

                         RETURN emp_info;

                 END LOOP;

              END highest_salary;

         BEGIN

              DBMS_OUTPUT.PUT_LINE('Emp: ' || highest_salary().emp_name ||

              'earns the highest salary of ' || highest_salary().salary);

         END;

 /

What is the outcome?

Which statements correctly describe the features of functions and procedures? Choose that to apply.
 

Which statements correctly describe the features of functions and procedures? Choose that to apply.
 

Examine the following package specification:

CREATE OR REPLACE PACKAGE comm_package
IS

   g_comm NUMBER := 10;

   PROCEDURE reset_comm (p_comm IN NUMBER);

END comm_package;

/


 User Jones starts his session and executes the following code at 9:01 AM:

 EXECUTE comm.package.g_comm := 15

 User Jones starts his session and executes the following code at 9:01 AM:

 EXECUTE comm.package.g_comm := 15

Examine the following package specification:

CREATE OR REPLACE PACKAGE comm_package
IS

   g_comm NUMBER := 10;

   PROCEDURE reset_comm (p_comm IN NUMBER);

END comm_package;

/


 User Jones starts his session and executes the following code at 9:01 AM:

 EXECUTE comm.package.g_comm := 15

 User Jones starts his session and executes the following code at 9:01 AM:

 EXECUTE comm.package.g_comm := 15

Examine the following PL/SQL code:

DECLARE 

     stock_price NUMBER := 9.73;
     
     net_earnings NUMBER := 0;

     pe-ratio NUMBER;

 BEGIN

      pe-ratio := stock_price / net_earnings;

      DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio);

  END;

 /

Which statement is true about the execution of the PL/SQL code?

Examine the following PL/SQL code:

DECLARE 

     stock_price NUMBER := 9.73;
     
     net_earnings NUMBER := 0;

     pe-ratio NUMBER;

 BEGIN

      pe-ratio := stock_price / net_earnings;

      DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio);

  END;

 /

Which statement is true about the execution of the PL/SQL code?

Examine the following PL/SQL code;

SQL > SET SERVEROUTPUT ON

SQL > DECLARE

      emp_name  employee.last_name%TYPE;

      emp_job employee.job_id%TYPE;

      CURSOR c1 IS

          SELECT last_nem ,job_id FROM employees

          WHERE job_id LIKE '%CLERK%' AND manager_id > 120 ;

      BEGIN

          FOR emp_name,emp_job IN c1 LOOP

                DBMS_OUTPUT.PUT_LINE('Name= ' || emp_name || ', Job = ' || emp_job);

          END LOOP;

      END;

      /

The execution of the code produces errors. Identify the error in the code.

Examine the following PL/SQL code;

SQL > SET SERVEROUTPUT ON

SQL > DECLARE

      emp_name  employee.last_name%TYPE;

      emp_job employee.job_id%TYPE;

      CURSOR c1 IS

          SELECT last_nem ,job_id FROM employees

          WHERE job_id LIKE '%CLERK%' AND manager_id > 120 ;

      BEGIN

          FOR emp_name,emp_job IN c1 LOOP

                DBMS_OUTPUT.PUT_LINE('Name= ' || emp_name || ', Job = ' || emp_job);

          END LOOP;

      END;

      /

The execution of the code produces errors. Identify the error in the code.

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

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

Examine the following DECLARE section of PL/SQL block:

DECLARE

   v_job_type VARCHAR2 := 'TEMP';

   v_startdate DATE := SYSDATE;

   v_enddate DATE := v_startdate + 10;

   c_tax_rate CONSTANT NUMBER(2) := 8.25;

   v_valid BOOLEAN NOT NULL DEFAULT TRUE;

Which line in the above declaration would generate an error?

Examine the following DECLARE section of PL/SQL block:

DECLARE

   v_job_type VARCHAR2 := 'TEMP';

   v_startdate DATE := SYSDATE;

   v_enddate DATE := v_startdate + 10;

   c_tax_rate CONSTANT NUMBER(2) := 8.25;

   v_valid BOOLEAN NOT NULL DEFAULT TRUE;

Which line in the above declaration would generate an error?

View the Exhibit and examine the code:

SQL > CREATE OR REPLACE PROCEDURE proc1 AS
      
      x CONSTANT BOOLEAN := TRUE;

      BEGIN

      IF x THEN

          DBMS_OUTPUT.PUT_LINE('TRUE');
      
      ELSE

          DBMS_OUTPUT.PUT_LINE('FALSE');

      END IF;

      END proc1;

      /


SQL > EXECUTE DBMS_WARNING.SET_WARNING_SETTING_STRING('DISABLE:ALL','SESSION');

SQL > CREATE OR REPLACE PROCEDURE compile_code(p_pkg_name VARCHAR2) IS

        v_warn_value VARCHAR2(200);
        
        v_compile_stat VARCHAR2(200) := 'ALTER PACKAGE ' || p_pkg_name || 'COMPLIE';
      BEGIN

        v_warn_value := DBMS_WARNING.GET_WARNING_SETTING_STRING;

        DBMS_WARNING.ADD_WARNING_SETTING_CAT('PERFORMANCE','ENABLE','SESSION');

        EXECUTE IMMEDIATE v_compile_stat;

        DBMS_WARNING.SET_WARNING_SETTING_STRING (v_warn_value,'SESSION');

      END;
       
     /

      

Which statement is true about the COMPILE_CODE procedure?

View the Exhibit and examine the code:

SQL > CREATE OR REPLACE PROCEDURE proc1 AS
      
      x CONSTANT BOOLEAN := TRUE;

      BEGIN

      IF x THEN

          DBMS_OUTPUT.PUT_LINE('TRUE');
      
      ELSE

          DBMS_OUTPUT.PUT_LINE('FALSE');

      END IF;

      END proc1;

      /


SQL > EXECUTE DBMS_WARNING.SET_WARNING_SETTING_STRING('DISABLE:ALL','SESSION');

SQL > CREATE OR REPLACE PROCEDURE compile_code(p_pkg_name VARCHAR2) IS

        v_warn_value VARCHAR2(200);
        
        v_compile_stat VARCHAR2(200) := 'ALTER PACKAGE ' || p_pkg_name || 'COMPLIE';
      BEGIN

        v_warn_value := DBMS_WARNING.GET_WARNING_SETTING_STRING;

        DBMS_WARNING.ADD_WARNING_SETTING_CAT('PERFORMANCE','ENABLE','SESSION');

        EXECUTE IMMEDIATE v_compile_stat;

        DBMS_WARNING.SET_WARNING_SETTING_STRING (v_warn_value,'SESSION');

      END;
       
     /

      

Which statement is true about the COMPILE_CODE procedure?

Which three statements are true about wrapping? Choose that to apply.
 

Which three statements are true about wrapping? Choose that to apply.
 

View the Exhibit and examine the package code created by SCOTT. The execute privilege on this package is granted to green.

CREATE OR REPLACE PACKAGE pkgl IS

     PRAGMA SERIALLY_REUSABLE;

     num NUMBER := 0;

     PROCEDURE init_pkg_state(n NUMBER);

     PROCEDURE print_pkg_state;

END pkgl;

/

CREATE OR REPLACE PACKAGE BODY pkg1 IS

  PRAGMA SERIALLY_REUSABLE;

  PROCEDURE init_pkg_state (n NUMBER) IS

  BEGIN

       pkg1.num := n;

       DBMS_OUTPUT.PUT_LINE('Num:' || pkg1.num);

 END;

 PROCEDURE print_pkg_state IS

  BEGIN

      DBMS_OUTPUT.PUT_LINE('Num: ' || pkg1.num);

  END;

END pkg1;

/

Examine the following sequence of commands issued by SCOTT:

SQL > SET SERVEROUTPUT ON

SQL > EXEC pkg1.init_pkg_state95)

SQL > EXEC pkg1.print_pkg_state


GREEN logs in and issues the following commands

SQL > SET SERVEROUPUT ON

SQL > EXEC scott.pkg1.print_pkg_state

What is the outcome?

View the Exhibit and examine the package code created by SCOTT. The execute privilege on this package is granted to green.

CREATE OR REPLACE PACKAGE pkgl IS

     PRAGMA SERIALLY_REUSABLE;

     num NUMBER := 0;

     PROCEDURE init_pkg_state(n NUMBER);

     PROCEDURE print_pkg_state;

END pkgl;

/

CREATE OR REPLACE PACKAGE BODY pkg1 IS

  PRAGMA SERIALLY_REUSABLE;

  PROCEDURE init_pkg_state (n NUMBER) IS

  BEGIN

       pkg1.num := n;

       DBMS_OUTPUT.PUT_LINE('Num:' || pkg1.num);

 END;

 PROCEDURE print_pkg_state IS

  BEGIN

      DBMS_OUTPUT.PUT_LINE('Num: ' || pkg1.num);

  END;

END pkg1;

/

Examine the following sequence of commands issued by SCOTT:

SQL > SET SERVEROUTPUT ON

SQL > EXEC pkg1.init_pkg_state95)

SQL > EXEC pkg1.print_pkg_state


GREEN logs in and issues the following commands

SQL > SET SERVEROUPUT ON

SQL > EXEC scott.pkg1.print_pkg_state

What is the outcome?

View the Exhibit to examine the PL/SQL code.

DECLARE

    emp_column     VARCHAR2(30) := 'last_name';

    table_name     VARCHAR2(30) :=  'emp';

    temp_var       VARCHAR2(300;

BEGIN

    temp_var := emp_column;

    SELECT COLUMN_NAME INTO temp_var FROM USER_TAB_COLS

          WHERE TABLE_NAME = 'EMPLOYEES'

          AND COLUMN_NAME = 'UPPER'(emp_column);

    temp_var := table_name;

    SELECT OBJECT_NAME INTO temp_var FROM USER_OBJECTS

        WHERE OBJECT_NAME = UPPER(table_name)

        AND OBJECT_TYPE = 'TABLE';

 EXCEPTION

       WHEN NO_DATA_FOUND THEN

         DBMS_OUTPUT.PUT_LINE


            ('No Data found for SELECT on' || temp_var);

  END;

  /

Which statement is true about the exception handlers in the PL/SQL code?

View the Exhibit to examine the PL/SQL code.

DECLARE

    emp_column     VARCHAR2(30) := 'last_name';

    table_name     VARCHAR2(30) :=  'emp';

    temp_var       VARCHAR2(300;

BEGIN

    temp_var := emp_column;

    SELECT COLUMN_NAME INTO temp_var FROM USER_TAB_COLS

          WHERE TABLE_NAME = 'EMPLOYEES'

          AND COLUMN_NAME = 'UPPER'(emp_column);

    temp_var := table_name;

    SELECT OBJECT_NAME INTO temp_var FROM USER_OBJECTS

        WHERE OBJECT_NAME = UPPER(table_name)

        AND OBJECT_TYPE = 'TABLE';

 EXCEPTION

       WHEN NO_DATA_FOUND THEN

         DBMS_OUTPUT.PUT_LINE


            ('No Data found for SELECT on' || temp_var);

  END;

  /

Which statement is true about the exception handlers in the PL/SQL code?

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

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

View the Exhibit to examine the PL/SQL code.

DECLARE

   type t_rec is record

        (v_sal number(8),

         v_minsal number(8) default 1000,

         v_hire_date employees.hire_date%type,

         v_recl employees%rowtype);

    v_myrec t_rec

 BEGIN

     v_myrec.v_sal := v_myrec.v_minsal + 500;

     v_myrec.v_hire_date := sysdate;

     SELECT * INTO v_myrec.v_rec1

           FROM employees WHERE employee_id = 100;

     DBMS_OUTPUT.PUT_LINE(v_myrec.v_rec1.last_name || ' ' ||
     
     to_char(v_myrec.v_hire_date) || ' ' || to_char(v_myrec.v_sal));

   END;

The record for the employee with employee_id 100 in the employees table is as follows:

SQL > SELECT employee_id,first_name,last_nem,

             email,hire_date,job_id,salary

      FROM employees

      WHERE employee_id = 100;

      
     EMPLOYEE_ID     FIRST_NAME     LAST_NAME      EMAIL    HIRE_DATE    JOB_ID    SALARY
     --------------  -------------- -------------  -------  -----------  --------- -----------
      100             Steven           King          SKING   17-JUN-87    AD_PRES   24000

Identify the correct output for the code.

View the Exhibit to examine the PL/SQL code.

DECLARE

   type t_rec is record

        (v_sal number(8),

         v_minsal number(8) default 1000,

         v_hire_date employees.hire_date%type,

         v_recl employees%rowtype);

    v_myrec t_rec

 BEGIN

     v_myrec.v_sal := v_myrec.v_minsal + 500;

     v_myrec.v_hire_date := sysdate;

     SELECT * INTO v_myrec.v_rec1

           FROM employees WHERE employee_id = 100;

     DBMS_OUTPUT.PUT_LINE(v_myrec.v_rec1.last_name || ' ' ||
     
     to_char(v_myrec.v_hire_date) || ' ' || to_char(v_myrec.v_sal));

   END;

The record for the employee with employee_id 100 in the employees table is as follows:

SQL > SELECT employee_id,first_name,last_nem,

             email,hire_date,job_id,salary

      FROM employees

      WHERE employee_id = 100;

      
     EMPLOYEE_ID     FIRST_NAME     LAST_NAME      EMAIL    HIRE_DATE    JOB_ID    SALARY
     --------------  -------------- -------------  -------  -----------  --------- -----------
      100             Steven           King          SKING   17-JUN-87    AD_PRES   24000

Identify the correct output for the code.

DECLARE

      jobid employees.job_id%TYPE;

      empid employees.employee_id%TYPE := 115;

      sal employees.salary%TYPE;

      sal_raise NUMBER(3,2);

BEGIN
  
      SELECT job_id,salary INTO jobid,sal from employees

      WHERE employee_id = empid;

         CASE

         WHEN jobid = 'PU_CLERK' THEN

             IF sal < 3000 THEN sal_raise := .12;

                 ELSE sal_raise := .09;

             END IF;

          WHEN jobid = 'SH_CLERK' THEN

             IF sal < 4000 THEN sal_raise := .11;

                 ELSE sal_raise := .08;

             END IF;

          WHEN jobid = 'ST_CLERK' THEN

             IF sal < 3500 THEN sal_raise := .10;

                 ELSE sal_raise := .07;

             END IF;
   
             BEGIN

                  DBMS_OUTPUT.PUT_LINE9'No raise for this job:' || jobid);


             END;

             END CASE;

         UPDATE employees SET salary = salary + salary + salary + sal_raise

         WHERE employee_id = empid;

       COMMIT;

   END;

        

SERVEROUTPUT is on for the session.

Which statement is true about the execution of the code?

DECLARE

      jobid employees.job_id%TYPE;

      empid employees.employee_id%TYPE := 115;

      sal employees.salary%TYPE;

      sal_raise NUMBER(3,2);

BEGIN
  
      SELECT job_id,salary INTO jobid,sal from employees

      WHERE employee_id = empid;

         CASE

         WHEN jobid = 'PU_CLERK' THEN

             IF sal < 3000 THEN sal_raise := .12;

                 ELSE sal_raise := .09;

             END IF;

          WHEN jobid = 'SH_CLERK' THEN

             IF sal < 4000 THEN sal_raise := .11;

                 ELSE sal_raise := .08;

             END IF;

          WHEN jobid = 'ST_CLERK' THEN

             IF sal < 3500 THEN sal_raise := .10;

                 ELSE sal_raise := .07;

             END IF;
   
             BEGIN

                  DBMS_OUTPUT.PUT_LINE9'No raise for this job:' || jobid);


             END;

             END CASE;

         UPDATE employees SET salary = salary + salary + salary + sal_raise

         WHERE employee_id = empid;

       COMMIT;

   END;

        

SERVEROUTPUT is on for the session.

Which statement is true about the execution of the code?

View the Exhibit and examine the structure of the customer table.

You need to create a trigger to ensure that customers in category “A” and “B” have a credit limit ofmore than 8000.

Examine the following trigger.

CREATE OR REPLACE TRIGGER verify_cust_category

      BEFORE INSERT ON customer

      BEGIN

          IF: NEW.cust_category IN ('A','B') AND :NEW .cust_credit_limit < 8000 THEN

          RAISE_APPLICATION_ERROR (-20202,'Credit Limit cannot be less than 8000');

          END IF;

 END;

 /

Which statement is correct about the outcome of this trigger?

View the Exhibit and examine the structure of the customer table.

You need to create a trigger to ensure that customers in category “A” and “B” have a credit limit ofmore than 8000.

Examine the following trigger.

CREATE OR REPLACE TRIGGER verify_cust_category

      BEFORE INSERT ON customer

      BEGIN

          IF: NEW.cust_category IN ('A','B') AND :NEW .cust_credit_limit < 8000 THEN

          RAISE_APPLICATION_ERROR (-20202,'Credit Limit cannot be less than 8000');

          END IF;

 END;

 /

Which statement is correct about the outcome of this trigger?

Which statements are true about the WHEN OTHERS exception handler? Choose that to apply.
 

Which statements are true about the WHEN OTHERS exception handler? Choose that to apply.
 

Examine the following package specification:

CREATE OR REPLACE PACKAGE comm_package

IS

   g_comm NUMBER := 10;

   PROCEDURE reset_comm (p_comm IN NUMBER);

END comm_package;

/


User Jones starts his session and executes the following code at 9:01 AM:

EXECUTE comm_package.g_comm := 15


User Smith starts his session and executes the following code at 9:05 AM:

EXECUTE comm_package.g_comm := 20

Which statement is true?

Examine the following package specification:

CREATE OR REPLACE PACKAGE comm_package

IS

   g_comm NUMBER := 10;

   PROCEDURE reset_comm (p_comm IN NUMBER);

END comm_package;

/


User Jones starts his session and executes the following code at 9:01 AM:

EXECUTE comm_package.g_comm := 15


User Smith starts his session and executes the following code at 9:05 AM:

EXECUTE comm_package.g_comm := 20

Which statement is true?

Which two statements are true about the exit statement encountered in loop?  Choose that to apply.
 

Which two statements are true about the exit statement encountered in loop?  Choose that to apply.
 

You create a procedure to handle the processing of bank current accounts which rolls back
payment transactions if the overdraft limit is exceeded.

The procedure should return an "error" condition to the caller in a manner consistent with other
Oracle server errors.

Which construct should be used to handle this requirement?

You create a procedure to handle the processing of bank current accounts which rolls back
payment transactions if the overdraft limit is exceeded.

The procedure should return an "error" condition to the caller in a manner consistent with other
Oracle server errors.

Which construct should be used to handle this requirement?

You execute the following block of code:

SQL > SET SERVEROUPUT ON

SQL > DECLARE

        v_customer VARCHAR2(50) := 'womansport';
           
        v_credit_rating VARCHAR2(50) := 'EXCELLENT';

        BEGIN

            DECLARE
     
        v_customer NUMBER(7) := 201;
  
        v_name VARCHAR2(25) := 'Unisports';

        BEGIN

            v_credit_rating := 'GOOD';

            DBMS_OUTPUT.PUT_LINE('Customer' || v_customer || 'rating is ' ||  v_credit_rating);

        END;

        /

Which statement is true about the outcome?

You execute the following block of code:

SQL > SET SERVEROUPUT ON

SQL > DECLARE

        v_customer VARCHAR2(50) := 'womansport';
           
        v_credit_rating VARCHAR2(50) := 'EXCELLENT';

        BEGIN

            DECLARE
     
        v_customer NUMBER(7) := 201;
  
        v_name VARCHAR2(25) := 'Unisports';

        BEGIN

            v_credit_rating := 'GOOD';

            DBMS_OUTPUT.PUT_LINE('Customer' || v_customer || 'rating is ' ||  v_credit_rating);

        END;

        /

Which statement is true about the outcome?