I have to run queries that are the basis for some Discoverer reports. I don't have access to Discoverer itself, so I'm trying to find them in the database. (we have EUL5* as well, and three owners, for a total of 5 sets of tables).
I have found references to EUL4_OBJS joined to EUL4_SEGMENTS:
SELECT O.OBJ_NAME, S.SEG_CHUNK1 ||S.SEG_CHUNK2 ||S.SEG_CHUNK3 ||S.SEG_CHUNK4
FROM DISCOVERER.EUL4_OBJS O
LEFT OUTER JOIN DISCOVERER.EUL4_SEGMENTS S ON O.OBJ_ID = S.SEG_CUO_ID
WHERE UPPER(O.OBJ_NAME) LIKE '%PROMO%'
OR UPPER(O.OBJ_DEVELOPER_KEY) LIKE '%PROMO%'
OR UPPER(O.OBJ_DESCRIPTION) LIKE '%PROMO%'
ORDER BY O.OBJ_NAME, S.SEG_SEQUENCE
But some of the names of Discoverer reports aren't in EUL4_OBJS (or EUL5_OBJS, with any of our owners)
I can find them in EUL4.DOCUMENTS:
WHERE UPPER(DOC_NAME) LIKE '%PROMO%';
How can I get from EUL4.DOCUMENTS to the related SQL code?
Thanks for reading
There is no link between EULn_DOCUMENTS and the folders used by the worksheet. The SQL code in the worksheet itself is in a binary format and therefore cannot be read in a query. If you want to get the SQL for a workbook the easiest way is to use the Discoverer Desktop SQL export which will give you the SQL query for the worksheet you export.
If the SQL is stored in binary, then what is the SQL in EUL5_OBJS and EUL5_SEGMENTS?
Another developer had selected a report, in Discoverer and opened the SQL, copy/pasted it to an email for me. I found the text of the SQL in EUL5_OBJS and EUL5_SEGMENTS. Unfortunately, it turned out it was the query from the wrong report.
Anyway, there's at least two stores of SQL: EUL5_DOCUMENTS, in an illegible binary form, and EUL5_OBJS/EUL5_SEGMENTS, in clear text.