Deleting and replacing an Oracle table is causing issues and a passthrough wont work. What will?

[mohassan99]

PROC DELETE DATA=HOA.OBSER_QA;
RUN;

DATA HOA.OBSER_QA;
 SET D.SUMMARY&YEARMTH._0762;
 LOAD_DATE=TODAY(); 
 FORMAT LOAD_DATE MMDDYY10.;
 LENGTH LOAD_BY $20;
 LOAD_BY="&_CLIENTUSERID.";
RUN;

PROC DELETE DATA=HOA.OBSER_QA;
RUN;

DATA HOA.OBSER_QA;
	SET D.SUMMARY&YEARMTH._0762;
	LOAD_DATE=TODAY(); 
	FORMAT LOAD_DATE MMDDYY10.;
	LENGTH LOAD_BY $20;
	LOAD_BY="&_CLIENTUSERID.";
RUN;

Resource: http://support.sas.com/kb/40/132.html

The error stops the process due to the options in SAS and/or Oracle.

I cant use a pass through because the replacement dataset or lines are from a SAS dataset.

LOG:

1                                                          The SAS System                               11:54 Tuesday, July 31, 2018

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='p05_Load';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='\\nashq1\SAS_HOA\ETL
5        ! Process\Dashboards\AdministrativeObservation\pgm\ELM_AdminObser_Official_MH.egp';
6          %LET _CLIENTPROJECTPATHHOST='LHQ08907';
7          %LET _CLIENTPROJECTNAME='ELM_AdminObser_Official_MH.egp';
8          %LET _SASPROGRAMFILE='\\Nashq1\sas_hoa\ETL Process\Dashboards\AdministrativeObservation\pgm\p05_Load.sas';
9          %LET _SASPROGRAMFILEHOST='LHQ08907';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         FILENAME EGSR TEMP;
14         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
15             STYLE=HTMLBlue
16             STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
17             NOGTITLE
18             NOGFOOTNOTE
19             GPATH=&sasworklocation
20             ENCODING=UTF8
21             options(rolap="on")
22         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
23         
24         /* Start of Node: p06_Load.sas */
25         options options sastrace=',,,db' sastraceloc=saslog;
26         
27         LIBNAME HOA 	ORACLE USER="&USR.[HOA]" PW="&PWD" PATH="@HOAP"  SCHEMA=HOA;
NOTE: Libref HOA was successfully assigned as follows: 
      Engine:        ORACLE 
      Physical Name: @HOAP
