On writing binary data from within Oracle Forms 6i

January 29, 2007 by Michael

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.

25 comments

  1. Matt Jernigan wrote:

    This is how I did it. Works with CLOBs fine, I expect BLOBs would be similar.

    	---------------------------------------------------------------------------
    	PROCEDURE LP_LOB_TO_FILE
    	---------------------------------------------------------------------------
    	(	lp_clob			CLOB,
    		lp_file_dir		VARCHAR2,
    		lp_file_name	VARCHAR2 )
    	IS
    		l_clob			CLOB;
    		l_clob_length	PLS_INTEGER;
    		l_iterations	PLS_INTEGER;
    		l_chunk_vc2		VARCHAR2(32000);
    		l_chunk_raw		RAW(32000);
    		l_chunk_length	PLS_INTEGER := 32000;
    		l_file_handle	UTL_FILE.FILE_TYPE;
    		i				PLS_INTEGER;
    	BEGIN
    		l_clob := lp_clob;
    		l_clob_length := DBMS_LOB.GETLENGTH(l_clob);
    		l_iterations := CEIL(l_clob_length / l_chunk_length);
    		DBMS_OUTPUT.PUT_LINE('***   Clob loops, size  : '||l_iterations||', '||l_clob_length);
     
    		l_file_handle := UTL_FILE.FOPEN(lp_file_dir, lp_file_name, 'W', 32767);
     
    		FOR i IN 0 .. l_iterations - 1 LOOP
    			l_chunk_vc2 := DBMS_LOB.SUBSTR(l_clob, l_chunk_length, i * l_chunk_length + 1);
    			l_chunk_raw := UTL_RAW.CAST_TO_RAW(l_chunk_vc2);
    			UTL_FILE.PUT_RAW(l_file_handle, l_chunk_raw, TRUE);
    			DBMS_OUTPUT.PUT_LINE('***   XML loop, chunk   : '||i||', '||LENGTH(l_chunk_vc2));
    		END LOOP;
     
    		UTL_FILE.FCLOSE(l_file_handle);
    		DBMS_OUTPUT.PUT_LINE('***   File written..... : '||lp_file_name);
    	END;
    	---------------------------------------------------------------------------
    Posted on September 26, 2008 at 5:52 PM | Permalink
  2. Michael wrote:

    Hi Matt, this works without question but the big difference is that the resulting files reside on the server side in some virtual directory the database can access.

    My example is about writing c|blob data from server to the client via Forms.

    Posted on September 26, 2008 at 6:23 PM | Permalink
  3. Matt Jernigan wrote:

    Ok, I’m with you now. The issue is the TEXT_IO package does not have the equivalent of UTL_FILE.PUT_RAW and TEXT_IO.PUT is not overloaded for RAW datatypes (I would expect CLIENT_TEXT_IO.PUT to have the same limitations). I believe when Oracle does an implicit raw to char conversion that it does the equivalent of RAWTOHEX(raw) — but I’m not positive on that. When using your first method, if your BLOBs are coming out twice the expected size and filled with nothing but hex characters then this is what is happening.

    Assuming you change p_buffer to RAW, then something like the following _might_ get you what you want (assuming the Oracle character set in use doesn’t mess with you on the cast_to_varchar2)

    pck_xml.read_export(p_export_id, i, offset, vrBuffer);
    vcBuffer := UTL_RAW.CAST_TO_VARCHAR2(vrBuffer);
    text_io.put(fhDate1Out, vcBuffer);

    If that doesn’t work, or if you want to build something that is character-set-proof, then you will need to build your own RAW_TO_VARCHAR2 converter. One way to do this is to convert the raw to hex using RAWTOHEX(raw), nibble off each hex-character-pair and convert its value to decimal, then rebuild the string with CHR(decimal) concats. Another way, avoiding RAWTOHEX, is to feed the raw one byte at a time to CHR(UTL_RAW.CAST_TO_BINARY_INTEGER(raw_byte)). No guess as to which might perform better. They both seem slow.

    Anyhow, here are some queries that demonstrate what is going on. They both correctly return the value of 13 (hex 0D):

    SELECT UTL_RAW.CAST_TO_BINARY_INTEGER(HEXTORAW(‘0D’)) FROM dual;

    SELECT ASCII(UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW(‘0D’))) FROM dual;

    Posted on September 26, 2008 at 11:37 PM | Permalink
  4. Michael wrote:

    Hey Matt, i appreciate your comments.

    Maybe i wasn’t clear, but in short my solution is to base64 encode the blob into a clob (with java on the server side in the database) and put this thing is written by forms text_io.put to the client and then again base64 decoded.

    Cheers,
    Michael.

    Posted on September 27, 2008 at 9:03 AM | Permalink
  5. Matt Jernigan wrote:

    I understand what you are doing. I’m just offering a way to avoid Java and to avoid base64 encoding and to save your files directly to their original binary format using TEXT_IO.PUT.

    Posted on September 30, 2008 at 2:00 AM | Permalink
  6. Michael wrote:

    Ok, now i’m with you.

    I’ll see that i try it some time. I’m curious, which performs better… The Javastored Procedure version isn’t slow.

    Thanks for you kind comments by the way 🙂

    Posted on September 30, 2008 at 8:43 AM | Permalink
  7. Matt Jernigan wrote:

    Yeah, I can’t say I was concerned about performance. I just thought it would be convenient to not need to base64 decode the output file.

    I expect the first method, CAST_TO_VARCHAR2, will work in most situations because a varchar2 can actually hold binary data if you load it right (that is, if you don’t do anything to cause Oracle to try to do a character set conversion on it). But if your database is set to Unicode or some other non-8-bit character set, then all bets are off.

    If CAST_TO_VARCHAR2 doesn’t work then you will need to build your own procedure that loops through the raw one byte at a time and concats the equivalent character to a varchar2(xx byte). If Unicode is still messing with you then I would consider altering the session to an 8-byte character set such as WE8ISO8859P1.

    Regarding performance, I saw a test that shows it is a great deal faster to use TEXT_IO (which writes to the app server) and then move the file to the client, than it is to use CLIENT_TEXT_IO to write to the client directly. But this doesn’t appear to apply to you.

    Yet one other possibility that also eliminates the Java, is to write the RAWTOHEX output to a file (what I believe your first attempt was doing–assuming the buffers were big enough) and then use a bin2hex/hex2bin tool to decode it.

    Posted on October 1, 2008 at 11:37 PM | Permalink
  8. Ranjith wrote:

    If you want to avoid that Java thingie, you can use utl_encode.base64_encode

    Posted on May 11, 2009 at 5:54 PM | Permalink
  9. Eduard wrote:

    Is that codes also applicable in forms 6i? thanx

    best regards..

    Posted on November 5, 2009 at 7:45 AM | Permalink
  10. Michael wrote:

    The original article is about using that technique in Forms 6i

    Posted on November 5, 2009 at 12:06 PM | Permalink
  11. Eduard wrote:

    ah ok. just like to ask if the dbms_lob is applicable in forms 6i because everytime i use the dbms_lob procedure it always terminate the program that i run.

    thanx again and best regards.

    Posted on November 6, 2009 at 1:28 AM | Permalink
  12. Michael wrote:

    Eduard: dbms_lob isn’t applicable in forms 6i, thats why i presented the function “read_export” in my original article.

    Posted on November 6, 2009 at 9:15 AM | Permalink
  13. Eduard wrote:

    ah ok. but u uses there the dbms_lob.read()? right?

    means the code that you show is not also applicable in forms6i? im right?

    im sorry im just new in using clob data type in oracle forms thanx.
    best regards. God bless

    Posted on November 6, 2009 at 9:44 AM | Permalink
  14. Eduard wrote:

    hmmm ist the code that you have is only for the database side? how will you call the procedure in the forms?

    Posted on November 6, 2009 at 9:46 AM | Permalink
  15. Michael wrote:

    Eduard:

    The code in the original article consists of 2 parts: One for the db as a wrapper around dbms_lob. The other part (below “and in a forms on client side:”) is is forms code.
    Forms uses the db function “read_export” as a wrapper around dbms_lob.

    Forms itself can handle clob data directly from the table but has _no_ support for dbms_lob and the associated datatypes. Therefore the wrapper function.

    Have a great day,
    Michael.

    Posted on November 6, 2009 at 12:28 PM | Permalink
  16. Eduard wrote:

    Just like to ask if it is ok. I just to know if there is other way to concatenate a varchar2 datatype and clob datatype because when i concatenate this two datatype it show me an error of inconsistent datatype.

    thanx and best regards

    Posted on November 9, 2009 at 7:55 AM | Permalink
  17. Eduard wrote:

    Thanx for the help i already made it. Thanx best regards.. God Bless.

    Posted on November 12, 2009 at 10:19 AM | Permalink
  18. Michael wrote:

    You’re welcome 🙂

    Posted on November 12, 2009 at 10:47 AM | Permalink
  19. Alraies wrote:

    Thank you very much for the code
    I used it, and used Matt’s modification because I can’t use external tools. But there is still a problem, the put function do as the special characters says, when find 10, it adds chr(13) automatically, on EOF, it ends the file!
    Is there any alternative way to avoid this and also not use external .exe file?

    Thanks

    Posted on April 11, 2011 at 5:58 PM | Permalink
  20. Michael wrote:

    Alraies: Sorry, i don’t know. Maybe Matt has subscripted to the comments and reads this.

    Posted on April 11, 2011 at 8:52 PM | Permalink
  21. Larry Brendel wrote:

    Don’t know where I got most of this but only made miner changes to the original authors code:
    CREATE OR REPLACE FUNCTION BLOB2CLOB(B BLOB)
    RETURN CLOB IS
    C CLOB;
    N NUMBER;
    BEGIN
    IF (B IS NULL) THEN
    RETURN NULL;
    END IF;
    IF (DBMS_LOB.GETLENGTH(B)=0) THEN
    RETURN EMPTY_CLOB();
    END IF;
    DBMS_LOB.CREATETEMPORARY(C,TRUE);
    N:=1;
    WHILE (N+32767<=DBMS_LOB.GETLENGTH(B)) LOOP DBMS_LOB.WRITEAPPEND(C,32767,UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(B,32767,N))); N:=N+32767; END LOOP; DBMS_LOB.WRITEAPPEND(C,DBMS_LOB.GETLENGTH(B)-N+1,UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(B,DBMS_LOB.GETLENGTH(B)-N+1,N))); RETURN C; END; /

    Posted on April 12, 2011 at 5:11 PM | Permalink
  22. Alraies wrote:

    Never mind 🙂

    Finally I solved the problem, I used Windows APIs to write the file in binary mode instead of text_io package, and it worked!

    Thank you!

    Posted on April 17, 2011 at 6:05 PM | Permalink
  23. mls wrote:

    Well, I’d really like to see a working example.

    I tried to do quite the same thing, well, reading a client file in Forms 6i instead of writing. I used native Windows I/O functions and ORA_FFI package to make them available in Forms 6i. However, I just couldn’t make them work properly: the “ReadFile” function always set the error code to 998 which would be “Invalid memory reference” or something like that, and nothing was returned. (Despite that the file was opened (“CreateFile”) and closed (“CloseHandle”) successfully.) I just don’t know what the heck was not working OK—wrong declaration of “ReadFile” or what? The test file for reading is OK and resides in the local machine with ORACLE client and Forms 6i. Everything seems allright, only reading the file fails and I don’t know why.

    Thanks for any reply.

    Marko L. S.
    Slovenia

    Posted on August 22, 2011 at 11:11 AM | Permalink
  24. Mario wrote:

    hi could you give me a code to get an image of a database field blob forms 6i. tks

    Posted on February 19, 2014 at 4:52 AM | Permalink
  25. Michael wrote:

    Mario: In short, no. I have none. But it should be easily written with all the tips here.

    Posted on February 19, 2014 at 2:54 PM | Permalink
Post a Comment

Your email is never published. We need your name and email address only for verifying a legitimate comment. For more information, a copy of your saved data or a request to delete any data under this address, please send a short notice to michael@simons.ac from the address you used to comment on this entry.
By entering and submitting a comment, wether with or without name or email address, you'll agree that all data you have entered including your IP address will be checked and stored for a limited time by Automattic Inc., 60 29th Street #343, San Francisco, CA 94110-4929, USA. only for the purpose of avoiding spam. You can deny further storage of your data by sending an email to support@wordpress.com, with subject “Deletion of Data stored by Akismet”.
Required fields are marked *