On writing binary data from within Oracle Forms 6i

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:

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:

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.

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)

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):

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.

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.

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.

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.

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?

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;
/

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.