How to get TXFT from F00165 by SQL--Oracle

no1see

Active Member
Hi all,
I wanna use PL/SQL to get the TEXT from some Media Object, I know that In JDE tools ,there are some BSFN to process it and get it from F00165.
But if I wanna use SQL to convert the codes in TXFT to real text I typed in. How can I make it?
Thanks for ur help, can anyone tell me the function to do it in SQL?
 
Hi,
Can anyone let me know how to retrieve thr BLOB values of any Media object text attachment from F00165.

Any help is much appreciated.

Regards
WorldJDE
 
This depends on what MO structre & object type you are using to store and write. THere are ready System functions available in JDE.
-In event rule window clikc on F(s) and select media object and you can see the structures. select one which you want to use
-select action you want to perform. (this can vary if you are in Interactive app and batch or function)
-use variable assigned to the MO text

However you are able to retrieve only first text attachments if there are multiple then you might have to build your own custom function. A work around with this is define a new MO structure with suffix and attach additional text to this.

Hope this helps
 
Hi Thanks for reply. This I know but I wanted to know if there is any way out to read the BLOB values apart from using it through the MO. Like, if there is a MO GT4016A, I can retrieve its value using GetText in a variable. But if I just want to read the text without using this MO. This is saved in F00165 in BLOB but where it is exactly stored? Is it possible to know?

Thanks in advance.
WorldJDE
 
Hi WorldJDE,

There were more enhancement in Media Object Handling and storage in XE.
Please, check-out the following white paper from Oracle:

Old Knowledge Garden ID: ott-01-0068
Ne Solution ID: 200783528
Title: Media Object Functionality.doc

Since you haven't attached your release level and system information, I didn't know, is it the appropriate document for you.

I do not know, what kind of improvments had been done for MO in later releases, if had been done any at all.

Regards,

Zoltán
 
No1see,

I made the below PL/SQL function to decode the Text in F00165 into a VARCHAR2 variable (limit 4000 characters). Function is typically used as part of a View query (see sample below). Text will still be in Rich text Format however. If you don't want RTF then you'll need to google for ways to parse that out.

----------------------------------
CREATE OR REPLACE FUNCTION "PRODDTA"."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;
------------------------------------------------------------------

CREATE OR REPLACE VIEW "PRODDTA"."F00165_VIEW"
("NAMEOBJECT_GDOBNM","GENERICTEXTKEY_GDTXKY","SEQNO_GDMOSEQN",
"MEDIAOBJTYPE_GDGTMOTYPE","LANGUAGEPREFERENCE_GDLNGP",
"UPDATEDBYUSER_GDUSER","DATEUPDATED_GDUPMJ",
"TIMEOFDAY_GDTDAY","OBJECTTYPENAME_GDGTITNM",
"QUEUENAME_GDQUNAM","FILENAME_GDGTFILENM",
"MEDIAOBJECTVARIABLELENG_GDTXVC") AS
SELECT
GDOBNM,
GDTXKY,
GDMOSEQN,
GDGTMOTYPE,
GDLNGP,
GDUSER,
JDE2Date(GDUPMJ),
GDTDAY,
GDGTITNM,
GDQUNAM,
GDGTFILENM,
JDETextFix(ROWID)
FROM F00165
WHERE GDGTMOTYPE = 0 WITH READ ONLY
------------------------------------------------------------------
 
Thank you Zoltan for the information provided.

Release: XE, E810,E811,E812

Regards
WorldJDE
 
Hi Larry,

Thanks for ur PL/SQL Function Spec, I will try it tomorrow:)
Thank you very much:)
laugh.gif
 
Hi, Larry_Jones,

thanks for your spec, but i have an other problem: if the BLOB vaule is not English, the result from the view is not correct. how can i convert the value to the correct language.

I try the function:UTL_RAW.convert, but i insuccess.

thanks for any help.

Duane
E812,ORACLE,
 
View BLOB with PL/SQL

Hi, Larry_Jones,

thanks for your spec, but i have an other problem: if the BLOB vaule is not English, the result from the view is not correct. how can i convert the value to the correct language.

I try the function:UTL_RAW.convert, but i insuccess.

thanks for any help.

Duane
E812,ORACLE,


Hi,

Although maybe outdated I came across this one.
I so far have found the following statement to be successful (most of the times...)

SELECT GDOBNM, GDTXKY, CAST(GDTXFT AS BLOB) AS REMARKS, UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(GDTXFT))
FROM F00165
WHERE GDOBNM = 'GT0101A'
AND GDGTMOTYPE = '0'

Good luck
Rick
 
Back
Top