PL/SQL Development

1. Diego Thanh Nguyen PL/SQL Development www.smartbiz.vn

2. Table Of Content • I. Test-Driven PL/SQL Development • I.1. Front-end & Back-end • I.2. Development Workflow • II. Exception Handling Architecture • III. Optimize SQL in PL/SQL • IV. Java & PL/SQL • IV.1. Client Server Java/PLSQL Architecture • IV.2. Java - PL/SQL: Ad-hoc Call • IV.3. Java - PL/SQL: Function/Procedure Call www.smartbiz.vn

3. I.1. Front-end & Back-end Browser Java Oracle Developer Frontend • We usually just talk about front-end and back-end, but there's more to it than that. "Assign calls" app_call_mgr • Developers spend most of their time in the top two layers. • But they often don't have clearly defined boundaries. Backend (in the Oracle Database) Application Logic Backend "Is call open?" cm_calls_rp.is_open Business Rules "Log error" em_errors Infrastructure "Parse string" tb_string_utils Generic Utilities "Add new call" cm_calls_cp.ins Data Access "Call Table" cm_calls Data (tables) www.smartbiz.vn

4. I.2. Development Workflow Application Preparation 7 X 1 Coding Conventions The Build Cycle Test and Review SQLAccess Debug To QA /Production ErrorMgt Build / Fix Code 6 Single Unit Preparation 2 3 4 5 DefineReq’ments ConstructHeader Define Tests Build Test Code Post-Production Post-Production Bug Report Enhance. Request www.smartbiz.vn

5. I.3. Testing • Functional/System Tests • Performed by QA teams and users, tests entire application. • Stress Tests • Program works for 1 user, how about 10,000? Usually done by DBAs and system admins. • Quest's Benchmark Factory automates this process. • Unit Tests, aka ”Programmer Tests" • The test of a single unit of code. • These are the responsibility of developers, of the people who wrote the program. • All testing is important, but unit tests are the most fundamental kind of testing. www.smartbiz.vn

6. II.1. Error Management • Manage errors effectively and consistently: • Errorsare raised, handled, logged and communicated in a consistent, robust manner. • Some special issues for PL/SQL developers • The EXCEPTION datatype • How to find the line on which the error is raised? • Communication with non-PL/SQL host environments • Achieving ideal error management • Define your requirements clearly • PL/SQL error management & best practices www.smartbiz.vn

7. II.2. PL/SQL Error Management • Defining exceptions • Give name to Error with EXCEPTION_INIT PRAGMA • Raising exceptions • RAISE_APPLICATION_ERROR: communicates specific error back to a non-PL/SQL host environment • DML aren’t rolled back when exception is raised • Rollback Handling • Log information is committed, while leaving the business transaction unresolved • Handing exceptions www.smartbiz.vn

8. II.3. Handing Exceptions • The EXCEPTION section consolidates all error handling logic in a block. • But only traps errors raised in executable section of block. • Several useful functions usually come into play: • SQLCODE and SQLERRM • DBMS_UTILITY.FORMAT_ERROR_STACK • DBMS_UTILITY.FORMAT_ERROR_BACKTRACE • The DBMS_ERRLOG package • Quick and easy logging of DML errors. • The AFTER SERVERERROR trigger • Instance-wide error handling: most useful for non-PL/SQL front ends executing SQL statements directly. www.smartbiz.vn

9. II.4. Best Practices for Error Management • Some general guidelines - standards for logging mechanisms, error message text; must consistent • Avoid hard-coding of Error Numbers and Messages. • Build and use reusable components for Raising, Handling and Logging Errors. • Application-level code should not contain: • RAISE_APPLICATION_ERROR: don't leave it to the developer to decide how to raise. • PRAGMA EXCEPTION_INIT: avoid duplication of error definitions. • Object-like representation of an exception (DB) www.smartbiz.vn

10. II.4.1. Prototype Exception Manager PACKAGE errpkg IS PROCEDURE raise (err_in IN PLS_INTEGER); PROCEDURE raise (err_in in VARCHAR2); PROCEDURE record_and_stop ( err_in IN PLS_INTEGER := SQLCODE ,msg_in IN VARCHAR2 := NULL); PROCEDURE record_and_continue ( err_in IN PLS_INTEGER := SQLCODE ,msg_in IN VARCHAR2 := NULL); END errpkg; Generic Raises EXCEPTION WHEN NO_DATA_FOUND THEN errpkg.record_and_continue( SQLCODE, ' Not Exist ' || TO_CHAR (v_id)); WHEN OTHERS THEN errpkg.record_and_stop; END; Record and Stop Record & Continue • The rule: developers should only call a pre-defined handler inside an exception section • Easier for developers to write consistent, high-quality code • They don't have to make decisions about form of log & how the process should be stopped www.smartbiz.vn

