Antoine_Sauteron_1266

Is there any best practice approach to extract the Structured Documentation out of the AE database?

Discussion created by Antoine_Sauteron_1266 on Sep 26, 2017
Latest reply on Sep 26, 2017 by Carsten_Schmitz
Oracle Database.
Is there any example how we can extract structured documentation out of the uc4 database. Especially:
* When using oracle xml functions
* the structured docu is larger then 2000 characters
ODOC is the table that contains this information. These questions are Oracle-related topics, however here are a few hints :

 * Oracle xml function > not sure if the ODOC field may be queried with such a function. Tried the following SQL and it failed (produced ORA-31011, ORA-19202 and ORA-06512 errors):
Select XMLTYPE(ODOC_CONTENT, 1)
from ODOC
WHERE ODOC_TYPE != '0';
Then tried on SQL server, which allows easier conversion of BLOB fields to 'varchar' type. It turned out that only a few converted ODOC_Content fields contained xml:

spkvxkjhf0td.png


* ODOC_content is a BLOB field which is too long to be converted using dmbs_lob.substr(), because of a built-in Oracle limitation (max valkue for RAW= 4000 bytes).
A workaround is to develop a custom PL/SQL procedure for that purpose, as explained here: http://www.dba-oracle.com/t_convert_blob_varchar_datatype.htm

I'm not the Oracle expert though, so if anybody reading this post has ideas, feel free to write them down below :)

Best regards,
Antoine

Outcomes