6 Replies Latest reply: Mar 29, 2012 1:54 AM by 922032 RSS

    Update operation in Oracle Warehouse Builder

    922032
      Hello,

      i would like to give in my tables the parameters created date (CREATED_DT) and last update date (LAST UPD_DT). For this want use the "Mapping Input Parameter" Component. I hope this component is the right?

      So my Steps until now.
      1. Create "Mapping Input Parameter"
      2. define the attributes (CREATED_DT) and LAST_UPD_DT
      3. Setting the Data Type to Date
      4. write CREATED_DT = current_date - 10; or set CREATED_DT = current_date - 10; or *(CREATED_DT = current_date -10;)* into Expression Field, possible the mistake is here but where :(
      5. Mapping with the table
      6. Deployment and Start (both is going perfect no failure or warnings)

      but it doesn´t work:(

      the same action for LAST_UPD_DT

      So my Question for this case:

      Is that the right component for so an action? If not which is the right component?
      How i can make so an action with this component if possible (what i must type into Expression Field?) or how i can make it in another component? If i must use a another component so give an example step-step, please

      I look forward for your replies:)

      __________________________________________________________________________________________________________________________________

      I am a newbie but only who ask know later more :)
        • 1. Re: Update operation in Oracle Warehouse Builder
          David Allan-Oracle
          This should be OK (by expression I take it you mean the default value for your mapping input parameter), may be a different error happening in your mapping. Worth getting the intermediate SQL from the mapping and try executing that in SQLPlus - then you cut to the chase about what the problem might be (without all the OWB wrappering).

          Cheers
          David
          • 2. Re: Update operation in Oracle Warehouse Builder
            922032
            Thanks for first. I know unfortunately not if we understand us or think the same. The parameter which i would like set - in this case are not constants but variables. By each loading prozess should the parameter CREATED_DT and LAST_UPD_DT so set be that i have later in my table the value CREATED_DT = current date -10. For an example if i load today the data that Parameter CREATED_DT should have the value 18. March (CREATED_DT = current_date -10) and so on..
            It it possible with this component? If yes what make i wrong in this case?

            I look forward for your replies :)
            • 3. Re: Update operation in Oracle Warehouse Builder
              Pnreddy
              Hi,

              You need to set the default value for the variable.

              Edit the mapping and click on the variable of the input parameter, the property inspector for that column will be displayed.(If not you have to go to view -> Property Inspector)

              Under 'Operator Specific Parameters' provide the default value for the parameter (in your case it is CURRENT_DATE-10)

              Thanks,
              Pnreddy
              • 4. Re: Update operation in Oracle Warehouse Builder
                922032
                that is my problem exactly

                i have tried some thing but nothing works.

                i have tried this:

                UPDATE S_ACCOUNT
                SET CREATED = current_date-10;

                or this

                Set CREATED_DT = current_date-10;

                or this

                UPDATE S_ACCOUNT
                SET CREATED_DT = current_date-10;
                FROM S_ACCOUNT

                or this

                DECLARE
                CREATED_DT DATE ();
                BEGIN
                UPDATE S_ACCOUNT
                SET CREATED_DT = current_date-10;
                END;

                but absolutly nothing works:(

                what i make wrong? the best for this case would be an example

                i look forward for your replies:)
                • 5. Re: Update operation in Oracle Warehouse Builder
                  Pnreddy
                  I am sorry, what is the problem exactly.

                  Did you provide the default value (through Property Inspector)and still it is not working?

                  I have tried this and i am able to load the target table successfully.

                  Here is the mapping that i created:

                  1)Created mapping with a source, Mapping Input Parameter and target oprators(The source table i took is EMP table from scott schema)
                  2)Mapped all the columns from source to target.
                  3)For the mapping Input parameter operator i created one output variable(SAMP_DAT with DATE data type) and mapped this column to the target table
                  with the value as CURRENT_DATE-10 in the expression editor provided
                  4)Deployed and executed the map
                  But the value for the SAMP_DAT was not loaded (it was NULL)
                  5)Then i opened the property inspector for SAMP_DAT and provided the default value as CURRENT_DATE-10 and deployed and executed the map again
                  And now i am able to see the data for the column SAMP_DAT in target table.

                  Hope this helps

                  Regards,
                  Pnreddy
                  • 6. Re: Update operation in Oracle Warehouse Builder
                    922032
                    Thanks for your help it works :)