28         
29         PROC DELETE DATA=HOA.ADMIN_QA;
30         RUN;

  9090 1848685467 tkvercn1 0 DELETE (
ORACLE_1: Prepared: on connection 1 9091 1848685467 tkvercn1 0 DELETE (
SELECT * FROM HOA.ADMIN_QA 9092 1848685467 tkvercn1 0 DELETE (
  9093 1848685467 tkvercn1 0 DELETE (
  9094 1848685467 tkvercn1 0 DELETE (
ORACLE_2: Executed: on connection 1 9095 1848685467 tkvercn1 0 DELETE (
DROP TABLE  HOA.ADMIN_QA 9096 1848685467 tkvercn1 0 DELETE (
  9097 1848685467 tkvercn1 0 DELETE (
NOTE: Deleting HOA.ADMIN_QA (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
      real time           0.26 seconds
      cpu time            0.03 seconds
      

31         
32         DATA HOA.ADMIN_QA;
33         	SET D.SUMMARY&YEARMTH._0169;
34         	LOAD_DATE=TODAY();
35         	FORMAT LOAD_DATE MMDDYY10.;
36         	LENGTH LOAD_BY $20;
37         	LOAD_BY="&_CLIENTUSERID.";
38         RUN;
2                                                          The SAS System                               11:54 Tuesday, July 31, 2018


  9098 1848685468 tkvercn1 0 DATASTEP
ORACLE_3: Prepared: on connection 1 9099 1848685468 tkvercn1 0 DATASTEP
SELECT * FROM HOA.ADMIN_QA 9100 1848685468 tkvercn1 0 DATASTEP
  9101 1848685468 tkvercn1 0 DATASTEP
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
  9102 1848685468 tkvercn1 0 DATASTEP
ORACLE_4: Executed: on connection 2 9103 1848685468 tkvercn1 0 DATASTEP
CREATE TABLE HOA.ADMIN_QA(YEARMTH DATE,PPG VARCHAR2 (64),LOB VARCHAR2 (56),PLAN_PARTNER VARCHAR2 (16),SEGMENT VARCHAR2 
(64),FACILITY VARCHAR2 (800),FACILITY_ID VARCHAR2 (800),SUM_Administrative NUMBER ,SUM_MEMBER NUMBER ,Administrative_PTMPY NUMBER 
(7,2),LOAD_DATE DATE,LOAD_BY VARCHAR2 (80)) 9104 1848685468 tkvercn1 0 DATASTEP
  9105 1848685468 tkvercn1 0 DATASTEP
  9106 1848685468 tkvercn1 0 DATASTEP
ORACLE_5: Prepared: on connection 2 9107 1848685468 tkvercn1 0 DATASTEP
INSERT  INTO HOA.ADMIN_QA 
(YEARMTH,PPG,LOB,PLAN_PARTNER,SEGMENT,FACILITY,FACILITY_ID,SUM_Administrative,SUM_MEMBER,Administrative_PTMPY,LOAD_DATE,LOAD_BY) 
VALUES 
(TO_DATE(:YEARMTH,'DDMONYYYY','NLS_DATE_LANGUAGE=American'),:PPG,:LOB,:PLAN_PARTNER,:SEGMENT,:FACILITY,:FACILITY_ID,:SUM_Administrat
ive,:SUM_MEMBER,:Administrative_PTMPY,TO_DATE(:LOAD_DATE,'DDMONYYYY','NLS_DATE_LANGUAGE=American'),:LOAD_BY) 9108 1848685468 
tkvercn1 0 DATASTEP
  9109 1848685468 tkvercn1 0 DATASTEP
**NOTE**: ORACLE_5 on connection 2The Execute statements associated with this Insert statement are suppressed due to SASTRACE 
brief setting-SASTRACE=',,,bd'. Remove the 'b' to get full trace. 9110 1848685468 tkvercn1 0 DATASTEP
NOTE: There were 7103 observations read from the data set D.SUMMARY201807_0169.
NOTE: The data set HOA.ADMIN_QA has 7103 observations and 12 variables.
NOTE: DATA statement used (Total process time):
      real time           0.92 seconds
      cpu time            0.07 seconds
      

39         
40         PROC DELETE DATA=HOA.OBSER_QA;
41         RUN;

  9111 1848685468 tkvercn1 0 DELETE (
ORACLE_717: Prepared: on connection 1 9112 1848685468 tkvercn1 0 DELETE (
SELECT * FROM HOA.OBSER_QA 9113 1848685468 tkvercn1 0 DELETE (
  9114 1848685468 tkvercn1 0 DELETE (
  9115 1848685469 tkvercn1 0 DELETE (
ORACLE_718: Executed: on connection 1 9116 1848685469 tkvercn1 0 DELETE (
DROP TABLE  HOA.OBSER_QA 9117 1848685469 tkvercn1 0 DELETE (
  9118 1848685469 tkvercn1 0 DELETE (
NOTE: Deleting HOA.OBSER_QA (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
      real time           0.11 seconds
      cpu time            0.00 seconds
      

42         
43         DATA HOA.OBSER_QA;
44         	SET D.SUMMARY&YEARMTH._0762;
45         	LOAD_DATE=TODAY();
46         	FORMAT LOAD_DATE MMDDYY10.;
47         	LENGTH LOAD_BY $20;
48         	LOAD_BY="&_CLIENTUSERID.";
49         RUN;

  9119 1848685469 tkvercn1 0 DATASTEP
3                                                          The SAS System                               11:54 Tuesday, July 31, 2018

ORACLE_719: Prepared: on connection 1 9120 1848685469 tkvercn1 0 DATASTEP
SELECT * FROM HOA.OBSER_QA 9121 1848685469 tkvercn1 0 DATASTEP
  9122 1848685469 tkvercn1 0 DATASTEP
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
  9123 1848685469 tkvercn1 0 DATASTEP
ORACLE_720: Executed: on connection 2 9124 1848685469 tkvercn1 0 DATASTEP
CREATE TABLE HOA.OBSER_QA(YEARMTH DATE,PPG VARCHAR2 (64),LOB VARCHAR2 (56),PLAN_PARTNER VARCHAR2 (16),SEGMENT VARCHAR2 
(64),FACILITY VARCHAR2 (800),FACILITY_ID VARCHAR2 (800),SUM_Observation NUMBER ,SUM_MEMBER NUMBER ,Observation_PTMPY NUMBER 
(7,2),LOAD_DATE DATE,LOAD_BY VARCHAR2 (80)) 9125 1848685469 tkvercn1 0 DATASTEP
  9126 1848685469 tkvercn1 0 DATASTEP
  9127 1848685469 tkvercn1 0 DATASTEP
ORACLE_721: Prepared: on connection 2 9128 1848685469 tkvercn1 0 DATASTEP
INSERT  INTO HOA.OBSER_QA 
(YEARMTH,PPG,LOB,PLAN_PARTNER,SEGMENT,FACILITY,FACILITY_ID,SUM_Observation,SUM_MEMBER,Observation_PTMPY,LOAD_DATE,LOAD_BY) VALUES 
(TO_DATE(:YEARMTH,'DDMONYYYY','NLS_DATE_LANGUAGE=American'),:PPG,:LOB,:PLAN_PARTNER,:SEGMENT,:FACILITY,:FACILITY_ID,:SUM_Observation
,:SUM_MEMBER,:Observation_PTMPY,TO_DATE(:LOAD_DATE,'DDMONYYYY','NLS_DATE_LANGUAGE=American'),:LOAD_BY) 9129 1848685469 tkvercn1 0 
DATASTEP
  9130 1848685469 tkvercn1 0 DATASTEP
**NOTE**: ORACLE_721 on connection 2The Execute statements associated with this Insert statement are suppressed due to SASTRACE 
brief setting-SASTRACE=',,,bd'. Remove the 'b' to get full trace. 9131 1848685469 tkvercn1 0 DATASTEP
ERROR: ERROR: ERROR: ORACLE execute error: ORA-01438: value larger than specified precision allowed for this 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: 15422 
      Rows failed         : 1
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 15430 observations read from the data set D.SUMMARY201807_0762.
WARNING: The data set HOA.OBSER_QA may be incomplete.  When this step was stopped there were 15429 observations and 12 variables.
ERROR: ROLLBACK issued due to errors for data set HOA.OBSER_QA.DATA.
NOTE: DATA statement used (Total process time):
      real time           1.50 seconds
      cpu time            0.26 seconds
      

50         
51         
52         
53         
54         %LET _CLIENTTASKLABEL=;
55         %LET _CLIENTPROCESSFLOWNAME=;
56         %LET _CLIENTPROJECTPATH=;
57         %LET _CLIENTPROJECTPATHHOST=;
58         %LET _CLIENTPROJECTNAME=;
59         %LET _SASPROGRAMFILE=;
60         %LET _SASPROGRAMFILEHOST=;
61         
62         ;*';*";*/;quit;run;
63         ODS _ALL_ CLOSE;
64         
65         
66         QUIT; RUN;
67         

[ChrisNZ]

You show us a data step and an SQL error?

Run this before the code

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

and show us the full log please

[mohassan99]

As you wish.  Done.

[ChrisNZ]

Marked as spam to avoid duplicates.

[mohassan99]

I didn't intend to post a duplicate, but move back to SAS rather than a pass-through. I have a lot going on, maybe its that my Dad passed, maybe that this is one of 10 things I'm stuck on and this is where I'm least stuck. This post is SAS, the other is at Oracle PT. I thought that was a sufficient difference for another post.

[ChrisNZ]

It is best to avoid attached documents, especially MS documents, as these can contain nasty payloads. 

Many are not allowed, or are unwilling to download such documents.

It not not necessary here, Just post the log using {i} to format it. or attach a text file please.

[mohassan99]

To clarify the issue:

How do I figure out which column is causing the problem: ORACLE execute error: ORA-01438: value larger than specified precision allowed for this column?

I am running SAS code and getting an incomplete dataset with an Oracle error.

I've searched the internet and know that this cannot be caused by a VARCHAR. I am not sure if it can be a date, but I'm assuming not though I don’t have proof. I haven't seen anything saying it can or cant.

There are three NUMBER variables in the dataset I'm trying to update. Specifically,

DESCRIBE OBSER_QA:

Name	Null	Type
YEARMTH		DATE
PPG		VARCHAR2(64)
LOB		VARCHAR2(56)
PLAN_PARTNER		VARCHAR2(16)
SEGMENT		VARCHAR2(64)
FACILITY		VARCHAR2(800)
FACILITY_ID		VARCHAR2(800)
SUM_OBSERVATION		NUMBER
SUM_MEMBER		NUMBER
OBSERVATION_PTMPY		NUMBER(7,2)
LOAD_DATE		DATE
LOAD_BY		VARCHAR2(80)

My understanding is that Number = Number(38,0) = Number(*,0), right?

I’m taking values from a SAS table and putting them into this Oracle one when I get the error.

ERROR: ORACLE execute error: ORA-01438: value larger than specified precision allowed for this column.

I assumed the issue was OBSERVATION_PTMPY since it’s the only one that’s limited. NUMBER(8,2) -> ERROR, NUMBER(9,2) -> ERROR, NUMBER(*,2) -> NUMBER(38,2) -> ERROR.

I also looked at the max values for the numeric columns I was bringing in:

M_OBS	M_MBR	M_OBSPTMPY
451	126628	120000

Precisions of 3, 6, and 2, right?

[ChrisNZ]

Thank you for the log.

The help you are now seeking is for a different error. The message you first reported was 
"ERROR: This SQL statement will not be passed to the DBMS for processing because it involves a join across librefs with different connection properties."

It is difficult to help when posts shift like this.

About the message now questioned "ERROR: ORACLE execute error: ORA-01438: value larger than specified precision allowed for this column." , Oracle is always very thrifty when giving information about its errors. 

Since you have identified possible candidate values, I would just just try inserting these see if they trigger the error.

Otherwise I'd look the values for OBSERVATION_PTMPY first (still by trying to insert).

Trial and error will help you isolate what makes Oracle choke.

Number(38,0) = Number(*,0), right?

Yes

[mohassan99]

When I do:

DATA HOA.SUMMARY201807_0762;
	SET D.SUMMARY201807_0762;
RUN;

I get:

ERROR: ERROR: ERROR: ORACLE execute error: ORA-01438: value larger than specified precision allowed for this 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: 15422 
      Rows failed         : 1

[ChrisNZ]

That's same message you had.

You need to:

- Identify which value generated the message as discussed above. Do do this only insert one record with the value you suspect is the culprit and see if the message is generated.

- Decide what to do then. Either you modify the SAS value before insertion, or you ask the Oracle people to change the field type to accept your large and valid value..

[mohassan99]

Thanks again.

I mentioned the above because in that case I was not doing a delete then a create table which sql seems to interpret as truncate and insert. (Is that right?)

I was doing a set from a sas libname to an oracle libname with a table name that did not previoiusly exist in that oracle db and got the same error.

Originially I thought the dataset had a predetermined column value type and that a value was being inserted that could not be displayed. The simple set to a new dataset makes me think that that's not the issue.

Am I wrong? Why?

I didn't know what value to suspect. I narrowed down the column. As Obsertations_PTMPY.

I suspected the value was that of Obs 15430, based on the log, but that's ".". Then I pulled 15422-15450. 15422 looks questionable. It is 108000.0. It looks like an 8.1 (w.d) when the variable format is 8.2.

[ChrisNZ]

1. I mentioned the above because in that case I was not doing a delete then a create table which sql seems to interpret as truncate and insert. (Is that right?)

- Use the trace option I gave you and you'll see the code sent to Oracle

I suspected the value was that of Obs 15430, based on the log, but that's ".". Then I pulled 15422-15450. 15422 looks questionable. 

- So have you isolated the row? and then the column? Just insert this to see if the error message comes.

Originially I thought the dataset had a predetermined column value type and that a value was being inserted that could not be displayed. The simple set to a new dataset makes me think that that's not the issue.

- I don't see how using SET when inserting would avoid the problem.

a table name that did not previoiusly exist

- I doubt SAS would create an Oracle column with type NUMBER(7,2). If it does that is worth looking into. Again look at the code sent to Oracle 

[mohassan99]

Problem solved, it seems. We didn't retain the structure and just insert new rows.  We created a table from scratch or replaced one. We also changed the format of the problem variable.

We used format = 8.

The following went through without errors:

proc sql;
     create table hoa.obser_qa_matt as
     select 
          yearmth
          ,ppg
          ,plan_partner
          ,segment
          ,facility
          ,facility_id
          ,sum_observation
          ,sum_member
          ,observation_ptmpy format = 8.
     from work.summary201807_07626950;
quit;

[ChrisNZ]

Good! This number(7.2) variable always looked like the culprit, and as long as Oracle had that column you were going to have trouble.

So you took the second option of:  

Either you modify the SAS value before insertion, or you [..] change the field type to accept your large and valid value.

which is the better one as you can keep your data intact,