11. III. Optimize SQL in PL/SQL • Advantage of PL/SQLenhancements for SQL. • III.1. BULK COLLECT • Use with implicit and explicit queries. • Move data from tables into collections. • III.2. FORALL • Use with inserts, updates and deletes. • Move data from collections to tables. • III.3. Table functions • III.4. Top Tip: Stop Writing So Much SQL • A key objective of this presentation is to have you stop taking SQL statements for granted inside your PL/SQL code. • Instead, you should think hard about when, where and how SQL statements should be written in your code. www.smartbiz.vn

12. III.1. SQL and PL/SQL Oracle Server PL/SQL Runtime Engine SQL Engine Procedural statement executor PL/SQL block SQL statement executor FOR rec IN emp_cur LOOP UPDATE employee SET salary = ... WHERE employee_id = rec.employee_id; END LOOP; Performance penalty for many “context switches”

13. III.1. Different process with FORALL Oracle server PL/SQL Runtime Engine SQL Engine Procedural statement executor PL/SQL block SQL statement executor FORALL indx IN list_of_emps.FIRST.. list_of_emps.LAST UPDATE employee SET salary = ... WHERE employee_id = list_of_emps(indx); Much less overhead for context switching

14. III.1. Use BULK COLLECT INTO for Queries Declare a collection of records to hold the queried data. Use BULK COLLECT to retrieve all rows. Iterate through the collection contents with a loop. DECLARE TYPE employees_aat IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER; l_employees employees_aat; BEGIN SELECT * BULK COLLECT INTO l_employees FROM employees; FOR indx IN 1 .. l_employees.COUNT LOOP process_employee (l_employees(indx)); END LOOP; END; WARNING! BULK COLLECT will not raise NO_DATA_FOUND if no rows are found. Always check contents of collection to confirm that something was retrieved.

15. III.2. FORALL Bulk Bind Statement • Instead of executing repetitive, individual DML statements, you can write your code like this: • Things to be aware of: • You MUST know how to use collections to use this feature! • Only a single DML statement is allowed per FORALL. • New cursor attributes: SQL%BULK_ROWCOUNT returns number of rows affected by each row in array. SQL%BULK_EXCEPTIONS... • Use SAVE EXCEPTIONS to continue past errors. • When Cursor FOR Loop vs. BULK COLLECT? • If you want to do complex processing on each row as it is queried – and possibly halt further fetching. PROCEDURE upd_for_dept (...) IS BEGIN FORALL indx IN list_of_emps.FIRST .. list_of_emps.LAST UPDATE employee SET salary = newsal_in WHERE employee_id = list_of_emps (indx); END;

16. III.2. Tips and Fine Points • BULK COLLECT • Collection is always filled sequentially, starting at row 1. • Production-quality code should generally use the LIMIT clause to avoid excessive memory usage. • Note: Oracle will automatically optimize cursor FOR loops to BULK COLLECT performance levels. • FORALL • Use whenever executing multiple single-row DML statements. • Used with Collection; Collection subscripts cannot be expressions. • Cannot reference fields of collection-based records inside FORALL. • But you can use FORALL to insert & update entire records. • Use the INDICES OF clause to use only the row numbers defined in another array. • Use VALUES OF clause to use only values defined in another array. www.smartbiz.vn

17. III.3. Table Function • The Wonder Of Table Functions • Table function allow to perform arbitrarily complex transformations of data and then make that data available through a query. • Not everything can be done in SQL. • Combined with REF CURSORs, you can now more easily transfer data from within PL/SQL to host environments. • Java, works very smoothly with cursor variables • Building a table function • Return nested table or varray based on schema-defined type. • Types defined – only used with pipelined table functions. • The function header and the way it is called must be SQL-compatible: all parameters use SQL types; no named notation. • In some cases (streaming and pipelined functions), the IN parameter must be a cursor variable -- a query result set. www.smartbiz.vn

18. III.3. Streaming Data with Table Functions CREATE OR REPLACE PACKAGE refcur_pkg IS TYPE refcur_t IS REF CURSOR RETURN stocktable%ROWTYPE; END refcur_pkg; / CREATE OR REPLACE FUNCTION stockpivot (dataset refcur_pkg.refcur_t) RETURN tickertypeset ... BEGIN INSERT INTO tickertable SELECT * FROM TABLE (stockpivot (CURSOR (SELECT * FROM stocktable))); END; / • "stream" data through several stages within a single SQL statement. • Example: transform one row in the stocktable to two rows in the tickertable. www.smartbiz.vn

