PRO - PL SQL Programming

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

EMPLOYEE_SEQ is an existing sequence.

Examine the following block of code:

BEGIN

   BEGIN

       INSERT INTO employees(employee_id,first_name,last_name,email,
 
                      hire_date,job_id,salary)

       VALUES(employees_seq.NEXTVAL,'Ruth',

       'Cores','RCORES',CURRENT_DATE,

            'AD_ASST',4000);

   END;

   BEGIN

       INSERT INTO employees (employee_id,first_name,last_name,email,

                       hire_date,job_id,salary)

       VALUES(employees_seq.NEXTVAL,'Tom','Jones','TJones',CURRENT_DATE,

                                          'AD_MGR',6000);

   END;

END;

/

Which statement is true about the above block of code?

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

EMPLOYEE_SEQ is an existing sequence.

Examine the following block of code:

BEGIN

   BEGIN

       INSERT INTO employees(employee_id,first_name,last_name,email,
 
                      hire_date,job_id,salary)

       VALUES(employees_seq.NEXTVAL,'Ruth',

       'Cores','RCORES',CURRENT_DATE,

            'AD_ASST',4000);

   END;

   BEGIN

       INSERT INTO employees (employee_id,first_name,last_name,email,

                       hire_date,job_id,salary)

       VALUES(employees_seq.NEXTVAL,'Tom','Jones','TJones',CURRENT_DATE,

                                          'AD_MGR',6000);

   END;

END;

/

Which statement is true about the above block of code?

1 Mark(s)

It consists of two transactions
It consists of a single transaction.
The data is automatically committed after the block execution ends.
It gives an error on execution because sequences cannot be used in anonymous blocks.

What are the methods used to navigate across a varray in the forward and reverse directions?

1 Mark(s)

BEFORE and AFTER
BEFORE and NEXT
FIRST and LAST
PRIOR and NEXT

View the Exhibit to examine the PL/SQL 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;

          ELSE

          BEGIN

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

          END;

          END CASE;

       UPDATE employees SET 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 to examine the PL/SQL 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;

          ELSE

          BEGIN

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

          END;

          END CASE;

       UPDATE employees SET 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?

1 Mark(s)

The execution fails because of the misplacedelseclause.
The execution is successful even if there is no employee withEMPLOYEE_ID 115.
The execution fails and throws exceptionsif no employee withEMPLOYEE_ID 115is found.
The execution is successful, but it displays an incorrect output if no employee withEMPLOYEE_ID 115is found.

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

Exhibit Missing

CUST_ID and CUST_LIMIT are existing columns in the CUSTOMER table.

Examine the following trigger code:

CREATE OR REPLACE TRIGGER audit_cust

AFTER UPDATE OF cust_credit_limit ON customer

FOR EACH ROW

BEGIN

     INSERT INTO audit_cust (user_name,change_time,cust_id,old_credit_limit,new_credit_limit)

     VALUES (USER,SYSDATE,:OLD.cust_id,:OLD.cust_credit_limit,:NEW.cust_credit_limit);

     COMMIT;

END;

/

Which statement is true about the above trigger?

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

Exhibit Missing

CUST_ID and CUST_LIMIT are existing columns in the CUSTOMER table.

Examine the following trigger code:

CREATE OR REPLACE TRIGGER audit_cust

AFTER UPDATE OF cust_credit_limit ON customer

FOR EACH ROW

BEGIN

     INSERT INTO audit_cust (user_name,change_time,cust_id,old_credit_limit,new_credit_limit)

     VALUES (USER,SYSDATE,:OLD.cust_id,:OLD.cust_credit_limit,:NEW.cust_credit_limit);

     COMMIT;

END;

/

Which statement is true about the above trigger?

1 Mark(s)

It gives an error on compilation because it should be a statement-level trigger.
It compiles and fires successfully when the credit limit is updated in the customer table.
It gives an error on compilation because of the commit command in the trigger code.
It compiles successfully, but gives anerror when the credit limit is updated in the CUSTOMER table because the PRAGMA AUTONOMOUS_TRANSACTION statement should be introduced in the trigger.

