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.
Click image to register for webinar
