PRO - PL SQL Programming

View Exhibit 1 and examine the structure of the DO 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.

SQL > CREATE OR REPLACE FUNCTION job_chk (p_empno NUMBER)

      RETURN BOOLEAN IS

      v_job emp.job%TYPE;

      BEGIN

         SELECT job INTO v_job FROM emp WHERE empno = p_empno;

         IF v_job 'SALESMAN' THEN

            RETURN TRUE;

         ELSE

             RETURN FALSE;

         END IF;

       END job_chk;

/



SQL > DECLARE

           v_job BOOLEAN;

           dyn_stmt VARCHAR2(200);

           v_comm NUMBER := NULL;

           v_empno emp.empno%TYPE;

           BEGIN

               dyn_stmt := 'BEGIN:v_job := job_chk(100);END;';
               EXECUTE IMMEDIATE dyn_stmt USING OUT v_job;
               IF v_job THEN

                   EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x WHERE empno = :y';
    
                   USING v_comm,v_empno;

                END IF;
            
             END;

The anonymous block gives an error on execution. What is the reason?

View Exhibit 1 and examine the structure of the DO 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.

SQL > CREATE OR REPLACE FUNCTION job_chk (p_empno NUMBER)

      RETURN BOOLEAN IS

      v_job emp.job%TYPE;

      BEGIN

         SELECT job INTO v_job FROM emp WHERE empno = p_empno;

         IF v_job 'SALESMAN' THEN

            RETURN TRUE;

         ELSE

             RETURN FALSE;

         END IF;

       END job_chk;

/



SQL > DECLARE

           v_job BOOLEAN;

           dyn_stmt VARCHAR2(200);

           v_comm NUMBER := NULL;

           v_empno emp.empno%TYPE;

           BEGIN

               dyn_stmt := 'BEGIN:v_job := job_chk(100);END;';
               EXECUTE IMMEDIATE dyn_stmt USING OUT v_job;
               IF v_job THEN

                   EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x WHERE empno = :y';
    
                   USING v_comm,v_empno;

                END IF;
            
             END;

The anonymous block gives an error on execution. What is the reason?

1 Mark(s)

The assignment in line 7 is not valid.
The SQL does not support the Boolean data type.
A null value cannot be applied to the bind arguments in the using clause in line 10.
The names of bind variables must be thesame as the using clause bind arguments in line 10.

Examine the following code that you plan to execute:

SQL > CREATE OR REPLACE PACKAGE p1 IS

         x NUMBER;

         PROCEDURE proc1;

         PROCEDURE proc2;

      END p1;

Package created.

CREATE OR REPLACE PACKAGE BODY p1 IS

  PROCEDURE proc1 IS

  BEGIN

    x := 1;

  END;

  PROCEDURE proc3 IS

  BEGIN

    DBMS_OUTPUT.PUT_LINE(x);

  END proc3;

  END p1;

What correction should be performed in the above code?

Examine the following code that you plan to execute:

SQL > CREATE OR REPLACE PACKAGE p1 IS

         x NUMBER;

         PROCEDURE proc1;

         PROCEDURE proc2;

      END p1;

Package created.

CREATE OR REPLACE PACKAGE BODY p1 IS

  PROCEDURE proc1 IS

  BEGIN

    x := 1;

  END;

  PROCEDURE proc3 IS

  BEGIN

    DBMS_OUTPUT.PUT_LINE(x);

  END proc3;

  END p1;

What correction should be performed in the above code?

1 Mark(s)

The PROC2 procedure code should be defined in the package body.
The PROC3 procedure should be declared in the package specification.
The PROC3 procedure header should be declared at the beginning of the package body.
The variable x must be declared in the package body and removed from the specification,

Which two statements are correct about PL/SQL package components? Choose that to apply.

1 Mark(s)

A package must have both specification andbody.
A package body can exist without the package specification.
A package specification can exist without the package body.
When a packaged public variable is called for the first time in a session, the entire package is loaded into memory.

In which of the following scenarios would you recommend using PL/SQL records?

1 Mark(s)

when you want toretrieve an entire row from a table and perform calculations
when you know the number of elements in advance and the elements are usually accessed sequentially
when you want to create a separate lookup table with multiple entries for each row of themain table, and access it through join queries
when you want to create a relatively small lookup table, where the collection can be constructed in memory each time a subprogram is invoked

Which system events can be used to create triggers that fire both at database and schema levels? Choose that to apply.

1 Mark(s)

AFTER LOGON
AFTER STARTUP
BEFORE SHUTDOWN
AFTER SERVERERROR

User SCOTT has been granted CREATE ANY TRIGGER AND ALTER ANY TABLE by the DBA.HR is an existing schema in the database.

SCOTT creates the following trigger:

CREATE OR REPLACE TRIGGER drop_trigger

BEFORE DROP ON hr.SCHEMA

BEGIN

  RAISE_APPLICATION_ERROR (-20000, ‘Cannot drop object');

END:

SCOTT does not grant the execute privilege on this trigger to any other users.

For which user(s) would this trigger fire by default when they drop an object in the hr schema?

User SCOTT has been granted CREATE ANY TRIGGER AND ALTER ANY TABLE by the DBA.HR is an existing schema in the database.

SCOTT creates the following trigger:

CREATE OR REPLACE TRIGGER drop_trigger

BEFORE DROP ON hr.SCHEMA

BEGIN

  RAISE_APPLICATION_ERROR (-20000, ‘Cannot drop object');

END:

SCOTT does not grant the execute privilege on this trigger to any other users.

For which user(s) would this trigger fire by default when they drop an object in the hr schema?

1 Mark(s)

Only HR
SCOTT and HR
Only SCOTT
SCOTT, HR, and SYS

View the Exhibit and examine the code and its outcome on execution:

SQL > CREATE PACKAGE my_debug IS

       debug CONSTANT BOOLEAN := TRUE;

       trace CONSTANT BOOLEAN := TRUE;

       END my_debug;

/

Package created.

SQL > CREATE PROCEDURE my_procl IS

       BEGIN

          IF my_debug.debug THEN

             DBMS_OUTPUT.put_line('Debuggin ON');
          
          ELSE

             DBMS_OUTPUT.put_line('Debuggin ON');
   
       END my_procl;

/

Procedure created.

What would be the effect on the two procedures if the value of debug is set to false? Choose that to apply.

View the Exhibit and examine the code and its outcome on execution:

SQL > CREATE PACKAGE my_debug IS

       debug CONSTANT BOOLEAN := TRUE;

       trace CONSTANT BOOLEAN := TRUE;

       END my_debug;

/

Package created.

SQL > CREATE PROCEDURE my_procl IS

       BEGIN

          IF my_debug.debug THEN

             DBMS_OUTPUT.put_line('Debuggin ON');
          
          ELSE

             DBMS_OUTPUT.put_line('Debuggin ON');
   
       END my_procl;

/

Procedure created.

What would be the effect on the two procedures if the value of debug is set to false? Choose that to apply.

1 Mark(s)

MY_PROC2 is not recompiled.
MY_PROC1 is recompiled but remains unchanged.
MY_PROC2 is recompiled but remains unchanged.
MY_PROC1 is recompiled without the debugging code.

Which two statements are true about statement-level and row-level triggers? Choose that to apply.

1 Mark(s)

A row trigger fires once even if no rows are affected.
A statement trigger fires once even if no rows are affected.
Row triggers are useful if the trigger action depends on the data of rows that are affected or on data that is provided by the triggering event itself.
Statement triggers are useful if the trigger action depends on the data of rows that are affected or on data that is provided by the triggering event itself.

Identify two features of obfuscation. Choose that to apply.

1 Mark(s)

The Import and Export utilities accept wrapped files.
SQL Plus cannot process the obfuscated source files.
Only the wrap utility can obfuscate multiple programs at a time.
Both the DBMS_DDL package and the Wrap utility can obfuscate multiple programs at a time.
The sourcecode is visible only through the DBA_SOURCE view and not through the USER_SOURCE or ALL_SOURCE View

Examine the following code:

SQL > SET SERVEROUTPUT ON;

SQL > DECLARE

         v_myage number;

    BEGIN

        IF v_myage < 11 THEN

                DBMS_OUTPUT.PUT_LINE('I am a child');
 
        ELSE

                DBMS_OUTPUT.PUT_LINE('I am not a child');

        END IF;
    
   END;

Which statement is true about the execution of the above code?

Examine the following code:

SQL > SET SERVEROUTPUT ON;

SQL > DECLARE

         v_myage number;

    BEGIN

        IF v_myage < 11 THEN

                DBMS_OUTPUT.PUT_LINE('I am a child');
 
        ELSE

                DBMS_OUTPUT.PUT_LINE('I am not a child');

        END IF;
    
   END;

Which statement is true about the execution of the above code?

1 Mark(s)

It executes and displays null.
It executes and the condition returns true.
It executes and control goes to the else statement.
It fails because no value isassigned to the v_myage variable.

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,20

MANAGER_ID                          NUMBER(6)

DEPARTMENT_ID                       NUMBER(4)

Execute the following block of code:

SQL > DECLARE

       v_sum_sal NUMBER;

       department_id employees.department_id%TYPE

       BEGIN

            SELECT SUM(salary)

               INTO v_sum_sal FROM employees

               WHERE department_id = department_id;

        DBMS_OUTPUT.PUT_LINE('The sum of salary is' || v_sum_sal);

        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,20

MANAGER_ID                          NUMBER(6)

DEPARTMENT_ID                       NUMBER(4)

Execute the following block of code:

SQL > DECLARE

       v_sum_sal NUMBER;

       department_id employees.department_id%TYPE

       BEGIN

            SELECT SUM(salary)

               INTO v_sum_sal FROM employees

               WHERE department_id = department_id;

        DBMS_OUTPUT.PUT_LINE('The sum of salary is' || v_sum_sal);

        END;

        /

What is the outcome?

1 Mark(s)

It gives an error because group functions cannot be used in anonymous blocks.
It executes successfully and correctly gives the result ofthe sum of salaries in department 60.
It executes successfully and incorrectly gives the result of the sum of salaries in department 60.
It gives an error because the variable name and column name are the same in the where clause of the select statement.

ORDER_TOTAL is a column in the orders table with the data type and size as number (8, 2)

Examine the following code:

SQL > SET SERVEROUTPUT ON

SQL > DECLARE

        v_order_id       orders.order_id%TYE;
           
        v_order_total    CONSTANT orders.order_total%TYPE := 1000;

        v_all_order_total v_order_total%TYPE;

      BEGIN

         v_order_id := NULL;
  
         DBMS_OUTPUT.PUT_LINE('Order Total is' || v_order_total);

      END;

/

Which statement is correct about the above code?

ORDER_TOTAL is a column in the orders table with the data type and size as number (8, 2)

Examine the following code:

SQL > SET SERVEROUTPUT ON

SQL > DECLARE

        v_order_id       orders.order_id%TYE;
           
        v_order_total    CONSTANT orders.order_total%TYPE := 1000;

        v_all_order_total v_order_total%TYPE;

      BEGIN

         v_order_id := NULL;
  
         DBMS_OUTPUT.PUT_LINE('Order Total is' || v_order_total);

      END;

/

Which statement is correct about the above code?

1 Mark(s)

It gives an error in line 3.
It gives an error in line 4.
It gives an error in line 6.
It executes successfully and displays the output.

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 apply.

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 apply.

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 the BXKCOTE privilege for the procedure and insert privilege for the table to

Which statement is true about triggers on data definition language (DDL) statements?

1 Mark(s)

They can be used to track changes only to a table or index.
They can be defined by all users in the database or only by a specific user.
They are fired only when the owner of the object Issues the DDL statement.
They can be used to track changes to a table, table space, view, or synonym.

In which of the following scenarios would you recommend using associative arrays?

1 Mark(s)

When you want to retrieve an entire row from a table and perform calculations
When you know the number of elements in advance and the elements are usually accessed sequentially
When you want to create a separate lookup table with multiple entries for each row of the main table, and access it through join queries
When you want to create a relatively small lookup table,where the collection can be constructed on memory each time a subprogram is invoked.

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

CREATE OR REPLACE FUNCTION dflt RETURN NUMBER IS

       cnt NUMBER := 0;

     BEGIN

        cnt := cnt + 1;

        RETURN 45;

     END dflt;

CREATE OR REPLACE PROCEDURE p(i IN NUMBER DEFAULT dflt()) IS 

   BEGIN
  
       DBMS_OUTPUT.PUT_LINE(i);

   END p;


DECLARE:

      cnt NUMBE := dflt();

   BEGIN

       FOR j IN 1..3 LOOP

          p(j);
     
       END LOOP;

      DBMS_OUTPUT.PUT_LINE('cnt:' || cnt);

      p();

      DBMS_OUTPUT.PUT_LINE('cnt:' || cnt);

    END;

Which statement is true about the blocks of code?

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

CREATE OR REPLACE FUNCTION dflt RETURN NUMBER IS

       cnt NUMBER := 0;

     BEGIN

        cnt := cnt + 1;

        RETURN 45;

     END dflt;

CREATE OR REPLACE PROCEDURE p(i IN NUMBER DEFAULT dflt()) IS 

   BEGIN
  
       DBMS_OUTPUT.PUT_LINE(i);

   END p;


DECLARE:

      cnt NUMBE := dflt();

   BEGIN

       FOR j IN 1..3 LOOP

          p(j);
     
       END LOOP;

      DBMS_OUTPUT.PUT_LINE('cnt:' || cnt);

      p();

      DBMS_OUTPUT.PUT_LINE('cnt:' || cnt);

    END;

Which statement is true about the blocks of code?

1 Mark(s)

All the blocks execute successfully and the anonymous block displays123cant: 4545cnt: 45.
All the blocks execute successfully and the anonymous block displays123cut: 045cart: 1.
The anonymous block gives an error because the function invocation in line 2 is not valid.
The procedure creation gives an error because the function invocation in line 1 is not valid.

You create the following table and execute the following code:

SQL > CREATE TABLE emp_temp (deptno NUMBER(2),job VARCHAR2(18));

SQL > DECLARE

           TYPE NumList IS TABLE OF NUMBER;

           depts NumList := NumList(10,20,30);

BEGIN
       
          INSERT INTO emp_temp VALUES(10,'Clerk');

          INSERT INTO emp_temp VALUES(20,'Bookkeeper');

          INSERT INTO emp_temp VALUES(30,'Analyst');

          FORALL j IN depts.FIRST..depts.LAST
                       
            UPDATE emp_temp SET job = job || '(Senior)' 

            WHERE deptno = depts(j);

          EXCEPTION

         WHEN OTHERS THEN

           DBMS_OUTPUT.PUT_LINE('Problem in the FORALL statement.');

           COMMIT;
END;

/

Which statement is true about the outcome of the above code?

You create the following table and execute the following code:

SQL > CREATE TABLE emp_temp (deptno NUMBER(2),job VARCHAR2(18));

SQL > DECLARE

           TYPE NumList IS TABLE OF NUMBER;

           depts NumList := NumList(10,20,30);

BEGIN
       
          INSERT INTO emp_temp VALUES(10,'Clerk');

          INSERT INTO emp_temp VALUES(20,'Bookkeeper');

          INSERT INTO emp_temp VALUES(30,'Analyst');

          FORALL j IN depts.FIRST..depts.LAST
                       
            UPDATE emp_temp SET job = job || '(Senior)' 

            WHERE deptno = depts(j);

          EXCEPTION

         WHEN OTHERS THEN

           DBMS_OUTPUT.PUT_LINE('Problem in the FORALL statement.');

           COMMIT;
END;

/

Which statement is true about the outcome of the above code?

1 Mark(s)

It executes successfully and all the rows are updated.
It gives an error but saves the inserted rows and the update to the first row.
It gives an error but saves the inserted rows; however, no rows are updated.
It gives anerror and all the data manipulation language (DML) statements are rolled back.

Which two statements are true about the continue statement? Choose that to apply.

1 Mark(s)

The PL/SQL block execution terminates immediately.
The CONTINUE statement cannot appear outside a loop.
The loop completes immediately and control passes to the statement after end loop.
The statements afterthe continue statement in the iteration are executed before terminating the LOOP
The current iteration of the loop completes immediately and control passes to the next iteration of the loop.

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

SQL > CREATE OR REPLACE PROCEDURE raise_salary (emp_id IN NUMBER,amount IN NUMBER,extra IN NUMBER DEFAULT 50)

 IS

 BEGIN

     UPDATE emp SET sal = sal + NVL(amount,0) + extra

     WHERE empno = emp_id;

 END raise_salary;

 /


SQL > DECLARE

          emp_num NUMBER(6) := 7900;

          bonus  NUMBER(6);
         
          merit  NUMBER(4);

      BEGIN

          raise_salary(7845);

          raise_salary(emp_num,extra => 25);

          raise_salary(7845,NULL,25);

          raise_salary(emp_num,extra => 25,amount => NULL);

       END;

       /

EKPNOS 7845 and 7900 exist in the EMP table.

Which two calls to the RAISE_SALABY procedure in the anonymous block execute successfully? 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

SQL > CREATE OR REPLACE PROCEDURE raise_salary (emp_id IN NUMBER,amount IN NUMBER,extra IN NUMBER DEFAULT 50)

 IS

 BEGIN

     UPDATE emp SET sal = sal + NVL(amount,0) + extra

     WHERE empno = emp_id;

 END raise_salary;

 /


SQL > DECLARE

          emp_num NUMBER(6) := 7900;

          bonus  NUMBER(6);
         
          merit  NUMBER(4);

      BEGIN

          raise_salary(7845);

          raise_salary(emp_num,extra => 25);

          raise_salary(7845,NULL,25);

          raise_salary(emp_num,extra => 25,amount => NULL);

       END;

       /

EKPNOS 7845 and 7900 exist in the EMP table.

Which two calls to the RAISE_SALABY procedure in the anonymous block execute successfully? Choose that to apply.

1 Mark(s)

call in line 6
call in line 7
call in line 8
call in line 9

You want to create a trigger that fires whenever rows are deleted from the customer table and that displays the number of rows remaining in the table.

Which two statements are correct about the trigger to be created for the above requirement?Choose that to apply.

1 Mark(s)

It should be an after trigger.
It should be a before trigger.
It should be a row-level trigger.
It should be astatement-level trigger.
It can be a before or an after trigger.

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