View the Exhibit and examine the blocks of code that you plan to execute.

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)

Examine the following block of code:

SQL > DECLARE

  v_sal NUMBER;

  BEGIN

  SELECT sal INTO v_sal FROM emp WHERE empno = 130;
 
  INSERT INTO emp(empno,ename,sal) VALUES (185,'Jones',v_sal+1000);

  END;

  /

Which stages are performed when the above block is executed? Choose that to applly.

View the Exhibit and examine the blocks of code that you plan to execute.

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)

Examine the following block of code:

SQL > DECLARE

  v_sal NUMBER;

  BEGIN

  SELECT sal INTO v_sal FROM emp WHERE empno = 130;
 
  INSERT INTO emp(empno,ename,sal) VALUES (185,'Jones',v_sal+1000);

  END;

  /

Which stages are performed when the above block is executed? Choose that to applly.

1 Mark(s)

Bind
Parse
Fetch
Execute

Which three statements are true about anonymous blocks and subprograms? Choose that to applly.

1 Mark(s)

Only subprograms can be parameterized.
Only subprograms are persistent database objects.
Both anonymous blocks and subprograms can be parameterized.
Both anonymous blocks and subprograms are persistent database objects.
Only subprograms can return values that persist after the execution of the subprogram.
Both anonymous blocks and subprograms can return values that persist in SQL*Plus variables after their execution.

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

Name              Null?           Type
---------------   ------------    -----------
USER_NAME         NOT NULL        VARCHAR2(30)

CHANGE_TIME       NOT NULL        TIMESTAMP(6)

OLD_CREDIT_LIMIT                  NUMBER

NEW_CREDIT_LIMIT                  NUMBER

CUST_ID                           NUMBER

CUST_ID and CUST_LIMIT are existing columns in the CUSTOMER table.

Examine the following trigger code

CREATE OR REPLACE TRIGGER audit_cust

AFTER UPDATE OF cust_credit_limit ON customer

FOR EACH ROW

BEGIN

   INSERT INTO audit_cust (user_name,change_time,cust_id,

                old_credit_limit,new_credit_limit)

     
  
    VALUES(USER,SYSDATE,:OLD.cust_id,:OLD.cust_credit_limit,
                
                        :NEW.cust_credit_limit);

    COMMIT;

 END;

  /

Which statement is true about the above trigger?

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

Name              Null?           Type
---------------   ------------    -----------
USER_NAME         NOT NULL        VARCHAR2(30)

CHANGE_TIME       NOT NULL        TIMESTAMP(6)

OLD_CREDIT_LIMIT                  NUMBER

NEW_CREDIT_LIMIT                  NUMBER

CUST_ID                           NUMBER

CUST_ID and CUST_LIMIT are existing columns in the CUSTOMER table.

Examine the following trigger code

CREATE OR REPLACE TRIGGER audit_cust

AFTER UPDATE OF cust_credit_limit ON customer

FOR EACH ROW

BEGIN

   INSERT INTO audit_cust (user_name,change_time,cust_id,

                old_credit_limit,new_credit_limit)

     
  
    VALUES(USER,SYSDATE,:OLD.cust_id,:OLD.cust_credit_limit,
                
                        :NEW.cust_credit_limit);

    COMMIT;

 END;

  /

Which statement is true about the above trigger?

1 Mark(s)

It gives an error oncompilation because it should be a statement-level trigger.
It compiles and fires successfully when the credit limit is updated in thecustomertable.
It gives an error on compilation because of thecommitcommand in the trigger code.
It compilessuccessfully, but gives an error when the credit limit is updated in the CUSTOMER table because the PRAGMA AUTONOMOUS_TRANSACTION statement should be introduced in the trigger.

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

View Exhibit 2 and examine the PL/SQL block of code.

