 |
|
| Problem |
Solution |
| Using pl/sql to fetch an XML file stored in a clob and display it as HTML page via OAS |
The procedure "makehtml" is a java wrapper for the java
code that applies the .XSL style sheet (see Java section).
The resultant HTML is then displayed like this: |
|
|
begin
select customer_data
into xmlclob
from reports
where fileID = rec.fileID;
exception when others then raise noxml;
end;
--
dbms_lob.createtemporary(htmlclob,TRUE);
--
makehtml(thestylesheet,xmlclob,htmlclob);
--
leng := dbms_lob.getlength(htmlclob);
--
while leng >= 32760 loop
dbms_lob.read(htmlclob,mleng,thestart,thexml);
leng := leng - mleng;
thestart := (32760 * theloop) + 1;
theloop := theloop + 1;
htp.p(thexml);
end loop;
--
dbms_lob.read(htmlclob,leng,thestart,thexml);
htp.p(thexml);
|
| Fast access to a frequently used lookup table |
Build a pl/sql table in memory for the real table
(if it's not too big), then use it to look up values like this: |
|
|
type chrtabtyp is table of varchar2(100)
index by binary_integer;
type numtabtyp is table of number
index by binary_integer;
mess_table chrtabtyp;
id_table numtabtyp;
keep_searching boolean := TRUE;
matching_row binary_integer := 1;
rid binary_integer := 1;
--
-- Build a pl/sql table in memory
-- for real table called "messages"
--
for mess_rec in (select * from MESSAGES) loop
id_table(rid) := mess_rec.mess_id;
mess_table(rid) := mess_rec.mess_words;
rid := rid + 1;
end loop;
--
-- The last row overshoots by 1 so take 1 off
--
rid := rid - 1;
--
-- Search the pl/sql table for a value
--
while keep_searching loop
if (id_table(matching_row) = p_mess_id) then
v_out_mess := mess_table(matching_row);
keep_searching := FALSE;
else
matching_row := matching_row + 1;
keep_searching := matching_row <= rid;
end if;
end loop;
|
| Most useful way of using owa_op_lock |
Save the checksum for a row to a hidden field on the web page.
Perform an if test prior to insert/update in the package that
receives the data like this: |
|
|
Input form end
fetch c1 into rec;
checksum := owa_opt_lock.checksum(USER, 'TABLE', rec.rowid);
Update package end
if ( owa_opt_lock.checksum( USER,'TABLE', rowid )
= to_number(checksum) ) then
|
| Passing an array of values via a URL to OAS for use in a pl/sql package |
Use owa_util.ident_arr as the parameter type then loop
through the array variable in the receiving package like this: |
|
|
for i in 1..thevname.count loop
localname := thevname(i);
end loop;
|
