4 Replies Latest reply: Jan 14, 2013 8:15 AM by Rod West RSS

    What tables hold Discoverer SQL?

    Cornell
      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:
      SELECT *
      FROM DISCOVERER.EUL4_DOCUMENTS
      WHERE UPPER(DOC_NAME) LIKE '%PROMO%';

      How can I get from EUL4.DOCUMENTS to the related SQL code?

      Thanks for reading
      Cornell
        • 1. Re: What tables hold Discoverer SQL?
          Rod West
          Hi,

          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.

          Rod West
          • 2. Re: What tables hold Discoverer SQL?
            Cornell
            Thank you for the reply, Rod. Unfortunately, as I'd originally indicated, we don't have access to the Discoverer front end.

            Thanks again.
            • 3. Re: What tables hold Discoverer SQL?
              Cornell
              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.
              • 4. Re: What tables hold Discoverer SQL?
                Rod West
                Hi,

                The SQL in the EUL5_objs and EUL5_segments table holds the SQL for the custom and simple folders.

                Rod West