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 [Link] 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 xs 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?