Any tools or methods to export SQL queries of oracle biee reports into plain text files?

Question

Toad has built-in method (Database|Export|Export DDL) to export all objects (i.e. tables, triggers, functions, stored procedures, packages etc) to plain text files

One of my project has all reports written in Oracle BIEE publisher. I would like to export all data models of these reports into plain text so that I could import them into an Eclipse project for ease of searching of some strings or sql patterns.

Is there any method or tool of achieving this?

Thank you very much.

please let me know if my answer helps, @Frog – cdabel Apr 08 '17 at 20:49 — cdabel, Apr 08 '17 at 20:49

cdabel · Answer 1 · 2017-04-11T05:51:26.577

Here's what you need to do:

While logged into xmlpserver (BI Publisher), enter the Catalog, then do the following:

Download the parent folder containing all the data models you will need.
Once downloaded, rename the downloaded .xdmz file to a .zip. You can now view the "guts" of the files.
Write a script in Linux or Windows to recursively rename as .ZIP all the nested XDMZ files underneath the parent folder.
Extract all of the _datamodel.xdm files underneath the data models' XDMZ files (now renamed as ZIP files).

Below is a sample _datamodel.xdm file. (Scroll beneath it for steps 5, etc.)

Note that the Data Model's Data Set SQL is located between the <sql></sql>tags:

   <sql dataSourceRef="ApplicationDB_APP">
      <![CDATA[select * from table]]>
   </sql>

Sample `_datamodel.xdm` file:

<?xml version = '1.0' encoding = 'utf-8'?>
<dataModel xmlns="http://xmlns.oracle.com/oxp/xmlp" version="2.0" xmlns:xdm="http://xmlns.oracle.com/oxp/xmlp" xmlns:xsd="http://wwww.w3.org/2001/XMLSchema" defaultDataSourceRef="demo">
   <dataProperties>
      <property name="include_parameters" value="true"/>
      <property name="include_null_Element" value="false"/>
      <property name="include_rowsettag" value="false"/>
      <property name="xml_tag_case" value="upper"/>
   </dataProperties>
   <dataSets>
      <dataSet name="asd" type="complex">
         <sql dataSourceRef="ApplicationDB_APP">
            <![CDATA[select * from table]]>
         </sql>
      </dataSet>
   </dataSets>
   <output rootName="DATA_DS" uniqueRowName="false">
      <nodeList name="data-structure">
         <dataStructure tagName="DATA_DS">
            <group name="G_1" label="G_1" source="asd">
               <element name="ENTITY_ID" value="ENTITY_ID" label="ENTITY_ID" dataType="xsd:double" breakOrder="" fieldOrder="1"/>
               <element name="ENTITY_NUMBER" value="ENTITY_NUMBER" label="ENTITY_NUMBER" dataType="xsd:string" breakOrder="" fieldOrder="2"/>
**[...more XML defining the table generated by the SQL...]**
            </group>
         </dataStructure>
      </nodeList>
   </output>
   <eventTriggers/>
   <lexicals/>
   <valueSets/>
   <parameters/>
   <bursting/>
   <display>
      <layouts>
         <layout name="asd" left="280px" top="0px"/>
         <layout name="DATA_DS" left="0px" top="32px"/>
      </layouts>
      <groupLinks/>
   </display>
</dataModel>

Now you need to parse these files to extract the SQL. (Use XPATH).

Or rather than writing a script to do this, you could do each file individually using copy/paste into your Eclipse.

I hope this helps.

Many thanks for your detailed explanation. It really helps. Salute! — Frog, Apr 11 '17 at 01:13
@Frog, Could you mark my answer as correct if you feel it is correct? — cdabel, Apr 11 '17 at 05:48
Oh, I missed this message. Where I can mark the answer as 'correct'? — Frog, Sep 13 '17 at 08:44
@Frog, there is a grey check mark underneath the number of votes at the left of my answer. Select it, and the check mark should turn green. — cdabel, Oct 03 '17 at 16:30

score 0 · Answer 2 · answered Mar 15 '17 at 18:31

0

Do you want the underlying physical SQL being generated? You could run each of the reports, and then pull the sql from Nqquery.log. You'll have to watch out that you don't overwrite the log, if you have a lot to run.

OBIEE 10G/11G - How to see the Physical SQL generated by a request in Presentation Service

answered Mar 15 '17 at 18:31

Hana

95
3
6

Thanks a lot hewills for your help. Maybe my questions are not specific enough, I re-phrase it. Actually I'd like to have a tool or method to export all SQL queries of all reports in Oracle BIEE Publisher for one project. Because it is impossible to search a pattern in SQL queries of these reports. – Frog Mar 17 '17 at 02:50

user1630164 · Answer 3 · 2023-02-01T07:19:49.013

0

How we can do this OAS 6.4, as new version is creating any Physical files, do any one any idea how we can do this new version of OBIEE (OAS 6.4),

Do we have any command download this files from Command prompt.

edited Feb 01 '23 at 07:19

answered Feb 01 '23 at 07:10

user1630164

11
4

This does not really answer the question. If you have a different question, you can ask it by clicking [Ask Question](https://stackoverflow.com/questions/ask). To get notified when this question gets new answers, you can [follow this question](https://meta.stackexchange.com/q/345661). Once you have enough [reputation](https://stackoverflow.com/help/whats-reputation), you can also [add a bounty](https://stackoverflow.com/help/privileges/set-bounties) to draw more attention to this question. - [From Review](/review/late-answers/33752580) – Ana GH Feb 02 '23 at 17:03

Any tools or methods to export SQL queries of oracle biee reports into plain text files?

3 Answers3

Sample _datamodel.xdm file:

Sample `_datamodel.xdm` file: