Problems with an SQL Insert

Post any question you may have in regards to GoAnywhere MFT and let our talented support staff and other users assist you.
6 posts Page 1 of 1

jwawrzon

Posts: 9
Joined: Mon Jun 29, 2015 7:59 am

Post by jwawrzon » 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
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'
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.
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), &apos;NSLP_RA_DATA.TXT&apos;)}" 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), &apos;NSLP_RA_DATA.TXT&apos;)}" 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="Sandra.Hadley@nysed.gov" version="2.0">
			<from address="Goanywhere@nysed.gov" />
			<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>

Support_Dan

Posts: 9
Joined: Fri Dec 02, 2016 1:39 pm

Post by Support_Dan » Fri Jan 27, 2017 5:20 pm
Jwawrzon,

In looking through your project (specifically base directory for file set) it appears that you are operating on a linux/unix operating system. The error message 'last known records near boundaries' may be eluding to the fact that linux/unix does not like the CRLF and is looking for just LF for delimiter option. Let's try changing the Record Delimiter option from CRLF to LF under the Import File task. Please let us know if this solves your issue.

If this does not mitigate the issue, can you please send us a copy of the data file you are trying to read in this project? Thanks.

jwawrzon

Posts: 9
Joined: Mon Jun 29, 2015 7:59 am

Post by jwawrzon » Mon Jan 30, 2017 11:11 am
Unfortunately that did not do it. I am now getting this error.
Code: Select all
1/30/17 10:49:46 AM           INFO      Start Date and Time: 1/30/17 10:49:46 AM
1/30/17 10:49:46 AM           INFO      Job Number: 1000000028349 
1/30/17 10:49:46 AM           INFO      Project Name: /CN/CN NSLP RA Data Import TEST
1/30/17 10:49:46 AM           INFO      Submitted By: administrator
1/30/17 10:49:46 AM           INFO      Submitted From: Administrator UI
1/30/17 10:49:46 AM           INFO      GoAnywhere 5.3.8 running on AIX 7.1 (ppc64)
1/30/17 10:49:46 AM           INFO      Executing project 'CN NSLP RA Data Import TEST' 
1/30/17 10:49:46 AM           INFO      Project location: /mars/goanywhere/GoAnywhere_Services/userdata/projects/CN/CN NSLP RA Data Import TEST.xml
1/30/17 10:49:46 AM           INFO      Executing module 'Main'
1/30/17 10:49:46 AM           INFO      Executing task 'timestamp 1.0'
1/30/17 10:49:46 AM           INFO      Default system date, time, and timestamp variables have been created and/or set to the current date and time '2017-01-30 10:49:46.315'
1/30/17 10:49:46 AM           INFO      Finished task 'timestamp 1.0'
1/30/17 10:49:46 AM           INFO      Executing task 'createWorkspace 1.0'
1/30/17 10:49:46 AM           INFO      Workspace directory for this job is set to '/mars/goanywhere/GoAnywhere_Services/userdata/workspace/1000000028349'.
1/30/17 10:49:46 AM           INFO      Finished task 'createWorkspace 1.0'
1/30/17 10:49:46 AM           INFO      Executing task 'createFileList 1.0'
1/30/17 10:49:46 AM           INFO      File List variable 'inboxFiles' was created containing 1 file(s)
1/30/17 10:49:46 AM           INFO      Finished task 'createFileList 1.0'
1/30/17 10:49:46 AM           INFO      Entering loop 'forEachLoop (CN NSLP RA Data Import)'
1/30/17 10:49:46 AM           INFO      Skipping if block labeled 'CN NSLP RA Data Import' as the condition "${inboxFiles}" was not met
1/30/17 10:49:46 AM           INFO      Executing task 'readFixedWidth 1.0 (Import file)'
1/30/17 10:49:46 AM           INFO      Data parsed successfully and the rowset variable 'fwImport' was created
1/30/17 10:49:46 AM           INFO      Finished task 'readFixedWidth 1.0 (Import file)'
1/30/17 10:49:46 AM           INFO      Executing task 'sql 1.0'
1/30/17 10:49:46 AM           INFO      Executing sub-task 'query'
1/30/17 10:49:46 AM           INFO      Executing statement - TRUNCATE TABLE CN.ST_NSLP_RA_DATA
1/30/17 10:49:46 AM           INFO      Query completed successfully and 0 row(s) were affected
1/30/17 10:49:46 AM           INFO      Finished sub-task 'query'
1/30/17 10:49:46 AM           INFO      Executing sub-task 'query'
1/30/17 10:49:46 AM           INFO      Executing 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 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?.?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
1/30/17 10:49:46 AM           INFO      Opening file '/mars/sftp/CN/NYCBOE/inbasket/01192017NSLP_RA_DATA.TXT'
1/30/17 10:49:46 AM           ERROR     [9013 - Import file] /mars/sftp/CN/NYCBOE/inbasket/01192017NSLP_RA_DATA.TXT: Invalid record was found with boundaries '1, 1, 2, 1'. Invalid character 
                                         at line 2 column 0. A record delimiter (LF) or end of file is expected 
