pl sql it s all in the presentation
Download
Skip this Video
Download Presentation
PL/SQL: It’s all in the presentation!

Loading in 2 Seconds...

play fullscreen
1 / 27

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


  • 174 Views
  • Uploaded on

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!.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' PL/SQL: It’s all in the presentation!' - dean


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

ad