Distributed Database Applications

1. Distributed Database Applications COSC 5050 Week Five

2. Outline • Managing PL/SQL Code • Exceptions and Error Handling • Transaction Management • Packages Distributed Database Applications

3. Data Dictionary • USER_ • USER_DEPENDENCIES • USER_ERRORS • USER_OBJECTS • USER_SOURCE • USER_TRIGGERS • USER_ARGUMENTS Distributed Database Applications

4. Data Dictionary SELECT object_type, object_name, status FROM user_objects WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE', 'TRIGGER' ) ORDER BY object_type, status, object_name; DESC ADD_EMPLOYEE SELECT TEXT FROM USER_SOURCE WHERE NAME = 'ADD_EMPLOYEE'; Distributed Database Applications

5. Debug PL/SQL Programs • Recompile invalid code • Source code debugger • Analyzing • Unit test • show err Distributed Database Applications

6. Exception Handler • System exception • An exception that is defined by Oracle and is usually raised by the PL/SQL runtime engine • With name or number • NO_DATA_FOUND • User defined exceptions • An exception that is defined by the programmer and is therefore specific to the application Distributed Database Applications

7. Exception DECLARE l_department department%ROWTYPE; BEGIN SELECT * INTO l_department FROM department WHERE dept_num = 3; DBMS_OUTPUT.PUT_LINE(l_department.dept_name); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No data!'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Too many rows'); END; / Distributed Database Applications

8. Exception create or replace function get_department( ssn_in in employee.ssn%type) return department.dept_name%type is l_dept_name department.dept_name%type; begin select dept_name into l_dept_name from department inner join employee on employee.dept_num = department.dept_num where ssn = ssn_in; dbms_output.put_line('department name: ' || l_dept_name); return l_dept_name; exception when no_data_found then dbms_output.put_line( 'no such employee or not in any department!'); return null; end; / Distributed Database Applications

9. Exception procedure delete_department (department_id_in innumber) is begin delete from department where dept_num = department_id_in; end; procedure delete_department (department_id_in in number) is begin delete from department where dept_num = department_id_in; exception when others then if sqlcode = -2292 then dbms_output.put_line ('Please delete employees for the department first.'); end if; end; SQLCODE is a built-in function that returns the number of the last error raised. Distributed Database Applications

10. Declare Exception • Declare an exception by listing the name of the exception followed by the keyword EXCEPTION • invalid_company_id EXCEPTION; • Be referenced in two ways • Raise the exception: • RAISE invalid_company_id; • Handle the raised exception: • WHEN invalid_company_id THEN Distributed Database Applications

11. Declare Exception procedure calc_annual_sales (company_id_in in company.company_id%type) is invalid_company_id exception; negative_balance exception; duplicate_company boolean; begin … body of executable statements … raise invalid_company_id; exception … when no_data_found –- system exception then … when invalid_company_id then … when negative_balance then … end; Distributed Database Applications

12. Declare Exception procedure delete_department(department_id_in in number) is l_count number; still_have_employees exception; begin select count(*) into l_count from employee where dept_num = department_id_in; if l_count > 0 then raise still_have_employees; end if; delete from department where dept_num = department_id_in; exception when still_have_employees then dbms_output.put_line ('Please delete employees for the department first.'); end; Distributed Database Applications

13. Associate Exception Name procedure delete_department(department_id_in in number) is still_have_employees exception; pragma exception_init (still_have_employees, -2292); begin delete from department where dept_num = department_id_in; exception when still_have_employees then dbms_output.put_line ('Please delete employees for the department first.'); end; ORA-2292 violated integrity constraining (OWNER.CONSTRAINT) – child record found. The PRAGMA keyword is used to signify that the remainder of the PL/SQL statement is a pragma, or directive, or pseudoinstruction to the compiler. Distributed Database Applications

14. Associate Exception Name • EXCEPTION_INIT • Compile-time command or pragma • Shot for pragmatic information • Instructs the compiler to associate an identifier, declared as an EXCEPTION, with a specific error number • The error number cannot be: • -1403 (error code for NO_DATA_FOUND), use 100 instead • 0 or any positive number besides 100 • A negative number less than -1000000 Distributed Database Applications

15. Associate Exception Name • Centralize EXCEPTION_INIT into packages CREATE OR REPLACE PACKAGE dynsql IS invalid_table_name EXCEPTION; PRAGMA EXCEPTION_INIT (invalid_table_name, -903); invalid_column_name EXCEPTION; PRAGMA EXCEPTION_INIT (invalid_column_name, -904); Distributed Database Applications

16. Raise Exception • Oracle might raise the exception when it detects an error • Programmer might raise an exception with the RAISE statement • RAISE exception_name; • RAISE package_name.exception_name; • RAISE; -- propagate out Distributed Database Applications

17. Raise Exception DECLARE invalid_id EXCEPTION; id_value VARCHAR2(30); BEGIN id_value := id_for ('SMITH'); IF SUBSTR (id_value, 1, 1) != 'X' THEN RAISE invalid_id; END IF; ... END; IF days_overdue (isbn_in, borrower_in) > 365 THEN RAISE overdue_pkg.book_is_lost; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN -- record all the "context" information errlog.putline (company_id_in); -- pass NO_DATA_FOUND unhandled RAISE; Distributed Database Applications

18. Handling Exception • Exception handlers are structured much like a conditional CASE statement Distributed Database Applications

19. Handling Exception PROCEDURE add_employee ( ssn in varchar2, fname in varchar2, lname in varchar2, dept_num in number, code in number, sup_ssn in varchar2) IS BEGIN insert into employee values (ssn, fname, lname, dept_num, code, sup_ssn); EXCEPTION WHEN OTHERS THEN DECLARE l_errcode PLS_INTEGER := SQLCODE; BEGIN IF l_errcode = -1 THEN DBMS_OUTPUT.put_line ('Employee ID already in use.'); RAISE; ELSIF l_errcode = -2291 THEN DBMS_OUTPUT.put_line ('Invalid department ID: '); RAISE; ELSE null; RAISE; END IF; END; END add_employee; Distributed Database Applications

20. Combing Multiple Exceptions DECLARE l_department department%ROWTYPE; BEGIN SELECT * INTO l_department FROM department WHERE dept_num = 3; DBMS_OUTPUT.PUT_LINE(l_department.dept_name); EXCEPTION WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('Wrong department number!'); END; / Distributed Database Applications

21. Named System Exception • P. 133 • CURSOR_ALREADY_OPEN (ORA-06511) • DUP_VAL_ON_INDEX (ORA-00001) • INVALID_CURSOR (ORA-01001) • INVALID_NUMBER (ORA-01722) • NO_DATA_FOUND (ORA-01403) • TOO_MANY_ROWS (ORA-01422) • VALUE_ERROR (ORA-06502) • ZERO_DIVIDE (ORA-01476) Distributed Database Applications

22. SQLCODE and SQLERRM • Built-in Error Functions • SQLCODE • Return the oracle error number • SQLERRM • Return the oracle message text associated with the error begin insert into employee values ('666666666', 'Dave', 'Letterman', NULL, 4581.12, NULL); exception when others then dbms_output.put_line('code : ' || sqlcode); dbms_output.put_line('message : ' || sqlerrm); if sqlcode = -1 then dbms_output.put_line ('Employee number exists!'); end if; end; Distributed Database Applications

23. SQLCODE and SQLERRM PROCEDURE DELETE_COMPANY (COMPANY_ID IN NUMBER) IS BEGIN DELETE FROM COMPANY WHERE COMPANY_ID = COMPANY_ID_IN; EXCEPTION WHEN OTHERS THEN DECLARE ERROR_CODE NUMBER := SQLCODE; ERROR_MSG VARCHAR2(512) := SQLERRM; BEGIN IF ERROR_CODE = -2292 /*CHILD RECORDS FOUND*/ THEN DELETE FROM EMPLOYEE WHERE COMPANY_ID = COMPANY_ID_IN; DELETE FROM COMPANY WHERE COMPANY_ID = COMPANY_ID_IN; ELSIF ERROR_CODE = -2291 /*PARENT KEY NOT FOUND*/ THEN DBMS_OUTPUT.PUT_LINE(‘INVALID COMPANY ID: ’||TO_CHAR(COMPANY_ID_IN)); ELSE DBMS_OUTPUT.PUT_LINE(‘ERROR DELETING COMPANY, ERROR: ’||ERROR_MSG); END IF; END; END DELETE_COMPANY; Distributed Database Applications

24. DML in PL/SQL • INSERT • INSERT INTO table VALUES (val1, …); • INSERT INTO table AS SELECT …; • UPDATE • UPDATE table SET col1 = val1 WHERE … • DELETE • DELETE FROM table WHERE … • Cursor attributes for DML operations • SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN Distributed Database Applications

25. DML in PL/SQL • Use SQL%FOUND to determine if your DML statement affected any rows CREATE OR REPLACE PROCEDURE change_author_name ( old_name_in IN books.author%TYPE, new_name_in IN books.author%TYPE, changes_made_out OUT BOOLEAN) IS BEGIN UPDATE books SET author = new_name_in WHERE author = old_name_in; changes_made_out := SQL%FOUND; END; Distributed Database Applications

26. DML in PL/SQL • Use SQL%ROWCOUNT when you need to know exactly how many rows were affected by your DML statement CREATE OR REPLACE PROCEDURE change_author_name ( old_name_in IN books.author%TYPE, new_name_in IN books.author%TYPE, rename_count_out OUT PLS_INTEGER) IS BEGIN UPDATE books SET author = new_name_in WHERE author = old_name_in; rename_count_out := SQL%ROWCOUNT; END; Distributed Database Applications

27. RETURNING Clause • Use returning clause with DML and retrieve information directly into variables Declare l_name employee.lname%type; l_code employee.code%type; Begin for rec in (select * from employee) loop update employee set code = rec.code*1.10 where ssn = rec.ssn returning code, lname into l_code, l_name; dbms_output.put_line ('New code for ' || l_name || ' = ' || l_code); end loop; End; / Distributed Database Applications

28. Use Records in Insert and Update procedure add_employee(emp_in in employee%rowtype) is begin insert into employee values emp_in; end; declare new_employee employee%rowtype; begin new_employee.ssn := '123123124'; new_employee.fname := 'Steve'; new_employee.lname := 'Hack'; new_employee.dept_num := '1'; new_employee.code := 123.45; new_employee.sup_ssn := '600000001'; add_employee(new_employee); new_employee.code := 333.09; update employee set row = new_employee where ssn = new_employee.ssn; end; Distributed Database Applications

29. Use Records in Insert and Update • Using records with the RETURNING clause DECLARE my_book_new_info books%ROWTYPE; my_book_return_info books%ROWTYPE; BEGIN my_book_new_info.isbn := '1-56592-335-9'; my_book_new_info.title := 'ORACLE PL/SQL PROGRAMMING'; my_book_new_info.summary := 'General user guide and reference'; my_book_new_info.author := 'FEUERSTEIN, STEVEN AND BILL PRIBYL'; my_book_new_info.page_count := 980; UPDATE books SET ROW = my_book_new_info WHERE isbn = my_book_new_info.isbn RETURNING isbn, title, summary, author, page_count INTO my_book_return_info; END; Distributed Database Applications

30. Transaction Management • Commit – save all outstanding changes • Rollback – erase all outstanding changes • Savepoint • SAVEPOINT savepoint_name; • Rollback to savepoint • ROLLBACK TO savepoint_name; • Lock table • LOCK TABLE emp, dept IN SHARE MODE • LOCK TABLE emp IN ROW EXCLUSIVE MODE Distributed Database Applications

