Generate table to different Oracle schemas

[former_member212650]

Hi

I have some data flows that write to an oracle table. The data flow for example writes to table "tablename1' in oracle datasource name 'OracleDataSourceName1' and schema 'OracleSchemaDev'

Can I redirect the an existing dataflow to another schema say OracleSchemaProd without having to define a new dataflow pointing to table

For example the designer under data source 'OracleDataSourceName1' I will see

TABLENAME1('OracleDataSourceName1.OracleSchemaDev')

I want to redirect the flow to the prod schema

TABLENAME1('OracleDataSourceName1.OracleSchemaProd')

So what is the best practice when you want to run a dataflow to one schema and then dump the table to another schema ..what is the way to do this now

Thanks

Mike

[former_member187605 · Accepted solution]

Use datastore configurations.

Read section 5.6 Creating and managing multiple datastore configurations in the SAP Data Services Designer Guide.

[former_member212650 · Accepted solution]

thanks we - already have DEV , TEST and PROD datastore configurations for oracle , the problem is

the data flows writes to a particular table

eg. the data flow Target table writes to

TABLENAME1('OracleDataSourceName1.OracleSchemaDev')

So yes we can change the configuration to 'TEST' and yes if we run a pure  'SQL' it will read or write to the 'TEST' schema , but in the data flow seems to have a hard coding eg.

TABLENAME1('OracleDataSourceName1.OracleSchemaDev')

How do we redirect this or make it more generic ? in 4.2

So even if we change configuration to 'TEST' the target table will still go to OracleSchemaDev because the Target table name is hard coded.

In DS 3.1 you could go TABLENAME1('OracleDataSourceName1.USER') this would pick up the correct configuration 'DEV' or 'TEST' this is not supported in 4.2.

One work around is to copy the dataflow and change the target table to TABLENAME1('OracleDataSourceName1.OracleSchemaTEst') but we would have to create copies of every data flow and table which seems silly as you have configurations..?

Any Ideas - How to get the configurations to write to a different database schema in oracle?

Or am I missing something..or is this not possible?

[Former Member · Accepted solution]

Hi Mic,

check the data base server name in data store configuration

Data store name would be hard coded not the schema name, whenever you change the config the pointing schema will change, if you want to point 2 different schema in same environment, we need to create 2 data stores and created duplicates of the data flows as you mentioned.

If you are looking for dynamic table name depending on the config, it may be possible but need to explore more

[former_member212650 · Accepted solution]

Hi

The areas in green are the same, I want to write & redirect a table to different

schema , depending on which config, DEV and TST are on the same oracle server, different oracle schemas . I 'm not to familiar with oracle but to me is each schema is a separate database - what should I be doing , I don't really want to have separate dataflows for every schema when the table name is the same?

..

Should I have a separate database server? or separate oracle database? How do you specify a separate oracle database? As far as I know there are only different schemas. Are schema = database? in oracle?

[Former Member · Accepted solution]

Why not using the Alias concept in BODS.

read section 5.6.4.1 Creating an alias in the SAP Data Services Designer Guide.

This might also useful.

* http://scn.sap.com/docs/DOC-61305

* https://decisionfirst.files.wordpress.com/2014/05/defining-aliases-within-your-datastore-in-data-ser...

[former_member187605 · Accepted solution]

I can only repeat what I said before: use datastore configurations.

Don't your TST_NEW and DEV_NEW configurations do the trick?

With the former, data will be written to the _TST schema (in Oracle: schema = user), with the latter to the _DEV schema, as specified.

[former_member212650 · Accepted solution]

Thanks Again ,

Hi we have DEV_NEW and TST_NEW,

In the image below you will see that the target table goes to ORACLEschemaTST so if your config is set to DEV the output will still go to TST due to the hard coding ! The question I need answering - How do I set up the data flow so that it will go to the schema as setup in the config? , or do I need to set up the config some way.

in the old 3.1 you could work around this but in 4.2 how? In the mean time i'll check out aliases..

Note I only have one machine, if I had 2 machines with the same schema name the configs would work but I have one machine with different schemas.

[former_member187605 · Accepted solution]

I am sorry, I should have read your posts more carefully. Youi're right, it's the schema name that's hardcoded, too.

The solution is still with datastore configurations. But you have to add an alias, indeed. This is my setup in MS SQL Server:

The table is created in the (default) schema DBO. Add a configuration with an ailias:

With SCN as the default configuration, the table will now be created in the SCN schema.

You wil have to define an alias OracleSchemaTest for OracleSchemaDev in the TST_NEW configuration.

[former_member212650 · Accepted solution]

Hi no worries we are all guilty of not reading posts!

Thanks the  'alias' works, if fact we had them set up to 'USER' , There was a SAP note that said USER is not supported ? So a helpful consultant came in and set  our alias  to [$$CertificationLogPath] ?

Anyway hard coding the alias to the 'schemaowner' and changing the data flow to use the alias in each config 'dev' , 'test' and 'prd'. works. I have tested this.

Thanks for you help.