Forum Stats

  • 3,838,635 Users
  • 2,262,385 Discussions
  • 7,900,715 Comments

Discussions

Running a PLSQL procedure only once at the beginning.

710706
710706 Member Posts: 24
edited Jul 8, 2009 4:19PM in Warehouse Builder
Hi,

I created a PL/SQL procedure which I like to run that populates a row in a table called warhousing_tag table. This table has a warehouse_id and a warehouse_date column. The warehouse_id gets updated with a sequence generator and the warehouse_date with sysdate.

In the mapping editor I created a pre-mapping process and specified the procedure but it does not seem to run. If it runs, then the warehousing_tag table gets updated with a new sequence and sysdate which I need to add to every row of 5 other tables, before those 5 tables are deployed to target. Do you know what I need to do in order for the procedure to kickoff at the beginning of every warehousing job only once? Also, can the same warehouse_tag table be joined to all the other 5 tables to augment their rows (Cartesian product)?

Thanks!
Tagged:

Answers

  • MccM
    MccM Member Posts: 442 Bronze Badge
    What do you mean by 'warehousing job once'? Do you need it to run only once when mapping starts or? If you do than this is it - premapping procedure will run only once.
  • ScoobySi
    ScoobySi Member Posts: 423
    What is the purpose of this table, to record when the load of the warehouse started?

    How many rows do you expect to be in this table, only 1 or a new row each time the load starts?

    If only one then you don't need to use a sequence, use a constant value and then update the row, you can then use a cartesian product to join. However, if you have many rows in the table you won't be able to use a cartesian product to join as I doubt you'll get the result you're after.

    The process will run in each mapping, if you want it to run only once before all of the mappings either place it in one mapping only or use a process flow and schedule it before the mappings run.

    Si
  • 710706
    710706 Member Posts: 24
    Hi,

    My design is to deploy 5 tables from a schema to a target schema but augment each row of each table with a warehouse_id and a sysdate so that later one can tell which rows were werehoused together.

    I create a warehouse_tag table would always have only one row. A PLSQL procedure would update the first column by a sesquence generator and the second column with sysdate. Then in OWB I created a pre-mapping operator which calls the PLSQL procedure.

    However, I am not sure why the PLSQL procedure does not get invoked or how to do it.

    I need to know the following:
    1) How to get the PLSQL procedure run at the beginning of each deployment once.
    2) How to augment each row of the 5 tables with the two additional columns of warehouse_tag (updated by the PLSQL procedure).

    Thanks!
  • 710706
    710706 Member Posts: 24
    Hi,

    Thanks! How does one define a cartasian product. The warehouse_tag will always have only one row. I know how to define a cartasian product with SQL but not with the OWB.

    Also, as I understand it, I only need to define the updating of the warehouse_tag in the first mapping, right?

    Thanks!
  • ScoobySi
    ScoobySi Member Posts: 423
    To define the cartesian, use a JOIN Operator as per standard join but there will be no join condition for your tag table. If there are only two tables in the join you can use a join condition of *1=1* to get rid of the Warning (of Cartesian Product).

    How are you scheduling the mappings? If you put the update in the first mapping, you need to ensure the other mappings don't run until that one has completed. An approach is to use a Process Flow to define the dependencies, the PL/SQL package can then be added to the Process Flow (use Transformation operator) as the first task and the mappings made dependent on that.

    Si
  • 710706
    710706 Member Posts: 24
    Thanks! That is what I used to define a cartasian product join condition (1=1).

    Thanks about using process flows to control the sequence of running the mappings.
  • 710706
    710706 Member Posts: 24
    Hi,

    The OWB I have does not have work flow. I tried to define a work flow and it asked for the component that was missing.

    Is there a work around to have one mapping run first before another?
This discussion has been closed.