info.michael-simons.eu

There is this nice project of ours from 2002…. developed with Oracle Forms 6i and database PL/SQL Procedures. This project runs fine with an estimated 6gig database. Plain old client server model.

We have some XML / XSLT based HTML reports that are processed – thanks to Oracle 9i – directly in the database with packages dbms_xmlgen and xmlparser. Data is collected in a mere handful of so called “object views”, that are denormalized, hierarchical views on data.

After receiving the assignment to generate pdf it was no big problem at all to write some java classes around iText and dom4j to generate new reports.
I decided agains xsl-fo as i found no inexpensive tools for easily designing adequate stylesheets. For the worse, xsl-fo lacks several important features, i.e. collapsing borders. My customers would have laughed at best if i had presented the prototype…

So i have this java classes – all in the database for sure, no need for java on the client side -, i have added code to my package and i thought it would be easy to write the resulting blob with Forms 6i to the client…

Bah! There is no such easy thing in Forms. I remembered havin’ problems reading a clob, as the whole bunch of dbms_lob functionality doesn’t work in forms… So my workaround for creating text files from clobs was on the database side:

PROCEDURE  read_export(
  p_export_id  IN     NUMBER,
  p_menge      IN OUT BINARY_INTEGER,
  p_offset     IN     BINARY_INTEGER,
  p_buffer     IN OUT VARCHAR2
) IS
 clInhalt  CLOB;
 BEGIN
  BEGIN
    SELECT processed_export
      INTO clInhalt
      FROM xmlt_exporte
     WHERE export_id = p_export_id;
    DBMS_LOB.READ(clInhalt, p_menge, p_offset, p_buffer);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      p_menge:= 0;
  END;
EXCEPTION
  WHEN OTHERS THEN
    p_menge := 0;
END;

and in a forms on client side:

fhDateiOut := text_io.fopen(vcPath || vcFile, 'W');   	  	   	
LOOP			
  pck_xml.read_export(p_export_id, i, offset, vcBuffer);
  EXIT WHEN i = 0;
  offset := offset + i;
  text_io.put(fhDateiOut, vcBuffer);
END LOOP;					   
text_io.fclose(fhDateiOut);

(text_io is a forms standard package).

This works quite well.. So i thought, well, d2kwutil has some functions to write binary files, so i copy the database procedure, use dbms_lob.read with blob and raw and i’m done…
Yeah, i was done. Well fucked off.

I have no idea in which way dbms_lob.read converts blob data to raw, but it’s everything but the original. So after banging my head against the wall, finding things like “use software developed in this century” in forums, i wrote the following java code:

final BASE64Encoder enc = new BASE64Encoder();
 
final CLOB rv = CLOB.createTemporary(tmp.getJavaSqlConnection(), true, CLOB.DURATION_SESSION);
 
int bufSize = 1024*1024*1;
 
final BufferedInputStream in = new BufferedInputStream(tmp.getBinaryStream(), bufSize * 2);        
final BufferedOutputStream out = new BufferedOutputStream(rv.setAsciiStream(0L), bufSize);
 
enc.encode(in, out);
out.flush();
out.close();

which runs in the database, takes a blob as parameter and base64 encodes it to a clob. This clob is written to my export table and read by forms in the above way. Then i have a little base64.exe along with my forms that is called from within forms that decodes the file….

So much trouble just for writing binary data from server to client… it’s a twoliner with c or java.

Share This