31. Autonomous Transaction procedure add_emp(…) is begin update …; update …; savepoint start_add; insert …; commit; exception when others then rollback to start_add; write_log (sqlcode, sqlerrm); end; MT begins procedure write_log(…) is pragma autonomous_transaction; begin insert into log values ( code, text, user, sysdate); commit; end; AT begins MT suspends AT ends MT resumes MT ends Distributed Database Applications

32. Autonomous Transaction • Define a PL/SQL block as autonomous transaction • anonymous block, procedure, function, packaged procedure, packaged function, database trigger • Isolate the DML in that block from the caller's transaction context • The block becomes an independent transaction Distributed Database Applications

33. Package • A package is a grouping or packaging of PL/SQL code elements • Package specification • Definition or specification of all the publicly available elements in the package • Package body • Code required to implement elements defined in the package specification Distributed Database Applications

34. Favorites_pkg Specification • Favorites_pkg (p. 625) CREATE OR REPLACE PACKAGE favorites_pkg AUTHID CURRENT_USER IS c_chocolate CONSTANT PLS_INTEGER := 16; c_strawberry CONSTANT PLS_INTEGER := 29; TYPE codes_nt IS TABLE OF INTEGER; my_favorites codes_nt; TYPE fav_info_rct IS REF CURSOR RETURN favorites%ROWTYPE; PROCEDURE show_favorites (list_in IN codes_nt); FUNCTION most_popular RETURN fav_info_rct; END favorites_pkg; / Distributed Database Applications

35. Favorites_pkg Body CREATE OR REPLACE PACKAGE BODY favorites_pkg IS g_most_popular PLS_INTEGER := c_strawberry; PROCEDURE show_favorites (list_in IN codes_nt) IS BEGIN FOR indx IN list_in.FIRST .. list_in.LAST LOOP DBMS_OUTPUT.put_line (list_in (indx)); END LOOP; END show_favorites; FUNCTION most_popular RETURN fav_info_rct IS retval fav_info_rct; null_cv fav_info_rct; BEGIN OPEN retval FOR SELECT * FROM favorites WHERE code = g_most_popular; RETURN retval; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN null_cv; END most_popular; END favorites_pkg; • Body (p.627) Distributed Database Applications

36. Package Specification CREATE OR REPLACE PACKAGE EMPLOYEE_PKG AS SUBTYPE FULLNAME_T IS VARCHAR2 (200); FUNCTION FULLNAME ( LAST_IN EMPLOYEE.LNAME%TYPE, FIRST_IN EMPLOYEE.FNAME%TYPE) RETURN FULLNAME_T; FUNCTION FULLNAME ( EMPLOYEE_SSN_IN IN EMPLOYEE.SSN%TYPE) RETURN FULLNAME_T; END EMPLOYEE_PKG; Distributed Database Applications

37. Package Body CREATE OR REPLACE PACKAGE BODY EMPLOYEE_PKG AS FUNCTION FULLNAME ( LAST_IN EMPLOYEE.LNAME%TYPE, FIRST_IN EMPLOYEE.FNAME%TYPE) RETURN FULLNAME_T IS BEGIN RETURN LAST_IN || ', ' || FIRST_IN; END; FUNCTION FULLNAME (EMPLOYEE_SSN_IN IN EMPLOYEE.SSN%TYPE) RETURN FULLNAME_T IS RETVAL FULLNAME_T; BEGIN SELECT FULLNAME (LNAME, FNAME) INTO RETVAL FROM EMPLOYEE WHERE SSN = EMPLOYEE_SSN_IN; RETURN RETVAL; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; WHEN TOO_MANY_ROWS THEN RETURN NULL; END; END EMPLOYEE_PKG; Distributed Database Applications

38. Calling Packaged Elements • Package is a container for code elements • Run or reference elements in a package • From outside of that package, use dot notation • package_name.element_name • Within the package (specification or body), no need to include the name of the package Distributed Database Applications

