Hi!!

First of all, here you have a link to a image resuming my issue:


So, the scenario is as follows:
I'd like to develop a parametric dataflow with DataMigrator, so that the origin source would point to tables in different schemas in the same Oracle instance: pre_webfocus.prueba_simultanear or webfocus.prueba_simultanear.

In order to achieve this, I make the following proof of concept:
.MAS

  
FILENAME=PRUEBA_SIMULTANEAR, SUFFIX=SQLORA  , $
  SEGMENT=PRUEBA_SIMULTANEAR, SEGTYPE=S0, $
    FIELDNAME=IDENTIFICADOR, ALIAS=IDENTIFICADOR, USAGE=A255, ACTUAL=A255,
      MISSING=ON, $
VARIABLE NAME=&&MUTABLE_SCHEMA, DEFAULT=pre_webfocus, $

.ACX

  
SEGNAME=PRUEBA_SIMULTANEAR, TABLENAME=&&MUTABLE_SCHEMA..PRUEBA_SIMULTANEAR, 
   CONNECTION=pre_sisaadn, KEYS=0, $

.FEX

  
-SET &MUTABLE_SCHEMA='pre_webfocus';
TABLE FILE PRUEBA_SIMULTANEAR
PRINT *
END
-RUN

And this works like a charm, being able to point to different tables in runtime.

Yet, when developing a dataflow with DataMigrator, the produced source code contains

  
...
-SET &ECHO = 'ALL'; 
-SET &&MUTABLE_SCHEMA = pre_webfocus; 
...
SQL PREPARE SQLIN FROM 
SELECT 
   T1.IDENTIFICADOR 
FROM 
   sisaad_carga.prueba_simultanear T1 
END 
-RUN 

which will fail due to a : ORA-00903: nombre de tabla no válido (Invalid table name).
It seems that a master with variable won't work when used insed a SQL PREPARE SQLIN.
Is there any workaround to achieve my goal? [Pointing to different tables in runtime]

Thank u all!

This message has been edited. Last edited by: Tamra,

Well, it seems the image is not working. Here u have the url:
https://postimg.org/image/63lgsrcmv/

Hi Glezo,

Did you try with a SQL Flow?

regards, Martin.

By doing so (DBMS SQL Flow) we would lose all the metdata enrichment, something we'd like to avoid.

Yet, I tried after your suggestion, with quite unpleasant results :

I configured the sql flow as follows:
-Set variables node, performing

  
-SET &SCHEMA='webfocus'

-SQL node with following embedded content:

  
select * from &SCHEMA.EVAL.prueba_simultanear

Then, when I'm prompted for a value for variable &SCHEMA, I enter 'webfocus' (table webfocus.prueba_simultanear certainly exists under the used adapter), and DM Client crashes and hangs

We're running 8.0.08 in both client and server side.

Picture of it all:
https://postimg.org/image/htiwq7lx3/

btw, I also tried to replace the embedded sql code to

  
select * from &SCHEMA.prueba_simultanear 

with same result

First of all DataMigrator is designed to be used from the graphical editors and should always generate valid syntax. If you edit in a text editor there's no guarantee.

The variables in a synonym are always GLOBAL variables, for example TABLENAME=&&TNAME
So the name in the SET command must have TWO ampersands (not one as in most of your examples).

In this case its value should be a fully qualified table name. For example:

-SET &&TNAME='sisaad_carga.prueba_simultanear' ;

In the Data Flow you select a synonym so the generated SQL ... SELECT statement should only reference a SYNONYM name, for example:

SQL PREPARE SQLIN FROM 
SELECT 
   T1.IDENTIFICADOR 
FROM 
   PRUEBA_SIMULTANEAR T1 
END 

It would not generate a variable in place of s table name. Also you would only see a two-part name (with a period) is if you had checked in Options, Data Flow Designer the checkbox to "Use application directory names with flow components" and means that the first part of the name is an application directory.

You may want load the tutorial "DataMigrator - Iterator" to see an example of how a parametrized synonym can be iterated over multiple values, such as table names or connections.

Finally 8.0 is based on the 7.7.05 server release which first shipped in January 2013. I would strongly advise upgrading to the current production 8.1/7.7.06 release if not the forthcoming 8.2/7.7.07.

First of all, thanks for your answer, Clif.


I've never, ever, edited a dataflow by means of text. I only open it as text when I need to figure what code is being executed, as now.
In my case it's generating a valid, non-working (due to my misunderstanding, surely) syntax.

Despite my wrong examples, as you can see in the picture url I posted, I certainly used a global variable in the dataflow.

Indeed, we use the setting "Use application directory names with flow components". I don't like it this way, since we lose the power of the application path, but my boss chose so.
I'll try to execute the very same flow without that setting, and, for sure, I'll study the tutorial you mention if I can find it.

In a different, parallel task, we're migrating to a 8.1.05 release


As a quick workaround, we finally did not use parametric flows, but "static" masters pointing to Oracle synonyms, which we dinamically change by means of a stored procedure.
I would've liked to achieve this by DataMigrator, but we were running out of time. Yet, I'll keep on trying.

thank you so, so much!!!

If the focexec is valid it should work!

Using application directory names is fine; I wanted to be sure you knew that's what they were and not a schema or user name.

You can find the tutorial in the DataMigrator User's Guide, Chapter 11, "Iterating Through Multiple Tables." As the name implies instead of just assigning a value to a global variable in a synonym it shows you how to set up a file with a list of values and then iterate through them. You can load the tutorial files from the DMC by right clicking on an application directory, selecting New, Tutorials and then DataMigrator - Iterator from the pull-down menu.

I solved a simmilar problem by running de dataflows from an procesflow. In this procesflow i use CMASAP to run the dataflow with the desired params.

When i run the datafllow in a 'normal' way i indeed get a simmilair message that de database can not be found. Seems some parameters only work when calling from another procedure with these global vars.

When you need more info, please contact me.