BODS 3.1 : How to view the sql code of the temp table used in DF ?

[Former Member]

Hi.

We have this urgent requirement which is taking a whole lot of time in creating the oracle table structure for target table, for the data which we are pulling from SAP R/3 into oracle thro BODS

Dataflow : SAP/R3>>> QUERY TRANSFORMATION >> TEMP TABLE OF ORACLE (TARGET)

I would like to know whether we could view the sql generated for this target temp table which we have used for test purpose.

This would be of great help to me in creating the table structure in oracle for real time.

Till date, if there are 10 DF in a WF - and each data flow is pulling data from each table of SAP R/3 AND loading into Oracle table, which has been manually created in oracle looking at the table structure of SAP R/3.

And this works pefectly fine.

We have already created a table with 52 columns in oracle as per SAP R/3 and have been successful in pulling data and in creating webi reports.

But this table creation is taking too much time, especially when you have tables with more than 100 or 150 columns.

While testing, i used a temp table as target for oracle in the data flow - and this is system generated temp table.

So am wondering whether i could view the sql code - so that i could just use it for real time table creation in oracle.

Can anyone advise - how we go about this

==================================================

As of now i have the reference file from SAP R/3 for each table :

with the table name, col name, data type, length etc

and saved it as excel / text

Which am using to create tables manually in oracle.

But this is becoming too cumbersome.

Many thanks.

indu

Edited by: Indumathy Narayanan on Aug 4, 2011 7:16 PM

[Former Member]

Actually, I think I've misunderstood, do you want to create a table for the first time? If so, use a template table, then if you need to create DDL for it, use one of the techniques above.

M

[Former Member]

Hi Michael.

Thanks for the responses.

May be am not clear :

Am looking at BODS. And wanting to see the sql code for the temp table which am using for target table of oracle, while testing the data flow. Am not seeing any option to view the sql at any stage in BODS in any of the dataflows.

As i have said earlier : SAPR/3> TRANSFORMATION > Oracle Target table.

It is this Oracle target table which am using as temp table directly by drag and drop, on a data flow while testing. And i wish to view the sql for this temp table creation inside BODS.

As regards turning the trace on, i shall check that and see whether the sql for the temp table which am temporarily using for target is visible. If the sql code to create table is visible, then, That would help me to create real time table on oracle for the final target table of the entire data flow. As of now am taking the reference directly from SAP R/3 table and creating the tables in oracle for the final target table. And if the source of R/3 and target in oracle, if it does not match,

the jobs would fail.

I was just wondering, if there could be a simpler way. Because we did not see any means of saving any table structure inside SAP R/3 into a script. Though one could say, that a ABAP program need to be written.

These may not be general requirements

Thanks again.

Regards

indu

Edited by: Indumathy Narayanan on Aug 4, 2011 9:32 PM

Edited by: Indumathy Narayanan on Aug 4, 2011 9:32 PM

Edited by: Indumathy Narayanan on Aug 4, 2011 9:35 PM

Edited by: Indumathy Narayanan on Aug 4, 2011 9:36 PM

[Former Member]

Hi,

While using the temp table your are running the jobs in BODS, witch means you are already creating the database structure and along with the data in the target database.

My question is : Why can't you uses those table in WEBi reports and for the temp table make it as permanent in DS level.

Please revert me if anything else.

Br,

Sudhir

[Former Member]

Hi

Here are 3 ways to generate the DDL for the template tables

1 - use a DB tool, most will generate the DDL for an existing table

2 - generate the ATL for the dataflow, open with a text editior and you'll see a create table statement similar to SQL

3 - at runtime, turn on the trace for the SQL loader, it will show the SQL create table statement

Michael