Skip to Content
0
Former Member
Aug 25, 2008 at 01:11 PM

Oracle varchar2(4000) - only first 25 char displays

91 Views

We have a report built using CR XI 11.0.0.1282 running against Oracle 10.2.0.4.0 that has a on-demand subreport which retrieves data from a stored procedure within a package. The stored procedure returns a cursor defined as follows in the package spec:

TYPE RPT_MAP_DETAILS_TYPE IS RECORD

(SOURCE_VALUE varchar2(4000),

XLATE_VALUE varchar2(4000),

USE_ADD_FLAG varchar2(1),

USE_BYPASS_FLAG varchar2(1),

ADD_TO_CPD_FLAG varchar2(1),

BYPASS_FLAG varchar2(1)

);

TYPE RPT_MAP_DETAILS IS REF CURSOR RETURN RPT_MAP_DETAILS_TYPE;

The subreport uses this stored procedure as its data source:

PROCEDURE GET_TRANSLATIONS_RPT

(ESR_ID_IN IN NUMBER, XLATE_TYPE_IN IN VARCHAR2,

CURSOR_IO OUT RPT_MAP_DETAILS);

Data is selected from different tables and concatonated into the XLATE_VALUE column of the return cursor depending on the XLATE_TYPE_IN value. When I use PL/SQL to execute the stored procedure I get the expecte results. When I use Crystal, I only see the first 25 characters of the XLATE_VALUE column and the first 36 characters of the SOURCE_VALUE column.

An example of the select looks like this (actual table names removed):

SELECT SPECT_IMPORT_SPECIALTY AS SOURCE_VALUE,

DECODE(SPECT_SPEC_ID,NULL,'',

'SPEC_ID='||SPECT_SPEC_ID||', '||

DECODE(SPEC_AUDIT_KEY,NULL,'',

SPEC_DESC||CHR(13)||CHR(10) ||

'CVC Approved='||

NVL(SPEC_CVC_APPROVED_FLAG,'N')||

'; Degree Required='||

NVL(SPEC_PHY_DEG_REQ_FLAG,'N')))

AS XLATE_VALUE,

'Y' AS USE_ADD_FLAG, 'N' AS USE_BYPASS_FLAG,

SPECT_ADD_TO_CPD AS ADD_TO_CPD_FLAG, 'N' AS BYPASS_FLAG

FROM TABLE_NAME_1 T1, TABLE_NAME_2 T2

WHERE T1.SPECT_SPEC_ID = T2.SPEC_ID(+) AND T1.SPECT_ESR_ID = 17;

We ensure that every column is populated; if the particular table used in the query does not employ one of the "flag" columns, a default "N" is substituted for its column value (like the "bypass flag" columns above).

When I use the Browse Field Data option in the Crystal IDE, it tells be the two column sizes are 25 and 36, but the definition of the size of the columns is 4000! How do I get Crystal to see the real sizes of the columns?

Thanks in advance!