1/30/17 10:49:46 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/01192017NSLP_RA_DATA.TXT'
1/30/17 10:49:46 AM           ERROR     [8098 - SQL] [9007 - Import file] /mars/sftp/CN/NYCBOE/inbasket/01192017NSLP_RA_DATA.TXT: Invalid record was found. Full stack trace written to '1000000028349_error_1.log'
1/30/17 10:49:46 AM           INFO      Routing the control to the module 'ErrorModule'
1/30/17 10:49:46 AM           INFO      Executing task 'sendEmail 2.0 (Send Error Email)'
1/30/17 10:49:46 AM           INFO      Connecting to mail server: uxmail.nysed.gov
1/30/17 10:49:51 AM           INFO      Opened the connection
1/30/17 10:49:51 AM           INFO      Message is delivered successfully
1/30/17 10:49:51 AM           INFO      The message has been delivered to the following 1 addresses: 
                                        Sandra.Hadley@nysed.gov
                                         
1/30/17 10:49:51 AM           INFO      Message 'Project CN NSLP RA Data Import TEST Failed' was sent to 1 recipient(s)	
1/30/17 10:49:51 AM           INFO      Finished task 'sendEmail 2.0 (Send Error Email)'
1/30/17 10:49:51 AM           INFO      Executing task 'deleteWorkspace 1.0'
1/30/17 10:49:51 AM           INFO      Closed the connection
1/30/17 10:49:51 AM           INFO      Workspace directory '/mars/goanywhere/GoAnywhere_Services/userdata/workspace/1000000028349' was deleted successfully
1/30/17 10:49:51 AM           INFO      Finished task 'deleteWorkspace 1.0'
1/30/17 10:49:51 AM           INFO      Executing task 'raiseError 1.0 (Flag Job as Error)'
1/30/17 10:49:51 AM           INFO      Finished task 'raiseError 1.0 (Flag Job as Error)'
1/30/17 10:49:51 AM           INFO      Finished project 'CN NSLP RA Data Import TEST'
1/30/17 10:49:51 AM           ERROR     [9001 - Flag Job as Error] [8098 - SQL] [9007 - Import file] /mars/sftp/CN/NYCBOE/inbasket/01192017NSLP_RA_DATA.TXT: Invalid record was found. Full stack trace written to '1000000028349_error_2.log'
1/30/17 10:49:51 AM           INFO      End Date and Time: 1/30/17 10:49:51 AM
I have also attached the file I am trying to import.
Attachments
012617NSLP_RA_DATA.TXT
(7.21 KiB) Downloaded 294 times

Support_Rick

Support Specialist
Posts: 592
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Thu Feb 09, 2017 9:18 am
I have imported your project (readfixedwidth task) and told it to loop through the Rowset to just display. It works as is...

Unless there is an issue with your SQL statement, I'm not sure what would be the issue. Try loading this and see if it works on your system.
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">

		<readFixedWidth label="Import file" inputFile="012617NSLP_RA_DATA.TXT" outputRowSetVariable="fwImport" skipFirstRow="false" recordDelimiter="CRLF" version="1.0" disabled="false">
			<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>

		<forEachLoop label="CN NSLP RA Data Import" itemsVariable="${fwImport}" currentItemVariable="col">

			<print version="1.0">
				<![CDATA[=======================================
${Col[1]}
${Col[2]}
${Col[3]}
${Col[4]}
${Col[5]}
${Col[6]}
${Col[7]}
${Col[8]}
${Col[9]}
${Col[10]}
${Col[11]}
${Col[12]}
=======================================
]]>
			</print>

		</forEachLoop>
	</module>

</project>
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

jwawrzon

Posts: 9
Joined: Mon Jun 29, 2015 7:59 am

