Ques-2

Database Programming with PL/SQL 9-3: Review of the Data Dictionary Practice Activities Vocabulary Identify the vocabulary word for each definition below: Try It / Solve It 1. Which of the following statements are true: A) The Data Dictionary is a list of hard coded table names in all Oracle databases. B) The Data Dictionary can be updated by all users with Select statements. C) All users of an Oracle Database can see details of all tables in that database. D) The Data Dictionary is owned by the user called SYS. 2. List the three different classes of Data Dictionary views, and state what kind of information is shown by each class. 3. Write and execute a SELECT statement that lists all the stored objects you have created in your account so far. The query should return the object name, its type, and its status. Order the output by type of object. 4. Modify the query from question 3 to show only functions and procedures to which you have access. Add the owner of the object to display in the results. 5. What is DICT and why is it useful? Copyright 8 2020, Orade andior its attliates, Al rights reserved. Orade and Java are registered trademarks of Crade andior its athlibes. Other names may be trademanks of their respective onners. 6. Write and execute a suitable SELECT...FROM DICT... statement to list dictionary views which contain information about all views which you own. Database Programming with PL/SQL 9-4: Managing Procedures and Functions Practice Activities Vocabulary Identify the vocabulary word for each definition below: Try It / Solve It 1. Complete the steps below to see how exceptions are propagated. A. Execute the following two SQL statements to create a duplicate of the DEPARTMENTS table, with department_id as the primary key. CREATE TABLE my_depts AS SELECT * FROM departments; ALTER TABLE my_depts ADD CONSTRAINT my_dept_id_pk PRIMARY KEY (department_id); B. Examine the following code and create the procedure. Save your work (you will need to modify the procedure code later). CREATE OR REPLACE PROCEDURE add_my_dept (p_dept_id IN VARCHAR2, p_dept_name IN VARCHAR2) IS BEGIN INSERT INTO my_depts (department_id, department_name) VALUES (p_dept_id, p_dept_name); END add_my_dept; ight e 2020, Dradie andior its aftliates. Aa nights reserved. Oracle and Java are registered trademanks af Oracle andior its afflishes. Other names myy be trademarks of their respective owners. C. What do you think would happen if you execute this procedure to insert department_id 10 (which already exists)? Write and execute an anonymous block to test your theory. D. Modify your procedure to handle the exception in a generic WHEN OTHERS exception handler. E. Now what do you think would happen if you execute this procedure for department_id 10 (which already exists)? Test it again as in step C. CREATE OR REPLACE PROCEDURE add_my_dept (p_dept_id IN VARCHAR2, p_dept_name IN VARCHAR2) IS BEGEIN INSERT INTO my_depts (department_id,department_name) VALUES (p_dept_id, p_dept_name); END add_my_dept; G. Execute the following code to create a new procedure called outer_proc which calls add_my_dept, passing department_id 10 to it: CREATE OR REPLACE PROCEDURE outer_proc IS V_dept NUMBER(2) :=10; V_dname VARCHAR2(30) := 'Admin'; BEGIN add_my_dept(v_dept, v_dname); EXCEPTION WHEN OTHERS THEN END; DBMS_OUTPUT.PUT_LINE('Exception was propagated to outer_proc'); H. Execute outer_proc from an anonymous block. What happens and why? 2. Write and execute a SELECT statement to list the names of all the procedures you have created so far. 3. Delete the last procedure you created: outer_proc. 4. Write and execute a SELECT statement to list the source code of your add_my_dept procedure. Make sure your SELECT statement list the lines of code in the correct order