JDE how to convert F983051.VRPODATA (long / blob) to a string (varchar2)?

Question

I'm not sure if I can do something like:

SELECT VRPID, VRVERS, FUNCTION(VRPODATA) FROM SCHEMA.F983051;

Where FUNCTION is something that converts the BLOB file into a varchar2.

P.d. I cannot use any kind of DDL.

Please advise,

Are you sure `VRPODATA` is a `BLOB` and not a `CLOB`? If it's a blob, we need to know it's encoding. — wolφi, May 31 '18 at 18:53
Hi Wolphi, it literally reads "(BLOB)". Not sure if its a generic term. — Felipe Vidal, May 31 '18 at 21:01

wolφi · Answer 1 · 2018-06-01T12:08:52.763

Please note that I do not have experience with CONVERTTOCLOB as we always use ClOBs to store text. Refering to the Rene's answer, you could do it something like that:

CREATE TABLE F983051 (vrpid NUMBER, vrvers NUMBER, vrpodata BLOB) 
  LOB (vrpodata) STORE AS SECUREFILE;

INSERT INTO F983051(vrpid, vrvers, vrpodata) 
VALUES (1, 2, rawtohex('Hello world'));

CREATE OR REPLACE FUNCTION myblobtovarchar(p_blob BLOB) 
  RETURN VARCHAR2 DETERMINISTIC
IS
  l_len          number;
  l_clob         clob;
  l_dest_offsset integer := 1;
  l_src_offsset  integer := 1;
  l_lang_context integer := dbms_lob.default_lang_ctx;
  l_warning      integer;
  l_varchar      varchar2(32767);
BEGIN
  IF p_blob IS NULL THEN RETURN NULL; END IF;

  DBMS_LOB.CREATETEMPORARY(lob_loc => l_clob, cache => false);

  l_len := DBMS_LOB.GETLENGTH(p_blob);

  IF l_len > 4000 THEN l_len := 4000; END IF;

  DBMS_LOB.CONVERTTOCLOB(dest_lob     => l_clob,
                         src_blob     => p_blob,
                         amount       => l_len,
                         dest_offset  => l_dest_offsset,
                         src_offset   => l_src_offsset,
                         blob_csid    => dbms_lob.default_csid,
                         lang_context => l_lang_context,
                         warning      => l_warning);

  l_varchar := DBMS_LOB.SUBSTR(l_clob, 32767, 1);

  RETURN l_varchar;
END myblobtovarchar;
/

SELECT vrpid, vrvers, myblobtovarchar(vrpodata) FROM F983051;
1   2   Hello world

If you cannot create functions, you might try (I don't have experience with it either):

SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(vrpodata, 4000)) FROM F983051;

Hi Wolphi, many thanks for your response. However, I'm afraid I don't have DDL privileges: I cannot create functions =S... — Felipe Vidal, Jun 01 '18 at 04:21

score 0 · Accepted Answer · answered Oct 25 '19 at 01:33

I actually found what I wanted: DBMS_LOB.SUBSTR({BLOB field}).

That field can help you for BLOB & HUGEBLOB data types, but it might not be able to recognize all of the values. It will substitute them with squares.

Example:

SELECT DBMS_LOB.SUBSTR({Huge Blob Field}) FROM {Table};

If you can work with that, it's the way to go.

Cheers.

score 0 · Answer 3 · answered Sep 21 '20 at 08:58

0

We host our JD Edwards instance on sql-server, to perform the same operation there, one just needs:

convert(nvarchar(max),convert(varbinary(max),vrpodata))

the result is still a pipe delimited string.

answered Sep 21 '20 at 08:58

Adge Cutler

34
1
5

JDE how to convert F983051.VRPODATA (long / blob) to a string (varchar2)?

3 Answers3