PL SQL _MCQ

1. Which of the following blocks will fail due to problems in the variable binding (the USING clause)?

a.
BEGIN
   EXECUTE IMMEDIATE 'UPDATE employees SET salary = :newsal
                       WHERE salary BETWEEN 1 AND :newsal'
               USING 1000, 1000;
END;

Correct.

b.
BEGIN
   EXECUTE IMMEDIATE 'UPDATE employees SET salary = :newsal
                       WHERE salary BETWEEN 1 AND :newsal'
               USING 1000;
END;

WRONG.

c.
BEGIN
   EXECUTE IMMEDIATE 'BEGIN UPDATE employees SET salary = :newsal
                       WHERE salary BETWEEN 1 AND :newsal; END;'
               USING 1000;
END;

Correct.


d.
DECLARE
   l_empid   employees.employee_id%TYPE;
BEGIN
   EXECUTE IMMEDIATE 'BEGIN UPDATE employees SET salary = :newsal
                       WHERE salary BETWEEN 1 AND :newsal
                       RETURNING employee_id INTO :empid; END;'
               USING 1000, OUT l_empid;
END;

WRONG




2. What is the minimum number of elements that must appear in the parameter list when calling the procedure with the following header? Give an example of calling business_as_usual to demonstrate your answer.
PROCEDURE business_as_usual (
   advertising_budget_in   IN       NUMBER
 , contributions_inout     IN OUT   NUMBER
 , merge_and_purge_on_in   IN       DATE DEFAULT SYSDATE
 , fat_bonus_out           OUT      NUMBER
 , cut_corners_in          IN       VARCHAR2 DEFAULT 'WHENEVER POSSIBLE'
) IS
BEGIN
   ...
END business_as_usual;

Ans:  2 Parameters are required.

3. Which of the following blocks will not successfully update the salaries of all the rows in the employees table to 1000?

a.
BEGIN
   EXECUTE IMMEDIATE 'UPDATE employees SET salary = :newsal'
      USING 1000;
END;

Ans: Correct

b.
DECLARE
   PROCEDURE set_column (column_in IN VARCHAR2)
   IS
   BEGIN
      EXECUTE IMMEDIATE 'UPDATE employees SET ' || column_in || ' = :newsal'
                  USING 1000;
   END set_column;
BEGIN
   set_column ('salary');
END;

Ans: CORRECT






c.
DECLARE
   PROCEDURE set_column (column_in IN VARCHAR2)
   IS
   BEGIN
      EXECUTE IMMEDIATE 'UPDATE employees SET :column_name = :newsal'
                  USING column_in, 1000;
   END set_column;
BEGIN
   set_column ('salary');
END;

Ans: Column_name bind variable not defined

d.
BEGIN
   EXECUTE IMMEDIATE 'UPDATE employees SET salary = 1000';
END;

Ans: CORRECT

4. What will the SELECT COUNT(*) query return after the following statements are executed?
CREATE TABLE temp_data (d DATE)
/

CREATE OR REPLACE PROCEDURE off_i_go
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   FOR indx IN 1 .. 10
   LOOP
      INSERT INTO temp_data VALUES (SYSDATE);
   END LOOP;
END off_i_go;
/

BEGIN
   off_i_go;
   COMMIT;
END;
/

SELECT COUNT(*) FROM temp_data
/



5. Which of the following dynamic SQL scenarios are best handled by DBMS_SQL, rather than Native Dynamic SQL (EXECUTE IMMEDIATE)?

a. My dynamic query is generated from a table with 1,000 columns and contains over 64K characters.

b. I need to query lots of rows from the database and want to take advantage of the BULK COLLECT syntax to do so.

c. I have a dynamic SQL method 4 requirement in which I do not know in advance the number of elements I will be retrieving in the SELECT list.

d. I need to find out what sort of statement I executed immediately after that execution. Was it a "DROP TABLE" request, an insert into a table, etc.?

Ans: B is Correct

6. Which of the following statements about dynamic PL/SQL execution is true?

a. You can only execute dynamic SQL statements. You cannot at runtime construct anonymous blocks of PL/SQL code, and then execute those blocks.

b. When executing a dynamic PL/SQL block, you can reference directly within the dynamic block of code variables declared in the block from which the dynamic code is executed.

c. You cannot call subprograms within a dynamic PL/SQL block that have IN OUT or OUT formal parameters.

d. When executing dynamic PL/SQL blocks with the EXECUTE IMMEDIATE statement, you can retrieve values of variables modified in the dynamic block by specifying OUT or IN OUT as modifiers in the USING clause.

Ans: D is Correct

7. The good, old emp table, provided by Oracle and used for decades by sales consultants (both Oracle and third party) to demonstrate their products, contains 14 rows. (U knew that, right?) So when I execute the following code, how many employee names will be displayed?
SET SERVEROUTPUT ON

DECLARE
   CURSOR emps_cur IS SELECT * FROM emp;

   TYPE emp_aat IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
   l_emps emp_aat;

   l_row PLS_INTEGER;
