Varchar2 column types changes CLOB when transferring data from SAS to Oracle.

turcay — Tue, 21 Jul 2015 14:01:59 GMT

Hello everyone,

We have trouble related to transferring data from SAS to Oracle.

Some of the columns are transferred from VARCHAR2 to CLOB and what could be done to prevent the data type transformations on SAS. Later, i've got the following error while loading the existing table by typing ORALIB.CRM_YENILEME_LISTE on OUTPUT NAME in ORACLE.Increasing number of the columns which is transformed into CLOB every time i run ORALIB.CRM_YENILEME_LISTE although i take the columns which are last transformed into CLOB as a result of the format inconsistency and i also wanted to prevent it.Also how to find the LIBNAME SYNTAX forming the LIBRARY description of ORALIB LIBRARY. The error i get while trying to send data from ORALIB is given below. I am waiting for your help.

the error i receive typing ORALIB.CRM_YENILEME_LISTE :

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

ERROR: ERROR: ERROR: ORACLE execute error: ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column.

With the occurrence of the above ERROR, the error limit of 1 set by the ERRLIMIT= option has been reached. ROLLBACK has been

issued(Any Rows processed after the last COMMIT are lost).

Total rows processed:

Rows failed : 1

WARNING: File deletion failed for ORALIB.CRM_YENILEME_LISTE.DATA.

the error i receive typing ORALIB.CRM_YENILEME_COGNOS :

Code;

%_eg_conditional_dropds(ORALIB.CRM_YENILEME_COGNOS);

PROC SQL;

CREATE TABLE ORALIB.CRM_YENILEME_COGNOS(label="QUERY_FOR_COGNOSDENEME") AS

SELECT DISTINCT t1.SK_ASEG_CRM,

t1.ID_TERCERO,

t1.NOM_TERCERO,

t1.CUSTOMER_TYPE,

t1.STRATEJIK_MUSTERI,

t1.NUM_POLIZA,

t1.NUM_RENOVACIONES,

t1.TIP,

t1.SUM_of_YAZILAN_PRIM,

t1.SUM_of_KAZANILMIS_PRIM,

t1.SUM_of_ODEME,

t1.SUM_of_TUM_HASAR_ADET,

t1.HASAR_PRIM_ORANI,

t1.FEC_INI_VIG_POLIZA,

t1.FEC_FIN_VIG_POLIZA,

t1.FEC_EMISION_SPTO,

t1.FEC_KAPANIS,

t1.COD_RAMO,

t1.DESC_SECTOR_RAMO,

t1.COD_TIPO_AUTO,

t1.DESC_TIPO_AUTO,

t1.NUM_SIMDIKI_BASAMAK,

t1.HSRSZ_KADEME,

t1.STOPLOSS,

t1.MCA_RENT_A_CAR,

t1.NUM_MODEL_YILI,

t1.COD_PLAKA_IL_KODU,

t1.TXT_PLAKA_NO,

t1.TXT_SASI_NO,

t1.YASAKLI_MUSTERI,

t1.ID_AGENTE,

t1.NOM_AGENTE,

t1.ACENTE_REGIONAL,

t1.ACENTE_OFICINA,

t1.YIY_FLAG,

t1.KAZA_KASKO,

t1.KAZA_TRAFIK,

t1.YANGIN,

t1.DASK,

t1.FERDI_KAZA,

t1.BRANS_850,

t1.BRANS_860,

t1.BRANS_880,

t1.Tramer_Sorgu_Sigorta_Sirketi,

t1.Tramer_Basamak_No,

t1.Tramer_Sorgu_Yururluk,

t1.Police_Bitis_Tarihi,

t1.Yenileme_Son_Durum,

t1.Police_Numarasi,

t1.Sigorta_Sirketi,

t1.Acente,

t1.Basamak,

t1.Prim,

t1.Stoploss_Rentacar,

t1.Plaka_Full

FROM WORK.QUERY_FOR_COGNOSDENEME_0004 t1;

QUIT;

Log;

1 The SAS System 07:24 Monday, July 13, 2015

1 ;*';*";*/;quit;run;

2 OPTIONS PAGENO=MIN;

3 %LET _CLIENTTASKLABEL='TEMMUZ COGNOS';

4 %LET _CLIENTPROJECTPATH='C:\Users\gtoptas\Desktop\YENILEME LISTE_AylıkveGunlukTakıp_13042015.egp';

5 %LET _CLIENTPROJECTNAME='YENILEME LISTE_AylıkveGunlukTakıp_13042015.egp';

7 ODS _ALL_ CLOSE;

8 OPTIONS DEV=ACTIVEX;

9 GOPTIONS XPIXELS=0 YPIXELS=0;

10 FILENAME EGSR TEMP;

11 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=HtmlBlue

11 ! STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/5.1/Styles/HtmlBlue.css") NOGTITLE NOGFOOTNOTE

11 ! GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

13 GOPTIONS ACCESSIBLE;

14 %_eg_conditional_dropds(ORALIB.CRM_YENILEME_COGNOS);

NOTE: Table ORALIB.CRM_YENILEME_COGNOS has been dropped.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.06 seconds

cpu time 0.00 seconds

16 PROC SQL;

17 CREATE TABLE ORALIB.CRM_YENILEME_COGNOS(label="QUERY_FOR_COGNOSDENEME") AS

18 SELECT DISTINCT t1.SK_ASEG_CRM,

19 t1.ID_TERCERO,

20 t1.NOM_TERCERO,

21 t1.CUSTOMER_TYPE,