Post by jwawrzon » Fri Feb 10, 2017 10:44 am
That ran perfectly and displayed the data. So the problem is definitely happening when I try to run sql using the data. Here is the error log that I get when I try to run the above script with an sql insert.
Code: Select all
2/10/17 10:39:34 AM           INFO      Start Date and Time: 2/10/17 10:39:34 AM
2/10/17 10:39:34 AM           INFO      Job Number: 1000000029392 
2/10/17 10:39:34 AM           INFO      Project Name: /CN/CN NSLP RA Data Import TEST
2/10/17 10:39:34 AM           INFO      Submitted By: administrator
2/10/17 10:39:34 AM           INFO      Submitted From: Administrator UI
2/10/17 10:39:34 AM           INFO      GoAnywhere 5.3.8 running on AIX 7.1 (ppc64)
2/10/17 10:39:34 AM           INFO      Executing project 'CN NSLP RA Data Import TEST' 
2/10/17 10:39:34 AM           INFO      Project location: /mars/goanywhere/GoAnywhere_Services/userdata/projects/CN/CN NSLP RA Data Import TEST.xml
2/10/17 10:39:34 AM           INFO      Executing module 'Main'
2/10/17 10:39:34 AM           INFO      Executing task 'readFixedWidth 1.0 (Import file)'
2/10/17 10:39:34 AM           INFO      Data parsed successfully and the rowset variable 'fwImport' was created
2/10/17 10:39:34 AM           INFO      Finished task 'readFixedWidth 1.0 (Import file)'
2/10/17 10:39:34 AM           INFO      Entering loop 'forEachLoop (CN NSLP RA Data Import)'
2/10/17 10:39:34 AM           INFO      Opening file '/mars/sftp/CN/NYCBOE/inbasket/012617NSLP_RA_DATA.TXT'
2/10/17 10:39:34 AM           INFO      Executing task 'print 1.0'
2/10/17 10:39:34 AM           INFO      =======================================
                                        300000010000
                                        47 The American Sign Language and English Secondary School
                                        2
                                        223 EAST 23 STREET
                                        MANHATTAN
                                        10010
                                        PS
                                        310200010047
                                        Brk
                                        N
                                        Sat-Prep
                                        09/01/2016
                                        =======================================
2/10/17 10:39:34 AM           INFO      Finished task 'print 1.0'
2/10/17 10:39:34 AM           INFO      Executing task 'print 1.0'
2/10/17 10:39:34 AM           INFO      =======================================
                                        300000010000
                                        47 The American Sign Language and English Secondary School
                                        2
                                        223 EAST 23 STREET
                                        MANHATTAN
                                        10010
                                        PS
                                        310200010047
                                        Lunch
                                        N
                                        Sat-Prep
                                        09/01/2016
                                        =======================================
2/10/17 10:39:34 AM           INFO      Finished task 'print 1.0'
2/10/17 10:39:34 AM           INFO      Executing task 'print 1.0'
2/10/17 10:39:34 AM           INFO      =======================================
                                        300000010000
                                        A Fantis Parochial School
                                        15
                                        195 STATE STREET
                                        BROOKLYN
                                        11201
                                        NS
                                        331500716881
                                        Brk
                                        N
                                        Satellite
                                        09/01/2016
                                        =======================================
2/10/17 10:39:34 AM           INFO      Finished task 'print 1.0'
2/10/17 10:39:34 AM           INFO      Executing task 'print 1.0'
2/10/17 10:39:34 AM           INFO      =======================================
                                        300000010000
                                        A Fantis Parochial School
                                        15
                                        195 STATE STREET
                                        BROOKLYN
                                        11201
                                        NS
                                        331500716881
                                        Lunch
                                        N
                                        Satellite
                                        09/01/2016
                                        =======================================
2/10/17 10:39:34 AM           INFO      Finished task 'print 1.0'
2/10/17 10:39:34 AM           INFO      Executing task 'print 1.0'
2/10/17 10:39:34 AM           INFO      =======================================
                                        300000010000
                                        A. Philip Randolph Campus High School
                                        6
                                        443 WEST 135 STREET
                                        MANHATTAN
                                        10031
                                        PS
                                        310600011540
                                        Brk
                                        N
                                        Sat-Prep
                                        09/01/2016
                                        =======================================
2/10/17 10:39:34 AM           INFO      Finished task 'print 1.0'
2/10/17 10:39:34 AM           INFO      Executing task 'print 1.0'
2/10/17 10:39:34 AM           INFO      =======================================
                                        300000010000
                                        A. Philip Randolph Campus High School
                                        6
                                        443 WEST 135 STREET
                                        MANHATTAN
                                        10031
                                        PS
                                        310600011540
                                        Lunch
                                        N
                                        Sat-Prep
                                        09/01/2016
                                        =======================================
