Problems with an SQL Insert
Posted: Thu Jan 26, 2017 10:38 am
I am having a huge problem with an SQL insert project. I am importing data from a fixed width file, and using the data to insert to an oracle database. I have made projects similar to this one in the past and they have worked fine, but for some reason this project will not run. When it gets to the insert SQL statement I get the followng error;
Code: Select all
Alternatively I have tried manually inserting data into a single column using this SQL statement. "INSERT INTO CN.ST_NSLP_RA_DATA (SA_LEA_CODE) VALUES (?) and get the following error.1/26/17 10:32:12 AM ERROR [8044 - SQL] Last known record near boundaries (start line: 1, start column: 1, end line: 2, end column: 1) of file '/mars/sftp/CN/NYCBOE/inbasket/01252017NSLP_RA_DATA.TXT'
1/26/17 10:32:12 AM ERROR [8098 - SQL] ORA-00919: invalid function. Full stack trace written to '1000000028017_error_1.log'
Code: Select all
1/26/17 10:37:22 AM ERROR [8044 - SQL] Last known record near boundaries (start line: 1, start column: 1, end line: 2, end column: 1) of file '/mars/sftp/CN/NYCBOE/inbasket/01252017NSLP_RA_DATA.TXT'
Code: Select all
<project name="CN NSLP RA Data Import TEST" mainModule="Main" version="2.0" logLevel="verbose">
<description>This project will process a file from
/mars/sftp/cn/nycboe/inbasket and
import its data into a database in PEMS.
It will then Archive the original file.</description>
<module name="Main" logLevel="verbose">
<timestamp version="1.0" />
<createWorkspace version="1.0" />
<createFileList fileListVariable="inboxFiles" version="1.0">
<fileset dir="/mars/sftp/CN/NYCBOE/inbasket">
<wildcardFilter>
<include pattern="*.*" />
</wildcardFilter>
</fileset>
</createFileList>
<forEachLoop label="CN NSLP RA Data Import" itemsVariable="${inboxFiles}" currentItemVariable="currentFile">
<if label="CN NSLP RA Data Import" condition="${inboxFiles}" />
<readFixedWidth label="Import file" inputFilesVariable="${currentFile}" outputRowSetVariable="fwImport" skipFirstRow="false" recordDelimiter="CRLF" version="1.0" executeOnlyIf="${EndsWith(String(currentFile), 'NSLP_RA_DATA.TXT')}" disabled="false" onError="call:ErrorModule">
<data trim="none">
<column index="1" name="SA_LEA_CODE" size="12" type="VARCHAR" />
<column index="2" name="RA_NAME" size="150" type="VARCHAR" />
<column index="3" name="DIST_CODE" size="2" type="NUMERIC" />
<column index="4" name="ADDRESS" size="36" type="VARCHAR" />
<column index="5" name="CITY" size="20" type="VARCHAR" />
<column index="6" name="ZIP" size="10" type="VARCHAR" />
<column index="7" name="AGENCY_TYPE" size="2" type="VARCHAR" />
<column index="8" name="RA_LEA_CODE" size="12" type="VARCHAR" />
<column index="9" name="PROGRAM" size="20" type="VARCHAR" />
<column index="10" name="SNACK_OVERRIDE" size="1" type="VARCHAR" />
<column index="11" name="METHOD_OF_SERVICE" size="16" type="VARCHAR" />
<column index="12" name="BEGIN_DATE" size="10" type="VARCHAR" />
<column index="13" name="END_DATE" size="10" type="VARCHAR" />
<column index="14" name="BEGIN_TIME" size="8" type="VARCHAR" />
<column index="15" name="END_TIME" size="8" type="VARCHAR" />
<column index="16" name="PROJ_ADP" size="7" type="NUMERIC" />
<column index="17" name="OFFER_SERVE" size="1" type="VARCHAR" />
<column index="18" name="REDUCED_PRICE" size="6" type="VARCHAR" />
<column index="19" name="PAID_PRICE" size="6" type="VARCHAR" />
<column index="20" name="NURSERY" size="1" type="VARCHAR" />
<column index="21" name="PRE_K" size="1" type="VARCHAR" />
<column index="22" name="HALF_K" size="1" type="VARCHAR" />
<column index="23" name="FULL_K" size="1" type="VARCHAR" />
<column index="24" name="FIRST" size="1" type="VARCHAR" />
<column index="25" name="SECOND" size="1" type="VARCHAR" />
<column index="26" name="THIRD" size="1" type="VARCHAR" />
<column index="27" name="FORTH" size="1" type="VARCHAR" />
<column index="28" name="FIFTH" size="1" type="VARCHAR" />
<column index="29" name="SIXTH" size="1" type="VARCHAR" />
<column index="30" name="SEVENTH" size="1" type="VARCHAR" />
<column index="31" name="EIGHTH" size="1" type="VARCHAR" />
<column index="32" name="NINTH" size="1" type="VARCHAR" />
<column index="33" name="TENTH" size="1" type="VARCHAR" />
<column index="34" name="ELEVENTH" size="1" type="VARCHAR" />
<column index="35" name="TWELFTH" size="1" type="VARCHAR" />
<column index="36" name="UNGRADED" size="1" type="VARCHAR" />
<column index="37" name="SPEC_ED" size="1" type="VARCHAR" />
<column index="38" name="CEP_ISP" size="8" type="VARCHAR" />
<column index="39" name="ADDRESS_CHANGE" size="1" type="VARCHAR" />
<column index="40" name="NAME_CHANGE" size="1" type="VARCHAR" />
<column index="41" name="PROGRAM_DELETE" size="1" type="VARCHAR" />
<column index="42" name="LEA_DEACTIVATED" size="1" type="VARCHAR" />
</data>
</readFixedWidth>
<sql resourceId="PEMS" autoCommit="false" user="cn" password="f33dk1ds" version="1.0" logLevel="debug" executeOnlyIf="${EndsWith(String(currentFile), 'NSLP_RA_DATA.TXT')}" onError="call:ErrorModule">
<query label="TRUNCATE TABLE ST_NSLP_RA_DATA">
<statement>TRUNCATE TABLE CN.ST_NSLP_RA_DATA</statement>
</query>
<query label="INSERT INTO ST_NSLP_RA_DATA" inputRowSetVariable="${fwImport}" outputVariable="Main" createScrollableRowSet="false">
<statement>INSERT INTO CN.ST_NSLP_RA_DATA (SA_LEA_CODE,RA_NAME,DIST_CODE,ADDRESS,CITY,ZIP,AGENCY_TYPE,RA_LEA_CODE,PROGRAM,SNACK_OVERRIDE,METHOD_OF_SERVICE,BEGIN_DATE,END_DATE,BEGIN_TIME,END_TIME,PROJ_ADP,OFFER_SERVE,REDUCED_PRICE,PAID_PRICE,NURSERY,PRE_K,HALF_K,FULL_K,FIRST,SECOND,THIRD,FORTH,FIFTH,SIXTH,SEVENTH,EIGHTH,NINTH,TENTH,ELEVENTH,TWELFTH,UNGRADED,SPEC_ED,CEP_ISP,ADDRESS_CHANGE,NAME_CHANGE,PROGRAM_DELETE,LEA_DEACTIVATED) VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?.?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
</statement>
</query>
</sql>
</forEachLoop>
<deleteWorkspace version="1.0" />
</module>
<module name="ErrorModule">
<sendEmail label="Send Error Email" resourceId="uxmail" toList="[email protected]" version="2.0">
<from address="[email protected]" />
<subject>
<![CDATA[Project ${system.project.name} Failed ]]>
</subject>
<message>
<![CDATA[Project ${system.project.name} failed due to the following reason:
${system.job.error}]]>
</message>
<attachment file="${system.job.log}" />
</sendEmail>
<deleteWorkspace version="1.0" />
<raiseError label="Flag Job as Error" version="1.0">
<message>${system.job.error}</message>
</raiseError>
</module>
</project>