Pl sql it s all in the presentation
This presentation is the property of its rightful owner.
Sponsored Links
1 / 27

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


  • 115 Views
  • Uploaded on
  • Presentation posted in: General

PL/SQL: It’s all in the presentation!. Tim Hall Oracle ACE Director Oracle ACE of the Year 2006 OCP 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). PL/SQL: It’s all in the presentation!.

Download Presentation

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

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Pl sql it s all in the presentation

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


Pl sql it s all in the presentation1

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


Physical organization of objects

Physical organization of objects

Schema Owner

Tables

API Owner

PL/SQL APIs

Views?

Login User

PHP

APEX

Java

.NET

http://www.oracle-base.com


Physical organization of objects1

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


Why break things up like this

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


How do we implement it

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


Without using apis

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


Do views help

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


Using a view

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


Cursor variables ref cursor

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


Returning a cursor from a function

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


Using ref cursors

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


I don t have a simple query what so i do

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


Pipelining table functions

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


Defining a pipelined table function

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


Using a pipelined table function

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


Can we improve on this

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


Rc ptf together rc ptf query php

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


What have we shown

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


Web services and xml

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


Web services and xml1

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


Xml db native web services

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


Physical organization of objects2

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


Xml over http

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


Xml over http semi static data

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


Rest web services

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


Summary

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


  • Login