SQL > SET SERVEROUTPUT ON

SQL > DECLARE

            TYPE EmpRecTyp IS RECORD (

             emp_name  VARCHAR2(30),

             salary    NUMBER(8,2);

             FUNCTION highest_salary RETURN EmpRectTyp IS

                emp_info EmpRectTyp;

                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;

SQL > /

What is the outcome?

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

View Exhibit 2 and examine the PL/SQL block of code.

SQL > SET SERVEROUTPUT ON

SQL > DECLARE

            TYPE EmpRecTyp IS RECORD (

             emp_name  VARCHAR2(30),

             salary    NUMBER(8,2);

             FUNCTION highest_salary RETURN EmpRectTyp IS

                emp_info EmpRectTyp;

                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;

SQL > /

What is the outcome?

1 Mark(s)

It gives an error because the return type is not valid.
It gives an error because the record type is not definedwithin the function.
It gives an error because the function call in DBMS_OUTPUT. PUT__LINE is not valid
It executes successfully and displays the names and salaries of all employees who earn the highest salary.
It executes successfully but does not display the names and salaries of all employees who earn the highest salary.

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 DIRECTORY my_dir AS '/temp/myfiles';

SQL > GRANT WRITE ON DIRECTORY my_dir TO scott;

View Exhibit 2 and examine the procedure code.

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.salary);

         END LOOP;

         UTL_FILE.FCLOSE (f_file);

         EXCEPTION

            WHEN UTL_FILE.INVALID_FILEHANDLE THEN

            RAISE_APPLICATION_ERROR9-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_status('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 DIRECTORY my_dir AS '/temp/myfiles';

SQL > GRANT WRITE ON DIRECTORY my_dir TO scott;

View Exhibit 2 and examine the procedure code.

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.salary);

         END LOOP;

         UTL_FILE.FCLOSE (f_file);

         EXCEPTION

            WHEN UTL_FILE.INVALID_FILEHANDLE THEN

            RAISE_APPLICATION_ERROR9-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_status('MY_DIR','EMPREPORT.TXT')

What is the outcome?

1 Mark(s)

It executes successfully and creates the report.
It gives an error because thetext file should be opened inappendmode.
It gives an error because the "no data found" condition is not handled to come out of the loop.
It gives an error because user SCOTT should be granted bothreadandwriteprivileges to the directory alias.
It executes but no data is written to the text file because the FFLUSH subprogram is not used to write all the data buffered in memory to a file.

View the Exhibit and examine the structure of the departments table in SCOTT’s schema.

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

DEPARTMENT_NAME   NOT NULL      VARCHAR2(30)

MANAGER_ID                      NUMBER(6)

LOCATION_ID                     NUMBER(4)

Examine the following block of code:

CREATE OR REPLACE PROCEDURE add_dept(

           p_id NUMBER,p_name VARCHAR2) IS

 BEGIN

           INSERT INTO departments

            VALUES(p_id,p_name,NULL,NULL);

 END;

 /

The above procedure is created by user SCOTT. Another user JONES needs to use the
procedure.

Which two statements are true in the above scenario? Choose that to applly.

View the Exhibit and examine the structure of the departments table in SCOTT’s schema.

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

DEPARTMENT_NAME   NOT NULL      VARCHAR2(30)

MANAGER_ID                      NUMBER(6)

LOCATION_ID                     NUMBER(4)

Examine the following block of code:

CREATE OR REPLACE PROCEDURE add_dept(

           p_id NUMBER,p_name VARCHAR2) IS

 BEGIN

           INSERT INTO departments

            VALUES(p_id,p_name,NULL,NULL);

 END;

 /

The above procedure is created by user SCOTT. Another user JONES needs to use the
procedure.

Which two statements are true in the above scenario? Choose that to applly.

1 Mark(s)

JONES executes the procedure with definers rights.
JONES executes the procedure with invokers rights.
SCOTT should grant only the execute privilege for the procedure to JONES.
SCOTT should grant both theexecuteprivilege for the procedure andinsertprivilege for the table to JONES

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

Name               Null?         Type
--------------     -----------   ----------------
CUST_ID            NOT NULL       NUMBER

CUST_LAST_NAME     NOT NULL        VARCHAR2(40)

CUST_CITY          NOT NULL        VARCHAR2(30)

CUST_CREDIT_LIMIT  NOT NULL        NUMBER

CUST_CATEGORY                      VARCHAR2(20)

You create the following trigger to ensure that customers belonging to category "A" or "B" in the
customer table can have a credit limit of more than 8000.

SQL > CREATE OR REPLACE TRIGGER restrict _credit_limit

                BEFORE INSERT OR UPDATE ON Customer

                FOR EACH ROW

                BEGIN

                   IF (:NEW.cust_category NOT IN ('A','B'))

                     AND :NEW.cust_credit_limit > 8000 THEN

                      DBMS_OUTPUT.PUT_LINE ('Credit Limit cannot be greater

                              than 8000 for this category');

                   END IF;

                END;

 /

you execute the following UPDATE command for CUST_ID 101 existing in the CUSTOMER table.

SQL > UPDATE customer SET cust_category = 'C', cust_credit_limit = 9000

       WHERE cust_id = 101;

What is the outcome?

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

Name               Null?         Type
--------------     -----------   ----------------
CUST_ID            NOT NULL       NUMBER

CUST_LAST_NAME     NOT NULL        VARCHAR2(40)

CUST_CITY          NOT NULL        VARCHAR2(30)

CUST_CREDIT_LIMIT  NOT NULL        NUMBER

CUST_CATEGORY                      VARCHAR2(20)

You create the following trigger to ensure that customers belonging to category "A" or "B" in the
customer table can have a credit limit of more than 8000.

SQL > CREATE OR REPLACE TRIGGER restrict _credit_limit

                BEFORE INSERT OR UPDATE ON Customer

                FOR EACH ROW

                BEGIN

                   IF (:NEW.cust_category NOT IN ('A','B'))

                     AND :NEW.cust_credit_limit > 8000 THEN

                      DBMS_OUTPUT.PUT_LINE ('Credit Limit cannot be greater

                              than 8000 for this category');

                   END IF;

                END;

 /

you execute the following UPDATE command for CUST_ID 101 existing in the CUSTOMER table.

SQL > UPDATE customer SET cust_category = 'C', cust_credit_limit = 9000

       WHERE cust_id = 101;

What is the outcome?

1 Mark(s)

The trigger is fired,a message is displayed, and the update is successful.
The trigger is fired and a message is displayed, but the update is rolled back.
The trigger is not fired because the when clause should be used to specify the condition, however, the update is successful
The trigger is not fired because column names must be specified with the update event to identify which columns must be changed to cause the trigger to fire, however, the update is successful.

View the Exhibit and examine the partial data in the PRODUCTS table.

PROD_ID is the primary key.

PRODUCTS

PROD_ID        PROD_NAME                                   PROD_LIST_PRICE
-------------  -------------------------------------       ------------------
126             3 1/2" Buk diskettes,Box of 100            28.00

127             Model CD13272 Tricolor Ink Cartridge       36.99

128             Model SM6273 Black Ink Cartridge           27.99

129             Model NM500X High Yield Toner Cartridge    192.99

Examine the following code:

SQL > SET SERVEROUTPUT ON

SQL > DECLARE

      v_prod_name VARCHAR2(50);

      v_price NUMBER;

      v_max_price NUMBER;

      v_compare VARCHAR2(40) := NULL;

      BEGIN

          SELECT prod_name,prod_list_price INTO v_prod_name,v_price

                 FROM products

                 WHERE prod_id = 128;

          SELECT MAX(prod_list_price) INTO v_max_price

                 FROM products;

          v_prod_name := SUBSTR(v_prod_name,1,INSTR(v_prod_name,''));

          v_compare DECODE(v-price,v_max_price,'Product has highest price',
 
                 'Product does not have the highest priced');

          DBMS_OUTPUT.PUT_LINE('PRODUCT NAME' || v_prod_name ||

                            'Remark'  ||  v_compare);

 END;

 /

What is the outcome on execution of the above code?

View the Exhibit and examine the partial data in the PRODUCTS table.

PROD_ID is the primary key.

PRODUCTS

PROD_ID        PROD_NAME                                   PROD_LIST_PRICE
-------------  -------------------------------------       ------------------
126             3 1/2" Buk diskettes,Box of 100            28.00

127             Model CD13272 Tricolor Ink Cartridge       36.99

128             Model SM6273 Black Ink Cartridge           27.99

129             Model NM500X High Yield Toner Cartridge    192.99

Examine the following code:

SQL > SET SERVEROUTPUT ON

SQL > DECLARE

      v_prod_name VARCHAR2(50);

      v_price NUMBER;

      v_max_price NUMBER;

      v_compare VARCHAR2(40) := NULL;

      BEGIN

          SELECT prod_name,prod_list_price INTO v_prod_name,v_price

                 FROM products

                 WHERE prod_id = 128;

          SELECT MAX(prod_list_price) INTO v_max_price

                 FROM products;

          v_prod_name := SUBSTR(v_prod_name,1,INSTR(v_prod_name,''));

          v_compare DECODE(v-price,v_max_price,'Product has highest price',
 
                 'Product does not have the highest priced');

          DBMS_OUTPUT.PUT_LINE('PRODUCT NAME' || v_prod_name ||

                            'Remark'  ||  v_compare);

 END;

 /

What is the outcome on execution of the above code?

1 Mark(s)

It executes successfully.
It gives an error because the DECODE function can be usedonly in a SQL statement.
It gives an error because the SUBSTR and INSTR functions can be used only in a SQL statement.
It gives an error because both the MAX and DECODE functions can be used only in a SQL statement.

DECLARE 

           jobid employee.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_CLEAR' THEN

              IF sal < 3000 THEN sal_raise := .12;

                    ELSE sal_raise := .09;

              END IF;

          WHEN jobid = 'SH_CLEAR' THEN

              IF sal < 4000 THEN sal_raise := .11;

                    ELSE sal_raise := .08;

              END IF;

          WHEN jobid = 'ST_CLEAR' THEN

              IF sal < 3500 THEN sal_raise := .10;

                    ELSE sal_raise := .07;

              END IF;

         ELSE

         BEGIN

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

         END;
         
         END CASE;

      UPDATE employees SET salary = salary + salary + sal_raise

      WHERE employee_id = emp_id;

      COMMIT;
 END;

SERVEROUTPUT is on for the session.

Which statement is true about the execution of the code?

DECLARE 

           jobid employee.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_CLEAR' THEN

              IF sal < 3000 THEN sal_raise := .12;

                    ELSE sal_raise := .09;

              END IF;

          WHEN jobid = 'SH_CLEAR' THEN

              IF sal < 4000 THEN sal_raise := .11;

                    ELSE sal_raise := .08;

              END IF;

          WHEN jobid = 'ST_CLEAR' THEN

              IF sal < 3500 THEN sal_raise := .10;

                    ELSE sal_raise := .07;

              END IF;

         ELSE

         BEGIN

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

         END;
         
         END CASE;

      UPDATE employees SET salary = salary + salary + sal_raise

      WHERE employee_id = emp_id;

      COMMIT;
 END;

SERVEROUTPUT is on for the session.

Which statement is true about the execution of the code?

1 Mark(s)

The execution fails because of the misplaced else clause.
The execution is successful even if there is no employee with EMPLOYEE_ID 115.
The execution falls and throwsexceptions if no employee with EMPLOYEE_ID us is found.
The execution is successful, but it displays an incorrect output if no employee with EMPLOYEE_ID 115 is found.

The STRING_TAB table has the following structure:

Name         Null?      Type
----------   --------   -------------
STRING1                 VARCHAR2(100)

View the Exhibit and examine the code.

SQL > SET SERVEROUTPUT ON

SQL > DECLARE

        in_string VARCHAR2(25) := 'This is my test string/';

        out_string VARCHAR2(25);

        PROCEDURE double (original IN VARCHAR2,


                  new_string OUT VARCHAR2) IS

        BEGIN

             new_string := original || '+' || original;

        EXCEPTION

             WHEN VALUE_ERROR THEN

                DBMS_OUTPUT.PUT_LINE('Output buffer not long enough.');

                COMMIT;

        END;

        BEGIN

             double(in_string,out_string);

             DBMS_OUTPUT.PUT_LINE(in_string || '-' || out_string);

         END;

         /

What is the outcome on execution?

The STRING_TAB table has the following structure:

Name         Null?      Type
----------   --------   -------------
STRING1                 VARCHAR2(100)

View the Exhibit and examine the code.

SQL > SET SERVEROUTPUT ON

SQL > DECLARE

        in_string VARCHAR2(25) := 'This is my test string/';

        out_string VARCHAR2(25);

        PROCEDURE double (original IN VARCHAR2,


                  new_string OUT VARCHAR2) IS

        BEGIN

             new_string := original || '+' || original;

        EXCEPTION

             WHEN VALUE_ERROR THEN

                DBMS_OUTPUT.PUT_LINE('Output buffer not long enough.');

                COMMIT;

        END;

        BEGIN

             double(in_string,out_string);

             DBMS_OUTPUT.PUT_LINE(in_string || '-' || out_string);

         END;

         /

What is the outcome on execution?

1 Mark(s)

It displays Output buffer not long enough. This is my test string.-.
It displays Output buffer not long enough. This is my test string.-.
It displays only This is my test string .- Because EXCEPTION should have been defined in the anonymous block to get the error message.
It does not display any of the DBMS_OUTPUT messages and gives an error because a transaction control statement cannot be used in theexceptionsection of a procedure.

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

Exhibit Missing

CUST_ID and CUST_LIMIT are existing columns in the CUSTOMER table.

Examine the following trigger code:.

CREATE OR REPLACE TRIGGER audit_cust

AFTER UPDATE OF cust_credit_limit ON customer

FOR EACH ROW

BEGIN

    INSERT INTO audit_cust(user_name,change_time,cust_id,old_credit_limit,

                                                        new_credit_limit);

    VALUES (USER,SYSDATE, :OLD.cust_id,: :OLD.cust_credit_limit,

            :NUEW.cust_credit_limit);

    COMMIT;

 END;

 /

Which statement is true about the above trigger?

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

Exhibit Missing

CUST_ID and CUST_LIMIT are existing columns in the CUSTOMER table.

Examine the following trigger code:.

CREATE OR REPLACE TRIGGER audit_cust

AFTER UPDATE OF cust_credit_limit ON customer

FOR EACH ROW

BEGIN

    INSERT INTO audit_cust(user_name,change_time,cust_id,old_credit_limit,

                                                        new_credit_limit);

    VALUES (USER,SYSDATE, :OLD.cust_id,: :OLD.cust_credit_limit,

            :NUEW.cust_credit_limit);

    COMMIT;

 END;

 /

Which statement is true about the above trigger?

1 Mark(s)

It gives an error on compilation because it should be a statement-level trigger.
It compiles and fires successfully when the credit limit is updated in the customer table.
It gives an error on compilation because of the commit command in the trigger code.
It compiles successfully, but gives anerror when the credit limit is updated in the CUSTOMER table because the PRAGMA AUTONOMOUS_TRANSACTION statement should be introduced in the trigger.

PRO - PL SQL Programming - 6 : Question 1 of 15