Thursday, November 30, 2006
Size of Tables
You must have DBA privileges.
select * From dba_segments a
where (a.owner, a.segment_name) in (
select owner, table_name
From dba_tables
where table_name like 'TABLE'
)
Create XML from SQL
Sample.
select
xmlelement(
"CUS", xmlelement(
"ROWS", xmlagg(xmlelement(
"ROW", xmlelement(
"URL", url),xmlelement(
"TIME", time),xmlelement(
"STATUS", status),xmlelement(
"TEXT",text)
)
))
).getclobval()
from check_url_status
Transform XML to SQL
Sample to transform XML to SQL.
select
to_number(to_char(to_date(extractvalue(value(a), '//Envelope/DateTime/date'),'yyyy-mm-dd'),'yyyy')) a2,
to_number(extractvalue(value(a), '//Envelope/numberOfDeclarations')) a3,
substr(extractvalue(value(a), '//Envelope/Party[@partyRole="sender"]/partyId'),1,2) partyIdDrz,
trim(substr(extractvalue(value(a), '//Envelope/Party[@partyRole="sender"]/partyId'),3,12)) partyIdDS,
substr(extractvalue(value(a), '//Envelope/Party[@partyRole="sender"]/partyId'),15,3) partyIdOE,
substr(extractvalue(value(a), '//Envelope/Party[@partyRole="receiver"]/partyId'),1,2) a7,
trim(substr(extractvalue(value(a), '//Envelope/Party[@partyRole="receiver"]/partyId'),3,12)) a8,
substr(extractvalue(value(a), '//Envelope/Party[@partyRole="receiver"]/partyId'),15,3) a9,
to_char(to_date(extractvalue(value(a), '//Envelope/DateTime/date'),'yyyy-mm-dd'),'yyyymmdd') a10,
to_char(to_date(extractvalue(value(a), '//Envelope/DateTime/time'),'hh24:mi:ss'),'hh24miss') a11,
extractvalue(value(a), '//Envelope/envelopeID') a12,
to_date(extractvalue(value(a),'//Envelope/DateTime/date')' '
extractvalue(value(a),'//Envelope/DateTime/time'),'yyyy-mm-dd hh24:mi:ss') a14,
extractvalue(value(a),'//Envelope/Party[@partyRole="sender"]/@partyType') DrzposType,
extract(value(a),'//Declaration') DeclarationXMLType
from table (xmlsequence(extract(xmltype(trim('
<Message>
<Header>
<OzDok>oznaka</OzDok>
</Header>
<Body><Data>
<INSTAT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
<Envelope>
<envelopeID>43575</envelopeID>
<DateTime>
<date>2006-08-11</date>
<time>14:41:37</time>
</DateTime>
<Party partyType="PSI" partyRole="sender">
<partyId>SI11111111 000</partyId>
</Party>
<Party partyType="CC" partyRole="receiver">
<partyId>SI47731811 000</partyId>
</Party>
<numberOfDeclarations>1</numberOfDeclarations>
<Declaration>
<declarationId>11111111000060811005</declarationId>
<referencePeriod>200607</referencePeriod>
<PSIId>SI11111111000</PSIId>
<Function>
<functionCode>I</functionCode>
</Function>
<flowCode>2</flowCode>
<currencyCode>SIT</currencyCode>
<totalNumberLines>1</totalNumberLines>
<declarationTypeCode>M</declarationTypeCode>
<firstLast>1</firstLast>
</Declaration>
</Envelope>
</INSTAT>
</Data>
<Signatures />
</Body>
</Message>')), '//INSTAT'))) a
Convert BLOB to CLOB
Sample to convert BLOB to CLOB.
function ConvertBlobXMLToClob(pfile documents.name%type) return clob is
type message_type is record (
content blob,
charset varchar2(30),
typ varchar2(20)
);
v_message message_type;
v_content clob;
v_dest_offset number := 1;
v_src_offset number := 1;
v_lang_context number := dbms_lob.default_lang_ctx;
v_warning number;
v_x1 number;
begin
-- preberimo dokument
select t.blob_content, 'UTF8' --t.dad_charset
, t.mime_type
into v_message.content, v_message.charset, v_message.typ
from documents t
where t.name = pfile;
-- to CLOB
dbms_lob.createtemporary(v_content, TRUE);
v_dest_offset := 1;
v_src_offset := 1;
v_lang_context := dbms_lob.default_lang_ctx;
DBMS_LOB.CONVERTTOCLOB(v_content, v_message.content, dbms_lob.getlength
(v_message.content), v_dest_offset, v_src_offset,
nls_charset_id(v_message.charset), v_lang_context, v_warning);
v_x1 := instr(lower(substr(v_content,1,1000)),'encoding="');
if v_x1 > 0 then
v_message.charset := replace(substr(v_content,v_x1+10,instr(lower
(substr(v_content,1,1000)),'"', v_x1+10)-v_x1-10),'-','');
dbms_lob.createtemporary(v_content, TRUE);
v_dest_offset := 1;
v_src_offset := 1;
v_lang_context := dbms_lob.default_lang_ctx;
DBMS_LOB.CONVERTTOCLOB(v_content, v_message.content,
dbms_lob.getlength(v_message.content), v_dest_offset, v_src_offset,
nls_charset_id(v_message.charset), v_lang_context, v_warning);
end if;
return v_content;
end;
Print CLOB to web
You can use this function:
procedure HtpPrn(pclob in out nocopy clob) is
v_excel varchar2(32000);
v_clob clob := pclob;
begin
while length(v_clob) > 0 loop
begin
if length(v_clob) > 16000 then
v_excel:= substr(v_clob,1,16000);
htp.prn(v_excel);
v_clob:= substr(v_clob,length(v_excel)+1);
else
v_excel := v_clob;
htp.prn(v_excel);
v_clob:='';
v_excel := '';
end if;
end;
end loop;
end;
Multifile upload
Procedure to upload multi files. Using solution from http://www.the-stickman.com.
procedure DocumentUploadMulti (
name_array in owa.vc_arr,
value_array in owa.vc_arr)
is
pdatoteka Documents.name%type;
FUNCTION namedParam(
p_searchVal in varchar2,
p_name_array in owa.vc_arr,
p_value_array in owa.vc_arr
) RETURN varchar2 IS
i integer;
BEGIN
FOR i IN 1..nvl(p_name_array.count,0) LOOP
if p_name_array(i)=p_searchVal then
return p_value_array(i);
end if;
END LOOP;
return null;
END;
begin
pdatoteka := namedParam('pdatoteka',name_array,value_array);
htp.p('
<html>
<HEAD>
<META HTTP-EQUIV="Content-Type" NAME="" CONTENT="text/html;CHARSET=WINDOWS-1250">
<META HTTP-EQUIV="Pragma" CONTENT="no-cache">
<META HTTP-EQUIV="Expires" CONTENT="-1">
<SCRIPT LANGUAGE="JavaScript">
// Multiple file selector by Stickman
-- http://www.the-stickman.com
// with thanks to: [for Safari fixes] Luis Torrefranca
-- http://www.law.pitt.edu and Shawn Parker & John Pennypacker
-- http://www.fuzzycoconut.com [for duplicate name bug] ''neal''
function MultiSelector( list_target, max ){
this.list_target = list_target;
this.count = 0;
this.id = 0;
if( max ){this.max = max;}
else {this.max = -1;};
this.addElement = function( element ){
if( element.tagName == ''INPUT'' && element.type == ''file'' ){
element.name = ''file_'' + this.id++;element.multi_selector = this;element.onchange = function(){
var new_element = document.createElement( ''input'' );
new_element.type = ''file'';
this.parentNode.insertBefore( new_element, this );
this.multi_selector.addElement( new_element );
this.multi_selector.addListRow( this );
this.style.position = ''absolute'';
this.style.left = ''-1000px'';};
if( this.max != -1 && this.count >= this.max ){
element.disabled = true;};
this.count++;this.current_element = element;}
else {alert( ''Error: not a file input element'' );};};
this.addListRow = function( element ){
var new_row = document.createElement( ''div'' );
var new_row_button = document.createElement( ''input'' );
new_row_button.type = ''button'';new_row_button.value = ''Delete'';
new_row.element = element;new_row_button.onclick= function(){
this.parentNode.element.parentNode.removeChild( this.parentNode.element );
this.parentNode.parentNode.removeChild( this.parentNode );
this.parentNode.element.multi_selector.count--;
this.parentNode.element.multi_selector.current_element.disabled = false;
return false;};
new_row.innerHTML = element.value;
new_row.appendChild( new_row_button );
this.list_target.appendChild( new_row );};
};
</SCRIPT>
<title>Upload</title>
</HEAD>
<body>
<TABLE width="100%" border="0" cellspacing="0" cellpadding="0" bgcolor="#FFFF99" summary="">
<tr><td><font size="5">Upload</font></TD></TR>
</TABLE>');
if pdatoteka is not null then
htp.p('
<BR> File "'substr(pdatoteka, instr(pdatoteka,'/',-1,1)+1
)'" is uploaded. <BR>');
else
htp.p('
<FORM enctype="multipart/form-data" action="!dd_lib.DocumentUploadMulti" method="POST">
<p>File:<INPUT type="file" id="my_file_element" name="pdatoteka"></p>
<p><INPUT type="submit" class=submit value="Submit" ></p>
</FORM>
Files:
<!-- This is where the output will appear -->
<div id="files_list"></div>
<script>
<!-- Create an instance of the multiSelector class, pass it the output target and the max number of files -->
var multi_selector = new MultiSelector( document.getElementById( ''files_list'' ), 200 );
<!-- Pass in the file element -->
multi_selector.addElement( document.getElementById( ''my_file_element'' ) );
</script>
<BR>');
end if;
htp.p('
</body>
</html>');
exception when others then
htp.p('
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;CHARSET=WINDOWS-1250">
<META HTTP-EQUIV="Pragma" CONTENT="no-cache">
<META HTTP-EQUIV="Expires" CONTENT="-1">
<TITLE>Message</TITLE>
</HEAD>
<BODY bgcolor="#C0C0C0">');
htp.p('
<table>
<tr><td><FONT COLOR="red" size="4">Error: </FONT></td>
<td>'replace(sqlerrm,'','<BR>')'</td>
</tr>
</table>
<BR>
<INPUT TYPE="button" VALUE="Back" class=submit onClick="javascript:history.go(-1)">
</BODY>
</HTML>');
end;
Passing SSL parameters in modplsql
If you like to pass SSL parameters from web server to PL/SQL you must add parameters into DAD configuration. You must add:
PlsqlCGIEnvironmentList SSL_CLIENT_S_DN_CN
PlsqlCGIEnvironmentList SSL_CLIENT_S_DN_O
PlsqlCGIEnvironmentList SSL_CLIENT_S_DN_OU
PlsqlCGIEnvironmentList SSL_CLIENT_I_DN_CN
PlsqlCGIEnvironmentList SSL_CLIENT_I_DN_O
PlsqlCGIEnvironmentList SSL_CLIENT_I_DN_OU
PlsqlCGIEnvironmentList SSL_SERVER_S_DN_CN
PlsqlCGIEnvironmentList SSL_SERVER_S_DN_O
PlsqlCGIEnvironmentList SSL_SERVER_S_DN_OU
PlsqlCGIEnvironmentList SSL_SERVER_I_DN_CN
PlsqlCGIEnvironmentList SSL_SERVER_I_DN_O
PlsqlCGIEnvironmentList SSL_SERVER_I_DN_OU
PlsqlCGIEnvironmentList SSL_SESSION_ID
Tuesday, November 28, 2006
Create CAPTCHA in PL/SQL
To create Captcha you must use dd_bmp package (package is using some functions from
http://www.adp-gmbh.ch/blog/2005/november/22.html).
To test Captcha you run program:
http://www.adp-gmbh.ch/blog/2005/november/22.html).
procedure Captcha (
name_array in owa.vc_arr,
value_array in owa.vc_arr
) is
pt raw(100);
l_lob blob;
vt varchar2(100);
FUNCTION namedParam(
p_searchVal in varchar2,
p_name_array in owa.vc_arr,
p_value_array in owa.vc_arr
) RETURN varchar2 IS
i integer;
BEGIN
FOR i IN 1..nvl(p_name_array.count,0) LOOP
if p_name_array(i)=p_searchVal then
return p_value_array(i);
end if;
END LOOP;
return null;
END;
begin
pt := namedParam(
'pt',
name_array ,
value_array
);
pt := utl_encode.base64_decode(pt);
vt := utl_raw.cast_to_varchar2(pt);
l_lob := dd_bmp.captcha(vt);
owa_util.mime_header( 'image/bmp', FALSE );
htp.p('Content-length: ' dbms_lob.getlength( l_lob ));
owa_util.http_header_close;
wpg_docload.download_file( l_lob );
end;
To test Captcha you run program:
procedure TestCaptcha (
name_array in owa.vc_arr,
value_array in owa.vc_arr
)
as
x raw(100);
vcapchapreveri raw(100);
vcapchavrednost raw(100);
v varchar2(100);
begin
for i__ in 1..nvl(name_array.count,0) loop
if name_array(i__) = 'capchavrednost' then vcapchavrednost := value_array(i__);
elsif name_array(i__) = 'capchapreveri' then vcapchapreveri := utl_raw.cast_to_raw(value_array(i__));
end if;
end loop;
begin
if utl_encode.base64_encode(vcapchapreveri) = vcapchavrednost then
htp.p ('OK<BR>');
else
htp.p ('NOT OK<BR>');
end if;
exception when others then null;
htp.p ('NOT OK<BR>');
end;
v := dd_random.rndchardd_random.rndchardd_random.rndchardd_random.rndchar;
x := utl_raw.cast_to_raw(v);
x := utl_encode.base64_encode(x);
htp.p('
<form method=post>
<img src="p_htmldokumenti.capcha?pt='x'">
<input type="text" name="capchapreveri" value="" size="4" maxlength="4">
<input type="hidden" name="capchavrednost" value="'x'">
<input type="submit" name="nnnn" value="Preveri">
</form>
');
exception when others then htp.p(sqlerrm);
end;
Create XML document from table data
Procedure to create XML document from table data:
procedure CreateXMLFromTable(ptabela varchar2) is
v_plsql varchar2(4000);
v_tabela varchar2(30) := upper(ptabela);
v_statussend varchar2(1) := 'N';
begin
v_plsql := 'declare
v_result clob := '''';
v_resultrid xmltype;
begin
savepoint sp;
select xmlelement(
"XYZ", xmlagg(xmlelement(
"ROWID", rowidtochar(r.rid))))
,
xmlelement("'v_tabela'", xmlagg(xmlelement("POST"
';
for r in
(select
'
, xmlelement("'u.COLUMN_NAME'", '
decode(u.DATA_TYPE,
'NUMBER','to_char(r.'u.COLUMN_NAME',''FM99999999999999999990.00000'')',
'DATE','to_char(r.'u.COLUMN_NAME',''dd.mm.yyyy hh24:mi:ss'')',
'r.'u.COLUMN_NAME
)')' polje, u.COLUMN_NAME
from user_tab_columns u
where u.TABLE_NAME = v_tabela
order by u.column_id) loop
if r.column_name = 'STATUSSEND' then
v_statussend := 'D';
end if;
if r.column_name <> 'RID' then
v_plsql := v_plsql r.polje;
end if;
end loop;
v_plsql := v_plsql '))).getclobval()
into v_resultrid, v_result
from 'v_tabela' r';
if v_statussend = 'D' then
v_plsql := v_plsql '
;
for r in (select
chartorowid(extractvalue(value(x), ''ROWID'')) rid
from table(xmlsequence(extract(v_resultrid,''XYZ/ROWID''))) x) loop
update 'v_tabela' set statusprepisa = 1 where rowid in r.rid;
end loop;';
else
v_plsql := v_plsql '
;';
end if;
/*v_plsql := v_plsql '
if instr(v_result,'''') > 0 then
insert into save_data
(id, table, sourcexml, statussend)
values
(prenos_produkcija_seq.nextval, '''v_tabela''', v_result, 0);
end if;';*/
v_plsql := v_plsql '
commit;
exception when others then
rollback to sp;
raise;
end;';
--htp.p(v_plsql);
execute immediate v_plsql;
end;
Show properties of web client
To show properties of web client call this procedure:
procedure ShowWebProperties(
name_array in owa.vc_arr,
value_array in owa.vc_arr
) is
v_baza varchar2(100);
x varchar2(50);
begin
select user'@'global_name into v_baza from global_name;
htp.p('user@baza = 'v_baza);
htp.p('<hr>');
for r in (select * from nls_session_parameters
order by parameter)
loop
htp.p(r.parameter' = ' r.value'<br>');
end loop;
htp.p('<hr>');
for r in (select * from nls_database_parameters
order by parameter)
loop
htp.p(r.parameter' = ' r.value'<br>');
end loop;
htp.p('<hr>');
owa_util.print_cgi_env;
htp.p('<hr>');
end;
Monday, November 27, 2006
Create QUEUE - AQ
To create queue you can use this program:
How to call?
procedure CreateRawAQ (
qname varchar2,
is_multi_consumer boolean default false
)
as
qtablename varchar2(110) := qname;
begin
dbms_aqadm.stop_queue (queue_name => qname);
dbms_aqadm.drop_queue (queue_name => qname);
dbms_aqadm.drop_queue_table (Queue_table => qtablename);
EXCEPTION
WHEN OTHERS THEN
null;
dbms_aqadm.create_queue_table (Queue_table => qtablename, Queue_payload_type=> 'RAW',
multiple_consumers => is_multi_consumer);
dbms_aqadm.create_queue (Queue_name => qname, Queue_table => qtablename);
dbms_aqadm.start_queue(qname);
end;
How to call?
DECLARE
enqueue_options dbms_aq.enqueue_options_t;
message_properties dbms_aq.message_properties_t;
msgid RAW(16);
raw_payload RAW(32767);
BEGIN
raw_payload := utl_raw.cast_to_raw('MESSAGE');
dbms_aq.enqueue(queue_name => 'MY_QUEUE',
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => raw_payload,
msgid => msgid);
END;
Check web page status
Program to check web page status.
Table where web page response are saved structure:
To invoke program you must run:
dd_check_url_status.izpisi_stanje
Program:
Table where web page response are saved structure:
create table CHECK_URL_STATUS
(
URL VARCHAR2(1000),
TIME DATE,
STATUS NUMBER,
TEXT VARCHAR2(4000)
);
To invoke program you must run:
dd_check_url_status.izpisi_stanje
Program:
create or replace package dd_check_url_status is
-- Author : DOMEN
-- Created : 3.10.2004 13:14:06
-- Purpose :
procedure izrisi_graf(p_url varchar2, p_dt integer, p_t1 date);
procedure izpisi_stanje(p_i varchar2 default null,
novurl varchar2 default null,
preveri varchar2 default null,
zoom varchar2 default '43200',
klicaj varchar2 default null);
--procedure izpisi_stanje(
-- name_array in owa.vc_arr,
-- value_array in owa.vc_arr
--);
procedure test_url(p_url varchar2);
procedure testiraj_vse_url;
procedure dodaj_med_job;
procedure dodaj_med_job_optimizacija;
procedure izdelaj_porocilo(d date default null, purl varchar2 default null);
procedure optimizacija_baze;
procedure izpisi_tekst(p_rid varchar2);
end;
create or replace package body dd_check_url_status is
NAPAKA_BARVA VARCHAR2(30) := 'Red';
OK_BARVA VARCHAR2(30) := '#C6E3FF';
NEDEFINIRANO_BARVA VARCHAR2(30) := 'Yellow';
procedure izpisi_tekst(p_rid varchar2) is
v_tekst varchar(4000);
begin
select text into v_tekst from CHECK_URL_STATUS p where rowid = chartorowid(p_rid);
htp.p(v_tekst);
end;
procedure izrisi_graf(p_url varchar2, p_dt integer, p_t1 date) is
dT integer;
n number;
t1 date := sysdate;
pot_cas integer;
pot_cas_razlika integer;
color varchar2(20);
vexit boolean;
begin
-- Test statements here
-- p_url := 'x'; -- url, ki ga spremljamo
-- p_dT := 3600*12; --s je interval, ki ga želimo prikazati 1h
-- p_t1 := sysdate; -- datum od katerega želimo spremljati dogajanje
dT := 100; --% lahko so potem tudi točke
n := dT/p_dT; -- število točk na premici
if p_t1 is not null then t1 := p_t1; end if;
pot_cas := 0;
vexit := false;
htp.p('
<table width="100%" border=0 cellpadding=0 cellspacing=0>
<TR>
<TD align=center colspan=1000><B><A href="'p_url'"
target="blank_">'p_url'</A></B></TD>
</TR>
<TR >
<TD width="10" style="border-bottom : 2px solid Black; border-right : 2px solid Black;">
</TD>
');
for r in (
select
time cas,
status, rowidtochar(rowid) rid
from CHECK_URL_STATUS
where url = p_url
and time <= t1
order by cas desc
) loop
if pot_cas > p_dT then
pot_cas_razlika := p_dt - pot_cas;
pot_cas := p_dt;
vexit := true;
else
pot_cas_razlika := (t1-r.cas)*24*3600 - pot_cas;
if pot_cas_razlika+pot_cas > p_dt then
pot_cas_razlika := p_dt - pot_cas;
pot_cas := p_dt;
vexit := true;
else
pot_cas := (t1-r.cas)*24*3600;
end if;
end if;
if pot_cas_razlika > p_dt then vexit := true; end if;
if r.status = 1 then color := NAPAKA_BARVA;
elsif r.status= -1 then color := NEDEFINIRANO_BARVA;
else color := OK_BARVA;
end if;
htp.p('
<TD ondblclick="javascript: var xxx = window.open(
''dd_check_url_status.izpisi_tekst?p_rid='r.rid''',''xxx'',''width=800,height=500,scrollbars=1'')"
style="border-bottom :
2px solid Black;border-right : 1px solid #939393;" align="right" valign="bottom"
title="'to_char(r.cas,'dd.mm.yyyy hh24:mi:ss')'" width="'round(pot_cas_razlika*n)'%" height="100"
bgcolor="'color'"><img src="x" width="1" height="1" border="0"></TD>
');
if vexit then exit; end if;
end loop;
if pot_cas < p_dt then
htp.p('
<TD style="border-bottom : 2px solid Black;" width="'round((p_dt-pot_cas)*n)'%" height="100"
bgcolor="White"> </TD>
');
end if;
htp.p('
</TR></table>
');
htp.p('
<TABLE width="100%" border=0 cellpadding=0 cellspacing=0>
<TR >
<TD align=left width="50%">'to_char(t1,'dd.mm.yyyy hh24:mi:ss')'</TD>
<TD align=right width="50%">'to_char(t1-(p_dt/(24*3600)),'dd.mm.yyyy hh24:mi:ss')'</TD>
</TR>
</table>');
end;
FUNCTION namedParam(
p_searchVal in varchar2,
p_name_array in owa.vc_arr,
p_value_array in owa.vc_arr) RETURN varchar2 IS
i integer;
BEGIN
FOR i IN 1..p_name_array.count LOOP
if p_name_array(i)=p_searchVal then
return p_value_array(i);
end if;
END LOOP;
return null;
END;
procedure izpisi_stanje(p_i varchar2 default null,
novurl varchar2 default null,
preveri varchar2 default null,
zoom varchar2 default '43200',
klicaj varchar2 default null) is
-- Local variables here
i integer;
p date;
k integer;
begin
-- Test statements here
i := to_number(zoom);
if p_i is null then
p := sysdate;
else
p := to_date(p_i,'dd.mm.yyyyhh24:mi:ss');
if p > sysdate then
p := sysdate;
end if;
end if;
select count(*) into k
from user_jobs u where lower(u.what) like '%dd_check_url_status.testiraj_vse_url;%';
htp.p('
<html>
<head>
<title>Show working of internet pages</title>
</head>
<body onLoad="setTimeout(
''location=\'''klicaj'dd_check_url_status.izpisi_stanje?zoom='zoom'\'''',300000);">
<H1 align=center>Show working of internet pages - ');
if k = 0 then
htp.p('NOT WORKING');
else
htp.p('WORKING');
end if;
htp.p('</H1>');
htp.p('<P align=center><A
href ="'klicaj'dd_check_url_status.izpisi_stanje?p_i='to_char(p+(i/(24*3600)),'dd.mm.yyyyhh24:mi:ss')
'&zoom='zoom'"><--</A>');
htp.p(' <A href ="'klicaj'dd_check_url_status.izpisi_stanje?zoom='round(i/2)'">+</A> ');
htp.p(' <A href ="'klicaj'dd_check_url_status.izpisi_stanje">@</A> ');
htp.p(' <A href ="'klicaj'dd_check_url_status.izpisi_stanje?zoom='round(i*2)'">-</A> ');
htp.p('<A href ="'klicaj'dd_check_url_status.izpisi_stanje?p_i='to_char(p-(i/(24*3600)),
'dd.mm.yyyyhh24:mi:ss')'&zoom='zoom'">--></A></P>');
htp.p('<TABLE width="90%"><TR width="90%"><TD>');
for r in (select distinct url from CHECK_URL_STATUS t
order by url) loop
htp.p('<BR><BR>');
izrisi_graf(r.url,i, p);
end loop;
htp.p('</TD></TR></TABLE>');
htp.p('<P align=center><A href ="'klicaj
'dd_check_url_status.izpisi_stanje?p_i='to_char(p+(i/(24*3600)),'dd.mm.yyyyhh24:mi:ss')'&zoom='zoom'"><
--</A>');
htp.p(' <A href ="'klicaj'dd_check_url_status.izpisi_stanje?zoom='round(i/2)'">+</A> ');
htp.p(' <A href ="'klicaj'dd_check_url_status.izpisi_stanje">@</A> ');
htp.p(' <A href ="'klicaj'dd_check_url_status.izpisi_stanje?zoom='round(i*2)'">-</A> ');
htp.p('<A href ="'klicaj'dd_check_url_status.izpisi_stanje?p_i='to_char(p-(i/(24*3600)),
'dd.mm.yyyyhh24:mi:ss')
'&zoom='zoom'">--></A></P>');
htp.p('<BR>Add new URL:<input type=text name=novurl><input type=button name=Potrdi value="Dodaj"
onclick="location='''klicaj'dd_check_url_status.izpisi_stanje?p_i='to_char(p+(i/(24*3600)),
'dd.mm.yyyyhh24:mi:ss')'&novurl=''+novurl.value">');
htp.p('<BR>Check working now? <A
href="'klicaj'dd_check_url_status.izpisi_stanje?preveri=TRUE">Yes</A> ');
htp.p('<BR><A href ="'klicaj'dd_check_url_status.izdelaj_porocilo">Working report</A> ');
htp.p('<P align=right><FONT size=1>Domen Dolar, domen.dolar@gmail.com</FONT></P>
</body>
</html>
');
if novurl is not null then
insert into CHECK_URL_STATUS (url,time,status) values (novurl,sysdate,-1);
end if;
if preveri = 'TRUE' then
if k = 0 then
dodaj_med_job;
update CHECK_URL_STATUS p set status = -1
where (url,time) in
(select p.url, max(time) from CHECK_URL_STATUS p group by p.url);
else
select u.job into k
from user_jobs u where lower(u.what) like '%dd_check_url_status.testiraj_vse_url;%';
dbms_job.run(k);
end if;
end if;
end;
--procedure izpisi_stanje(
-- name_array in owa.vc_arr,
-- value_array in owa.vc_arr
--) is
--begin
-- izpisi_stanje1(namedParam('p_i',name_array,value_array),
-- namedParam('novurl',name_array,value_array), namedParam('preveri',name_array,value_array),
-- namedParam('zoom',name_array,value_array), '!');
--end;
procedure test_url(p_url varchar2) is
v varchar2(4000);
a varchar2(10);
begin
v := utl_http.request(p_url);
for i in 1..length(v) loop
a := substr(v,i,1);
end loop;
if instr(upper(p_url),'IZRACUN') > 0 and instr(upper(v),'(3CV1)') = 0 then
raise_application_error('-20000','');
end if;
if instr(upper(v),'IO EXCEPTION: BROKEN PIPE') > 0 and instr(v,'(3CV1)') = 0 then
raise_application_error('-20000','');
end if;
if instr(upper(v),'SPLETKARADMIN.CVI@GOV.SI') > 0 then
raise_application_error('-20000','');
end if;
if instr(upper(v),'WAS NO RESPONSE FROM') > 0 then
raise_application_error('-20000','');
end if;
if instr(upper(v),'WAS NOT FOUND ON THIS SERVER') > 0 then
raise_application_error('-20000','');
end if;
insert into CHECK_URL_STATUS
(url, time, status,text)
values
(p_url,sysdate, 0,v);
commit;
exception when others then
insert into CHECK_URL_STATUS
(url, time, status,text)
values
(p_url,sysdate, 1,v);
commit;
end ;
procedure testiraj_vse_url is
begin
declare
v_interval varchar2(200);
v_lastdate date;
v_SQL varchar2(500);
begin
select interval into v_interval
from user_jobs where upper(what) like upper('%testiraj_vse_url%');
select max(x.time) into v_lastdate from CHECK_URL_STATUS x;
v_SQL := 'update CHECK_URL_STATUS p set status = -1
where (url,time) in
(select p.url, max(time) from CHECK_URL_STATUS p group by p.url)
and (sysdate - to_date('''to_char(v_lastdate,'dd.mm.yyyy hh24:mi:ss')'''
,''dd.mm.yyyy hh24:mi:ss'')) > 2*(sysdate - 'v_interval')';
execute immediate v_sql;
commit;
exception when others then null;
end;
for r in (select distinct url from CHECK_URL_STATUS t
order by url) loop
test_url(r.url);
end loop;
end;
procedure dodaj_med_job is
i integer;
begin
dbms_job.submit(i,'begin dd_check_url_status.testiraj_vse_url; end;',sysdate,'sysdate+5/(24*60)');
end;
procedure dodaj_med_job_optimizacija is
i integer;
begin
dbms_job.submit(i,'begin dd_check_url_status.optimizacija_baze; end;',to_date(to_char(sysdate,'dd.mm.yyyy')
' 5:00:00','dd.mm.yyyy hh24:mi:ss'),'sysdate+1');
end;
procedure izpis_porocila_url( purl varchar2, pd date default null) is
y date := sysdate;
x date := sysdate;
v_status CHECK_URL_STATUS.status%type;
v_url CHECK_URL_STATUS.url%type;
v_d date;
v_xd date;
begin
if pd is null then
v_d := sysdate - 7;
else
v_d := pd;
end if;
select nvl(max(time),sysdate) into v_xd
from CHECK_URL_STATUS
where time < v_d
and url = purl;
htp.p('
<P align="center"><B><A href="'purl'" target="blank_">'purl'</A> </B></P>
<TABLE width="50%" border="1" cellpadding=0 cellspacing=0>
<TR>
<TH width="20%">From</TH>
<TH width="20%">To</TH>
<TH width="10%">Difference (s)</TH>
</TR>
');
for r in (
select url, time cas, status, rownum ri from CHECK_URL_STATUS
where time >= v_xd
and url = purl
order by 1,2
) loop
if r.ri = 1 then
y := r.cas;
end if;
if r.status = 1 and (v_status <> r.status or v_url <> r.url) then --gremo na napako
y := r.cas;
end if;
if r.status <> 1 and (v_status <> r.status or v_url <> r.url) then
x := r.cas;
if x >= y and r.status = 0 then
htp.p('
<TR>
<TD>'to_char(y,'dd.mm.yyyy hh24:mi:ss')'</TD>
<TD>'to_char(x,'dd.mm.yyyy hh24:mi:ss')'</TD>
<TD align=right bgcolor="'NAPAKA_BARVA'">'to_char(round((x-y)*24*3600))'</TD>
</TR>
');
end if;
end if;
v_status := r.status;
v_url := r.url;
end loop;
if v_status = 1 then
htp.p('
<TR>
<TD>'to_char(y,'dd.mm.yyyy hh24:mi:ss')'</TD>
<TD>'to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')'</TD>
<TD align=right bgcolor="'NAPAKA_BARVA'">'to_char(round((sysdate-y)*24*3600))'</TD>
</TR>
');
end if;
htp.p('</TABLE>('to_char(v_xd,'dd.mm.yyyy hh24:mi:ss')'-'to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')')');
end;
procedure izdelaj_porocilo(d date default null, purl varchar2 default null) is
begin
-- Test statements here
htp.p('
<html>
<head>
<title>Report of NOT working </title>
</head>
<body>
<H1 align=center>Report of NOT working </H1>');
htp.p('<P align=center>');
htp.p(' <A href ="javascript: history.go(-1);">Back</A>');
htp.p('</P>');
for r1 in (select distinct url from CHECK_URL_STATUS where (url = purl or purl is null) order by 1) loop
izpis_porocila_url(r1.url,d);
end loop;
htp.p('<P align=center>');
htp.p(' <A href ="javascript: history.go(-1);">Back</A>');
htp.p('</P>');
htp.p('</BODY></HTML>');
end;
procedure optimizacija_baze is
v_status CHECK_URL_STATUS.status%type := -2;
begin
-- Test statements here
--ARHIVIRAMO STARE !!!
declare
x_datum date := sysdate;
begin
--insert into CHECK_URL_STATUS (url,cas,status,tekst)
--select url,time,status,text from preverjanje_delovanja p
--where p.time < x_datum-30;
delete from CHECK_URL_STATUS p
where p.time < x_datum-30;
end;
--OPTIMIZACIJA
for r1 in (select distinct url from CHECK_URL_STATUS order by 1) loop
for r in (
select url, time cas, status, rowid rid from CHECK_URL_STATUS
where url = r1.url
and time < (sysdate-1/24)
order by 1,2
) loop
if v_status <> r.status then
null;
else
delete from CHECK_URL_STATUS where rowid = r.rid;
end if;
v_status := r.status;
end loop;
end loop;
commit;
end;
end ;
Random function
Random function:
create or replace package dd_random is
/* Linear congruential random number generator */
/* Returns random integer between [0, r-1] */
function rndint(r in number) return number;
/* Returns random real between [0, 1] */
function rndflt return number;
end ;
create or replace package body dd_random is
/* Linear congruential random number generator */
m constant number:=100000000; /* initial conditions */
m1 constant number:=10000; /* (for best results) */
b constant number:=31415821; /* */
a number; /* seed */
the_date date; /* */
days number; /* for generating initial seed */
secs number; /* */
/*-------------------------- mult ---------------------------*/
/* Private utility function */
function mult(p in number, q in number) return number is
p1 number;
p0 number;
q1 number;
q0 number;
begin
p1:=trunc(p/m1);
p0:=mod(p,m1);
q1:=trunc(q/m1);
q0:=mod(q,m1);
return(mod((mod(p0*q1+p1*q0,m1)*m1+p0*q0),m));
end; /* mult */
/*-------------------------- rndint --------------------------*/
/* Returns random integer between [0, r-1] */
function rndint (r in number) return number is
begin
/* generate a random number and set it to be the new seed */
a:=mod(mult(a,b)+1,m);
/* convert it to integer between [0, r-1] and return it */
return(trunc((trunc(a/m1)*r)/m1));
end; /* rndint */
/*-------------------------- rndflt --------------------------*/
/* Returns random real between [0, 1] */
function rndflt return number is
begin
/* generate a random number and set it to be the new seed */
a:=mod(mult(a,b)+1,m);
/* return it */
return(a/m);
end; /* rndflt */
begin /* package body random */
/* Generate an initial seed "a" based on system date */
/* (Must be connected to database.) */
the_date:=sysdate;
days:=to_number(to_char(the_date, 'J'));
secs:=to_number(to_char(the_date, 'SSSSS'));
a:=days*24*3600+secs;
end ;
Create SOAP request from PL/SQL
Procedure to create SOAP request from database.
Sample:
Package:
Sample:
DECLARE
req soap.request;
resp soap.response;
BEGIN
req := soap.new_request('NS1:izracunv',
'xmlns:NS1="Izracunv"');
soap.add_parameter(req, 'id','xsd:string','zzi');
soap.add_parameter(req, 'pa','xsd:string','30.4.2004');
soap.add_parameter(req, 'p23','xsd:string','');
soap.add_parameter(req, 'p31iii','xsd:string','');
soap.add_parameter(req, 'p33i','xsd:string','03042013');
soap.add_parameter(req, 'p33ii','xsd:string','21');
soap.add_parameter(req, 'p33iii','xsd:string','');
soap.add_parameter(req, 'p33iv','xsd:string','');
soap.add_parameter(req, 'p33v','xsd:string','');
soap.add_parameter(req, 'p34a','xsd:string','IN');
soap.add_parameter(req, 'p35','xsd:string','1000.5');
soap.add_parameter(req, 'p36','xsd:string','100');
soap.add_parameter(req, 'p38','xsd:string','900');
soap.add_parameter(req, 'p39','xsd:string','');
soap.add_parameter(req, 'p41','xsd:string','');
soap.add_parameter(req, 'p42','xsd:string','100');
soap.add_parameter(req, 'p44','xsd:string','(3CV1)(120)');
soap.add_parameter(req, 'p46','xsd:string','');
resp := soap.invoke(req,
'http://storitve-curs.gov.si/sstoritve/Izracunpl',
'Izracunv');
return soap.get_return_value(resp, 'return',
'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"')
;
eND;
Package:
CREATE OR REPLACE PACKAGE dd_soap AS
/* A type to represent a SOAP RPC request */
TYPE request IS RECORD (
method VARCHAR2(256),
namespace VARCHAR2(256),
body VARCHAR2(32767));
/* A type to represent a SOAP RPC response */
TYPE response IS RECORD (
doc xmltype);
/*
* Create a new SOAP RPC request.
*/
FUNCTION new_request(method IN VARCHAR2,
namespace IN VARCHAR2)
RETURN request;
/*
* Add a simple parameter to the SOAP RPC request.
*/
PROCEDURE add_parameter(req IN OUT NOCOPY request,
name IN VARCHAR2,
type IN VARCHAR2,
value IN VARCHAR2);
/*
* Make the SOAP RPC call.
*/
FUNCTION invoke(req IN OUT NOCOPY request,
url IN VARCHAR2,
action IN VARCHAR2) RETURN response;
/*
* Retrieve the sipmle return value of the SOAP RPC call.
*/
FUNCTION get_return_value(resp IN OUT NOCOPY response,
name IN VARCHAR2,
namespace IN VARCHAR2) RETURN VARCHAR2;
PROCEDURE show_envelope(env IN VARCHAR2);
END;
CREATE OR REPLACE PACKAGE BODY dd_soap AS
FUNCTION new_request(method IN VARCHAR2,
namespace IN VARCHAR2)
RETURN request AS
req request;
BEGIN
req.method := method;
req.namespace := namespace;
RETURN req;
END;
PROCEDURE add_parameter(req IN OUT NOCOPY request,
name IN VARCHAR2,
type IN VARCHAR2,
value IN VARCHAR2) AS
BEGIN
req.body := req.body
'<'name' xsi:type="'type'">'value'</'name'>';
END;
PROCEDURE generate_envelope(req IN OUT NOCOPY request,
env IN OUT NOCOPY VARCHAR2) AS
BEGIN
env := '<?xml version="1.0"?><SOAP-ENV:Envelope
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/1999/XMLSchema"
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"><'req.method' 'req.namespace'>'
req.body'</'req.method'></SOAP-ENV:Body></SOAP-ENV:Envelope>';
END;
PROCEDURE show_envelope(env IN VARCHAR2) AS
i pls_integer;
len pls_integer;
BEGIN
i := 1; len := length(env);
WHILE (i <= len) LOOP
dbms_output.put_line(substr(env, i, 60));
i := i + 60;
END LOOP;
END;
PROCEDURE check_fault(resp IN OUT NOCOPY response) AS
fault_node xmltype;
fault_code VARCHAR2(256);
fault_string VARCHAR2(32767);
BEGIN
fault_node := resp.doc.extract('/soap:Fault',
'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/');
IF (fault_node IS NOT NULL) THEN
fault_code := fault_node.extract('/soap:Fault/faultcode/child::text()',
'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/').getstringval();
fault_string := fault_node.extract('/soap:Fault/faultstring/child::text()',
'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/').getstringval();
raise_application_error(-20000, fault_code ' - ' fault_string);
END IF;
END;
FUNCTION invoke(req IN OUT NOCOPY request,
url IN VARCHAR2,
action IN VARCHAR2) RETURN response AS
env VARCHAR2(32767);
http_req utl_http.req;
http_resp utl_http.resp;
resp response;
BEGIN
generate_envelope(req, env);
show_envelope(env);
return resp;
http_req := utl_http.begin_request(url, 'POST','HTTP/1.0');
utl_http.set_header(http_req, 'Content-Type', 'text/xml');
utl_http.set_header(http_req, 'Content-Length', length(env));
utl_http.set_header(http_req, 'SOAPAction', action);
utl_http.write_text(http_req, env);
show_envelope(http_req.method);
http_resp := utl_http.get_response(http_req);
utl_http.read_text(http_resp, env);
utl_http.end_response(http_resp);
resp.doc := xmltype.createxml(env);
resp.doc := resp.doc.extract('/soap:Envelope/soap:Body/child::node()',
'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"');
--show_envelope(resp.doc.getstringval());
check_fault(resp);
RETURN resp;
END;
FUNCTION get_return_value(resp IN OUT NOCOPY response,
name IN VARCHAR2,
namespace IN VARCHAR2) RETURN VARCHAR2 AS
BEGIN
RETURN resp.doc.extract('//'name'/child::text()',
namespace).getstringval();
END;
END;
How to read HTML document from web
Procedure to read HTML document from over the HTTP.
procedure ReadAndShowURL(urllocation varchar2) as
http_req utl_http.req;
http_resp utl_http.resp;
x varchar2(30000);
begin
http_req := utl_http.begin_request(urllocation, 'POST','HTTP/1.0');
http_resp := utl_http.get_response(http_req);
utl_http.read_text(http_resp,x);
htp.p(x);
end;
Write file to XMLDB
To write XML document into XMLDB structure to access it from FTP or WEBDAV.
vsource := XMLTYPE variable
vfile:= '/home/dbapp/arhiv/arh'to_char(sysdate,'hh24missddmmyyyy')'.xml';
y := dbms_xdb.CreateResource(vfile,vsource);
vsource := XMLTYPE variable
vfile:= '/home/dbapp/arhiv/arh'to_char(sysdate,'hh24missddmmyyyy')'.xml';
y := dbms_xdb.CreateResource(vfile,vsource);
Create HTML pages from SQL queries
To store SQL you must create table:
Sample SQL:
pshowd -- ID of SQL stored in SHOWSQLCODE table
pshow_no_buttons --Y/N --set to Y and buttons will hide
pshow_only_results --Y/N --set to Y and parameters will hide
psql --SQL query not stored in SHOWSQLCODE table
Procedure to generate page from SQL queries:
Bind variables are written like &"variable" and colons are written with alias description in "".
create table SHOWSQLCODE
(
ID NUMBER not null,
DESCRIPTION VARCHAR2(200) not null,
SQLCODE VARCHAR2(4000) not null,
VALID_FROM DATE not null,
VALID_TO DATE
)
Sample SQL:
select id "ID", description "Description"Parameters you can set when you call procedure.
from showsqlcode
where description like &"Description:"
order by id
pshowd -- ID of SQL stored in SHOWSQLCODE table
pshow_no_buttons --Y/N --set to Y and buttons will hide
pshow_only_results --Y/N --set to Y and parameters will hide
psql --SQL query not stored in SHOWSQLCODE table
Procedure to generate page from SQL queries:
procedure ShowSQLResults(
name_array in owa.vc_arr,
value_array in owa.vc_arr
) as
v_execimedv varchar2(32000);
v_execimed owa.vc_arr;
v_show boolean := true;
v_limit varchar2(2000);
p_showd varchar2(10);
p_show_only_results varchar2(10);
p_show_no_buttons varchar2(10);
xlen number;
v_sql varchar2(32000);
type rcolumn is record
(
name varchar(4000)
);
type rbind is record
(
name varchar(4000),
var varchar(4000)
);
TYPE tcolumns IS TABLE OF rcolumn index by binary_integer;
TYPE tbinds IS TABLE OF rbind index by binary_integer;
vcolumns tcolumns;
vbinds tbinds;
function get_vred(poljeime varchar2,name_array owa.vc_arr,value_array owa.vc_arr) return varchar2 is
begin
for i in 1..nvl(name_array.count,0) loop
if upper(name_array(i)) = upper(poljeime) then
return value_array(i);
end if;
end loop;
return '';
end;
begin
p_showd := get_vred('pshowd',name_array,value_array);
p_show_only_results := nvl(get_vred('pshow_only_results',name_array,value_array),'N');
p_show_no_buttons := nvl(get_vred('pshow_no_buttons',name_array,value_array),'N');
-- for i in 1..nvl(name_array.count,0) loop
-- htp.p( name_array(i)'='value_array(i)'<br>');
-- end loop;
if p_showd is null then
htp.p('<P><TABLE>');
for r in ( select * from SHOWSQLCODE order by description ) loop
htp.p('<TR><TD> </TD><TD><A CLASS=link
href="?pshowd='r.id'">'r.description'</A></TD></TR>');
end loop;
htp.p('</TABLE></P>');
else -- show SQL
if p_showd = 'SQL' then
v_sql := get_vred('psql',name_array,value_array);
else
select d.sqlcode into v_sql
from SHOWSQLCODE d
where d.id = p_showd;
end if;
if length(v_sql) > 0 then
--PREBERI COLUMNS
declare
v_sqlpod varchar2(4000) := substr (v_sql,
instr(upper(v_sql),'SELECT'),
instr(upper(v_sql),'FROM')-instr(upper(v_sql),'SELECT'));
i pls_integer := 0;
v_z pls_integer;
v_k pls_integer;
v_sqlpod1 varchar2(4000);
begin
if instr(v_sqlpod,'* ') > 0 or instr(v_sqlpod,'*,') > 0 then
v_sqlpod := substr (v_sql,
instr(upper(v_sql),'SELECT',1,1),
instr(upper(v_sql),'FROM',1,2)-instr(upper(v_sql),'SELECT',1,1));
v_sqlpod1 := substr (v_sql,
instr(upper(v_sql),'SELECT',1,2),
instr(upper(v_sql),'FROM',1,2)-instr(upper(v_sql),'SELECT',1,2));
end if;
if instr(v_sqlpod1,'* ') > 0 or instr(v_sqlpod1,'*,') > 0 then
v_sqlpod := substr (v_sql,
instr(upper(v_sql),'SELECT',1,1),
instr(upper(v_sql),'FROM',1,3)-instr(upper(v_sql),'SELECT',1,1));
end if;
while instr(v_sqlpod,'"') > 0 loop
i := i+1;
v_z := instr(v_sqlpod,'"')+1;
v_k := instr(v_sqlpod,'"',v_z);
vcolumns(i).name := substr(v_sqlpod,v_z, v_k-v_z);
v_sqlpod := substr(v_sqlpod,v_k+1);
end loop;
end;
--READ BIND
declare
v_sqlpod varchar2(4000) := v_sql;
i pls_integer := 0;
v_z pls_integer;
v_k pls_integer;
begin
while instr(v_sqlpod,'&"') > 0 loop
i := i+1;
v_z := instr(v_sqlpod,'&"')+2;
v_k := instr(v_sqlpod,'"',v_z);
vbinds(i).name := substr(v_sqlpod,v_z, v_k-v_z);
vbinds(i).var := replace(translate(substr(v_sqlpod,v_z, v_k-v_z),'!"#$%&/()=?*+''<>,.-_:;',
' '),' ','');
v_sqlpod := substr(v_sqlpod,v_k+1);
end loop;
end;
--READ SQL
for i in 1..vbinds.count loop
v_sql := replace(v_sql,'&"'vbinds(i).name'"',':'vbinds(i).var);
end loop;
--EXECUTE IMMEDIATE CODE
v_execimed(nvl(v_execimed.count+1,1)) := '
DECLARE
';
for i in 1..vbinds.count loop
v_show := true;
for j in i+1..vbinds.count loop
if vbinds(i).var = vbinds(j).var then v_show := false; end if;
end loop;
if v_show then
v_execimed(nvl(v_execimed.count+1,1)) := 'b' vbinds(i).var' varchar2(4000) :=
'''get_vred('b'vbinds(i).var,name_array,value_array)''';
';
end if;
end loop;
if instr(v_sql,'#NAVIGATION#') > 0 then
v_execimed(nvl(v_execimed.count+1,1)) :=
' vprowindex number := 'nvl(get_vred('bprowindex',name_array,value_array),1)';
vprows number := 'nvl(get_vred('bprows',name_array,value_array),10)';
';
end if;
v_execimed(nvl(v_execimed.count+1,1)) := '
vpaction varchar2(100) := nvl('''get_vred('bpaction',name_array,value_array)''',''Search'');
';
v_execimed(nvl(v_execimed.count+1,1)) := '
type rowsa is record
(';
for i in 1..vcolumns.count loop
if i = 1 then
v_execimed(nvl(v_execimed.count+1,1)) := ' s'i' varchar2(4000)
';
else
v_execimed(nvl(v_execimed.count+1,1)) := ' ,s'i' varchar2(4000)
';
end if;
end loop;
v_execimed(nvl(v_execimed.count+1,1)) := ' );
TYPE rowse IS TABLE OF rowsa index by binary_integer;
zapisicur rowse;
zapisi rowse;
TYPE CurTyp IS REF CURSOR;
cur_dyn CurTyp;
v_limit pls_integer;
c_limit pls_integer := 5000;
v_sprlimit varchar2(2000) := '''';
v_j pls_integer;
BEGIN
-- execute immediate ''alter session set nls_language=''''SLOVENIAN'''''';
-- execute immediate ''alter session set nls_territory=''''SLOVENIA'''''';
if vpaction = ''File'' then
owa_util.mime_header(''send'');
end if;
';
if p_show_only_results <> 'Y' and p_show_no_buttons <> 'Y' then
v_execimed(nvl(v_execimed.count+1,1)) := '
if vpaction <> ''File'' then
htp.p(''<FORM METHOD="POST" NAME="F1">'');
end if;
';
end if;
if instr(v_sql,'#NAVIGATION#') > 0 then
v_execimed(nvl(v_execimed.count+1,1)) := '
if vpaction in ('substr(v_sql,instr(v_sql,'#NAVIGATION#')+13,instr(v_sql,'*/')-
instr(v_sql,'#NAVIGATION#')-13 )') then
v_limit := vprowindex+vprows-1;
else
v_limit := c_limit;
end if;
';
else
v_execimed(nvl(v_execimed.count+1,1)) := '
v_limit := c_limit;
';
end if;
v_execimed(nvl(v_execimed.count+1,1)) := '
OPEN cur_dyn FOR
'''replace(v_sql,'''','''''')'''';
if vbinds.count > 0 then
v_execimed(nvl(v_execimed.count+1,1)) := '
USING ';
for i in 1..vbinds.count loop
if i = 1 then
v_execimed(nvl(v_execimed.count+1,1)) := 'b' vbinds(i).var;
else
v_execimed(nvl(v_execimed.count+1,1)) := ',b'vbinds(i).var;
end if;
end loop;
end if;
v_execimed(nvl(v_execimed.count+1,1)) := '
;
FETCH cur_dyn BULK COLLECT INTO zapisicur ';
if instr(v_sql,'#NAVIGATION#') > 0 then
v_execimed(nvl(v_execimed.count+1,1)) := ' LIMIT v_limit';
end if;
v_execimed(nvl(v_execimed.count+1,1)) := ';
CLOSE cur_dyn;
';
if instr(v_sql,'#NAVIGATION#') > 0 then
v_execimed(nvl(v_execimed.count+1,1)) := '
if vpaction in ('substr(v_sql,instr(v_sql,'#NAVIGATION#')+13,instr(v_sql,'*/')-
instr(v_sql,'#NAVIGATION#')-13 )') then
v_j := 1;
for v_i in vprowindex..zapisicur.count loop
zapisi(v_j) := zapisicur(v_i);
v_j := v_j + 1;
end loop;
else
zapisi := zapisicur;
if zapisi.count >= c_limit then
v_sprlimit := ''To many results (more than ''c_limit'' rows)!'';
end if;
end if;
';
else
v_execimed(nvl(v_execimed.count+1,1)) := '
zapisi := zapisicur;
if zapisi.count >= c_limit then
v_sprlimit := ''To many results (more than ''c_limit'' rows)!'';
end if;
';
end if;
if p_show_only_results <> 'Y' and p_show_no_buttons <> 'Y' then
v_execimed(nvl(v_execimed.count+1,1)) := '
if vpaction <> ''File'' then
htp.p('' <BR>
<INPUT TYPE="submit" NAME="action" CLASS=submit VALUE="Search">
<INPUT TYPE="button" NAME="xx" CLASS=submit VALUE="Back"
onclick="window.location=''''?1=1''''">
<hr>'');
end if;
';
end if;
if p_show_only_results <> 'Y' then
v_execimed(nvl(v_execimed.count+1,1)) := '
if vpaction <> ''File'' then
htp.p(''<TABLE>'');
end if;
';
for i in 1..vbinds.count loop
v_show := true;
for j in i+1..vbinds.count loop
if vbinds(i).var = vbinds(j).var then v_show := false; end if;
end loop;
if vbinds(i).var in ('prows','prowindex','paction') then
v_show := false;
end if;
if v_show then
v_execimed(nvl(v_execimed.count+1,1)) := 'if vpaction <> ''File'' then ';
v_execimed(nvl(v_execimed.count+1,1)) := 'htp.p(''<tr> <td
CLASS=labela1>' vbinds(i).name': </td>'');
';
v_execimed(nvl(v_execimed.count+1,1)) := 'htp.p(''<td><input
type=input name="b' vbinds(i).var'" value="'get_vred('b'vbinds(i).var,name_array,value_array)'">
</td></tr>'');
';
v_execimed(nvl(v_execimed.count+1,1)) := 'end if;';
end if;
end loop;
v_execimed(nvl(v_execimed.count+1,1)) := '
if vpaction <> ''File'' then
htp.p(''</TABLE><BR>'');
end if;
';
end if;
v_execimed(nvl(v_execimed.count+1,1)) := '
if vpaction <> ''File'' then
htp.p(''<TABLE CLASS=okvirb border="1" width="100%" cellspacing="0" cellpading="3">
<TR CLASS=izpistabela>'');
end if;
';
for i in 1..vcolumns.count loop
v_execimed(nvl(v_execimed.count+1,1)) := '
if vpaction <> ''File'' then
htp.p(''<TD CLASS=labela >'vcolumns(i).name'</TD>'');
else
htp.prn(''"'vcolumns(i).name'";'');
end if;
';
end loop;
v_execimed(nvl(v_execimed.count+1,1)) := '
if vpaction <> ''File'' then
htp.p(''</TR>'');
else
htp.p('''');
end if;
FOR i in 1..zapisi.count LOOP
if vpaction <> ''File'' then
htp.p(''<TR CLASS=izpistabela>'');
end if;
';
for i in 1..vcolumns.count loop
v_execimed(nvl(v_execimed.count+1,1)) := '
if vpaction <> ''File'' then
htp.p(''<TD'');
declare
yxd number;
begin
yxd := to_number(replace(replace(zapisi(i).s'i','','',''''),''.'',''''),''999G999G999G999G990D00'');
if yxd = 0 then
htp.p ('' align="right" '');
end if;
exception when others then null;
end;
htp.p(''> ''zapisi(i).s'i'''</TD>'');
else
htp.prn(''"''zapisi(i).s'i'''";'');
end if;
';
end loop;
v_execimed(nvl(v_execimed.count+1,1)) := '
if vpaction <> ''File'' then
htp.p(''</TR>'');
else
htp.p('''');
end if;
END LOOP;
if vpaction <> ''File'' then
htp.p(''</TABLE>
<BR>
<P>
''v_sprlimit''
</P>
'');
end if;
';
if p_show_only_results <> 'Y' and p_show_no_buttons <> 'Y' then
v_execimed(nvl(v_execimed.count+1,1)) := '
if vpaction <> ''File'' then
htp.p(''
<hr>
<INPUT TYPE="submit" NAME="action" CLASS=submit VALUE="Search">
<INPUT TYPE="button" NAME="xx" CLASS=submit VALUE="Back" onclick="window.location=''''?1=1''''">
'');
end if;
';
v_execimed(nvl(v_execimed.count+1,1)) := '
if vpaction <> ''File'' then
htp.p('' </FORM>
'');
end if;
';
end if;
v_execimed(nvl(v_execimed.count+1,1)) := '
END;';
begin
v_execimedv := '';
xlen := 0;
for i in 1..v_execimed.count loop
v_execimedv := v_execimedvv_execimed(i);
xlen := xlen + length(v_execimed(i));
end loop;
execute immediate v_execimedv;
--htp.p(v_execimedv);
end;
end if;
end if;
exception when others then
htp.p('<Tr><TD nowrap><font color=red>');
htp.p(substr(sqlerrm,1,11)' 'sqlerrm);
htp.p('</font></Td><TR>');
end;
Mail message from database
You can mail message from your database with this function.
procedure MailMessage( from_name varchar2,
to_name varchar2,
subject varchar2,
message varchar2,
smtp_server varchar2,
smtp_server_port number default 25,
max_size number default 9999999999,
filename1 varchar2 default null,
filename2 varchar2 default null,
filename3 varchar2 default null,
debug number default 0 ) is
v_smtp_server varchar2(50) := smtp_server;
v_smtp_server_port number := smtp_server_port;
v_directory_name varchar2(100);
v_file_name varchar2(100);
v_line varchar2(1000);
crlf varchar2(2):= '
';
mesg varchar2(32767);
conn UTL_SMTP.CONNECTION;
type varchar2_table is table of varchar2(200) index by binary_integer;
file_array varchar2_table;
i binary_integer;
v_file_handle utl_file.file_type;
v_slash_pos number;
mesg_len number;
mesg_too_long exception;
invalid_path exception;
mesg_length_exceeded boolean := false;
begin
file_array(1) := filename1;
file_array(2) := filename2;
file_array(3) := filename3;
conn:= utl_smtp.open_connection( v_smtp_server, v_smtp_server_port );
utl_smtp.helo( conn, v_smtp_server );
utl_smtp.mail( conn, from_name );
utl_smtp.rcpt( conn, to_name );
utl_smtp.open_data ( conn );
mesg:=
'Date: ' TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) crlf
'From: ' from_name crlf
'Subject: ' subject crlf
'To: ' to_name crlf
'Mime-Version: 1.0' crlf
'Content-Type: multipart/mixed; boundary="DMW.Boundary.605592468"' crlf
'' crlf
'text besedilo' crlf
'' crlf
'--DMW.Boundary.605592468' crlf
'' crlf
message crlf;
mesg_len := length(mesg);
if mesg_len > max_size then
mesg_length_exceeded := true;
end if;
utl_smtp.write_data ( conn, mesg );
for i in 1..3 loop
exit when mesg_length_exceeded;
if file_array(i) is not null then
begin
v_slash_pos := instr(file_array(i), '/', -1 );
if v_slash_pos = 0 then
v_slash_pos := instr(file_array(i), '\', -1 );
end if;
v_directory_name := substr(file_array(i), 1, v_slash_pos - 1 );
v_file_name := substr(file_array(i), v_slash_pos + 1 );
v_file_handle := utl_file.fopen(v_directory_name, v_file_name, 'r' );
mesg := crlf '--DMW.Boundary.605592468' crlf
'Content-Type: application/octet-stream; name="' v_file_name '"' crlf
'Content-Disposition: attachment; filename="' v_file_name '"' crlf
'Content-Transfer-Encoding: 8bit' crlf crlf ;
mesg_len := mesg_len + length(mesg);
utl_smtp.write_data ( conn, mesg );
loop
utl_file.get_line(v_file_handle, v_line);
if mesg_len + length(v_line) > max_size then
mesg := '*** truncated ***' crlf;
utl_smtp.write_data ( conn, mesg );
mesg_length_exceeded := true;
raise mesg_too_long;
end if;
mesg := v_line crlf;
utl_smtp.write_data ( conn, mesg );
mesg_len := mesg_len + length(mesg);
end loop;
exception
when utl_file.invalid_path then
if debug > 0 then
dbms_output.put_line('Error in opening attachment '
file_array(i) );
end if;
when others then
null;
end;
mesg := crlf;
utl_smtp.write_data ( conn, mesg );
utl_file.fclose(v_file_handle);
end if;
end loop;
mesg := crlf '--DMW.Boundary.605592468--' crlf;
utl_smtp.write_data ( conn, mesg );
utl_smtp.close_data( conn );
utl_smtp.quit( conn );
end;
Create triggers for LOG tables
Create triggers to load LOG tables:
procedure LogCreateTriggers as
v_vred varchar2(4000);
v_ime varchar2(4000);
begin
-- Test statements here
for r in (select t.table_name
from user_tables t
order by t.table_name) loop
htp.p('create or replace trigger 'substr(r.table_name,1,26)'_gbu');
htp.p(' before update on 'r.table_name' ');
htp.p(' for each row');
htp.p('declare');
htp.p(' -- local variables here');
htp.p('begin');
htp.p(' -- Generated: 'to_char(sysdate,'dd.mm.yyyy hh:mi:ss')'.');
htp.p(' -- When you change trigger rename it with deleteing char "g" in trigger name.');
htp.p(' if 1=2');
for r1 in (select t.table_name,t.column_name,t.data_type
from user_tab_cols t
where t.table_name = r.table_name
order by t.column_id) loop
if r1.data_type = 'VARCHAR2' then
htp.p(' or nvl(:new.'r1.column_name','' '') <> nvl(:old.'r1.column_name','' '')');
elsif r1.data_type = 'NUMBER' then
htp.p(' or nvl(:new.'r1.column_name',-111111111111111111111) <> nvl(:old.'r1.column_name',
-111111111111111111111)');
elsif r1.data_type = 'DATE' then
htp.p(' or nvl(:new.'r1.column_name',to_date(''01011900'',''ddmmyyyy'')) <> nvl(:old.'r1.column_name',
to_date(''01011900'',''ddmmyyyy''))');
end if;
end loop;
htp.p(' then');
v_ime := '';
v_vred := '';
for rx in (
select ','column_name ime, ',:old.'column_name vred
from user_tab_cols
where table_name = r.table_name
order by column_id
) loop
v_ime := v_ime rx.ime;
v_vred := v_vred rx.vred;
end loop;
htp.p(' insert into log$'r.table_name);
htp.p(' ('substr(v_ime,2)',logczsp, logoper, logakcija)');
htp.p(' values');
htp.p(' ('substr(v_vred,2)',sysdate, :new.aoper, ''U'');');
htp.p(' end if;');
htp.p('end;');
htp.p('/');
end loop;
end;
Create LOG tables
When you wont to create LOG tables for your sistem, jou can use this scripts:
Create LOG tables:
Create LOG tables:
procedure LogCreateTables as
v_vred varchar2(4000);
v_ime varchar2(4000);
begin
-- Test statements here
for r in (select t.table_name
from user_tables t
order by t.table_name) loop
htp.p('create table log$'r.table_name' as select * from 'r.table_name' where 1=2;');
htp.p('alter table log$'r.table_name' add logczsp date not null;');
htp.p('alter table log$'r.table_name' add logoper varchar2(50) not null;');
htp.p('alter table log$'r.table_name' add logakcija varchar2(50) not null;');
end loop;
end;
Sunday, November 26, 2006
Simple upload file program
This is simple program for uploading files from your computer into Oracle database.
You can call uppload procedure like:
http://your_web_server_where _you_configure_dad:port/xyz_test/!documentupload
procedure documentupload (
name_array in owa.vc_arr,
value_array in owa.vc_arr)
is
pdatoteka Documents.name%type;
FUNCTION namedParam(
p_searchVal in varchar2,
p_name_array in owa.vc_arr,
p_value_array in owa.vc_arr
) RETURN varchar2 IS
i integer;
BEGIN
FOR i IN 1..nvl(p_name_array.count,0) LOOP
if p_name_array(i)=p_searchVal then
return p_value_array(i);
end if;
END LOOP;
return null;
END;
begin
pdatoteka := namedParam('pdatoteka',name_array,value_array);
htp.p('
<html>
<HEAD>
<META HTTP-EQUIV="Content-Type" NAME="" CONTENT="text/html;CHARSET=WINDOWS-1250">
<META HTTP-EQUIV="Pragma" CONTENT="no-cache">
<META HTTP-EQUIV="Expires" CONTENT="-1">
<title>Upload</title>
</HEAD>
<body>
<TABLE width="100%" border="0" cellspacing="0" cellpadding="0" bgcolor="#FFFF99" summary="">
<tr><td><font size="5">Upload</font></TD></TR>
</TABLE>');
if pdatoteka is not null then
htp.p('
<BR> File "'substr(pdatoteka, instr(pdatoteka,'/',-1,1)+1
)'" is uploaded. <BR>');
else
htp.p('
<FORM enctype="multipart/form-data" action="!upload" method="POST">
<p>File:<INPUT type="file" name="pdatoteka"></p>
<p><INPUT type="submit" class=submit value="Submit" ></p>
</FORM>
<BR>');
end if;
htp.p('
</body>
</html>');
exception when others then
htp.p('
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;CHARSET=WINDOWS-1250">
<META HTTP-EQUIV="Pragma" CONTENT="no-cache">
<META HTTP-EQUIV="Expires" CONTENT="-1">
<TITLE>Message</TITLE>
</HEAD>
<BODY bgcolor="#C0C0C0">');
htp.p('
<table>
<tr><td><FONT COLOR="red" size="4">Error: </FONT></td>
<td>'replace(sqlerrm,'','<BR>')'</td>
</tr>
</table>
<BR>
<INPUT TYPE="button" VALUE="Back" class=submit onClick="javascript:history.go(-1)">
</BODY>
</HTML>');
end;
You can call uppload procedure like:
http://your_web_server_where _you_configure_dad:port/xyz_test/!documentupload
Saturday, November 25, 2006
Calling programs with ! or not
You can call PL/SQL procedures or packages with ! in front of the name. When you use !, it means you have dynamic parameters, then procedure must have this structure:
If you are using procedures with dynamic parameters and you wont write ! in URL location then you must write RewriteRule. You must put it in any .conf file of Apache. The best place is in the beginning of dads.conf. The rule can be like that:
procedure procedure_name (Parameters and theirs values are saved in name_array and value_array parameters.
name_array in owa.vc_arr,
value_array in owa.vc_arr
) is
begin
…
end;
If you are using procedures with dynamic parameters and you wont write ! in URL location then you must write RewriteRule. You must put it in any .conf file of Apache. The best place is in the beginning of dads.conf. The rule can be like that:
RewriteEngine onIf you don't use !, then your procedure parameters must exists on HTML page like input objects.
RewriteRule ^/xyz_test/docs(.*) /xyz_test/docs$1 [PT,L]
RewriteRule ^/xyz_test/(.*) /xyz_test/!$1 [PT,L]
Upload table structure and download procedure
Upload table should have this structure:
Download procedure:
create table Documents(The name must be unique.
NAME VARCHAR2(256) not null,
MIME_TYPE VARCHAR2(128),
DOC_SIZE NUMBER,
DAD_CHARSET VARCHAR2(128),
LAST_UPDATED DATE,
CONTENT_TYPE VARCHAR2(128),
BLOB_CONTENT BLOB,
// your additional columns
)
Download procedure:
procedure documentdownload is
v_filename Documents.name%type;
function getfilepath return varchar2 is
script_name varchar2(255) default owa_util.get_cgi_env( 'SCRIPT_NAME' );
path_info varchar2(255) default owa_util.get_cgi_env( 'PATH_INFO' );
pos number;
x varchar2(50) := 'docs'; --variable from PlsqlDocumentPath
begin
script_name := script_name path_info;
pos := instr(script_name, x);
script_name := substr(script_name, pos+length(x)+1, length(script_name)-pos-length(x));
return script_name;
end getfilepath;
begin
v_filename := upper(getfilepath);
select name into v_filename
from Documents
where UPPER(name) = v_filename;
wpg_docload.download_file(v_filename);
end;
Configure modplsql
MOD_PLSQL is an Apache (Web Server) extension module that allows one to create dynamic web pages from PL/SQL packages and stored procedures. It is ideal for developing fast and flexible applications that can run on the Internet or an Intranet. MOD_PLSQL was formerly called the Oracle PL/SQL Cartridge and OWA (Oracle Web Agent).
Usually is located in ORACLE_HOME/Apache/modplsql
When you try to connect to database you have to configure DAD - Database Access Descriptor.
Usually the configuration file is located in modplsql/conf/dads.conf
The main settings for DAD are:
http://your_web_server_where _you_configure_dad:port/xyz_test/test
Usually is located in ORACLE_HOME/Apache/modplsql
When you try to connect to database you have to configure DAD - Database Access Descriptor.
Usually the configuration file is located in modplsql/conf/dads.conf
The main settings for DAD are:
Testing connection and settings. In the databace you must create procedure:
<Location /xyz_test> #name you use in URL
SetHandler pls_handler
Order allow,deny
Allow from All
AllowOverride None
PlsqlDatabaseUsername userapp #user name
PlsqlDatabaseConnectString server:port:database_SID SIDFormat
PlsqlAuthenticationMode Basic #authentication type
PlsqlDocumentTablename Documents #Table name for uploading documents
PlsqlDocumentPath docs #name in URL to get documents from table
PlsqlDocumentProcedure documentdownload #procedure to show documents
PlsqlDefaultPage #default page
PlsqlDatabasePassword #password, it is optional
PlsqlNLSLanguage SLOVENIAN_SLOVENIA.UTF8 #NLS settings
</Location>
procedure test isand then call it from Internet browser like:
begin
htp.p('This is a test!');
end;
http://your_web_server_where _you_configure_dad:port/xyz_test/test
First post
In last eight years I wrote lots of PL/SQL programs. Because lots of tricks, tips, and samples are useful I am going to post them on this blog.
