1 Reply Latest reply: Mar 26, 2013 1:57 PM by CharlesAlmeida RSS

    Error while running OracletoFile

    Maydemir_Ayd
      Hi,

      I want to unload data oracle table to flat file. But it gives me an error... I am using ODI_11.1.1.5.0 and OS win7 64 bit.

      I declared FILE phsycal schema and its director like this : F:\temp and logical schema log_file.

      I created a file model folder and file datastore (delimited). It has 3 columns which are A,B,C and data type String.

      I also have a table (source table) and it has 3 columns also (A,B,C).

      I designed a new interface source is table , target is file , KM is IKM_SQL_TO_FILE_APPEND. After I executed ,it gives me an error like this :


      ODI-1228: Task Turkcell (Integration) fails on the target FILE connection FLAT.
      Caused By: java.sql.SQLException: ODI-40400: Invalid format description
           at com.sunopsis.jdbc.driver.file.impl.metadata.MetaDataExtractor.buildTableDescFromRequestSQLExceptionOnly(MetaDataExtractor.java:479)
           at com.sunopsis.jdbc.driver.file.FilePreparedStatement.<init>(FilePreparedStatement.java:62)
           at com.sunopsis.jdbc.driver.file.FileConnection.prepareStatement(FileConnection.java:299)
           at sun.reflect.GeneratedMethodAccessor66.invoke(Unknown Source)
           at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
           at java.lang.reflect.Method.invoke(Method.java:597)
           at oracle.odi.core.datasource.dwgobject.support.OnConnectOnDisconnectDataSourceAdapter$OnDisconnectCommandExecutionHandler.invoke(OnConnectOnDisconnectDataSourceAdapter.java:200)
           at $Proxy2.prepareStatement(Unknown Source)
           at oracle.odi.runtime.agent.execution.sql.SQLCommand.doInitializeStatement(SQLCommand.java:82)
           at oracle.odi.runtime.agent.execution.sql.SQLCommand.getStatement(SQLCommand.java:117)
           at oracle.odi.runtime.agent.execution.sql.SQLCommand.getStatement(SQLCommand.java:111)
           at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:158)
           at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:102)
           at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:1)
           at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
           at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2906)
           at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2609)
           at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:540)
           at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:453)
           at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1740)
           at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:338)
           at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:214)
           at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:272)
           at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:263)
           at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:822)
           at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:123)
           at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:83)
           at java.lang.Thread.run(Thread.java:662)

      This errors appears at all steps :

      Create table :

      create table "F:\temp/Test.txt"

      /*$$SNPS_START_KEYSNP$CRDWG_TABLESNP$CRTABLE_NAME=TestSNP$CRLOAD_FILE=F:\temp/Test.txtSNP$CRFILE_FORMAT=DSNP$CRFILE_SEP_FIELD=0x0009SNP$CRFILE_SEP_LINE=0x000D0x000ASNP$CRFILE_FIRST_ROW=1SNP$CRFILE_ENC_FIELD=SNP$CRFILE_DEC_SEP=SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=ABCSNP$CRTYPE_NAME=STRINGSNP$CRORDER=1SNP$CRLENGTH=50SNP$CRPRECISION=150SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=BBBSNP$CRTYPE_NAME=STRINGSNP$CRORDER=2SNP$CRLENGTH=50SNP$CRPRECISION=150SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=CCCSNP$CRTYPE_NAME=STRINGSNP$CRORDER=3SNP$CRLENGTH=50SNP$CRPRECISION=150SNP$CR$$SNPS_END_KEY*/

      Truncate table :

      truncate table "F:\temp/Test.txt"
      /*$$SNPS_START_KEYSNP$CRDWG_TABLESNP$CRTABLE_NAME=TestSNP$CRLOAD_FILE=F:\temp/Test.txtSNP$CRFILE_FORMAT=DSNP$CRFILE_SEP_FIELD=0x0009SNP$CRFILE_SEP_LINE=0x000D0x000ASNP$CRFILE_FIRST_ROW=1SNP$CRFILE_ENC_FIELD=SNP$CRFILE_DEC_SEP=SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=ABCSNP$CRTYPE_NAME=STRINGSNP$CRORDER=1SNP$CRLENGTH=50SNP$CRPRECISION=150SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=BBBSNP$CRTYPE_NAME=STRINGSNP$CRORDER=2SNP$CRLENGTH=50SNP$CRPRECISION=150SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=CCCSNP$CRTYPE_NAME=STRINGSNP$CRORDER=3SNP$CRLENGTH=50SNP$CRPRECISION=150SNP$CR$$SNPS_END_KEY*/

      Generate header:

      create header     (ABC,
           BBB,
           CCC)

      /*$$SNPS_START_KEYSNP$CRDWG_TABLESNP$CRTABLE_NAME=TestSNP$CRLOAD_FILE=F:\temp/Test.txtSNP$CRFILE_FORMAT=DSNP$CRFILE_SEP_FIELD=0x0009SNP$CRFILE_SEP_LINE=0x000D0x000ASNP$CRFILE_FIRST_ROW=1SNP$CRFILE_ENC_FIELD=SNP$CRFILE_DEC_SEP=SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=ABCSNP$CRTYPE_NAME=STRINGSNP$CRORDER=1SNP$CRLENGTH=50SNP$CRPRECISION=150SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=BBBSNP$CRTYPE_NAME=STRINGSNP$CRORDER=2SNP$CRLENGTH=50SNP$CRPRECISION=150SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=CCCSNP$CRTYPE_NAME=STRINGSNP$CRORDER=3SNP$CRLENGTH=50SNP$CRPRECISION=150SNP$CR$$SNPS_END_KEY*/

      And insert step source code:

      select      
           TEST.A     ABC,
           TEST.B     BBB,
           TEST.C     CCC
      from     STG.TEST TEST
      where     
           (1=1)     
           

      And insert step target code:


      insert into "F:\temp/Test.txt"
      (
           ABC,
           BBB,
           CCC

      )
      values
      (
           :ABC,
           :BBB,
           :CCC

      )
      /*$$SNPS_START_KEYSNP$CRDWG_TABLESNP$CRTABLE_NAME=TestSNP$CRLOAD_FILE=F:\temp/Test.txtSNP$CRFILE_FORMAT=DSNP$CRFILE_SEP_FIELD=0x0009SNP$CRFILE_SEP_LINE=0x000D0x000ASNP$CRFILE_FIRST_ROW=1SNP$CRFILE_ENC_FIELD=SNP$CRFILE_DEC_SEP=SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=ABCSNP$CRTYPE_NAME=STRINGSNP$CRORDER=1SNP$CRLENGTH=50SNP$CRPRECISION=150SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=BBBSNP$CRTYPE_NAME=STRINGSNP$CRORDER=2SNP$CRLENGTH=50SNP$CRPRECISION=150SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=CCCSNP$CRTYPE_NAME=STRINGSNP$CRORDER=3SNP$CRLENGTH=50SNP$CRPRECISION=150SNP$CR$$SNPS_END_KEY*/



      I couldn't find this error "ODI-40400: Invalid format description" about this topic.

      Pls help me.