Iris_Muttenthaler_7233

Retrieve Filetransfer Reports from the Database

Discussion created by Iris_Muttenthaler_7233 on Oct 13, 2016
A customer noticed, that the "Report" tab from Filetransfer reports is not saved in the database like other reports.
Usually a report is saved to the RT table and you can finde the whole report in the column RT_Content.

But for the filetransfer report tab this is different:
You will find some content with RT_Type REP, but there is one line in the database per line in the report and only the values but no text.

Now here's the REP for my dummy filetransfer:

240resdudoxb.jpg

That contains all the information but is not really readable.

Therefore you have to match the RT_MSGNR with the text of the corresponding message.
Rows with RT_MsgNr 11125 for example contain the name and path of the files which were transferred. Simple. But RT_MsgNr 11133 contains all the information for the finished transfer. The text missing in this case is:

OK    '&02' Bytes, '&03' Records for file '&04'->'&01' transferred. Duration '&05'.

Now you have to replace the parameters with the text of the RT_MsgInsert column. From V11 this is straightforward because you have the table MSGTX with all the U00.. messages, their number and text in all languages.

So here's a script to get the whole report tab in a "quite" simple way for V11 Oracle Databases:

set define off;

SELECT
replace(
replace(
  replace(
    replace(
      replace(
        replace(MSGTX_TEXT,'&01',regexp_substr( RT_MSGINSERT, '([^|]*)[|]{0,1}', 1, 1, 'i',1) )
       , '&02', regexp_substr( RT_MSGINSERT, '([^|]*)[|]{0,1}', 1, 2, 'i',1)) 
     , '&03', regexp_substr( RT_MSGINSERT, '([^|]*)[|]{0,1}', 1, 3, 'i',1))
  , '&04', regexp_substr( RT_MSGINSERT, '([^|]*)[|]{0,1}', 1, 4, 'i',1))
, '&05', regexp_substr( RT_MSGINSERT, '([^|]*)[|]{0,1}', 1, 5, 'i',1))
,'$01',regexp_substr( RT_MSGINSERT, '([^|]*)[|]{0,1}', 1, 1, 'i',1) )
report
FROM rt,
msgtx
WHERE MSGTX_MSG_IDNR = RT_MSGNR
AND RT_AH_Idnr = 1568136
AND RT_TYPE = 'REP'
AND MSGTX_MSGL_SHORT = 'E'
ORDER BY RT_LNR
;

Don't forget to execute the first line, otherwise the & will be interpreted as a parameter.

In older versions the table msgtx is missing unfortunately. So you could for example create your own table in your own schema to join with using the Message texts that you can find in the documentation.

And thanks to Andreas_Sprosec_7439 for the help!

Outcomes