[sqr-users] Fetched column value was truncated

["Alexander, Steven" <Steven.Alexander@sanjoseca.gov>]

When we run this program from our desktop (Peoplesoft's sqrw 8.20.06 on
Windows XP), it works.  When we run it from the Peoplesoft Process Scheduler
(Peoplesoft's pssqr 8.20.06 on Solaris), it stops with the following error
message:

 

ORA-01406: fetched column value was truncated
Previous CDV row: 001-294319-DEPT64-4003
 
(SQR 5528) ORACLE OCIStmtFetch error 1406 in cursor 6:
   ORA-01406: fetched column value was truncated
 
Error on line 222:
   (SQR 3725) Bad return fetching row from database.

 

 

Here is the select statement that fails:

 

 

!***************************************************************************
****

begin-procedure read_data_warehouse

  move 0 to #i

begin-select on-error=cdv_error

nvl(rtrim(FUND_CODE), ' ')       &CDV.FUND_CODE

nvl(rtrim(ACCOUNT), ' ')         &CDV.ACCOUNT

nvl(rtrim(CSJ_DEPT2), ' ')       &CDV.CSJ_DEPT2

nvl(rtrim(CLASS_FLD), ' ')       &CDV.CLASS_FLD

nvl(rtrim(DESCR), ' ')           &CDV.DESCR

nvl(rtrim(BUDGET_DEPTID), ' ')   &CDV.BUDGET_DEPTID

nvl(rtrim(SUBDIVISION_LBL), ' ') &CDV.SUBDIVISION_LBL

nvl(rtrim(ASSET_NBR), ' ')       &CDV.ASSET_NBR

  let $acct_cd = &CDV.FUND_CODE || '-' || &CDV.ACCOUNT || '-DEPT'

              || &CDV.CSJ_DEPT2 || '-' || &CDV.CLASS_FLD

  if &CDV.CSJ_DEPT2 = '57'

    let $dw_division = cond(instr($divisions, ' ' || &CDV.SUBDIVISION_LBL ||
' ', 1) = 0,

                            'ADMIN', &CDV.SUBDIVISION_LBL)

  else

    let $dw_division = ' '

  end-if

  put $acct_cd &CDV.DESCR &CDV.BUDGET_DEPTID $dw_division &CDV.ASSET_NBR
into dw(#i)

  add 1 to #i

 from PS_CSJ_DWGL001M_VW CDV

order by CDV.FUND_CODE, CDV.ACCOUNT, CDV.CSJ_DEPT2, CDV.CLASS_FLD

end-select

end-procedure read_data_warehouse

 

!***************************************************************************
****

begin-procedure cdv_error

  add 1 to #num_cdv_errors

  if #num_cdv_errors <= 10

    show $sql-error

    show 'Previous CDV row: ' $acct_cd

    show ' '

  else

    stop

  end-if

end-procedure cdv_error

 

I'm curious as to why my on-error routine doesn't keep the program running,
but I'm more interested in fixing the error.  One hint is that the failed
row has text with extended ASCII (value 194), but the successful row before
it also has that text.  Another possible issue is that I am reading
PS_CSJ_DWGL001M_VW, which is a view of a view in a different database
instance.  I am reading hundreds of rows from that view before the error,
though.  Here is the script for PS_CSJ_DWGL001M_VW:

 

CREATE OR REPLACE VIEW PS_CSJ_DWGL001M_VW

(FUND_CODE, ACCOUNT, CSJ_DEPT2, CLASS_FLD, DESCR, 

 BUDGET_DEPTID, SUBDIVISION_LBL, ASSET_NBR)

AS 

SELECT DISTINCT F_FUND , substr(CHARGE_CODE , 9 ,6) , substr(CHARGE_CODE , 7
,2) , AC_SUBACCT , TITLE , R_DEPT , DACREF , D_ASSET_NUMBER 

FROM  <mailto:GL001M07_V@DW02.X1A> GL001M07_V@DW02.X1A 

WHERE AC_SUBACCT IN
('4001','4003','4006','4008','4010','4011','4012','4032','4042','4043','4044
','4046','4060','4065','4102') 

AND ACTIVE_FLAG = 'A' 

AND ALRE_CODE = 'E'

 

And GL001M07_V is a view with the following script:

 

CREATE OR REPLACE VIEW "FMSDATA"."GL001M07_V" ("DEPT_GROUP",

    "CHARGE_CODE","F_FUND","F_SUBFUND","F_REPORT_CODE","R_DEPT",

    "R_SUBDEPT","R_DEPTFUNC","A_PROGRAM","A_SUBPROG","A_ACTIVITY",

    "D_ASSET_TYPE","D_ASSET_NUMBER","D_ASSET_COMP","P_PROJECT",

    "P_SUBPROJ","AC_ACCOUNT","AC_SUBACCT","AC_DETACCT","S_SOURCE",

    "ACTUAL_ENTERED","ACTUAL_APPROVED","ACTUAL_PROOFED",

    "ACTIVE_FLAG","TITLE","COMMIT_ENTERED","COMMIT_APPROVED",

    "COMMIT_PROOFED","ALRE_CODE","DACREF") AS 

    select dept_group, charge_code, f_fund, f_subfund, f_report_code,
r_dept, 

r_subdept, r_deptfunc, a_program, a_subprog, a_activity, d_asset_type,

d_asset_number, d_asset_comp, p_project, p_subproj, ac_account, ac_subacct, 

ac_detacct, s_source, actual_entered, actual_approved, actual_proofed,
active_flag, 

title, commit_entered, commit_approved, commit_proofed, alre_code, dacref 

from FMSDATA.gl001m07 

 WITH READ ONLY

 

 

 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users