PL/SQL: It’s all in the presentation!

1. PL/SQL: It’s all in the presentation! Tim Hall Oracle ACE Director Oracle ACE of the Year 2006OCP DBA (7, 8, 8i, 9i, 10g, 11g) OCA PL/SQL Developer http://www.oracle-base.com Oracle PL/SQL Tuning (Rampant) Oracle Job Scheduling (Rampant) http://www.oracle-base.com

2. PL/SQL: It’s all in the presentation! • Physical organization of objects • Present queries from APIs • Ref cursors • Table functions and pipelining • Presenting data as web services and XML • SOAP web services • XML over HTTP • REST web services http://www.oracle-base.com

3. Physical organization of objects Schema Owner Tables API Owner PL/SQL APIs Views? ✗ Login User ✗ PHP APEX Java .NET http://www.oracle-base.com

4. Physical organization of objects Schema Owner Tables API Owner API Owner PL/SQL APIs PL/SQL APIs Login User Login User Login User PHP APEX Java .NET http://www.oracle-base.com

5. Why break things up like this? • All data access via presentation (API) layer. • Better security. • Hides processing complexity from clients. • Presentation layer is sharable between applications. • Easier to tune and trace. • Hides schema changes from client applications. • Table or Transactional APIs? • Transactional APIs are important to me. APIs that perform business functions and and are understandable by the business. • I feel table APIs are unnecessary, but if you like them use them. • Don’t present table APIs to the outside world. http://www.oracle-base.com

6. How do we implement it? • Use existing techniques to abstract the data: • Packaged procedures and functions for data processing. • Ref cursors and pipelined table functions for data presentation. • schema_setup.sql http://www.oracle-base.com

7. Without using APIs $conn = get_connection(); $sql = "SELECT d.dname, WM_CONCAT(e.ename) AS employees FROM dept d JOIN empe ON e.deptno = d.deptno GROUP BY d.dname ORDER BY d.dname”; $stmt = oci_parse($conn, $sql); $result = oci_execute($stmt, OCI_DEFAULT); while (oci_fetch($stmt)) { $dname = oci_result($stmt, "DNAME"); $employees = oci_result($stmt, "EMPLOYEES"); // Do something with the data } oci_free_statement($stmt); oci_close($conn) ✗ Mix of data processing and visualization code basic_query.php http://www.oracle-base.com

8. Do views help? • Views do reduce complexity of code in client apps. • I prefer not to expose views to client developers. • Risk of client developers writing joins between views. • How would a view affect the previous client code? CREATE OR REPLACE VIEW v_emps_by_deptAS SELECTd.dname, WM_CONCAT(e.ename) AS employees FROM dept d JOINempeONe.deptno = d.deptno GROUP BY d.dname ORDER BY d.dname; web_view.sql http://www.oracle-base.com

9. Using a view $conn = get_connection(); $sql = "SELECT dname, employees FROM v_emps_by_dept”; $stmt = oci_parse($conn, $sql); $result = oci_execute($stmt, OCI_DEFAULT); while (oci_fetch($stmt)) { $dname = oci_result($stmt, "DNAME"); $employees = oci_result($stmt, "EMPLOYEES"); // Do something with the data } oci_free_statement($stmt); oci_close($conn); ✔ ✗ Reduced Complexity Mix still present view_query.php http://www.oracle-base.com

10. Cursor variables (REF CURSOR) • What are they? • A pointer to current row in multi-row query. • Why are they useful? • Allow us to separate opening and processing of cursors. • Can be passed as parameters. • Why is that useful to us? • Allows us to pass resultsets to client applications • Is that all they can do? • No, but it gets boring pretty fast… • Do we have to define REF CURSOR types? • No. We can be lazy and use SYS_REFCURSOR type. http://www.oracle-base.com

