Inserting into BLOB Column

[DaveH]

I have tried every trick offered in every known Oracle forum, to insert text into a BLOB field using dynamic SQL in PLSQL Developer. Having had no success, I am hoping you can help with a code example of how to do that. Surely there must be a way.

The important part is the use of Dynamic SQL. I need to have the tablename set a runtime, and I don't think there's any other way.

 

[Marco Kalter]

Can you provide an example of what you are trying to accomplish?

 

[DaveH]

CREATE OR REPLACE PROCEDURE WRITE_BLOB
(TABLENAME IN VARCHAR2,
LOG IN VARCHAR2,
TEXTDATA IN VARCHAR2)
AS
DECLARE
SQLSTMT VARCHAR2(200);
BEGIN
SQLSTMT := 'INSERT INTO ' || TABLENAME || '(LOGNUM, COMENT) VALUES (' || LOG || ','|| TEXTDATA ||')';
EXECUTE IMMEDIATE SQLSTMT;
COMMIT;
END;
This results in Error 'Invalid Hex Data'
beczause field COMENT is a BLOB. I Need to get a set of syntax which accomplishes this task to work. Can you help?

DavH

 

[vlevi]

I think you are wrong.
You can try two other ways. One of them is using parameteres, instead of concatenated text. In this way, the blob might be inserted corretly.

execute immediate 'insert into '||tablename||'(lognum,comment) values( :log, :textdata)' using log,textdata;

The second way is the dbms_log package.

 

[DaveH]

Tried the method you suggest. but the result is the same. Have not tried using DBMS method. If you could help with the syntax, I'd certainly try it. I was unable to easily decipher how to do the deed from online help.

Thanks.
Dave