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
This is how I did it. Works with CLOBs fine, I expect BLOBs would be similar.
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.
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;
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.
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.
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 🙂
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.
If you want to avoid that Java thingie, you can use utl_encode.base64_encode
Is that codes also applicable in forms 6i? thanx
best regards..
The original article is about using that technique in Forms 6i
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.
Eduard: dbms_lob isn’t applicable in forms 6i, thats why i presented the function “read_export” in my original article.
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
hmmm ist the code that you have is only for the database side? how will you call the procedure in the forms?
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.
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
Thanx for the help i already made it. Thanx best regards.. God Bless.
You’re welcome 🙂
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
Alraies: Sorry, i don’t know. Maybe Matt has subscripted to the comments and reads this.
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; /
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!
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
hi could you give me a code to get an image of a database field blob forms 6i. tks
Mario: In short, no. I have none. But it should be easily written with all the tips here.
Post a Comment