This discussion is archived
1 Reply Latest reply: Mar 26, 2013 11:57 AM by CharlesAlmeida RSS

Error while running OracletoFile

Maydemir_Ayd Newbie
Currently Being Moderated
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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points