22 t1.STRATEJIK_MUSTERI,

23 t1.NUM_POLIZA,

24 t1.NUM_RENOVACIONES,

25 t1.TIP,

26 t1.SUM_of_YAZILAN_PRIM,

27 t1.SUM_of_KAZANILMIS_PRIM,

28 t1.SUM_of_ODEME,

29 t1.SUM_of_TUM_HASAR_ADET,

30 t1.HASAR_PRIM_ORANI,

31 t1.FEC_INI_VIG_POLIZA,

32 t1.FEC_FIN_VIG_POLIZA,

33 t1.FEC_EMISION_SPTO,

34 t1.FEC_KAPANIS,

35 t1.COD_RAMO,

36 t1.DESC_SECTOR_RAMO,

37 t1.COD_TIPO_AUTO,

38 t1.DESC_TIPO_AUTO,

39 t1.NUM_SIMDIKI_BASAMAK,

40 t1.HSRSZ_KADEME,

41 t1.STOPLOSS,

42 t1.MCA_RENT_A_CAR,

43 t1.NUM_MODEL_YILI,

44 t1.COD_PLAKA_IL_KODU,

45 t1.TXT_PLAKA_NO,

46 t1.TXT_SASI_NO,

47 t1.YASAKLI_MUSTERI,

48 t1.ID_AGENTE,

49 t1.NOM_AGENTE,

2 The SAS System 07:24 Monday, July 13, 2015

50 t1.ACENTE_REGIONAL,

51 t1.ACENTE_OFICINA,

52 t1.YIY_FLAG,

53 t1.KAZA_KASKO,

54 t1.KAZA_TRAFIK,

55 t1.YANGIN,

56 t1.DASK,

57 t1.FERDI_KAZA,

58 t1.BRANS_850,

59 t1.BRANS_860,

60 t1.BRANS_880,

61 t1.Tramer_Sorgu_Sigorta_Sirketi,

62 t1.Tramer_Basamak_No,

63 t1.Tramer_Sorgu_Yururluk,

64 t1.Police_Bitis_Tarihi,

65 t1.Yenileme_Son_Durum,

66 t1.Police_Numarasi,

67 t1.Sigorta_Sirketi,

68 t1.Acente,

69 t1.Basamak,

70 t1.Prim,

71 t1.Stoploss_Rentacar,

72 t1.Plaka_Full

73 FROM WORK.QUERY_FOR_COGNOSDENEME_0004 t1;

WARNING: The option LABEL is not implemented in the ORACLE engine.

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

ERROR: ERROR: ERROR: ORACLE execute error: ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column.

With the occurrence of the above ERROR, the error limit of 1 set by the ERRLIMIT= option has been reached. ROLLBACK has been

issued(Any Rows processed after the last COMMIT are lost).

Total rows processed: 1

Rows failed : 1

WARNING: File deletion failed for ORALIB.CRM_YENILEME_COGNOS.DATA.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

74 QUIT;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.16 seconds

cpu time 0.00 seconds

76 GOPTIONS NOACCESSIBLE;

79 %LET _CLIENTTASKLABEL=;

80 %LET _CLIENTPROJECTPATH=;

81 %LET _CLIENTPROJECTNAME=;

83 ;*';*";*/;quit;run;

84 ODS _ALL_ CLOSE;

87 QUIT; RUN;

Thank you.

Re: Varchar2 column types changes CLOB when transferring data from SAS to Oracle.

turcay — Wed, 05 Aug 2015 21:33:44 GMT

Hello,

When i want to transfer data from SAS into Oracle via SAS code, the formats of some variables changes from varchar/varchar2 to clob and code causes following error:

ERROR: ORACLE execute error: ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column.

it didn't have any problems before but for the last 3 or 4 transfers, the data type which is varchar in the some parts of the table is transformed into CLOB. To solve the problem temporarily, parts transformed into CLOB are put in the bottom of the table according DBA's recommendations however CLOB parts change everytime they repeat the process.

when i transfer data from SAS Work to any SAS base library, i didn't get same problem.

Could you please help and guide me related this error?

Re: Varchar2 column types changes CLOB when transferring data from SAS to Oracle.

LinusH — Wed, 05 Aug 2015 23:28:32 GMT

I'm not really following your thoughts, and I'm having a hard time believe that SAS creates CLOB objects automatically. Can you verify this?

Ask the SAS admin for the libname. IN the meantime you can see some of options by

libname oralib list;

Or check SASHELP.VLIBNAM.

Also, by adding

options sastrace=',,,d' sastraceloc=saslog;

you can obtain more response from Oracle on what is going on.

Re: Varchar2 column types changes CLOB when transferring data from SAS to Oracle.

Bipasha — Wed, 26 Jan 2022 04:59:03 GMT

Hi,
Have you found the solution for this?

I have faced something similar and solved it by changing the length of the character variables. It seems like the auto transformation occurs if the character length is huge. Though I am not sure what is the threshold value for the length. For now defining the length as 100 worked for me.

Hope this helps. Also let me know if you found any other solution.

Regards,
Bipasha

topic Re: Varchar2 column types changes CLOB when transferring data from SAS to Oracle. in SAS Enterprise Guide

Varchar2 column types changes CLOB when transferring data from SAS to Oracle.

Re: Varchar2 column types changes CLOB when transferring data from SAS to Oracle.

Re: Varchar2 column types changes CLOB when transferring data from SAS to Oracle.

Re: Varchar2 column types changes CLOB when transferring data from SAS to Oracle.