9 Replies Latest reply: Nov 13, 2010 3:52 AM by sahar_ad RSS

    OWB, External Table, and Test Data - Help?

    808788
      Running into an issue I can't seem to find an answer to, I admit, I am a complete newbie to OWB, and I'm learning as I go.

      We're running on Oracle 10.2. I've created a series of SQL scripts for table creation/inserts/updates. Now, I am trying to move the insert/update into OWB. I've created the originating data via an external table.

      I can validate the mapping for the initial data load, but when I go to debug, I receive the "Could not find test data" error. I can access the data through the owner account, the OWB account, and by right clicking on the external object, choosing 'data', I can see all rows (its a small table).

      The closest I have found to an answer is via: http://gerardnico.com/wiki/dw/etl/owb/owb_graphical_mapping_debugger , but I cannot find out what 'create and bind the operator' means I should do.

      Any insight or direction would help,

      Thank you!

      -- Kerry
        • 1. Re: OWB, External Table, and Test Data - Help?
          808882
          Hi Kerry,

          If you add a table/external operator to a mapping using "create unbound operator with no attributes", the table/external table is not bound to any table/extertable, you can not find the table/external table in Table Node or External Table Node of the project tree. If you right-click the operator, select "create and bind" to bind the operator to a table/external table, the table/external table will be created and you can find it in the project tree. Then you can deploy the tables, external tables and mappings to your repository.

          Thanks,
          David
          • 2. Re: OWB, External Table, and Test Data - Help?
            sahar_ad
            Hi,

            I have a flat file with 100,854 KB Size and the number of records are 947,740.
            I import my flat file into a file module in owb 11g and then I have created an external table as though this flat file to import data.But the number of records are loaded in external table is about 811,566.And I dont know why miss the rest of records.
            Please anyone know to solve this problem please guide me.
            Regards,
            Sahar
            • 3. Re: OWB, External Table, and Test Data - Help?
              David Allan-Oracle
              Hi Sahar

              Sounds like either errors in the data file or some issues with how the data is being consumed by your flat file/external table definition.

              You need to enable logging so that you get detailed information about the bad records. To do this;
              1. configure the external table
              2. In Access Specification group set the 'Log file name' property to a useful name such as myexttab.log
              3. In Access Specification group set the 'Bad file name' property to a useful name such as myexttab.bad
              4. In Access Specification group set the 'Discard file name' property to a useful name such as myexttab.dis

              You will need to redeploy the external table. You can also change the directory/location if you want for each or leave to the default location.

              Check the log file after querying the external table it should give you an insight into the problem...maybe an invalid number, date or many other things.

              Cheers
              David
              • 4. Re: OWB, External Table, and Test Data - Help?
                sahar_ad
                Hi

                I want to load my flat file which is tab delimited with null values in some fields.I use owb 11g to load my flat file into an Extenral tables but since my column12 is not comlepety filled with value and have null in some fileds of this column,so the null fields are ignored and the next field that does have a value, is moved up and is located to previous field, resulting data being inserted to wrong columns .I got this error in my log file for external table:

                KUP-04021: field formatting error for field C13
                KUP-04023: field start is after end of record
                KUP-04101: record 4 rejected in file E:\finalflatfile\test.txt

                please anyone know about this problem to guide me.

                Regards,
                Sahar
                • 5. Re: OWB, External Table, and Test Data - Help?
                  Oleg
                  Hi Sahar,
                  did you specify "enclosed by" parameter in file specification (Enclosures Left/Right in Edit Flat File dialog window)?
                  Is it (field enclosure specification) really needed in your case (do you have enclosed fields in your file)?

                  It is known SQL*Loader (and external table) problem - incompatibility whitespace delimiter and "enclosed by" specification...

                  Regards,
                  Oleg
                  • 6. Re: OWB, External Table, and Test Data - Help?
                    David Allan-Oracle
                    Hi Sahar

                    Also ....there is a configuration property 'Load Nulls when Missing Values' that is set to false be default, you should check this. Configure the external table, under Field Editing group, the property 'Load Nulls when Missing Values' has default value false, change to true and redeploy the external table.

                    Cheers
                    David
                    • 7. Re: OWB, External Table, and Test Data - Help?
                      808788
                      Through trial and error; I found it was the copnnectors/locations that was leading me astray. Thank you!
                      • 8. Re: OWB, External Table, and Test Data - Help?
                        sahar_ad
                        Hi

                        I have set the property load nulls when missing value in config of external tables but my problem is , the null fields are ignored and the next field that does have a value, is moved up and is located to previous field, resulting data being inserted to wrong columns .I got this error in my log file for external table.
                        Please tell me a way for solvinh this problem.

                        Regards,
                        Sahar
                        • 9. Re: OWB, External Table, and Test Data - Help?
                          sahar_ad
                          Hi,

                          I have defined " for left and right enclosurein in editing flat file.But I have the problem as I told before.

                          Regards,
                          Sahar