Problem:

When running a Database Table query we are receiving an error ORA-01403: no data found, when picx=varchar2 directive is specified, and even though the data is present in the table. The query is as given below:

EXEC SQL
SELECT CUSTOMER_NBR,
		CONTRACT_ID,
		CONTRACT_STATUS,
		EFFECTIVE_DATE,
		ACQUISITION_DATE,
		SETUP_DATE,
		CYCLE_DATE,
		LATEST_END_DATE,
		DT_FOR_INVOICING,
		PREBILL_SPAN,
		LATE_CHRG_DATE,
		LATE_CHARGE_NBR,
		BILL_DATE,
		COLL_FIRST_UNPD,
		CURRENT_AGED,
		LEO_STATUS,
		BRANCH_CO_NBR,
		BRANCH_NBR,
		SUSPEND_DATE,
		CLOSE_BILL_IND,
		LEO_IND
INTO :ALF4000-CONTRACT :ALF4000-STAT-CD
FROM CONTRACT
WHERE CUSTOMER_NBR = :ALFSQL-CUSTOMER-NBR
		AND CONTRACT_ID = :ALFSQL-CONTRACT-ID
END-EXEC.

Resolution:

Use CHARF instead of VARCHAR in PICX directive:

PICX

Purpose

Specifies the default datatype of PIC X variables.

Syntax

PICX={CHARF | VARCHAR2}

Default

CHARF

Usage Notes

This can be entered only on the command line or in a configuration file.

Starting in Pro*COBOL 8.0, the default datatype of PIC X, N, or G variables was changed from VARCHAR2 to CHARF. PICX is provided for backward compatibility. This new default behavior is consistent with the normal COBOL move to semantics. This is a change in behavior for the case where you are inserting a PIC X variable (with MODE=ORACLE) into a VARCHAR2 column. Any trailing blanks which had formerly been trimmed will be preserved. The new default lessens the occurrence of the following anomaly: Using a PIC X bind variable initialized with trailing blanks in a WHERE clause would never match a value with the same number of trailing blanks which was stored in a char column because the bind variable's trailing blanks were stripped before the comparison.

When PICX=VARCHAR2, Oracle treats local CHAR variables in a PL/SQL block like variable-length character values. When PICX=CHARF, however, Oracle treats the CHAR variables like ANSI-compliant, fixed-length character values.

Incident Number: 2264375