39. Pets_inc Package CREATE OR REPLACE PACKAGE pets_inc IS max_pets_in_facility CONSTANT INTEGER := 120; pet_is_sick EXCEPTION; CURSOR pet_cur (pet_id_in in pet.id%TYPE) RETURN pet%ROWTYPE; FUNCTION next_pet_shots (pet_id_in IN pet.id%TYPE) RETURN DATE; PROCEDURE set_schedule (pet_id_in IN pet.id%TYPE); END pets_inc; Distributed Database Applications

40. Pets_inc Package CREATE OR REPLACE PACKAGE BODY pets_inc IS CURSOR pet_cur (pet_id_in IN pet.id%TYPE) RETURN pet%ROWTYPE IS SELECT * FROM pet p WHERE p.id = pet_id_in; FUNCTION next_pet_shots (pet_id_in IN pet.id%TYPE) RETURN DATE /* This Implementation is Just a Stub, Presumably to be Replaced by Something that Does Something Meaningful Later On */ IS BEGIN RETURN SYSDATE; END next_pet_shots; PROCEDURE set_schedule (pet_id_in IN pet.id%TYPE) /* This Implementation is Just a Stub, Presumably to be Replaced by */ /* Something that Does Something Meaningful Later On */ IS BEGIN /* Null is a No-Op. */ /* The Statement is Included to Satisfy the Compile-Time Syntax-Checker */ NULL; END; END pets_inc; / Distributed Database Applications

41. Pets_inc Package declare c_pet constant pet.id%type:=1099; v_net_appointment date; begin if pets_inc.max_pets_in_facility > 100 then open pets_inc.pet_cur(c_pet); else v_next_appointment:=pets_inc.next_pet_shots(c_pet); end if; exception when pets_inc.pet_is_sick then pets_inc.set_scheducle(c_pet); end; Distributed Database Applications

42. Package Data • Package level data • Persist for the session • Public and private data • State of the packaged cursors persists for the session • Open/fetch/close it in different program • Close packaged cursor promptly Distributed Database Applications

43. Packaged Cursors CREATE OR REPLACE PACKAGE book_info IS CURSOR byauthor_cur ( author_in IN books.author%TYPE ) IS SELECT * FROM books WHERE author = author_in; CURSOR bytitle_cur ( title_filter_in IN books.title%TYPE ) RETURN books%ROWTYPE; TYPE author_summary_rt IS RECORD ( author books.author%TYPE, total_page_count PLS_INTEGER, total_book_count PLS_INTEGER); CURSOR summary_cur ( author_in IN books.author%TYPE ) RETURN author_summary_rt; END book_info; Distributed Database Applications

44. Packaged Cursors CREATE OR REPLACE PACKAGE BODY book_info IS CURSOR bytitle_cur ( title_filter_in IN books.title%TYPE ) RETURN books%ROWTYPE IS SELECT * FROM books WHERE title LIKE UPPER (title_filter_in); CURSOR summary_cur ( author_in IN books.author%TYPE ) RETURN author_summary_rt IS SELECT author, SUM (page_count), COUNT (*) FROM books WHERE author = author_in; END book_info; Distributed Database Applications

45. Packaged Cursors DECLARE onebook book_info.bytitle_cur%ROWTYPE; BEGIN OPEN book_info.bytitle_cur ('%PL/SQL%'); LOOP EXIT WHEN book_info.bytitle_cur%NOTFOUND; FETCH book_info.bytitle_cur INTO onebook; book_info.display (onebook); END LOOP; CLOSE book_info.bytitle_cur; END; Distributed Database Applications

46. Use of Packages • Encapsulating data manipulation • Avoiding the hardcoding of literals • Grouping together logically related functionality • Caching session-static data to improve application performance Distributed Database Applications

47. Homework • Given the package code and the database table. • List the sequence of procedure and function calls initiated for the PL/SQL command. • What is the output for the PL/SQL command? • Add exception handling in function. • Lab activities Distributed Database Applications