PL/SQL Server Pages (Web Programming with PL/SQL)

1
PL/SQL Server Pages(Web Programming with PL/SQL)

• Erdogan Dogdu
• Georgia State University
• Computer Science Department
• edogdu_at_cs.gsu.edu
• http//db.gsu.edu/edogdu

2
Content

• Disadvantages of Web Toolkit
• PL/SQL Server Pages
• Syntax
• Loading PSP Pages
• Example

3
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)

4
PL/SQL Server Pages (PSP)

• Author web pages using script-friendly HTML
  authoring tools.
• Drop in PL/SQL code within HTML code using
  scripting tag lt gt.
• In short
• Web Toolkit generate HTML from PL/SQL
• PSP embedded PL/SQL within HTML

5
Syntax of PL/SQL Server Pages

• Same script tag syntax as in ASP and JSP lt gt
• PSP files should have .psp extension
• Can be as simple as an HTML page (no PL/SQL
  script)
• Specifying the scripting language
• lt_at_ page language"PL/SQL" gt

6
Syntax of PL/SQL Server Pages

• Parameter passing
• lt_at_ plsql parameter"varname" gt
• Returned document type
• lt_at_ page contentType"MIMEtype" gt
• Such as text/xml, text/plain, image/jpeg
• Stored procedure name
• lt_at_ page procedure"procname" gt

7
Loading PL/SQL Server Pages

• Loading psp document to Oracle
• loadpsp -replace -user username/password_at_co
  nnect_string include_file_name ...
  error_file_name psp_file_name ...

8
More Syntax

• Statements
• lt PL/SQL Statement
• PL/SQL Statement gt
• Expression
• lt PL/SQL Expression gt
• Conditional blocks
• lt if condition then gt
• HTML code
• lt else gt
• HTML code
• lt end if gt

9
More Syntax

• Including files
• lt_at_ include file"path name" gt
• PL/SQL declarations
• lt! PL/SQL declaration
• PL/SQL declaration ... gt

10
PSP Example Grade Book

• First page get_access.psp
• Overview Allows login to application
• lt_at_ page language"PL/SQL" gt
• lt_at_ plsql procedure"get_access" gt
• ltHTMLgt
• ltHEADgt
• ltTITLEgtGet passwordlt/TITLEgt
• ltH1gtGrade Book Access Pagelt/H1gt
• lt/HEADgt
• (cont. next slide)

11
PSP Example Grade Book

• ltBODYgt
• ltFORM ACTION"ltowa_util.get_owa_service_pathgtst
  art_session" METHOD"POST"gt
• ltBRgt
• Today is ltto_char(sysdate, 'Day')gt,
• ltto_char(sysdate, 'Dd / Mon / YYYY HHMI AM')gt
  
• ltBRgtltBRgt
• (cont. next slide)

12
PSP Example Grade Book

• ltTABLEgtltTRgt
• ltTDgtltSTRONGgtUSER ID lt/STRONGgtlt/TDgt
• ltTDgtltINPUT TYPE"text" NAME"usid" SIZE"20"
  MAXLENGTH"50"gtlt/TDgt
• lt/TRgt
• ltTRgt
• ltTDgtltSTRONGgtPASSWORD lt/STRONGgtlt/TDgt
• ltTDgtltINPUT TYPE"password" NAME"passwd"
  SIZE"20" MAXLENGTH"50"gtlt/TDgt
• lt/TRgtlt/TABLEgt
• ltINPUT TYPE"submit" VALUE" Proceed "gt
• ltINPUT TYPE"reset" VALUE"Reset"gt
• lt/FORMgtlt/BODYgtlt/HTMLgt

13
PSP Example Grade Book
14
Form Processing
This form processing program does not generate
any HTML code. Therefore, we leave it as a PL/SQL
program (start_session.sql)

• create or replace procedure start_session(
• usid in varchar2 DEFAULT NULL,
• passwd in varchar2 DEFAULT NULL) AS
• begin
• select A.userid, A.password,
• A.lastAccess, A.authority
• into user_buffer, passwd_buffer,
  time_buffer,
• auth_buffer
• from users A
• where upper(A.userid)upper(start_session.usid
  )
• end start_session

15
Form Processing in PSP

• Example
• lt_at_ page language"PL/SQL" gt
• lt_at_ plsql procedure"teacher_menu" gt
• lt_at_ plsql parameter"u_access" type"varchar2"
  default"null" gt
• lt_at_ plsql parameter"userid" type"varchar2"
  default"null" gt