AnsweredAssumed Answered

HowTo: Catering for null values in Database Profile (Oracle)

Question asked by allan.ford577519 on Jun 26, 2017

A  How-To note   

 

Subject :   Catering for null values in Database Profile (Oracle)

 

I believe I have managed to get this working OK.

 

Do any other Boomi customers people have an approach to update of Oracle data, catering for nulls (string, numeric, date)  and willing to describe their approach here ?

 

Catering for string/number/date columns to be null also ..

 

Some notes are:

 

I used a statement Type of: Standard Insert / Update / Delete

 

I used an update statement like this largish example with mixture of string, numeric, date columns in the database.  

 

 

 

I guess the dates and catering for null dates was perhaps tricky part ..   This works for incoming null dates .. 

 

This update statement is suitable for the Oracle database type  :

 

 

 

 

My current thinking is:

 

In this approach I am using:

 

- Set all columns to be of type Character in target database profile fields list .. 

- in update statement using just a ? for columns of type character in database,

- in update statement using to_date(substr(?,1,15),'YYYYMMDD HH24MISS') for columns of type date

- in update statement using nvl(?,null) for columns of type number .. float / integer 

 

i.e. this example

 

UPDATE PIMSDB.BASIC_WELLS
set
PARENT_WELL_CODE=nvl(?,null),
WELL_NAME=?,
WELL_NUMBER=?,
WELL_NUMBER_SORTED=?,
LINE_NAME=?,
SHOTPOINT_NUMBER=nvl(?,null),
GROUND_LEVEL_ELEVATION=nvl(?,null),
KELLY_BUSHING_ELEVATION=nvl(?,null),
SPUD_DATE=to_date(substr(?,1,15),'YYYYMMDD HH24MISS'),
RIG_RELEASE_DATE=to_date(substr(?,1,15),'YYYYMMDD HH24MISS'),
TOTAL_DEPTH_DRILLER=nvl(?,null),
TOTAL_DEPTH_LOGGER=nvl(?,null),
RIG_TYPE_CODE=nvl(?,null),
OPERATOR=?,
DATA_UNITS=?,
STRUCTURE_TYPE=?,
DIST_FROM_SHOTPOINT=?,
DATA_SOURCE=?,
RIG_CONTRACTOR_CODE=?,
RIG_NUMBER=?,
REFERENCE_DATUM=?,
ONSHORE_OFFSHORE=?,
COMMON_DATA_POINT=?,
LOCATION_SOURCE=?,
WELL_COST_ORIG=nvl(?,null),
WELL_CAT_PRIMARY=?,
WELL_CAT_SECONDARY_1=?,
WELL_CAT_SECONDARY_2=?,
WELL_CAT_SECONDARY_3=?,
WELL_INTENT_CODE=?,
WELL_STATUS_INITIAL=?,
WELL_STATUS_CURRENT=?,
FLAG_FULL_HOLE_CORE_CUT=?,
FLAG_SIDE_WALL_CORE_CUT=?,
FLAG_DEVIATED_WELL=?,
FLAG_UNIT_JV=?,
TV_TOTAL_DEPTH=nvl(?,null),
LOCN_REMARKS_1=?,
COMPLETION_DETAILS=?,
WELL_HOLE_TYPE_CODE=?,
ANS_LATITUDE=?,
ANS_LONGITUDE=?,
LATITUDE_HEMISPHERE=?,
LONGITUDE_HEMISPHERE=?,
LATITUDE=nvl(?,null),
LONGITUDE=nvl(?,null),
BH_ANS_LATITUDE=?,
BH_ANS_LONGITUDE=?,
BH_LATITUDE=nvl(?,null),
BH_LONGITUDE=nvl(?,null),
BH_LATITUDE_HEMISPHERE=?,
BH_LONGITUDE_HEMISPHERE=?,
GPR_GEOLOGICAL_PROVINCE_CODE=?,
JVE_JOINT_VENTURE_CODE_INIT=?,
JVE_JOINT_VENTURE_CODE_CURR=?,
FIELD_UID=?,
BUN_BUSINESS_UNIT_CODE=?,
JV_POLY_GROUP_NAME=?,
STA_STATE_CODE=?,
COU_COUNTRY_CODE=?,
EDP_PERSON_ID_INSERT=?,
DATE_INSERT=to_date(substr(?,1,15),'YYYYMMDD HH24MISS'),
EDP_PERSON_ID_UPDATE=?,
DATE_UPDATE=to_date(substr(?,1,15),'YYYYMMDD HH24MISS'),
UNIT_JV_CODE=?,
KB_RT_DATUM_ELEVATION=nvl(?,null),
TOTAL_DEPTH_DATE=to_date(substr(?,1,15),'YYYYMMDD HH24MISS'),
COORD_SYSTEM_ID=?,
WELL_LABEL=?,
API_NUMBER=?,
COUNTY=?,
COMPLETION_TYPE=?,
COMPLETION_STATUS=?
where
WELL_CODE =?

 

 

 

 

I am setting the Data Type for the date columns as being data type Character for fields of the target Database Profile:

 

 

 

I hope this info helps other Boomi / Oracle customers !!  

 

any comments/suggestions/improvements (other approaches) most welcome !!

 

cheers, 

Al

Outcomes