Results 1 to 10 of 10

Thread: Blob fields in database.

  1. #1

    Blob fields in database.

    Hello List,

    Is there a way I can read the blob fields in database, the database being oracle 10g ?

    Regards,
    Freddy

    E811 8.94P1 win/SQL 2000 JAS 5.2.0.3
    EO8.9 8.94_F1 win/oracle 9.2.0.3
    XE SP22Y1 V5R1M0 JAS SP22J_Y1

  2. #2
    Member
    Join Date
    May 2004
    Location
    Ontario, Canada
    Posts
    170

    Re: Blob fields in database.

    Someone posted this earlier, but here it is. Create the function before you create the view.

    Part I: the view

    CREATE OR REPLACE VIEW PRODDTA.F00165_VIEW
    (NAMEOBJECT_GDOBNM, GENERICTEXTKEY_GDTXKY, SEQNO_GDMOSEQN, MEDIAOBJTYPE_GDGTMOTYPE, LANGUAGEPREFERENCE_GDLNGP,
    UPDATEDBYUSER_GDUSER, TIMEOFDAY_GDTDAY, OBJECTTYPENAME_GDGTITNM, QUEUENAME_GDQUNAM, FILENAME_GDGTFILENM,
    MEDIAOBJECTTRIMMED)
    AS
    SELECT
    GDOBNM,
    GDTXKY,
    GDMOSEQN,
    GDGTMOTYPE,
    GDLNGP,
    GDUSER,
    GDTDAY,
    GDGTITNM,
    GDQUNAM,
    GDGTFILENM,
    substr(substr(JDETextFix(ROWID),156,length(JDEText Fix(ROWID))),0,instr(substr(JDETextFix(ROWID),156, length(JDETextFix(ROWID))),'\')-1)
    FROM F00165
    WHERE GDGTMOTYPE = 0;


    Part two: The function jdetextfix

    CREATE OR REPLACE FUNCTION jdetextfix (p_rowid in ROWID) RETURN VARCHAR2
    IS
    v_textout VARCHAR2(4000);
    v_lob BLOB;
    v_buffer RAW(8192);
    v_offset INTEGER := 1;
    v_textlen BINARY_INTEGER := 8192;
    v_rawlen NUMBER;
    v_from_set RAW(2);
    v_to_set RAW(1);
    v_output RAW(8192);
    BEGIN
    SELECT gdtxft INTO v_lob
    FROM F00165 where ROWID = p_rowid;
    DBMS_LOB.READ (v_lob, v_textlen, v_offset,v_buffer);
    v_textout := ' ';
    v_from_set := HEXTORAW('7D00');
    v_to_set := HEXTORAW('7D');
    v_output := UTL_RAW.TRANSLATE (v_buffer, v_from_set, v_to_set);
    v_rawlen := UTL_RAW.LENGTH (v_output);
    if (v_rawlen > 4000) then
    v_rawlen := 4000;
    end if;
    if (v_rawlen > 0) then
    v_textout := UTL_RAW.CAST_TO_VARCHAR2 (UTL_RAW.SUBSTR(v_output,1,v_rawlen));
    end if;
    RETURN (v_textout);
    EXCEPTION WHEN NO_DATA_FOUND THEN
    RETURN (v_textout);
    END;
    /

    This won't be fast, since its a blob, but it does work.

    Morglum
    JDE 9.1, 9.1.5.3 Intel, SQL, cluster, etc

  3. #3
    Senior Member altquark's Avatar
    Join Date
    Oct 2000
    Location
    Kansas City, MO
    Posts
    2,641

    Re: Blob fields in database.

    Yes and no.

    There are some BLOB readers out there (I think Toad can view Blobs if I recall) - but without the actual knowledge of how the blob field is being handled, all you're going to see is gobblygook.

    What are you trying to achieve ? Can you perform the lookup using the UTB ?
    Jon Steel
    EnterpriseOne/SOA Technical Architect
    erpSOURCING LLC
    http://www.erpsourcing.com
    cto@spla.sh
    24/7 Assistance - (904) 382 5701

  4. #4
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    3,247

    Re: Blob fields in database.

    The referenced view and function was my contribution - and it was specific to the text blob in F00165. If the OP is looking for something else - the referenced function won't work.

    Regards,
    Larry Jones
    E1 9.2 - TR 9.2.2.6 on Win 2016 R2. SQL Server 2016
    Wintel, BI Publisher

  5. #5
    Senior Member
    Join Date
    Apr 2001
    Location
    QLD, Australia
    Posts
    154

    Re: Blob fields in database.

    At one client we used the PL\SQL Developer - a truly awesome kit of kit.

    In the query results table, against a blob was a ... button, press it and it would display the hex and the ascii, gave us enough interactivley to work out what was going on (excellent for diagnosing MRP generation errors in SBS mode)
    E900 XU1 - TR8.98.2.2 - OAS 10.1.3.1 - SQL Server 2008

  6. #6

    Re: Blob fields in database.

    Has anyone translated these objects to SQLServer SQL syntax?

  7. #7
    New Member Carl's Avatar
    Join Date
    Feb 2005
    Location
    Pennsylvania, USA
    Posts
    9

    Re: Blob fields in database.

    I use this:

    select *,gdobnm,gdgtitnm,gdtxky,convert(nvarchar(4000),co nvert(binary(8000),gdtxft))as RTFtext from jde_development.testdta.f00165 where gdobnm='ABGT'

    E1=8.9 SP7, Tools=8.97.03, db=SQL Server 2000 SP4, OpSys=Win2003
    Carl L. Nurnberg
    JDE E1 8.9 SP7, Tools 8.97.03,
    SQL Server 2000 SP4, Win2003

  8. #8
    Member Adam (DF)'s Avatar
    Join Date
    May 2003
    Location
    Wisconsin, USA
    Posts
    56
    Has anyone written this query for AS/400/iSeries yet? We have tried a few things, but DB/2 for iSeries chokes on the conversion to double-byte characters.
    (E1=E920, Tools=9.2.3.1, db=iSeries v7r3)

  9. #9
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    3,247
    I have a improved version of JDETEXTFIX for SQL Server but basically what it does is this:

    cast(cast(F00165.GDTXFT as varbinary(max)) as nvarchar(max))
    Larry Jones
    E1 9.2 - TR 9.2.2.6 on Win 2016 R2. SQL Server 2016
    Wintel, BI Publisher

  10. #10
    New Member
    Join Date
    Sep 2019
    Location
    Spain
    Posts
    3
    In SQL Developer.

    Crtl + Click to table.

    Select tab "Data" and edit de blob field with the pencil.
    You have one option to download as a file.

    You can then edit with notepad and upload again.

    Thanks

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither endorsed nor approved by JD Edwards® & Company and, or Oracle.