Hints and tricks for Oracle and other stuff...

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
)

select id "ID", description "Description"
from showsqlcode
where description like &"Description:"
order by id

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;