OWS Example (Cookies, Cursors, Frames, etc...)
OWS Example
including cookies, cursors, frames, etc...
The following example is OWS Source Code that
demonstrates PL/SQL Cursors, Cookies and many of the features that
OWS offers.
A basic understanding of OWS, PL/SQL, HTML, Frames, and Cursors is assumed.
This is the Package Body
CREATE OR REPLACE PACKAGE course AS
PROCEDURE q_employee
(p_last_name IN VARCHAR2 DEFAULT NULL);
PROCEDURE q_employee
(p_last_name IN VARCHAR2 DEFAULT NULL,
p_submit IN owa_image.point);
PROCEDURE q_cust_ord
(p_cust_id IN VARCHAR2 DEFAULT NULL);
PROCEDURE q_product
(p_product_name IN VARCHAR2 DEFAULT NULL);
PROCEDURE u_employee_form
(p_rowid IN VARCHAR2 DEFAULT NULL);
PROCEDURE u_employee
(p_rowid IN VARCHAR2,
p_first_name IN s_emp.first_name%TYPE,
p_last_name IN s_emp.last_name%TYPE,
p_userid IN s_emp.userid%TYPE,
p_manager_id IN s_emp.manager_id%TYPE,
p_title IN s_emp.title%TYPE,
p_dept_id IN s_emp.dept_id%TYPE,
p_salary IN s_emp.salary%TYPE,
p_comm_pct IN s_emp.commission_pct%TYPE);
PROCEDURE home;
PROCEDURE login_form
(p_message IN VARCHAR2 DEFAULT NULL,
p_username IN VARCHAR2 DEFAULT NULL,
p_password IN VARCHAR2 DEFAULT NULL);
PROCEDURE validate_user
(p_username IN VARCHAR2 DEFAULT NULL,
p_password IN VARCHAR2 DEFAULT NULL);
PROCEDURE toc;
PROCEDURE toolbar (p_window_name IN VARCHAR2 default NULL);
PROCEDURE frame (p_window_name IN VARCHAR2 default NULL);
PROCEDURE content;
END course;
/
This is the Package
CREATE OR REPLACE PACKAGE BODY course AS
/*FORMTEXT procedure
Creates a series of table related tags */
PROCEDURE formtext
(p_name IN VARCHAR2,
p_value IN VARCHAR2,
p_label IN VARCHAR2)
IS
BEGIN
htp.tableRowOpen;
htp.tableData (p_label);
htp.tableData (htf.formText (p_name,25,25,p_value),'LEFT');
htp.tableRowClose;
END formtext;
/*U_EMPLOYEE_FORM procedure
Creates the Employee update form */
PROCEDURE u_employee_form
(p_rowid IN VARCHAR2 DEFAULT NULL)
IS
emp_rec s_emp%ROWTYPE;
BEGIN
generic.startup ('OWS: Employee Update Form');
htp.bodyOpen ('/img/backgd.gif');
generic.create_target (1,'Example','CENTER');
SELECT * INTO emp_rec
FROM s_emp WHERE rowid = p_rowid;
htp.anchor ('course.q_employee','Return to Employee query');
htp.line;
generic.create_target (2,'Update employee number '||emp_rec.id);
htp.CenterOpen;
htp.formOpen ('course.u_employee');
htp.formHidden ('p_rowid',p_rowid);
htp.tableOpen;
formtext('p_first_name', emp_rec.first_name, 'First name: ');
formtext('p_last_name', emp_rec.last_name, 'Last name: ');
formtext('p_userid', emp_rec.userid, 'Userid: ');
formtext('p_manager_id', emp_rec.manager_id, 'Manager number: ');
formtext('p_title', emp_rec.title, 'Title: ');
formtext('p_dept_id', emp_rec.dept_id, 'Department number: ');
formtext('p_salary', emp_rec.salary, 'Salary: ');
formtext('p_comm_pct', emp_rec.commission_pct, 'Commission percent: ');
htp.tableClose;
htp.br;
htp.br;
htp.formSubmit (cvalue=>'Update');
htp.formReset;
htp.formClose;
htp.CenterClose;
generic.footer;
END u_employee_form;
/*U_EMPLOYEE procedure
Updates the Employee table */
PROCEDURE u_employee
(p_rowid IN VARCHAR2,
p_first_name IN s_emp.first_name%TYPE,
p_last_name IN s_emp.last_name%TYPE,
p_userid IN s_emp.userid%TYPE,
p_manager_id IN s_emp.manager_id%TYPE,
p_title IN s_emp.title%TYPE,
p_dept_id IN s_emp.dept_id%TYPE,
p_salary IN s_emp.salary%TYPE,
p_comm_pct IN s_emp.commission_pct%TYPE)
IS
BEGIN
UPDATE s_emp SET
first_name = p_first_name,
last_name = p_last_name,
userid = p_userid,
manager_id = p_manager_id,
title = p_title,
dept_id = p_dept_id,
salary = p_salary,
commission_pct = p_comm_pct
WHERE rowid = p_rowid;
COMMIT;
u_employee_form (p_rowid);
END u_employee;
/* Q_EMPLOYEEE_TABLE
Creates the Employee results table */
PROCEDURE q_employee_table
(p_last_name IN VARCHAR2)
IS
CURSOR emp_cursor IS
SELECT e.rowid theRowid, e.id id, e.first_name first_name, e.last_name last_name, d.name name
FROM s_emp e, s_dept d
WHERE e.dept_id = d.id
AND UPPER(e.last_name) LIKE UPPER ('%'||p_last_name||'%');
no_rows_returned EXCEPTION;
v_rowid ROWID;
v_id s_emp.id%TYPE;
v_first_name s_emp.first_name%TYPE;
v_last_name s_emp.last_name%TYPE;
v_dept_name s_dept.name%TYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_rowid, v_id, v_first_name, v_last_name, v_dept_name;
IF emp_cursor%NOTFOUND THEN
RAISE no_rows_returned;
END IF;
htp.tableOpen('BORDER');
htp.tableRowOpen;
htp.tableHeader ('Employee Id');
htp.tableHeader ('First Name');
htp.tableHeader ('Last Name');
htp.tableHeader ('Department');
htp.tableHeader ('Update Record');
htp.tableRowClose;
WHILE emp_cursor%FOUND LOOP
htp.tableRowOpen;
htp.tableData (v_id);
htp.tableData (v_first_name);
htp.tableData (v_last_name);
htp.tableData (v_dept_name);
htp.tableData (htf.anchor('course.u_employee_form?p_rowid=' || v_rowid, 'Update'));
htp.tableRowClose;
FETCH emp_cursor INTO v_rowid, v_id, v_first_name, v_last_name, v_dept_name;
END LOOP;
htp.tableClose;
EXCEPTION
WHEN no_rows_returned THEN
htp.header(2,'No records matched your request.');
END q_employee_table;
/* Q_EMPLOYEE procedure
Generate the employee query form
with a image submit button */
PROCEDURE q_employee
(p_last_name IN VARCHAR2 DEFAULT NULL,
p_submit IN owa_image.point)
IS
BEGIN
generic.startup ('OWS: Employee Query');
htp.bodyOpen ('/img/backgd.gif');
generic.create_target (1,'Example','CENTER');
generic.create_target (2,'Search for employees: ');
htp.formOpen ('course.q_employee', 'GET');
htp.print ('Last name: ');
htp.formText ('p_last_name');
htp.formImage ('p_submit','/img/submit.gif', calign=>'CENTER', cattributes=>'WIDTH=50 HEIGHT=50');
htp.formClose;
IF (p_last_name IS NOT NULL) THEN
q_employee_table(p_last_name);
END IF;
generic.footer;
END q_employee;
/* Q_EMPLOYEE procedure
Generate the employee query form */
PROCEDURE q_employee
(p_last_name IN VARCHAR2 DEFAULT NULL)
IS
BEGIN
generic.startup ('OWS: Employee Query');
htp.bodyOpen ('/img/backgd.gif');
generic.create_target (1,'Example','CENTER');
generic.create_target (2,'Search for employees: ');
htp.formOpen ('course.q_employee', 'GET');
htp.print ('Last name: ');
htp.formText ('p_last_name');
htp.formImage ('p_submit','/img/submit.gif', cattributes=>'WIDTH=50 HEIGHT=50');
htp.formClose;
IF (p_last_name IS NOT NULL) THEN
q_employee_table(p_last_name);
END IF;
generic.footer;
END q_employee;
/*Q_CUST_ORD_TABLE procedure
Creates the customer order results table */
PROCEDURE q_cust_ord_table
(p_cust_id IN VARCHAR2 DEFAULT NULL)
IS
CURSOR ord_c (v_cust_id NUMBER) IS
SELECT id
FROM s_ord
WHERE customer_id = v_cust_id;
CURSOR item_c (v_ord_id NUMBER) IS
SELECT p.name, i.quantity
FROM s_product p, s_item i
WHERE i.product_id = p.id
AND i.ord_id = v_ord_id;
p_cust_name s_customer.name%TYPE;
BEGIN
SELECT name INTO p_cust_name
FROM s_customer WHERE id = TO_NUMBER(p_cust_id);
generic.create_target (3,'Customer order for '||p_cust_name);
htp.tableOpen ('BORDER');
htp.tableRowOpen;
htp.tableHeader ('Order ID');
htp.tableHeader ('Product Name');
htp.tableHeader ('Quantity');
htp.tableRowClose;
FOR ord_record IN ord_c(p_cust_id) LOOP
htp.tableRowOpen;
htp.tableData (ord_record.id, cattributes=>'VALIGN="TOP"');
nsp.tableDataOpen;
FOR item_record IN item_c (ord_record.id) LOOP
htp.prn (item_record.name);
htp.br;
END LOOP;
nsp.tableDataClose;
nsp.tableDataOpen;
FOR item_record IN item_c (ord_record.id) LOOP
htp.prn (item_record.quantity);
htp.br;
END LOOP;
nsp.tableDataClose;
htp.tableRowClose;
END LOOP;
htp.tableClose;
END q_cust_ord_table;
/* Q_CUST_ORD procedure
Generate the customer order query form */
PROCEDURE q_cust_ord
(p_cust_id IN VARCHAR2 DEFAULT NULL)
IS
CURSOR cust_cursor IS
SELECT id, name
FROM s_customer
ORDER BY name;
BEGIN
generic.startup ('OWS: Customer Query');
htp.bodyOpen ('/img/backgd.gif');
generic.create_target (1,'Example','CENTER');
generic.create_target (2,'Search for customer orders: ');
htp.formOpen ('course.q_cust_ord');
/* Create the select list */
htp.formSelectOpen ('p_cust_id','Select a customer: ');
FOR i IN cust_cursor LOOP
htp.formSelectOption (i.name,cattributes=>'VALUE='||i.id);
END LOOP;
htp.formSelectClose;
/* Close the select list */
htp.br;
htp.br;
htp.formSubmit (cvalue=>'Execute');
htp.formReset;
htp.formClose;
IF (p_cust_id IS NOT NULL) THEN
q_cust_ord_table (p_cust_id);
END IF;
generic.footer;
END q_cust_ord;
/*Q_PRODUCT_TABLE procedure
Creates the Product results table */
PROCEDURE q_product_table
(p_product_name IN VARCHAR2 DEFAULT NULL)
IS
BEGIN
htp.tableOpen ('BORDER');
htp.tableRowOpen;
htp.tableHeader ('ID');
htp.tableHeader ('Name');
htp.tableHeader ('Description');
htp.tableRowClose;
/* Select the data with owa_sql.cells_from_query */
owa_sql.cells_from_query (
'SELECT ID, NAME, SHORT_DESC
FROM s_product
WHERE UPPER (name) LIKE UPPER (''%'||p_product_name||'%'') ');
/* End of selecting data */
htp.tableClose;
END q_product_table;
/* Q_PRODUCT procedure
Generate the product query form */
PROCEDURE q_product
(p_product_name IN VARCHAR2 DEFAULT NULL)
IS
BEGIN
generic.startup ('OWS: Product Query');
htp.bodyOpen ('/img/backgd.gif');
generic.create_target (1,'Example','CENTER');
generic.create_target (2,'Search for products: ');
htp.formOpen ('course.q_product');
htp.print ('Product name: ');
htp.formText ('p_product_name');
htp.br;
htp.br;
htp.formSubmit (cvalue=>'Execute');
htp.formReset;
htp.formClose;
IF (p_product_name IS NOT NULL) THEN
q_product_table (p_product_name);
END IF;
generic.footer;
END q_product;
/* QUERY_LIST procedure
Creates the list of queries for use in the
TOC procedure and the HOME procedure */
PROCEDURE query_list
(p_window_name IN VARCHAR2 DEFAULT NULL)
IS
BEGIN
htp.img('/img/ballred.gif','TOP','ball');
htp.anchor2 (owa_util.get_owa_service_path || 'course.q_employee', 'Employees by last name', NULL, p_window_name);
htp.br;
htp.img('/img/ballblue.gif','TOP','ball');
htp.anchor2 (owa_util.get_owa_service_path || 'course.q_cust_ord', 'Customer orders', NULL, p_window_name);
htp.br;
htp.img('/img/ballred.gif','TOP','ball');
htp.anchor2 (owa_util.get_owa_service_path || 'course.q_product', 'Products by name', NULL, p_window_name);
htp.br;
END query_list;
/* TOC procedure
Creates the page of queries*/
PROCEDURE toc
IS
BEGIN
generic.startup ('Query List');
htp.bodyOpen (cattributes=>'BGCOLOR="#FFFFFF"');
htp.br;
generic.create_target(3, 'Queries');
htp.p ('');
query_list ('main_win');
htp.p ('');
htp.bodyClose;
htp.htmlClose;
END toc;
/* TOOLBAR procedure
Creates the frame toolbar */
PROCEDURE toolbar
(p_window_name IN VARCHAR2 DEFAULT NULL)
IS
v_oracle_url VARCHAR2(100) := 'http://www-dc.us.oracle.com';
v_admin_url VARCHAR2(100)
:= 'http://'||owa_util.get_cgi_env ('SERVER_NAME')||
':8888/ows-doc/index.html';
BEGIN
generic.startup ('Toolbar');
htp.bodyOpen (cattributes=>'BGCOLOR="#FFFFFF"');
htp.centerOpen;
htp.tableOpen ('1');
htp.tableRowOpen;
htp.tableData (htf.anchor2('course.content',
htf.img ('/img/home_a.gif',null,'Home', cattributes=>'WIDTH=33 HEIGHT=33'),null, p_window_name));
htp.tableData (htf.anchor2(v_oracle_url,
htf.img ('/img/oraonweb.gif',null,'Oracle on the Web'),null,p_window_name));
htp.tableData (htf.anchor2(v_admin_url,
htf.img ('/img/owsicon.gif',null,'OWS Admin'),null,p_window_name));
htp.tableData (htf.anchor2('course.home',
htf.img ('/img/noframes.gif',null,'No Frames'),null,'_top'));
htp.tableRowClose;
htp.tableClose;
htp.centerClose;
htp.bodyClose;
htp.htmlClose;
END toolbar;
/*CONTENT procedure
Creates the HOME page content*/
PROCEDURE content
IS
BEGIN
htp.Comment ('body');
htp.bodyOpen (cattributes=>'BGCOLOR="#FFFFFF"');
generic.create_target( 1,'Example', 'CENTER');
generic.create_target( 2,'Welcome to the OWS 2.0 Building Applications Course');
htp.p('
This is a simple example.
');
END content;
/* HOME procedure
Generate the dynamic home page using
CONTENT and QUERY_LIST procedures*/
PROCEDURE home
IS
BEGIN
generic.startup ('OWS: Application Course Home');
content;
generic.create_target(2,'Query List');
query_list;
htp.br;
htp.strong(htf.anchor(owa_util.get_owa_service_path||'course.frame','Frames version'));
generic.footer;
END home;
/* FRAME procedure
Creates a frame set */
PROCEDURE FRAME
(p_window_name IN VARCHAR2 DEFAULT 'main_win')
IS
BEGIN
generic.startup ('Course Application: Frame Version');
htp.framesetOpen (null,'22*,78*');
htp.frame ('course.toc','toc',cscrolling=>'NO');
htp.framesetOpen ('*,65');
htp.frame ('course.content','main_win');
htp.frame ('course.toolbar?p_window_name=main_win','toolbar',cscrolling=>'NO');
htp.framesetClose;
htp.framesetClose;
/*No frames version for frame-incapable browsers*/
htp.noframesOpen;
content;
query_list;
htp.noframesClose;
htp.htmlClose;
END frame;
/* LOGIN_FORM procedure
Creates the login screen */
PROCEDURE login_form
(p_message IN VARCHAR2 DEFAULT NULL,
p_username IN VARCHAR2 DEFAULT NULL,
p_password IN VARCHAR2 DEFAULT NULL)
IS
v_username VARCHAR2(8) := p_username;
BEGIN
generic.startup ('OWS Course Application Login');
htp.Comment ('Login Screen');
htp.bodyOpen (cattributes=>'BGCOLOR="#FFFFFF"');
htp.centerOpen;
htp.img('/img/owsdrive.gif');
generic.create_target (1,'Example', 'CENTER');
htp.centerClose;
IF p_message IS NOT NULL THEN
generic.create_target (4, p_message);
END IF;
htp.formOpen ('course.validate_user', 'POST');
htp.tableOpen;
htp.tableRowOpen;
htp.tableData ('Userid:', 10);
htp.tableData (htf.formText('p_username',null,null,v_username));
htp.tableRowClose;
htp.tableRowOpen;
htp.tableData ('Password:');
htp.tableData (htf.formPassword('p_password'));
htp.tableRowClose;
htp.tableRowOpen;
htp.tableData (htf.formSubmit(cvalue=>'Login'));
htp.tableData (htf.formReset);
htp.tableRowClose;
htp.tableClose;
htp.formClose;
htp.line;
htp.print('ŠThis page created by OWS. ');
htp.address ('If you have any questions, please email the '||
htf.anchor('mailto:webmaster@yourcompany.com','WebMaster'));
htp.bodyClose;
htp.htmlClose;
END login_form;
/* LOGIN_COOKIE function
Creates and increments a counter for login attempts */
FUNCTION login_cookie
RETURN INTEGER
IS
v_cookie owa_cookie.cookie;
BEGIN
/* Create the ATTEMPT cookie */
v_cookie := owa_cookie.get('ATTEMPT');
/* Reset the cookie
Uncomment this code to remove the cookie
for testing purposes */
/* owa_util.mime_header('text/html', FALSE);
owa_cookie.remove('ATTEMPT', v_cookie.num_vals);
owa_util.http_header_close;
RETURN 1; */
/* Print the cookie
Comment this code to remove the cookie */
owa_util.mime_header('text/html', FALSE);
IF (v_cookie.num_vals > 0) THEN
owa_cookie.send('ATTEMPT', v_cookie.vals(1) + 1, Sysdate + 1);
ELSE
owa_cookie.send('ATTEMPT', 1, Sysdate + 1);
END IF;
owa_util.http_header_close;
RETURN (v_cookie.vals(1) + 1);
-- RETURN (v_cookie.num_vals + 1);
END;
/* VALIDATE_USER procedure
Verifies the username and password */
PROCEDURE validate_user
(p_username IN VARCHAR2,
p_password IN VARCHAR2)
IS
v_count INTEGER;
CURSOR user_cursor IS
SELECT username
FROM valid_users
WHERE username = p_username
AND password = p_password;
v_user_name valid_users.username%TYPE;
invalid_user exception;
v_url VARCHAR2(100) := 'course.home';
v_id VARCHAR2(8);
BEGIN
/* Use this SELECT statement to test the WHEN OTHERS exception
SELECT last_name INTO v_id FROM s_emp;
*/
OPEN user_cursor;
FETCH user_cursor INTO v_user_name;
IF user_cursor%NOTFOUND THEN
CLOSE user_cursor;
RAISE invalid_user;
ELSE
htp.htmlOpen;
htp.headOpen;
htp.p( '');
htp.headClose;
htp.bodyOpen;
htp.bodyClose;
htp.htmlClose;
END IF;
EXCEPTION
WHEN invalid_user THEN
v_count := login_cookie;
IF v_count < 3 THEN
login_form('Username/Password was invalid. Please re-enter.', p_username);
ELSE
generic.error('8000: ', 'Three invalid login attempts. See your system adminstrator');
END IF;
WHEN OTHERS THEN
generic.error(SQLERRM(SQLCODE), SQLCODE);
END validate_user;
END course;
/