BEGIN
   OPEN emps_cur;

   LOOP
      FETCH emps_cur BULK COLLECT INTO l_emps LIMIT 10;
      EXIT WHEN emps_cur%NOTFOUND;
             
      l_row := l_emps.FIRST;

      WHILE (l_row IS NOT NULL)
      LOOP
         DBMS_OUTPUT.PUT_LINE (l_emps (l_row).ename);
         l_row := l_emps.NEXT (l_row);
      END LOOP;

   END LOOP;
END;
/
Ans: It will display records from 1 to 10.


8. You want to determine the location of identical rows in a table before attempting to place a unique index on the table, how can this be done?

9. Which of the following anonymous blocks contain invalid syntax?

a.

BEGIN
   NULL;
EXCEPTION
   WHEN NO_DATA_FOUND AND VALUE_ERROR
   THEN
      RAISE;
END;

b.
BEGIN
   NULL;
EXCEPTION
   WHEN NO_DATA_FOUND OR VALUE_ERROR
   THEN
      RAISE;
END;

c.
BEGIN
   NULL;
EXCEPTION
   WHEN ANY_ERROR
   THEN
      RAISE;
END;

d.
DECLARE
   x NUMBER := 10;
BEGIN
   IF x > 10
   THEN
      RAISE;
   END IF;
END;

Ans: A,C,D have invalid syntax.
A.    Error in exception block( WHEN NO_DATA_FOUND AND VALUE_ERROR)
C.     ANY_ERROR is not oracle predefined exception
D.    Exception block is not defined

10. You want to use SQL to build SQL, what is this called and give an example

11.You have been assigned the task of updating worker salaries. If a salary is less than 1000, it must be incremented by 10%. The SQL Plus substitution variable will be used to accept a worker number. Write program for this. Assume standard tables.

12. Write a package that should have following interest calculations.

A=P*(r/100)*n
A=P*((1+(r/100))**n - 1)
A is the amount of interest, P the principal, r the interest rate as a percentage, and n the number of time periods elapsed since the loan was taken

13. Evaluate the following:

DECLARE
V_result NUMBER(2);
BEGIN
DELETE
FROM worker
WHERE division_id IN(10,20,30);
V_result:= SQL/ROWCOUNT;
COMMIT;
END;

What will be the value of v_result if no rows are deleted?

Ans: ZERO is returned;


14. Examine this package:

CREATE OR REPLACE PACKAGE discounts IS
g_id NUMBER := 7829;
discount_rate NUMBER := 0.00;
PROCEDURE display_price (p_price NUMBER);
END discounts;
cls/

CREATE OR REPLACE PACKAGE BODY discounts IS
PROCEDURE display_price (p_price NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE( .Discounted .||  TO_CHAR(p_price*NVL(discount_rate, 1)));
END display_price;
BEGIN
discount_rate :=0.10;
END discounts;
/
Which statement is true?
A. The value of DISCOUNT_RATE always remains 0.00 in a session.
B. The value of DISCOUNT_RATE is set to 0.10 each time the package is invoked in a session.
C. The value of DISCOUNT_RATE is set to 1.00 each time the procedure DISPLAY_PRICE is invoked.
D. The value of DISCOUNT_RATE is set to 0.10 when the package is invoked for the first time in a session.

Ans: B is CORRECT

15. Evaluate the following:

BEGIN
FOR i IN 1..10 LOOP
IF I=4 OR I=6 THEN null;
   I:=0;
ELSE
 I:=i+1;
INSERT INTO test(result)
VALUES (I);
END IF;
COMMIT;
END LOOP;
ROLL BACK;
END.

How many values will be inserted into the test table?

Ans: Inserts 10 valaues;

16. Examine this executable section of a PL/SQL block:

BEGIN
FOR worker_record IN pay_cursor LOOP
Worker_id_table(worker_id):=
Worker_record.last_name;
END LOOP;
CLOSE salary_cursor;
END;

Why does this section cause an error?

Ans: Here cursor is not declared .

17. Create a trigger on Emp_tab table that should give a message ‘There are now’ || a || ‘employees’ When any employee is deleted from the table.

 Ans: CREATE OR REPLACE TRIGGER  emp_trg
                  AFTER delete on emp
         DECLARE
                  I NUMBER;
         BEGIN
                 IF DELETING THEN
                       SELECT COUNT(*) INTO i   FROM EMP;
                 dbms_output.put_line('THERE ARE' ||I|| 'EMPLOYEES');
                  END IF;
           END;



18. Create a package that should have following functionality.
Increasing an employee salary by x%  (if employee salary is more than 1000 them increase by 10% else increase by 20%)
Deleting a given employee
Adding an employee

19. Write a script with following specs
An outer block declares two variables named x and counter and loops four times. Inside this loop is a sub-block that also declares a variable named x. The values inserted into the temp table show that the two x’s are indeed different. For each inner and outer loop insert a row in to temp table.


20. Explain me personally following
  • Diff between Procedure and Function
  • Diff between Trigger and procedure
  • Diff between package and procedure
  • Different triggers and when they are used
  • Serially reusable
  • Exception handling (default and user defined)
  • Different cursors (strong, weak)
  • What is use of hints in SQL?


Subscribe to: Posts (Atom)