Home » SQL & PL/SQL » SQL & PL/SQL » How do you read and print a BLOB field when doing a file upload
How do you read and print a BLOB field when doing a file upload [message #200624] Tue, 31 October 2006 09:22 Go to next message
Duane
Messages: 581
Registered: December 2002
Senior Member
Ok, I'm working on getting a web page working that allows a user to upload a file from their desktop to an Oracle server.
The page is just like the one here with "File Attachments" and the "Upload File" button.

I've setup the document table used by the DAD and the uploaded file is put into the BLOB content column. Now, how do I get the file out of the BLOB column so that I can insert the data into a table?
The uploaded file is a comma-delimited file that I need to insert into another table. This is what I don't know how to do.

CREATE TABLE DOCUMENTTABLE
(
  NAME          VARCHAR2(128 BYTE) NOT NULL,
  MIME_TYPE     VARCHAR2(128 BYTE),
  DOC_SIZE      NUMBER,
  DAD_CHARSET   VARCHAR2(128 BYTE),
  LAST_UPDATED  DATE,
  CONTENT_TYPE  VARCHAR2(128 BYTE),
  CONTENT       LONG RAW,
  BLOB_CONTENT  BLOB
)

Re: How do you read and print a BLOB field when doing a file upload [message #200640 is a reply to message #200624] Tue, 31 October 2006 11:18 Go to previous messageGo to next message
Duane
Messages: 581
Registered: December 2002
Senior Member
Looks like I have it.

I have to use DBMS_LOB.READ and UTL_FILE.PUT_LINE once I read the column contents into a variable.

At least, it seems to be working.
Re: How do you read and print a BLOB field when doing a file upload [message #200643 is a reply to message #200624] Tue, 31 October 2006 11:38 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If you are just looking to insert the BLOB contents into another table you could simply do:

INSERT INTO <new_table> (<Ccolumn_name>) SELECT blob_content FROM documenttable;
Re: How do you read and print a BLOB field when doing a file upload [message #200652 is a reply to message #200643] Tue, 31 October 2006 13:53 Go to previous message
Duane
Messages: 581
Registered: December 2002
Senior Member
I don't think that will work since the file that is contained/uploaded in the blob_content is comma-delimited.

The uploaded data/file that is in the blob_content of the documenttable looks like this:

"29431", "38967"
"28631", "23478"
"30789", "38543"
"31290", "45610"
"35760", "25741"


It's a comma-delimited file that will need to be inserted into another table.

CREATE TABLE high_school
(
  reference_number VARCHAR2 (5 BYTE),
  hs_ceeb_code     VARCHAR2 (5 BYTE)
)


They upload the file using a web page and then that data/file is used to populate the high_school table. I got the DAD to do the upload but I couldn't figure out how to get the data/file back out of the documenttable.
Previous Topic: about trigger
Next Topic: inserting carriage return in an email
Goto Forum:
  


Current Time: Fri Feb 07 00:02:49 CST 2025