2/10/17 10:39:34 AM           INFO      Finished task 'print 1.0'
2/10/17 10:39:34 AM           INFO      Executing task 'print 1.0'
2/10/17 10:39:34 AM           INFO      =======================================
                                        300000010000
                                        Abraham Lincoln High School
                                        21
                                        2800 OCEAN PARKWAY
                                        BROOKLYN
                                        11235
                                        PS
                                        332100011410
                                        Brk
                                        N
                                        Sat-Prep
                                        09/01/2016
                                        =======================================
2/10/17 10:39:34 AM           INFO      Finished task 'print 1.0'
2/10/17 10:39:34 AM           INFO      Executing task 'print 1.0'
2/10/17 10:39:34 AM           INFO      =======================================
                                        300000010000
                                        Abraham Lincoln High School
                                        21
                                        2800 OCEAN PARKWAY
                                        BROOKLYN
                                        11235
                                        PS
                                        332100011410
                                        Lunch
                                        N
                                        Sat-Prep
                                        09/01/2016
                                        =======================================
2/10/17 10:39:34 AM           INFO      Finished task 'print 1.0'
2/10/17 10:39:34 AM           INFO      Executing task 'print 1.0'
2/10/17 10:39:34 AM           INFO      =======================================
                                        300000010000
                                        Abraham Lincoln YABC
                                        21
                                        2800 OCEAN PARKWAY
                                        BROOKLYN
                                        11235
                                        PS
                                        800000084257
                                        Brk
                                        N
                                        Satellite
                                        09/01/2016
                                        =======================================
2/10/17 10:39:34 AM           INFO      Finished task 'print 1.0'
2/10/17 10:39:34 AM           INFO      Executing task 'print 1.0'
2/10/17 10:39:34 AM           INFO      =======================================
                                        300000010000
                                        Abraham Lincoln YABC
                                        21
                                        2800 OCEAN PARKWAY
                                        BROOKLYN
                                        11235
                                        PS
                                        800000084257
                                        Lunch
                                        N
                                        Satellite
                                        09/01/2016
                                        =======================================
2/10/17 10:39:34 AM           INFO      Finished task 'print 1.0'
2/10/17 10:39:34 AM           INFO      Executing task 'print 1.0'
2/10/17 10:39:34 AM           INFO      =======================================
                                        300000010000
                                        Academic Leadership Charter Middle School
                                        7
                                        470 JACKSON AVENUE
                                        BRONX
                                        10455
                                        PC
                                        800000084067
                                        Brk
                                        N
                                        Satellite
                                        07/01/2016
                                        =======================================
2/10/17 10:39:34 AM           INFO      Finished task 'print 1.0'
2/10/17 10:39:34 AM           INFO      Executing task 'print 1.0'
2/10/17 10:39:34 AM           INFO      =======================================
                                        300000010000
                                        Academic Leadership Charter Middle School
                                        7
                                        470 JACKSON AVENUE
                                        BRONX
                                        10455
                                        PC
                                        800000084067
                                        Lunch
                                        N
                                        Satellite
                                        07/01/2016
                                        =======================================
2/10/17 10:39:34 AM           INFO      Finished task 'print 1.0'
2/10/17 10:39:34 AM           INFO      Executing task 'print 1.0'
2/10/17 10:39:34 AM           INFO      =======================================
                                        300000010000
                                        Academic Leadership Charter School
                                        7
                                        677 EAST 141 STREET
                                        BRONX
                                        10454
                                        PC
                                        320700860957
                                        Brk
                                        N
                                        Satellite
                                        07/01/2016
                                        =======================================
2/10/17 10:39:34 AM           INFO      Finished task 'print 1.0'
2/10/17 10:39:34 AM           INFO      Executing task 'print 1.0'
2/10/17 10:39:34 AM           INFO      =======================================
                                        300000010000
                                        Academic Leadership Charter School
                                        7
                                        677 EAST 141 STREET
                                        BRONX
                                        10454
                                        PC
                                        320700860957
                                        Lunch
                                        N
                                        Satellite
                                        07/01/2016
                                        =======================================
2/10/17 10:39:34 AM           INFO      Finished task 'print 1.0'
2/10/17 10:39:34 AM           INFO      Executing task 'print 1.0'
2/10/17 10:39:34 AM           INFO      =======================================
                                        300000010000
                                        Academy for Careers in Television and Film
                                        30
                                        1-50 51ST AVENUE
                                        QUEENS
                                        11101
                                        PS
                                        343000011301
                                        Brk
                                        N
                                        Sat-Prep
                                        09/01/2016
                                        =======================================
