Analytics Software

no one?

Hello
There is no Oracle built mechanism for easily seeing all of the conditions attached to all of the worksheets. Using the workbook dump utility you can see the data one workbook at a time but this is tedious and no less cumbersome than opening up a report and copying what is in the title.

I personally believe there is a way to do it but I have never been able to determine all of the right steps. Let me explain.

The data in the EUL5_DOCUMENTS is not actually encrypted, it is stored in a LONG RAW data type. This is a mix of binary and text and so it is very difficult to understand what is going one.

If you want to search the text in the workbooks, for example, if you want to check which workbooks use parameters, then you can do this by converting the LONG RAW to a LOB in a separate table

CREATE TABLE EUL5_DOCUMENTS_COPY
AS SELECT DOC_ID, DOC_NAME, DOC_DEVELOPER_KEY, TO_LOB(DOC_DOCUMENT) DOC_DOCUMENT FROM schema.EUL5_DOCUMENTS where schema is the owner of the EUL

With this done you can search for all workbooks that contain parameters by using this SQL:

SELECT DOC_NAME FROM EUL5_DOCUMENTS_COPY
WHERE DBMS_LOB.INSTR(DOC_DOCUMENT, UTL_RAW.CAST_TO_RAW('Parameter')) > 0

Now I'm certain in my mind that more intricate SQL could expand on the above and do some form of offset from where the word Parameter is found to read the name of the parameter.

I also think you can examine the QPP_STATS table, if gathering of statistics is enabled, to look for workbooks with parameters. I have not worked out the code for doing this as so far I have only been able to work out which EUL folders and items have been used in worksheets.

Hope this helps and maybe gives someone else the clue on what to do and maybe someone can extend on what I have said to give you the answer you are looking for

Best wishes
Michael

Thanks Michael! I guess that's as good as it gets. I'll try that.
That would also interest me heavily. How did you do it?

Hello
This is rather tricky and a lot of SQL code, dar too much to put in a posting. Drop me an email (look in my profile) and I will send you the code.

I'm also going to put this code in my new Oracle Discoverer 11g Handbook which is bring written right now and will be published later this year in time for Open World.

Best wishes
Michael