1 2 Previous Next 28 Replies Latest reply: Feb 24, 2014 1:08 AM by 7a791387-8613-4469-b417-d7eb8772a031 RSS

    Oracle Data Integrator: Exporting data from flat file to a flat file

    7a791387-8613-4469-b417-d7eb8772a031

      Hi,

       

      I am working on oracle data integrator 11g to export data from a flat file to another flat file.

      I am following the steps in the following tutorial:

      http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/odi/odi_11g/odi_project_ff-to-ff/odi_project_flatfile-to-flatfile.htm

       

      But as I execute the interface I get the following error:

      ODI-1227: Task SrcSet0 (Loading) fails on the source FILE connection FILE_GENERIC.
      Caused By: java.sql.SQLSyntaxErrorException: ORA-00906: missing left parenthesis

      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
      at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
      at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
      at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
      at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
      at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)
      at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:947)
      at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1283)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1441)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)
      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3823)
      at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1671)
      at oracle.odi.query.JDBCTemplate.executeQuery(JDBCTemplate.java:189)
      at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:94)
      at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:1)
      at oracle.odi.runtime.agent.execution.DataMovementTaskExecutionHandler.handleTask(DataMovementTaskExecutionHandler.java:70)
      at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2913)
      at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
      at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)
      at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
      at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
      at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
      at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
      at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
      at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
      at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
      at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
      at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82).
      at java.lang.Thread.run(Thread.java:662)

       

      I have followed all the steps as per the tutorial and only while execution I face this issue.

      Can anybody explain the reason I am facing this issue?

        • 1. Re: Oracle Data Integrator: Exporting data from flat file to a flat file
          Shiv_Kumar

          HI,

           

          Are you able to view data from the source file? please check.

          Try below steps for it:-

          •   In designer , right click onto your file datastore
          •   Choose view data
          • 2. Re: Oracle Data Integrator: Exporting data from flat file to a flat file
            1223551

            Hi Shiv_Kumar,

            I am also facing the same error. I also tried your suggesstion and I can see data but still error is getting populated when I execute the interface.

            Any other solution/hint?

             

            Please help.

            Ajay

            • 3. Re: Oracle Data Integrator: Exporting data from flat file to a flat file
              7a791387-8613-4469-b417-d7eb8772a031

              Hi Shiv,

               

              Yes I am able to view data.

              The interface mappings are also done properly. During the flow knowledge modules have also been selected and there default values have also been set.

              Only while execution of the interface I get an error as the data is not getting loaded in the staging table.

              • 4. Re: Oracle Data Integrator: Exporting data from flat file to a flat file
                Shiv_Kumar

                Hi,

                 

                Try to create fresh empty target file on Machine externally(like create empty file on desktop of machine) and make datasource in ODI for that empty file and  then create the mapping.

                Use this empty created file in mapping as a target and put data from file in which you are able to view data in ODI.

                • 5. Re: Oracle Data Integrator: Exporting data from flat file to a flat file
                  7a791387-8613-4469-b417-d7eb8772a031

                  Hi Shiv,


                  As per your suggestion, I exported data for a source file and created an empty target  file.

                  Then followed the steps of the tutorial and while creating model , the data could be viewed for the source data store.Then I made a duplicate selection for the target datastore, and selected the empty target file in the resource name.After that I created the interface, mapped the souce and target and in the flow tab set the values of truncate and generate header to true and false respectively(in the IKM SQL TO FILE APPEND knowledge module) and executed the interface.But still I'am facing issues.

                   

                  Any idea how to resolve this issue?

                  • 6. Re: Oracle Data Integrator: Exporting data from flat file to a flat file
                    FPonte

                    Hi.

                     

                    A few questions.

                     

                    The original error happens in the LOAD Data step (From file to Staging)?

                     

                    Did your file has any line break in each record or special characters in it?

                     

                    Cheers.

                    • 7. Re: Oracle Data Integrator: Exporting data from flat file to a flat file
                      7a791387-8613-4469-b417-d7eb8772a031

                      Hi,

                       

                      Yes the error happens in the load data step.

                       

                      Actually my source file just has one record right now and as such there are no special characters but there are spaces between column values.

                      We also tried using fixed and delimited file format.

                      • 8. Re: Oracle Data Integrator: Exporting data from flat file to a flat file
                        FPonte

                        Ok

                         

                        What are you using as field delimiter? Space, Tab?

                         

                        It seems to me that something in the record is breaking the code.

                         

                        If you don't mind I would like to see the code that generates the error ORA-00906.

                         

                        Can you post it here?

                         

                        Cheers.

                        • 9. Re: Oracle Data Integrator: Exporting data from flat file to a flat file
                          7a791387-8613-4469-b417-d7eb8772a031

                          I am using space as a delimiter in the field .But I am giving FIXED file format while creating the datastore.

                           

                          Iam posting the code of the knowledge module for LKM:

                          DROP WORK TABLE:

                          drop table <%=odiRef.getTable("L", "COLL_NAME", "W")%>

                           

                          CREATE WORK TABLE:

                          create table <%=odiRef.getTable("L", "COLL_NAME", "W")%>

                          (

                          <%=odiRef.getColList("", "[CX_COL_NAME]\t[DEST_WRI_DT] " + odiRef.getInfo("DEST_DDL_NULL"), ",\n\t", "","")%>

                          )

                           

                          LOAD DATA:

                          TARGET:

                          insert into <%=odiRef.getTable("L", "COLL_NAME", "W")%>

                          (

                          <%=odiRef.getColList("", "[CX_COL_NAME]", ",\n\t", "","")%>

                          )

                          values

                          (

                          <%=odiRef.getColList("", ":[CX_COL_NAME]", ",\n\t", "","")%>

                          )

                          SOURCE:

                          select <%=odiRef.getColList("", "[COL_NAME]\t[CX_COL_NAME]", ",\n\t", "", "")%>

                          from  TABLE

                          <%=odiRef.getSrcTablesList("", "[METADATA]", "", "")%>

                           

                          DROP WORK TABLE:

                          TARGET:drop table <%=odiRef.getTable("L", "COLL_NAME", "W")%>

                          • 10. Re: Oracle Data Integrator: Exporting data from flat file to a flat file
                            FPonte

                            Hi.

                             

                            I'm still trying to figure out the problem. I would like to see the final code generated by the interface.

                             

                            It's a syntax error.

                             

                            I can reproduce the "ORA-00906: missing left parenthesis" If I have a bad configuration in the data store. If I miss some Logical Lenght in one attribute of the table the CREATE WORK TABLE will fail.

                             

                            The create table will have a missing size definition like this (varchar2 withouth a lenght).

                            create table C$_0TEST_FILE

                            (

                              C1_MY_ID VARCHAR2 NULL,

                              C2_OTHER_ATTRIBUTE VARCHAR2 NULL,

                              C3_ANOTHER_ATTRIBUTE VARCHAR2 NULL

                            )

                             

                            If you are creating the target table the CREATE TARGET TABLE will not fail. It will only display a Warning with the same ORA error then you next step will fail saying that the table doesn't exist.

                             

                            Check the generated code and you will identify what is missing.

                             

                            Cheers.

                            • 11. Re: Oracle Data Integrator: Exporting data from flat file to a flat file
                              FPonte

                              Hi.

                               

                              I'm still trying to figure out the problem. I would like to see the final code generated by the interface.

                               

                              It's a syntax error.

                               

                              I can reproduce the "ORA-00906: missing left parenthesis" If I have a bad configuration in the data store. If I miss some Logical Length in one attribute of the table the CREATE WORK TABLE will fail.

                               

                              The create table will have a missing size definition like this (varchar2 withouth a length).

                              create table C$_0TEST_FILE

                              (

                                C1_MY_ID VARCHAR2 NULL,

                                C2_OTHER_ATTRIBUTE VARCHAR2 NULL,

                                C3_ANOTHER_ATTRIBUTE VARCHAR2 NULL

                              )

                               

                              If you are creating the target table the CREATE TARGET TABLE will not fail. It will only display a Warning with the same ORA error then you next step will fail saying that the table doesn't exist.

                               

                              Check the generated code and you will identify what is missing.

                               

                              Cheers.

                              • 12. Re: Oracle Data Integrator: Exporting data from flat file to a flat file
                                7a791387-8613-4469-b417-d7eb8772a031

                                Hi,

                                 

                                I started this tutorial from the beginning. I created the source table :

                                CREATE table ODI_STAGE."ODI_TAB" ("SALES_PERSON_ID" NUMBER(8,0) NOT NULL,

                                "FIRST_NAME" VARCHAR2(80),"LAST_NAME" VARCHAR2(80),

                                "DATE_HIRED" VARCHAR2(80),"DATE_UPDATED" DATE NOT NULL);

                                and inserted 3 records in it.

                                 

                                Then I created target table:

                                CREATE table ODI_STAGE."TRG_ODI_TAB"

                                AS

                                SELECT * FROM  ODI_STAGE."ODI_TAB"

                                WHERE 1=2;

                                This table does not have any rows.

                                Then the steps of the tutorial are followed.again I get the same error.

                                The CREATE WORK TABLE gets executed correctly.

                                The error is in the LOAD DATA step.

                                 

                                The create table does not have any size missing definition in it. So I think this is not the reason for the error.

                                Can  you please explain what code for the interface you are talking about.

                                The code is only present in the knowledge modules.

                                • 13. Re: Oracle Data Integrator: Exporting data from flat file to a flat file
                                  FPonte

                                  Hi.

                                   

                                  I'm assuming that you are using ODI11g.

                                   

                                  Whe you run the interface you can see the execution code generated by the Interface in the Operator.

                                   

                                  Expand the interface execution and double click the failed step. You can then click in the tab "Code" and copy the executed script against the database, file, etc...

                                   

                                  Cheers.


                                  • 14. Re: Oracle Data Integrator: Exporting data from flat file to a flat file
                                    7a791387-8613-4469-b417-d7eb8772a031

                                    I created a new project and here is the execution code generated by interface.
                                    Please check

                                    SOURCE:
                                    select SALES_PERSON_ID C1_SALES_PERSON_ID,
                                    FIRST_NAME C2_FIRST_NAME,
                                    LAST_NAME C3_LAST_NAME,
                                    DATE_HIRED C4_DATE_HIRED,
                                    DATE_UPDATED C5_DATE_UPDATED
                                    from  TABLE
                                    /*$$SNPS_START_KEYSNP$CRDWG_TABLESNP$CRTABLE_NAME=SRC_SALES_PERSONSNP$CRLOAD_FILE=D:\ODI_INSTALLED\oracledi\Demo\File/SRC_SALES_PERSON.txtSNP$CRFILE_FORMAT=FSNP$CRFILE_SEP_FIELD=0x0009SNP$CRFILE_SEP_LINE=0x000D0x000ASNP$CRFILE_FIRST_ROW=0SNP$CRFILE_ENC_FIELD=SNP$CRFILE_DEC_SEP=SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=SALES_PERSON_IDSNP$CRTYPE_NAME=NUMERICSNP$CRORDER=1SNP$CRLINE_OFFSET=1SNP$CRLENGTH=9SNP$CRPRECISION=9SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=FIRST_NAMESNP$CRTYPE_NAME=STRINGSNP$CRORDER=2SNP$CRLINE_OFFSET=10SNP$CRLENGTH=24SNP$CRPRECISION=24SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=LAST_NAMESNP$CRTYPE_NAME=STRINGSNP$CRORDER=3SNP$CRLINE_OFFSET=34SNP$CRLENGTH=23SNP$CRPRECISION=23SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=DATE_HIREDSNP$CRTYPE_NAME=STRINGSNP$CRORDER=4SNP$CRLINE_OFFSET=57SNP$CRLENGTH=32SNP$CRPRECISION=32SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=DATE_UPDATEDSNP$CRTYPE_NAME=DATESNP$CRORDER=5SNP$CRLINE_OFFSET=89SNP$CRLENGTH=24SNP$CRPRECISION=24SNP$CR$$SNPS_END_KEY*/


                                    TARGET:
                                    insert into "C$_0TRGT_SRC_SALES_PERSON"
                                    (
                                    C1_SALES_PERSON_ID,
                                    C2_FIRST_NAME,
                                    C3_LAST_NAME,
                                    C4_DATE_HIRED,
                                    C5_DATE_UPDATED
                                    )
                                    values
                                    (
                                    :C1_SALES_PERSON_ID,
                                    :C2_FIRST_NAME,
                                    :C3_LAST_NAME,
                                    :C4_DATE_HIRED,
                                    :C5_DATE_UPDATED
                                    )

                                    1 2 Previous Next