2/10/17 10:39:34 AM           INFO      Finished task 'print 1.0'
2/10/17 10:39:34 AM           INFO      Executing task 'print 1.0'
2/10/17 10:39:34 AM           INFO      =======================================
                                        300000010000
                                        Academy for Careers in Television and Film
                                        30
                                        1-50 51ST AVENUE
                                        QUEENS
                                        11101
                                        PS
                                        343000011301
                                        Lunch
                                        N
                                        Sat-Prep
                                        09/01/2016
                                        =======================================
2/10/17 10:39:34 AM           INFO      Finished task 'print 1.0'
2/10/17 10:39:34 AM           INFO      Executing task 'print 1.0'
2/10/17 10:39:34 AM           INFO      =======================================
                                        300000010000
                                        Academy for College Preparation and Career Exploration: A College Board School
                                        17
                                        911 FLATBUSH AVENUE
                                        BROOKLYN
                                        11226
                                        PS
                                        337800010465
                                        Brk
                                        N
                                        Satellite
                                        09/01/2016
                                        =======================================
2/10/17 10:39:34 AM           INFO      Finished task 'print 1.0'
2/10/17 10:39:34 AM           INFO      Executing task 'print 1.0'
2/10/17 10:39:34 AM           INFO      =======================================
                                        300000010000
                                        Academy for College Preparation and Career Exploration: A College Board School
                                        17
                                        911 FLATBUSH AVENUE
                                        BROOKLYN
                                        11226
                                        PS
                                        337800010465
                                        Lunch
                                        N
                                        Satellite
                                        09/01/2016
                                        =======================================
2/10/17 10:39:34 AM           INFO      Finished task 'print 1.0'
2/10/17 10:39:34 AM           INFO      Executing task 'print 1.0'
2/10/17 10:39:34 AM           INFO      =======================================
                                        300000010000
                                        Academy for College Preparation and Career Exploration: A College Board School
                                        17
                                        911 FLATBUSH AVENUE
                                        BROOKLYN
                                        11226
                                        PS
                                        337800010465
                                        Snack
                                        N
                                        Satellite
                                        09/01/2016
                                        =======================================
2/10/17 10:39:34 AM           INFO      Finished task 'print 1.0'
2/10/17 10:39:34 AM           INFO      Executing task 'print 1.0'
2/10/17 10:39:34 AM           INFO      =======================================
                                        300000010000
                                        Academy for Conservation and the Environment
                                        18
                                        6565 FLATLANDS AVENUE
                                        BROOKLYN
                                        11236
                                        PS
                                        331800011637
                                        Brk
                                        N
                                        Satellite
                                        09/01/2016
                                        =======================================
2/10/17 10:39:34 AM           INFO      Finished task 'print 1.0'
2/10/17 10:39:34 AM           INFO      Exiting loop 'forEachLoop (CN NSLP RA Data Import)'
2/10/17 10:39:34 AM           INFO      Executing task 'sql 1.0'
2/10/17 10:39:34 AM           INFO      Executing sub-task 'query'
2/10/17 10:39:34 AM           INFO      Executing statement - TRUNCATE TABLE CN.ST_NSLP_RA_DATA
2/10/17 10:39:34 AM           INFO      Query completed successfully and 0 row(s) were affected
2/10/17 10:39:34 AM           INFO      Finished sub-task 'query'
2/10/17 10:39:34 AM           INFO      Executing sub-task 'query'
2/10/17 10:39:34 AM           INFO      Executing 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
                                        (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?.?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
2/10/17 10:39:34 AM           INFO      Opening file '/mars/sftp/CN/NYCBOE/inbasket/012617NSLP_RA_DATA.TXT'
2/10/17 10:39:34 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/012617NSLP_RA_DATA.TXT'
2/10/17 10:39:34 AM           ERROR     [8098 - SQL] ORA-00919: invalid function. Full stack trace written to '1000000029392_error_1.log'
2/10/17 10:39:34 AM           INFO      Finished project 'CN NSLP RA Data Import TEST'
2/10/17 10:39:34 AM           ERROR     [8098 - SQL] ORA-00919: invalid function
                                         
2/10/17 10:39:34 AM           INFO      End Date and Time: 2/10/17 10:39:34 AM
I am not sure why it doesn't work properly as I have other projects with this exact setup. They work perfectly. The only difference in those is they modify the rowsets before inserting them. In fact if I run the project with a single insert in to 1 field after modifying the rowset, but making no changes to it. It will insert properly. If I try to insert that one field without running the modify rowset it will not insert properly.

avinash

Posts: 1
Joined: Wed Sep 18, 2019 6:43 am

Post by avinash » Wed Sep 18, 2019 7:21 am
Thanks, it is helpful for me.
6 posts Page 1 of 1