web programming with pl sql l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Web Programming with PL/SQL PowerPoint Presentation
Download Presentation
Web Programming with PL/SQL

Loading in 2 Seconds...

play fullscreen
1 / 25

Web Programming with PL/SQL - PowerPoint PPT Presentation


  • 366 Views
  • Uploaded on

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

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 'Web Programming with PL/SQL' - hammer


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
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
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
oracle web extensions
Oracle Web Extensions
  • 3-tier: client, application server (OAS 9i), database (Oracle 9i)
  • mod_plsql in OAS
  • PL/SQL Web Toolkit
web request processing
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.
web request processing cont
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.
invoking mod plsql
Invoking mod_plsql
  • protocol://hostname[:port]/DAD location/[[!][schema.][package.]proc_ name[?query_string]]
  • Example:
    • http://db.gsu.edu:7777/pls/csc8711/simple
slide7
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.
a simple example
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;

a simple example cont
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

a simple example cont10
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

web toolkit
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.
htp package
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;

htp and htf package
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, …
owa util
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)

owa util15
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>

passing parameters
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>

html forms
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>
html forms18
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>
html forms19
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)
html forms20
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’);

disadvantages of web toolkit
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)
pl sql server pages psp
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
pl sql server pages psp23
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" %>

pl sql server pages psp24
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" %>

pl sql server pages psp25
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 ...