Desktop productivity for business analysts and programmers

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

Reply
Super Contributor
Posts: 381

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

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';

6

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

12

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

15

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

75

76         GOPTIONS NOACCESSIBLE;

77

78

79         %LET _CLIENTTASKLABEL=;

80         %LET _CLIENTPROJECTPATH=;

81         %LET _CLIENTPROJECTNAME=;

82

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

84         ODS _ALL_ CLOSE;

85

86

87         QUIT; RUN;

88

Thank you.

Super Contributor
Posts: 381

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

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?

Esteemed Advisor
Posts: 5,198

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

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.

Data never sleeps
Ask a Question
Discussion stats
  • 2 replies
  • 1029 views
  • 0 likes
  • 2 in conversation