Forum Stats

  • 3,813,964 Users
  • 2,258,798 Discussions
  • 7,892,455 Comments

Discussions

Categories

Oracle 10g Release 2 Discoverer - eul_trigger$post_save_document

user1832363
user1832363 Member Posts: 3
edited Jul 17, 2020 3:09AM in Discoverer

Hi,

I'm attempting to extract Report SQL from Discoverer via eul_trigger$post_save_document, as seen here:

https://docs.oracle.com/cd/B25016_08/doc/dl/bi/B13916_04/appendix_b.htm#sthref2416

However, there is a VARCHAR size limit to the SQLSegment parameter, and the query sizes I'm dealing with are much larger. I've modified this to a LONG and CLOB which has produced some results as opposed to none, but only the back end of the final worksheet of the workbook's SQL appears in the my_sql table.

Can anyone help please?

Kind Regards,

Mark

user1832363Alexander Pavlik
· Share on TwitterShare on Facebook

Best Answer

  • Alexander Pavlik
    Alexander Pavlik Member Posts: 44 Blue Ribbon
    edited Jul 6, 2020 2:21AM Answer ✓

    Hi, Mark

    So is it not acceptable for you to have a query divided into several segments as done by default?
    In this case you can construct the query by concatenating when querying from the my_sql table.

    Regards,

    Alex

    user1832363user1832363
    · Share on TwitterShare on Facebook

Answers

  • Alexander Pavlik
    Alexander Pavlik Member Posts: 44 Blue Ribbon
    edited Jul 6, 2020 2:21AM Answer ✓

    Hi, Mark

    So is it not acceptable for you to have a query divided into several segments as done by default?
    In this case you can construct the query by concatenating when querying from the my_sql table.

    Regards,

    Alex

    user1832363user1832363
    · Share on TwitterShare on Facebook
  • user1832363
    user1832363 Member Posts: 3
    edited Jul 16, 2020 5:53AM

    Hi Alex,

    Thanks for your reply. Unfortunately I don't see the query split across several segments. For instance, there is only a single row in the my_sql table which contains WORKBOOKOWNER, WORKBOOK, WORKSHEET, SEG_SEQUENCE and SQL_SEGMENT columns. The SEG_SEQUENCE column contains the number 2, which may be significant. I'll do some more reading around this, as it sounds from your reply, like there should be a lot more records in the table and possibly the SEG_SEQUENCE column describes the order the SQL_SEGMENTS should be assembled in.

    Kind Regards,

    Mark

    · Share on TwitterShare on Facebook
  • user1832363
    user1832363 Member Posts: 3
    edited Jul 16, 2020 7:02AM

    Hi Alex,

    Thanks for your help on this. I can now see where I've gone wrong. There was a delete from my_sql at the start of the post_save_document function. When I removed that, all the SQL appeared in segments over a number of records. The SQL does differ from whats in the SQL Inspector in Discoverer Desktop but I think that there is a more detailed query in the my_sql table which does extra things like sorting, which isn't included in the SQL Inspector output.

    Thanks again,

    Mark

    Alexander Pavlik
    · Share on TwitterShare on Facebook
  • Alexander Pavlik
    Alexander Pavlik Member Posts: 44 Blue Ribbon
    edited Jul 17, 2020 3:09AM

    Hi Mark

    I'm happy to help you!

    Regards,

    Alex

    · Share on TwitterShare on Facebook