19. III.3. Piping rows out from Pipelined function CREATE FUNCTION stockpivot (p refcur_pkg.refcur_t) RETURN tickertypeset PIPELINED IS out_rectickertype := tickertype (NULL, NULL, NULL); in_recp%ROWTYPE; BEGIN LOOP FETCH p INTO in_rec; EXIT WHEN p%NOTFOUND; out_rec.ticker := in_rec.ticker; out_rec.pricetype := 'O'; out_rec.price := in_rec.openprice; PIPE ROW (out_rec); END LOOP; CLOSE p; RETURN; END; Add PIPELINED keyword to header Pipe a row of data back to calling block or query RETURN...nothing at all! Return data iteratively, asynchronous to termination of function. As data is produced within the function, it is passed back to the calling process/query. www.smartbiz.vn

20. III.4. Top Tip: Stop Writing So Much SQL Order Entry Program Order Table Item Table Order Table Item Table Application Code Intermediate Layer Bottom line: if everyone writes SQL whenever and wherever they want to, it is very difficult to maintain and optimize the code. General principle: figure out what is volatile and then hide that stuff behind a layer of code to minimize impact on application logic. Single Point of (SQL) Robust Definition: if same statement appears in multiple places in application code, very difficult to maintain and optimize that code. Avoid SQL Repetition www.smartbiz.vn

21. IV.1. Client Server Java/PLSQL Architecture • PL/SQL Programming Language: • If/then/else; Loops;Functioncalls • Transactional • Procedural www.smartbiz.vn

22. IV.2. Ways to call PL/SQL • ADHOC • Send the PL/SQL block of code from the Client-Java program to the Server for processing. • Query based on Prepared Statement • FUNCTION • Client Side calls a PL/SQL function in Oracle. PL/SQL is already compiled and loaded in Oracle. • Function will return a value • PROCEDURE • Client calls a PL/SQL procedure • Similar to function, but does not return values. www.smartbiz.vn

23. IV.2. ADHOC: PL/SQL Example 1 2 3 4 5 6 7 PreparedStatementps=null; sSQL=new String("declare" + " l_id number:=0;" + " ret number:=0;" + " lssno varchar2(32):=?;" + " error_msg varchar2(1026):=null;" + " BEGIN" + " select count(*) into ret from person where SSNO= lssno ;" + " if(ret=0) then" + " insert into person(id,ssno) values(seq_id.nextval, lssno) returning id into ret;" + " end if;" + " ?:=ret;" + " EXCEPTION WHEN OTHERS THEN" + " ?:=SQLERRM;" + " END;"); ps =connection.prepareStatement(sbSQL.toString()); intindx=1; ps.setString(indx++,SSNO); intrettype=Types.INTEGER; ps.registerOutParameter(indx++,rettype); rettype=Types.VARCHAR; ps.registerOutParameter(indx++,rettype); ps.executeUpdate(); l_sError=ps.getString(2); if(l_sError!=null) { System.err.println("PL/SQL Error: " + l_sError); return; }else { ret=ps.getInt(1); System.out.println("New USERID: " + ret); } www.smartbiz.vn

24. IV.3. Java PL/SQL: Function/Procedure Call • Key Things to Note: • Minimize SQL code on client side. • loaded into Oracle once at install time. • Ready for portability • Within Oracle – yes • JDBC Standard - using other database – perhaps • SQL Standard - SQL92 syntax? • Consider using global temporary table. import java.sql.*; import oracle.sql.*; … PreparedStatementps =null; ArrayDescriptordesc = ArrayDescriptor.createDescriptor("JOEL.STR_VARRAY”,m_Conn); String sCar=""; String cars[]=new String[10]; for(inti=0;i<10;i++) { sCar="carX"+i; // CONTRIVE A NAME of a CAR cars[i]=new String(sCar); } ARRAY array3 = new ARRAY (desc, m_Conn, cars); String sql=new String("{call api_person_pkg.storePersonCar('joelt',?) }"); ps= m_Conn.prepareStatement(sql); // Set the values to insert ((oracle.jdbc.driver.OraclePreparedStatement)ps).setARRAY(1, array3); // Insert the new row ps.executeUpdate(); m_Conn.commit(); www.smartbiz.vn

25. III.3. Calling PL/SQL Procedures • Step1: Create and Prepare a CallableStatementObject • CallableStatementmyCallableStatement = myConnection.prepareCall( "{call update_product_price(?, ?)}”); • Step2: Provide Parameter Values • myCallableStatement.setDouble(2, 1.1); • Step3: Call the execute() Method • myCallableStatement.execute(); • myCallableStatement.close(); www.smartbiz.vn

26. III.3. Calling PL/SQL Functions • Step 1: Create and Prepare a CallableStatementObject • CallableStatementmyCallableStatement = myConnection.prepareCall( "{call update_product_price_func (?, ?)}”); • Step 2: Register the Output Parameter • myCallableStatement.registerOutParameter(1,java.sql.Types.INTEGER); • Step2: Provide Parameter Values • myCallableStatement.setInt(2, 1); • Step 4: Run the CallableStatement • myCallableStatement.execute(); • Step 5: Read the Return Value • int result = myCallableStatement.getInt(1); • myCallableStatement.close(); www.smartbiz.vn