Distributed Database Applications

1. Distributed Database Applications COSC 5050 Week Seven

2. Outline • Dynamic SQL and dynamic PL/SQL • NDS statements • EXCUTE IMMEDIATE • OPEN FOR • Oracle's object features • Globalization and localization Distributed Database Applications

3. Dynamic SQL and PL/SQL • Constructed and executed at runtime • Stored in character strings that are input to, or built by, the program at runtime • Execute DDL statements • Build back-ends applications • DBMS_SQL package • Native Dynamic SQL (NDS) Distributed Database Applications

4. NDS Statement • Native dynamic SQL • Is an integral part of the PL/SQL language itself • Significantly simpler to user and faster • EXECUTE IMMEDIATE statement • Execute a specified SQL statement immediately • OPEN FOR statement • Perform multiple-row dynamic queries Distributed Database Applications

5. DDL, DML, Anonymous Blocks declare l_sqlstring varchar2(200); l_plsqlblock varchar2(200); begin execute immediate 'create table execute_table (col1 varchar(10))'; for l_counter in 1..10 loop l_sqlstring := 'insert into execute_table values (''row ' || l_counter || ''')'; execute immediate l_sqlstring; end loop; l_plsqlblock := 'begin for l_rec in (select * from execute_table) loop dbms_output.put_line(l_rec.col1); end loop; end;'; execute immediate l_plsqlblock; execute immediate 'drop table execute_table'; end; Distributed Database Applications

6. EXECUTE IMMEDIATE Statement execute immediate 'create index emp_u_1 on employee (last_name)'; create or replace procedure execddl (ddl_string in varchar2) is begin execute immediate ddl_string; end; exec execddl( 'create index emp_u_i on employee(last_name)'); Distributed Database Applications

7. EXECUTE IMMEDIATE Statement CREATE OR REPLACE FUNCTION tabCount ( tab IN VARCHAR2, whr IN VARCHAR2 := NULL, sch IN VARCHAR2 := NULL) RETURN INTEGER IS retval INTEGER; BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || NVL (sch, USER) || '.' || tab || ' WHERE ' || NVL (whr, '1=1') INTO retval; RETURN retval; END; / if tabcount ('emp', 'deptno = ' || v_dept) > 100 then dbms_output.put_line('growing fast!'); end if; Distributed Database Applications

8. EXECUTE IMMEDIATE StatementBind Variable CREATE OR REPLACE FUNCTION updNVal ( tab IN VARCHAR2, col IN VARCHAR2, val IN NUMBER, whr IN VARCHAR2 := NULL, sch IN VARCHAR2 := NULL) RETURN PLS_INTEGER IS BEGIN EXECUTE IMMEDIATE 'UPDATE ' || NVL (sch, USER) || '.' || tab || ' SET ' || col || ' = :the_value WHERE ' || NVL (whr, '1=1') USING val; RETURN SQL%ROWCOUNT; END; / PL/SQL engine replaces the various placeholders with the values in the USING clause Distributed Database Applications

9. EXECUTE IMMEDIATE StatementBind Variable CREATE OR REPLACE PROCEDURE run_9am_procedure ( id_in IN employee.employee_id%TYPE, hour_in IN INTEGER) IS v_apptcount INTEGER; v_name VARCHAR2 (100); BEGIN EXECUTE IMMEDIATE 'BEGIN ' || TO_CHAR (SYSDATE, 'DAY') || '_set_schedule (:id, :hour, :name, :appts); END;' USING IN id_in, IN hour_in, OUT v_name, OUT v_apptcount; DBMS_OUTPUT.put_line ( 'Employee ' || v_name || ' has ' || v_apptcount || ' appointments on ' || TO_CHAR (SYSDATE)); END; / Distributed Database Applications

10. OPEN FOR Statement • Extend cursor variables to support dynamic SQL create or replace procedure show_parts_inventory ( parts_table in varchar2, where_n in varchar2 := null) is type query_curtype is ref cursor; dyncur query_curtype; begin open dyncur for ‘select * from ’ || parts_table ‘where ’ || nvl(where_in, ‘1=1’); … end; Distributed Database Applications

11. OPEN FOR Statement create or replace procedure show_employee ( table_in in varchar2, where_in in varchar2 := null) is type query_curtype is ref cursor; dyncur query_curtype; emp employee%rowtype; begin open dyncur for 'select * from ' || table_in || ' where ' || nvl (where_in, '1=1'); dbms_output.put_line('Employee list:'); loop fetch dyncur into emp; exit when dyncur%notfound; dbms_output.put_line(emp.lname || ' ' || emp.fname); end loop; close dyncur; end; / Distributed Database Applications

12. Multirow Queries with Cursor Variables CREATE OR REPLACE PROCEDURE showcol ( tab IN VARCHAR2, col IN VARCHAR2, whr IN VARCHAR2 := NULL) IS TYPE cv_type IS REF CURSOR; cv cv_type; val VARCHAR2(32767); BEGIN OPEN cv FOR 'SELECT ' || col || ' FROM ' || tab || ' WHERE ' || NVL (whr, '1 = 1'); LOOP FETCH cv INTO val; EXIT WHEN cv%NOTFOUND; IF cv%ROWCOUNT = 1 THEN DBMS_OUTPUT.PUT_LINE (RPAD ('-', 60, '-')); DBMS_OUTPUT.PUT_LINE ('Contents of '||UPPER(tab)||'.'||UPPER(col)); DBMS_OUTPUT.PUT_LINE (RPAD ('-', 60, '-')); END IF; DBMS_OUTPUT.PUT_LINE (val); END LOOP; CLOSE cv; END; / Distributed Database Applications

13. FETCH into Variables Declare type cv_type is ref cursor; cv cv_type; mega_bucks company.ceo_compensation%type; achieved_by company.cost_cutting%type; Begin Open cv for ‘select ceo_compensation, cost_curtting from company where ’ || nvl (whr, ‘1=1’); loop fetch cv into mega_bucks, achieved_by; Distributed Database Applications

14. FETCH into Records Declare type cv_type is ref cursor; cv cv_type; ceo_info company%rowtype; Begin Open cv for ‘select * from from company where ’ || nvl (whr, ‘1=1’); loop fetch cv into ceo_info; Distributed Database Applications

15. FETCH into Records create or replace package company_struc is type dynsql_curtype is ref cursor; type ceo_info_rt is record ( mega_bucks company.ceo_compensation%type, achieved_by company.cost_cutting%type); declare cv company_struc.dynsql_curtype; rec company_struc.ceo_info_rt; begin open cv for ‘select ceo_eompensatin, cost_cutting from company where ’ || nvl (whr, ‘1=1’); loop fetch cv into rec; Distributed Database Applications

16. The USING Clause in OPEN FOR OPEN cv FOR 'SELECT ' || col || ' FROM ' || tab || ' WHERE ' || dtcol || ' BETWEEN TRUNC (:startdt) AND TRUNC (:enddt)' USING dt1, NVL (dt2, dt1+1); LOOP FETCH cv INTO val; EXIT WHEN cv%NOTFOUND; IF cv%ROWCOUNT = 1 THEN … Distributed Database Applications

17. Binding Variables • Binding utilizes placeholders and USING clause EXECUTE IMMEDIATE 'UPDATE ' || tab || ' SET sal = :new_sal' USING v_sal; • Concatenation adds values directly to the SQL string EXECUTE IMMEDIATE 'UPDATE ' || tab || ' SET sal = ' || v_sal; • Binding is faster • Binding is easier to write and maintain • Binding negates the chance of code injection Distributed Database Applications

18. Code Injection CREATE OR REPLACE PROCEDURE get_rows ( ssn_in in VARCHAR2 ) IS l_where VARCHAR2(32767); BEGIN l_where := 'ssn = ' || ssn_in; EXECUTE IMMEDIATE 'DECLARE l_row employee%ROWTYPE; BEGIN SELECT * INTO l_row FROM employee WHERE ' || l_where || '; dbms_output.put_line(l_row.lname); END;'; END get_rows; / Distributed Database Applications

19. Avoid Code Injection CREATE OR REPLACE PROCEDURE get_rows ( ssn_in in VARCHAR2 ) IS l_where VARCHAR2(32767); BEGIN l_where := 'ssn = :ssnumber'; EXECUTE IMMEDIATE 'DECLARE l_row employee%ROWTYPE; BEGIN SELECT * INTO l_row FROM employee WHERE ' || l_where || '; dbms_output.put_line(l_row.lname); END;' USING ssn_in; END get_rows; / Distributed Database Applications

20. Argument Modes • IN, OUT, IN OUT create or replace procedure wrong_incentive( company_in in integer, new_layoffs in number) is sql_string varchar2(2000); sal_after_layoffs number; begin sql_string := ‘update ceo_compensation set salary = salary + 10 * :layoffs where company_id = :company returning salary into :newsal’; execute immediate sql_string using new_layoffs, company_in, out sal_after_layoffs; dbms_output.put_line (ceo compensation after latest round of layoffs $’ || sal_after_layoffs); end; Distributed Database Applications

21. Dynamic SQL and Static SQL • More versatile than plain embedded SQL programs • Can be built interactively with input from users having little or no knowledge of SQL • For highly flexible applications • Require complex coding • Use of special data structures • More runtime processing Distributed Database Applications

22. Oracle's Object Features • Object programming features • Creating base type and subtype • Creating object • Storing, retrieving, and using persistent objects Distributed Database Applications

23. Object Programming Features Distributed Database Applications

24. Object Programming Features Distributed Database Applications

25. Library Catalog Type Hierarchy Distributed Database Applications

26. Creating Base Type CREATE OR REPLACE TYPE catalog_item_t AS OBJECT ( id INTEGER, title VARCHAR2(4000), NOT INSTANTIABLE MEMBER FUNCTION ck_digit_okay RETURN BOOLEAN, MEMBER FUNCTION print RETURN VARCHAR2 ) NOT INSTANTIABLE NOT FINAL; / Distributed Database Applications

27. Creating Subtype CREATE OR REPLACE TYPE book_t UNDER catalog_item_t ( isbn VARCHAR2(13), pages INTEGER, CONSTRUCTOR FUNCTION book_t ( id IN INTEGER DEFAULT NULL, title IN VARCHAR2 DEFAULT NULL, isbn IN VARCHAR2 DEFAULT NULL, pages IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT, OVERRIDING MEMBER FUNCTION ck_digit_okay RETURN BOOLEAN, OVERRIDING MEMBER FUNCTION print RETURN VARCHAR2 ); / Distributed Database Applications

28. Creating Subtype CREATE OR REPLACE TYPE serial_t UNDER catalog_item_t ( issn VARCHAR2(10), open_or_closed VARCHAR2(1), CONSTRUCTOR FUNCTION serial_t ( id IN INTEGER DEFAULT NULL, title IN VARCHAR2 DEFAULT NULL, issn IN VARCHAR2 DEFAULT NULL, open_or_closed IN VARCHAR2 DEFAULT NULL) RETURN SELF AS RESULT, OVERRIDING MEMBER FUNCTION ck_digit_okay RETURN BOOLEAN, OVERRIDING MEMBER FUNCTION print RETURN VARCHAR2 ) NOT FINAL; / Distributed Database Applications

29. Creating Object DECLARE generic_item catalog_item_t; abook book_t; BEGIN abook := NEW book_t(title => 'Out of the Silent Planet', isbn => '0-6848-238-02'); generic_item := abook; DBMS_OUTPUT.PUT_LINE('BOOK: ' || abook.print()); DBMS_OUTPUT.PUT_LINE('ITEM: ' || generic_item.print()); END; Distributed Database Applications

30. Storing Persistent Objects CREATE TABLE catalog_items OF catalog_item_t (CONSTRAINT catalog_items_pk PRIMARY KEY (id)); CREATE TABLE my_writing_projects ( project_id INTEGER NOT NULL PRIMARY KEY, start_date DATE, working_title VARCHAR2(4000), catalog_item catalog_item_t); DESC catalog_items desc my_writing_projects INSERT INTO catalog_items VALUES (NEW book_t(10003, 'Perelandra', '0-684-82382-9', 222)); INSERT INTO catalog_items VALUES (NEW serial_t(10004, 'Time', '0040-781X', 'O')); Distributed Database Applications

31. Retrieving Persistent Objects • The VALUE function • Accepts a single argument, which must be a table alias in the current FROM clause • Returns an object of the type on which the table is defined SELECT VALUE(c) FROM catalog_items c; SELECT VALUE(c).id, VALUE(c).print() FROM catalog_items c; Distributed Database Applications

32. Retrieving Persistent Objects DECLARE catalog_item catalog_item_t; CURSOR ccur IS SELECT VALUE(c) FROM catalog_items c; BEGIN OPEN ccur; FETCH ccur INTO catalog_item; DBMS_OUTPUT.PUT_LINE('I fetched item #' || catalog_item.id); CLOSE ccur; END; Distributed Database Applications

33. Object Downcasting • The TREAT function • Treat a generic base type object as the more narrowly defined subtype object DECLARE book book_t; catalog_item catalog_item_t := NEW book_t(); BEGIN book := TREAT(catalog_item AS book_t); END; / Distributed Database Applications

34. Test Object Type DECLARE CURSOR ccur IS SELECT VALUE(c) item FROM catalog_items c; arec ccur%ROWTYPE; BEGIN FOR arec IN ccur LOOP CASE WHEN arec.item IS OF (book_t) THEN DBMS_OUTPUT.PUT_LINE('Found a book with ISBN ' || TREAT(arec.item AS book_t).isbn); WHEN arec.item IS OF (serial_t) THEN DBMS_OUTPUT.PUT_LINE('Found a serial with ISSN ' || TREAT(arec.item AS serial_t).issn); ELSE DBMS_OUTPUT.PUT_LINE('Found unknown catalog item'); END CASE; END LOOP; END; Distributed Database Applications

35. Globalization and Localization • Unicode support • Variable precision • Single-byte vs. multi-byte • Sorting order • Non-character data • Date and time • Currency Distributed Database Applications

36. Character Codes • ASCII The ASCII Character set: characters 32 – 127. Distributed Database Applications

37. ASCII 1967 Distributed Database Applications

38. ISO 8859-1 Distributed Database Applications

39. Unicode Support • Unicode is a standard for representing characters • Character encoding with UTF • UTF-8 • UTF-16 • UTF-32 Distributed Database Applications

40. Unicode Encoding Space F, 10: Private Use Planes E: Supplementary Special-Purpose Plane Unassigned 2: Supplementary Ideographic Plane 1: Supplementary Multilingual Plane 0: Basic Multilingual Plane Distributed Database Applications

41. Associating characters with values in the code space Encoding Characters Distributed Database Applications

42. Three equivalent and interconvertible binary representations Encoding Forms Distributed Database Applications

43. Oracle Character Sets • Character set • NLS_CHARACTERSET • NLS_NCHAR_CHARACTERSET • Data types • CHAR and VARCHAR • NCHAR and NVARCHAR Distributed Database Applications

44. Oracle Character Sets Distributed Database Applications

45. Unicode Support Distributed Database Applications