11. Returning a cursor from a function CREATE OR REPLACE PACKAGE BODYweb_rc_apiAS FUNCTIONget_emps_by_deptRETURN SYS_REFCURSOR AS l_cursorSYS_REFCURSOR; BEGIN OPENl_cursorFOR SELECTd.dname, WM_CONCAT(e.ename) AS employees FROM dept d JOIN empeONe.deptno = d.deptno GROUP BYd.dname ORDER BYd.dname; RETURNl_cursor; ENDget_emps_by_dept; ENDweb_rc_api; / web_rc_api.sql http://www.oracle-base.com

12. Using ref cursors $conn = get_connection(); $sql = "BEGIN :rc := web_rc_api.get_emps_by_dept; END;”; $stmt = oci_parse($conn, $sql); $cursor = oci_new_cursor($conn); oci_bind_by_name($stmt, ':rc', $cursor, -1, OCI_B_CURSOR); $result = oci_execute($stmt, OCI_DEFAULT); $result = oci_execute($cursor, OCI_DEFAULT); while (oci_fetch($stmt)) { $dname = oci_result($stmt, "DNAME"); $employees = oci_result($stmt, "EMPLOYEES"); // Do something with the data } oci_free_statement($stmt); oci_close($conn); ✔ rc_query.php http://www.oracle-base.com

13. I don’t have a simple query. What so I do? • You could use a table function. • What’s one of those? • Functions that return collections are known as table functions. • How can that help me? • In combination with the TABLE function, they can be used in the FROM clause of a query like a regular table.tf_test.sql • For regular table functions, the collection must be defined using database OBJECT types. • Is that all I need to know? • Not really. You need to know about pipelining. http://www.oracle-base.com

14. Pipelining table functions • A table function builds the entire collection before returning any data, while a pipelined table function “pipes” each row out as soon as it is created. • How does that help me? • It reduces first row lag…ptf_schema.sql, ptf_package.sql, ptf_query.sql • As the collection is never fully resident in memory, pipelining can produce a considerable memory saving.memory_usage.sql • Since 9i R2, the types used to define the pipelined table function can be defined in a package, but this method can produce management problems, so I prefer the explicit method.implicit_types.sql • But how does that really help me? • You can use PL/SQL to build the row then pass it out. http://www.oracle-base.com

15. Defining a Pipelined table Function CREATE OR REPLACE PACKAGE BODY web_ptf_api AS FUNCTION get_emps_by_dept RETURN t_emps_by_dept_tabPIPELINED AS BEGIN -- Using implicit array processing (array size 100). FOR cur_recIN (SELECT d.dname, WM_CONCAT(e.ename) AS employees FROM dept d JOIN empe ON e.deptno = d.deptno GROUP BY d.dname ORDER BY d.dname) LOOP PIPE ROW (t_emps_by_dept_row(cur_rec.dname, cur_rec.employees)); END LOOP; RETURN; END get_emps_by_dept; END web_ptf_api; web_ptf_api.sql http://www.oracle-base.com

16. Using a pipelined table function $conn = get_connection(); $sql = "SELECT dname, employees FROM TABLE(web_ptf_api.get_emps_by_dept)”; $stmt = oci_parse($conn, $sql); $result = oci_execute($stmt, OCI_DEFAULT); while (oci_fetch($stmt)) { $dname = oci_result($stmt, "DNAME"); $employees = oci_result($stmt, "EMPLOYEES"); // Do something with the data } oci_free_statement($stmt); oci_close($conn); ✔ ✗ ptf_query.php http://www.oracle-base.com

17. Can we improve on this? • We can combine Pipelined Table Functions and Ref Cursors.web_rc_ptf_api.sql • How will that affect the client code? http://www.oracle-base.com

18. RC & PTF together (rc_ptf_query.php) $conn = get_connection(); $sql = "BEGIN :rc := web_rc_api.get_emps_by_dept_rc; END;”; $stmt = oci_parse($conn, $sql); $cursor = oci_new_cursor($conn); oci_bind_by_name($stmt, ':rc', $cursor, -1, OCI_B_CURSOR); $result = oci_execute($stmt, OCI_DEFAULT); $result = oci_execute($cursor, OCI_DEFAULT); while (oci_fetch($stmt)) { $dname = oci_result($stmt, "DNAME"); $employees = oci_result($stmt, "EMPLOYEES"); // Do something with the data } oci_free_statement($stmt); oci_close($conn); ✔ rc_ptf_query.php http://www.oracle-base.com

