1 / 25

Web Programming with PL/SQL

Web Programming with PL/SQL. Erdogan Dogdu Georgia State University Computer Science Department edogdu@cs.gsu.edu http://db.gsu.edu/~edogdu. Content. Oracle Architecture for Web Apps Oracle mod_plsql Apache module HTTP A Simple PL/SQL Web Toolkit Example PL/SQL Web Toolkit

hammer
Download Presentation

Web Programming with PL/SQL

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. Web Programming with PL/SQL Erdogan Dogdu Georgia State University Computer Science Department edogdu@cs.gsu.edu http://db.gsu.edu/~edogdu

  2. Content • Oracle Architecture for Web Apps • Oracle mod_plsql Apache module • HTTP • A Simple PL/SQL Web Toolkit Example • PL/SQL Web Toolkit • Parameter Passing • HTML Forms • PL/SQL Server Pages

  3. Oracle Web Extensions • 3-tier: client, application server (OAS 9i), database (Oracle 9i) • mod_plsql in OAS • PL/SQL Web Toolkit

  4. Web Request Processing • The Oracle HTTP Server receives a PL/SQL Server Page request from a client browser. • The Oracle HTTP Server routes the request to mod_plsql. • The request is forwarded by mod_plsql to the Oracle Database. By using the configuration information stored in your DAD, mod_plsql connects to the database.

  5. Web Request Processing (cont.) • Mod_plsql prepares the call parameters, and invokes the PL/SQL procedure in the application. • The PL/SQL procedure generates an HTML page using data and the PL/SQL Web Toolkit accessed from the database. • The response is returned to mod_plsql. • The Oracle HTTP Server sends the response to the client browser.

  6. Invoking mod_plsql • protocol://hostname[:port]/DAD location/[[!][schema.][package.]proc_ name[?query_string]] • Example: • http://db.gsu.edu:7777/pls/csc8711/simple

  7. HTTP • GET, POST, HEAD methods • GET. Parameters in query string. • http://host:port/pls/DAD/foo?a=v&b=1 • POST. For large parameter data. • http://host:port/pls/DAD/foo, POST data="a=v&b=1“ • HEAD. No content data is streamed back. Only a confirmation.

  8. A Simple Example • Write a PL/SQL procedure (simple.sql) using PL/SQL Web Toolkit: CREATE OR REPLACE PROCEDURE simple AS l_user varchar2(30); BEGIN select USER into l_user from dual; htp.htmlOpen; htp.headOpen; htp.title('A Very Very Simple Example'); htp.headClose; htp.bodyOpen; htp.line; htp.header(1,'Simple Example'); htp.line; htp.paragraph; …

  9. A Simple Example (cont.) … htp.print('Today''s date is: ' || to_char(sysdate, 'DD/MM/YYYY')); htp.br; htp.print('Today''s day is: ' || to_char(sysdate, 'DAY')); htp.paragraph; htp.print('Ordinary <em>tags</em> can be used in the strings that we send.'); htp.print('Your Oracle USERID is ' || l_user); htp.line; htp.address('Raj Sunderraman'); -- owa_util.signature('orarbkx.simple'); htp.bodyClose; htp.htmlClose; EXCEPTION When others Then htp.print(SQLERRM); htp.bodyClose; htp.htmlClose; END; / show errors

  10. A Simple Example (cont.) • Upload the procedure ‘simple’ to the database: > sqlplus SQL> @simple.sql Procedure created. No errors. • Run procedure: http://db.gsu.edu:7777/pls/csc8711/simple

  11. Web Toolkit Oracle Web Toolkit includes a number of packages: • htp and htf package: hypertext procedures and functions, • owa_cookie: to handle HTTP cookies, • owa_util: utility subprograms, • etc.

  12. htp package Example: create or replace procedure hello AS BEGIN htp.htmlopen; -- generates <HTML> htp.headopen; -- generates <HEAD> htp.title('Hello'); -- generates <TITLE>Hello</TITLE> htp.headclose; -- generates </HEAD> htp.bodyopen; -- generates <BODY> htp.header(1, 'Hello'); -- generates <H1>Hello</H1> htp.bodyclose; -- generates </BODY> htp.htmlclose; -- generates </HTML> END;

  13. htp and htf package • More htp functions: • Comment: htp.comment • Applet: htp.appletopen, … • List: htp.olistOpen, htp.ulinstOpen, … • Form: htp.formOpen, … • Table: htp.tableOpen, htp.tableData, … • Image: htp.img, etc. • Formatting: htp.print, htp.bold, htp.underline, … • Frame: htp.frame, …

  14. owa_util • owa_util.tablePrint create or replace procedure showemps is ignore_more boolean; begin ignore_more := owa_util.tablePrint('emp', 'BORDER', OWA_UTIL.PRE_ TABLE); end; This procedure gives the output (next slide)

  15. owa_util <PRE> ----------------------------------------------------------------- | EMPNO |ENAME |JOB |MGR |HIREDATE | SAL | COMM | DEPTNO | ----------------------------------------------------------------- | 7369| SMITH | CLERK | 7902 | 17-DEC-80 | 800 | | 20 | | 7499| ALLEN | SALESMAN| 7698 | 20-FEB-81 | 1600 | 300 | 30 | | 7521| WARD | SALESMAN| 7698 | 22-FEB-81 | 1250 | 500 | 30 | | 7566| JONES | MANAGER | 7839 | 02-APR-81 | 2975 | | 20 | | 7654| MARTIN | SALESMAN| 7698 | 28-SEP-81 | 1250 | 1400| 30 | | 7698| BLAKE | MANAGER | 7839 | 01-MAY-81 | 2850 | | 30 | | 7900| JAMES | CLERK | 7698 | 03-DEC-81 | 950 | | 30 | | 7902| FORD | ANALYST | 7566 | 03-DEC-81 | 3000 | | 20 | | 7934| MILLER | CLERK | 7782 | 23-JAN-82 | 1300 | | 10 | ----------------------------------------------------------------- </PRE>

  16. Passing Parameters • GET method http://db:7777/pls/csc8711/pl1?str=Hello&num=4 create or replace procedure pl1(str in varchar2, num in number) as begin for i in 1..num loop htp.print(i || '. ' || str); htp.br; end loop; end; 1. Hello <br> 2. Hello <br> 3. Hello <br> 4. Hello <br>

  17. HTML Forms • htp.formOpen(curl in varchar2, cmethod in varchar2 default ‘POST’, ctarget in varchar2, cenctype in varchar2 default null, cattributes in varchar2 default null); • <FORM ACTION=“curl” METHOD=“cmethod” TARGET=“ctarget” ENCTYPE=“cenctype” cattributes>

  18. HTML Forms • htp.formSubmit(cname in varchar2 default null, cvalue in varchar2 default ‘Submit’, cattributes in varchar2 default null) • <INPUT TYPE=“submit” NAME=“cname” VALUE=“cvalue” cattributes>

  19. HTML Forms • HTML forms can have the following elements: • Single line input text (htp.formText) • Single line input password (htp.formPassword) • Checkboxes (htp.formCheckbox) • Radio buttons (htp.formRadio) • Submit buttons (htp.formSubmit) • Text areas (htp.formTextarea) • Selects (htp.formSelectOpen, htp.formSelectOption, htp.formSelectClose)

  20. HTML Forms • Useful functions: owa_util.get_owa_service_path: Returns the prefix of the URL pointing to PL/SQL procedure Example: http://db.gsu.edu:7777/pls/survey/login • Use in PL/SQL web program as: htp.formOpen(owa_util.get_owa_service_ path|| ‘start_session’);

  21. Disadvantages of Web Toolkit • PL/SQL Web Toolkit (htp, htf) generates HTML code form PL/SQL programs. • Generating nice web pages is difficult, you cannot author PL/SQL programs in Frontpage. • Solution is PSP (next)

  22. PL/SQL Server Pages (PSP) • Author web pages using script-friendly HTML authoring tools. • Drop in PL/SQL code in web pages. • In short: • Web Toolkit: generate HTML from PL/SQL • PSP: embedded PL/SQL within HTML

  23. PL/SQL Server Pages (PSP) • Same script tag syntax as JSP, also similar to ASP • PSP files should have .psp extension • Can be as simple as an HTML page (no PL/SQL script) • Specifying the scripting language <%@ page language="PL/SQL" %>

  24. PL/SQL Server Pages (PSP) • Parameter passing <%@ plsql parameter="varname" %> • Returned document type <%@ page contentType="MIMEtype" %> Such as text/xml, text/plain, image/jpeg • Stored procedure name <%@ page procedure="procname" %>

  25. PL/SQL Server Pages (PSP) • Loading psp document to Oracle loadpsp [ -replace ] -user username/password[@connect_string] [ include_file_name ... ] [ error_file_name ] psp_file_name ...

More Related