1 / 27

Enhancing PL/SQL Presentations: Best Practices and Techniques by Tim Hall

Join Tim Hall, Oracle ACE Director and 2006 ACE of the Year, as he explores the intricacies of PL/SQL and effective data presentation strategies. This presentation covers various topics such as the physical organization of database objects, the use of ref cursors, table functions, and pipelining to create robust APIs. Learn how to present data as web services (SOAP, REST) and the advantages of abstracting data access through an API layer. Enhance your understanding of PL/SQL tuning and job scheduling for optimal performance in Oracle databases.

ronan-romero
Télécharger la présentation

Enhancing PL/SQL Presentations: Best Practices and Techniques by Tim Hall

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related