1. Jeff Wayt
  2. PowerBuilder
  3. Monday, 1 July 2019 22:54 PM UTC

How do I update a blob in an Oracle table column dynamically?

I know about UPDATEBLOB in PowerBuilder imbedded statements, like

   UPDATEBLOB “tablename” set “blob_column_name” = :lblob;

This script uses SQLSA because the column name is known only at runtime. There is no UpdateBLOB statement in Oracle’s PL/SQL. So the statement becomes:

        ls_sql = "UPDATE tablename set " + ls_column_name + " = ?, update_timestamp = ? where header_id = ?";
        PREPARE SQLSA FROM :ls_sql;
        EXECUTE SQLSA USING :lb_data,:ldt_update,:al_header_id;

Trying to pass a row data to a string, convert to a blob, and pass as an argument-- it is seeing those tab characters and throwing the invalid hex value error. I suspect this worked in PB 12.6. I'd rather not use a CASE statement for each blob column.

Any ideas?

Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 2 July 2019 08:56 AM UTC
  2. PowerBuilder
  3. # 1

What I would try is the following:

Before calling the dynamic sql, insert your blob data value in a temporary fixed table (i.e. TEMP_BLOB) with a unique id, using first insert and then UpdateBlob.

Then modify your dynamic sql to NOT use :lb_data, but instead get the blob value from the TEMP_BLOB table.

In that case, maybe it would be even easier if you construct your sql statement and use EXECUTE IMMEDIATE.

HTH

Comment
  1. Chris Pollach @Appeon
  2. Tuesday, 2 July 2019 17:46 PM UTC
Hi Jeff;

I would concur with Miguel and suggest using the "EXECUTE IMMEDIATE" approach.

Regards ... Chris
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.