[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
Re: [sqr-users] Importing data into Peoplesoft 8
Thanks to all for the assistance. Trailing spaces was definitely a good
thing to check for. I guess what bothers me the most is that the Oracle
error message doesn't indicate which column it is. I should not have to
spend the whole day figuring this out.
Thanks again,
Daniel Brinkley
Techneglas, Inc.
Columbus, OH.
>>> pmenta@umuc.edu 05/01/2003 2:32:08 PM >>>
Display the values and their legnths before the insert . Also append
on-error at your insert procedure so that it helps
Prasanna
Dan Brinkley wrote:
>
> Hey all, this is my first posting. I guess you only hear from me
when I
> have a problem :)
> I'm now using SQR to load data into PS (ver 8) for Health and
Safety.
>
> I'm getting the following Oracle error when trying to do my INSERT
INTO
> PS_INCIDENT_DATA...
>
> (SQR 5528) ORACLE OCIStmtExecute error 1401 in cursor 5:
> ORA-01401: inserted value too large for column
>
> It would be nice if Oracle told us what column has a problem.
>
> To figure this out, I've ran SQRW with the -S to produce the full
SQL
> in the log file, then taken the INSERT statement and pasted it into
> SQL-PLUS, replaced the bind variables (:1, :2, etc...) with
appropriate
> values.
> AND IT WORKS JUST FINE!!!!
>
> The Procedure is below, and I'm not really feeding in much user data
at
> all; most is just field defaults. EXACT_LOCATION (char 240) and
> DESCRLONG (long char) are the ones I might suspect of causing a
problem.
> But the input values appear to be within string limits.
>
> Does anyone have a better method of solving these kinds of problems
in
> SQR. I've checked my code over and over again. I'm clueless.
> Anyone's comment is greatly appreciated.
>
> Daniel Brinkley
> Programmer/Analyst/Worker Bee
> Techneglas, Inc. / Columbus, OH
>
> !******************************
> Begin-Procedure Insert-Incident-Data
> !******************************
> #debugp show 'PROC: Insert-Incident-Data'
> Begin-SQL
> INSERT INTO PS_INCIDENT_DATA
> (
> INCIDENT_NBR,
> INCIDENT_TYPE,
> REG_REGION,
> DANGER_OCC_CD,
> INCIDENT_DT,
> INCIDENT_TIME,
> RECURRENCE,
> RESULT_INJURY_ILL,
> REPORTED_BY_EMPL,
> REPORTED_BY_NONEMP,
> REPORTED_TO,
> DT_REPORTED,
> TIME_REPORTED,
> DT_RECORDED,
> TIME_RECORDED,
> INVESTIGATED,
> EMPLOYER_PREMISES,
> SETID_LOCATION,
> LOCATION,
> LOCATION_NAME,
> COUNTRY,
> ADDRESS1,
> ADDRESS2,
> ADDRESS3,
> ADDRESS4,
> CITY,
> NUM1,
> NUM2,
> HOUSE_TYPE,
> ADDR_FIELD1,
> ADDR_FIELD2,
> ADDR_FIELD3,
> COUNTY,
> STATE,
> POSTAL,
> GEO_CODE,
> IN_CITY_LIMIT,
> EXACT_LOCATION,
> ROAD_CONDITION,
> WEATHER_CONDITION,
> INC_OUTCOME_CAN,
> WCB_JURISDICT_SW,
> CLASS_SUBCLASS,
> EXPOSURE_ST_DT,
> EXPOSURE_ST_TM,
> EXPOSURE_END_DT,
> EXPOSURE_END_TM,
> BUSINESS_TYPE,
> OCCURRENCE_TYPE,
> DATE_TIME_EXACT_SW,
> INCDNT_OP_LOCN_SW,
> SCENE_DESCR,
> INCDNT_TMP_SITE_SW,
> CLAIM_CONTACT_ID,
> HS_CONTACT_EMPLID,
> LOCN_SIZE_CD,
> DISABLED_PROG_SW,
> DIS_PROG_EMPLID,
> RTW_AVAILABLE_SW,
> STREET_NBR,
> STREET_NAME,
> STREET_UNIT_NBR,
> WCB_PO_BOX,
> WCB_RR_NBR,
> INCIDENT_STAT_FRA,
> INCIDENT_TYPE_FRA,
> REP_BY_CLASS_FRA,
> REP_POLICE_SW_FRA,
> REP_POLICE_NAM_FRA,
> LOCATION_CLASS_FRA,
> SIGNER_EMPLID_FRA,
> WHERE_REPORTED_FRA,
> LOCAL_AUTHORITY_UK,
> PUBLIC_PLACE_SW_UK,
> DESCRLONG
> )
> VALUES
> (
> $Incident_Nbr, ! INCIDENT_NBR
> 'I', ! INCIDENT_TYPE
> 'USA', ! REG_REGION
> ' ', ! DANGER_OCC_CD
> $Incident_Dt, ! INCIDENT_DT
> '', ! INCIDENT_TIME
> 'N', ! RECURRENCE
> 'Y', ! RESULT_INJURY_ILL
> $EmplID, ! REPORTED_BY_EMPL
> ' ', ! REPORTED_BY_NONEMP
> ' ', ! REPORTED_TO
> $Incident_Dt, ! DT_REPORTED
> '', ! TIME_REPORTED
> $Incident_Dt, ! DT_RECORDED
> '', ! TIME_RECORDED
> 'N', ! INVESTIGATED
> 'Y', ! EMPLOYER_PREMISES
> 'COL', ! SETID_LOCATION
> ' ', ! LOCATION
> ' ', ! LOCATION_NAME
> 'USA', ! COUNTRY
> ' ', ! ADDRESS1
> ' ', ! ADDRESS2
> ' ', ! ADDRESS3
> ' ', ! ADDRESS4
> ' ', ! CITY
> ' ', ! NUM1
> ' ', ! NUM2
> ' ', ! HOUSE_TYPE
> ' ', ! ADDR_FIELD1
> ' ', ! ADDR_FIELD2
> ' ', ! ADDR_FIELD3
> ' ', ! COUNTY
> ' ', ! STATE
> ' ', ! POSTAL
> ' ', ! GEO_CODE
> ' ', ! IN_CITY_LIMIT
> $Specific_Area, ! EXACT_LOCATION
> ' ', ! ROAD_CONDITION
> ' ', ! WEATHER_CONDITION
> ' ', ! INC_OUTCOME_CAN
> 'Y', ! WCB_JURISDICT_SW
> 0, ! CLASS_SUBCLASS
> '', ! EXPOSURE_ST_DT
> '', ! EXPOSURE_ST_TM
> '', ! EXPOSURE_END_DT
> '', ! EXPOSURE_END_TM
> ' ', ! BUSINESS_TYPE
> ' ', ! OCCURRENCE_TYPE
> 'N', ! DATE_TIME_EXACT_SW
> 'N', ! INCDNT_OP_LOCN_SW
> ' ', ! SCENE_DESCR
> 'N', ! INCDNT_TMP_SITE_SW
> ' ', ! CLAIM_CONTACT_ID
> ' ', ! HS_CONTACT_EMPLID
> ' ', ! LOCN_SIZE_CD
> 'N', ! DISABLED_PROG_SW
> ' ', ! DIS_PROG_EMPLID
> ' ', ! RTW_AVAILABLE_SW
> ' ', ! STREET_NBR
> ' ', ! STREET_NAME
> ' ', ! STREET_UNIT_NBR
> ' ', ! WCB_PO_BOX
> ' ', ! WCB_RR_NBR
> ' ', ! INCIDENT_STAT_FRA
> ' ', ! INCIDENT_TYPE_FRA
> ' ', ! REP_BY_CLASS_FRA
> ' ', ! REP_POLICE_SW_FRA
> ' ', ! REP_POLICE_NAM_FRA
> ' ', ! LOCATION_CLASS_FRA
> ' ', ! SIGNER_EMPLID_FRA
> ' ', ! WHERE_REPORTED_FRA
> ' ', ! LOCAL_AUTHORITY_UK
> ' ', ! PUBLIC_PLACE_SW_UK
> $Activity ! DESCRLONG
> )
> End-SQL
>
> End-Procedure
>
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users