19. What have we shown? • We can use APIs to hide complexity from client application developers. • How do we present queries from our APIs? • Ref cursors • Pipelined table functions • Views? • Remember: The technology dictates will and won’t want to do with APIs, not just our ideals. • Is that the only way we can present data from PL/SQL? • Certainly not… http://www.oracle-base.com

20. Web Services and XML • eGov AU: Craig Thomler's personal eGovernment and Gov 2.0 thoughts and speculations from an Australian perspective • Overcoming public sector hurdles to Gov 2.0http://egovau.blogspot.com/2009/10/overcoming-public-sector-hurdles-to-gov.html • Choice quotes: • "...people are generally most comfortable with the technologies they grew up with...” • "...government systems are struggling in some areas to keep up with the rate of change...” • "If our systems can't support Gov 2.0 initiatives then it is unlikely that our senior management will.” http://www.oracle-base.com

21. Web Services and XML • What does this have to do with PL/SQL? • PL/SQL allows you to create web applications, without the learning curve of Java or .NET. • Oracle allows you to present existing PL/SQL code as web services with zero effort. • Oracle's web toolkit allows you to present data as XML really easily. • PL/SQL is mature, not legacy. http://www.oracle-base.com

22. XML DB Native Web Services • Oracle XML DB Native Web Services present your PL/SQL as Simple Object Access Protocol (SOAP) web services. • Requires a single configuration step on the database. • Enabled for “schema” by granting: • GRANT XDB_WEBSERVICES TO user; • Optionally: • GRANT XDB_WEBSERVICES_OVER_HTTP TO user; • GRANT XDB_WEBSERVICES_WITH_PUBLIC TO user; --? • web_services_setup.sql • XML DB auto-generates a WSDL file. • We send a SOAP Request and get a SOAP Response returned. • call_ws.pl (run it) • Physical organization of schema keeps things neat. http://www.oracle-base.com

23. Physical organization of objects Schema Owner Tables WS API Owner API Owner PL/SQL APIs PL/SQL APIs Login User Java .NET APEX http://www.oracle-base.com

24. XML over HTTP • We can also present XML directly from the database. • First we define a Database Access Descriptor (DAD) to give a “schema” access to the web toolkit functionality.xml_api_setup.sql • Next we define a package to generate our XML.xml_api.sql (run it) • This is an incredibly simple way to get XML out of the database. • Once again, physical organization is important. http://www.oracle-base.com

25. XML over HTTP (Semi-Static Data) • The previous method regenerates the XML each time the URL is called. • For semi-static data this is a waste of resources. • Solution? Generate once and re-present. • How? Place it in the XML DB file system.xml_db_setup.sql • Next we define a procedure to generate our XML.semi_static.sql • How do we access this file?HTTP, FTP access, WebDAV • This can reduce resource usages. http://www.oracle-base.com

26. REST web services • The previous method is similar to Representational State Transfer (REST) web services, with the exception of the URL. • We can mimic REST web services using the EPG parameters: • PATH-ALIAS • PATH-ALIAS-PROCEDURE • rest_api_setup.sql • rest_api.sql • REST Demo • We can easily code REST web services directly from PL/SQL. http://www.oracle-base.com

27. Summary • Physical organization of objects • Important for us to present business driven functionality. • Ref cursors • Allows us to hide complexity from client applications. • Table functions • Allow us to return complex data as if it were a simple query. • Presenting data as web services and XML is easy using PL/SQL • Presenting existing PL/SQL code as SOAP web services. • Producing REST web services from PL/SQL. • Which all keeps us relevant in the new world order. • Demo Code: http://www.oracle-base.com/workshops http://www.oracle-base.com