Re: Uploading files and saving binary files

Well you named it, the frustration I have with OAS and content service is indeed that we cannot choose the table nor the format (blob, long raw, or even better: bfile...) of the target column. Plus the fact that , as long as I know, the cartridge does not pass the unique id of the uploaded object to the pl/sql it calls once the file is uploaded. The latter meaning you have to use the file name which is not unique ,hence the danger of using it in a multi-user environment.

In Ora 8.1 it is now possible to easily convert from long raw to blob by using the TO_LOB sql function. Thus we could move the content to a blob column. The advantage is that you can then use the DBMS_BLOB package to manipulate the binary. To read it by chunk or to modify it even.

Saving to a binary file is another story. First you need to be able to read the data by chunk cause PL/SQL raw data type is limited to 32K. This is possible if you have a blob by using the DBMS_LOB package. If you have a long (not a long raw) , you can use DBMS_SQL to read the data by chunk. BUT to save it to a file you need to use UTL_FILE and it supports only TEXT FILES ! Meaning you must convert the chunk to HEX with UTL_RAW and then run a utility on the file system to convert it back to binary. On Metalink, Oracle gives an alternative by means of an extern procedure written in C and called from within PL/SQL. In that example they do not use UTL_FILE, the C program receives the binary chunk in HEX, convert it back to binary and stores it on the file system. Plenty of info about all this in METALINK's site. I can mail some of their samples if you want to.  

While writing this down, I'm having another idea. Thru the OAS you can download a binary file stored in the DB by means of the htp.download proc (though you need to take some precautions like sending first the mime-type then the content-length). This means, I suppose, that one could write a program in perl or java that could act as a browser requesting a file download . I have no clue how to do this, if you have an idea...

So in the end, uploading the file with perl seems a good idea to me. This does not means the file is lost for Oracle. In oracle 8 you now have BFILE. Turning an uploaded file into a bfile is very easy since it only is a pointer in the DB to the file. The advantages are: you can use this BFILE in PL/SQL with the DBMS_LOB package and you can build a Report 6.0 that will use these files.

Zab.

In article <3870d163$0$23779_at_reader3.casema.net>, Onno M. van der Straaten <ovds_at_casema.net> wrote:

> I used the UTL_FILE-package. This Perl-implementation is suitable for
> uploading and processing flat text files. I haven't used it for uploading
> binary files. Furthermore, the database was 7.3.4. (dbms_lob not available).
>
> The problem with the content service of OAS 4.08 in combination with 7.3.4.
> is the impossibility to extract the files from the database and save to the
> filesystem, without having to write in filehandler in C++. Uploading (and
> dowloading) large (binary) files to (and from) the database with the content
> service works fine. But what to do if the files need further processing!
>
> I suspect this is not a problem with Oracle8 (UTL_RAW?). Is it possible to
> save a file to the filesystem from the long raw column where files are
> stored by the content service of OAS?
>
> I'm interested in the limitations you experienced with OAS.
>
> Onno
Received on Tue Jan 04 2000 - 21:38:21 CET