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.
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?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.