SQL*Loader Date

[Msolar]

Using SQL*Loader, can a packed decimal input field be mapped to a Date column? ex.: jdat pic 9(7) comp-3 and is formatted as 'yyyydd'. Need to make it a DATE in the table with mask as 'YYYYMMDD'
Thanks

 

[SantaMufasa]

MSolar,

There are a few issues that I detect with your posting.

1) Oracle does not support packed decimal (also known as COMP-3) data-type conversions. Despite that deficiency, we can build a user-defined function to translate to/from COMP-3.

2) I have a question re: the data you are transmitting:

jdat pic 9(7) comp-3 and is formatted as 'yyyydd'

If you have no month, and only two digits for days, how do you store enough days for the year? (Or did you simply forget a third digit for days?)


Since use mention SQL*Loader, I infer that your incoming data are in flat files. If my inferrence is correct, then I suggest using Oracle's EXTERNAL (i.e., flat-file) table support and we then deal with your COMP-3 data conversion using the user-defined COMP-3 conversion function I mention, above.

Let me know your thoughts,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[oradba101]

Good afternoon,

Couldn't the ZONED keyword be used in this situation?

Regards,



William Chadbourne
Oracle DBA

 

[keigh]

I am trying to run this query but its erroring out saying 08:05:11 [SQL> - 0 row(s), 0.000 secs] [Error Code: 900, SQL State: 42000] ORA-00900: invalid SQL statement
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]

can someone hepl
set head off
set pagesize 0
set feedback off
select ' ADDRESS_TYPE_LOV : '||count(1) from ADDRESS_TYPE_LOV ;
select ' APPUSER : '||count(1) from APPUSER ;
select ' APPUSER_ADDRESS : '||count(1) from APPUSER_ADDRESS ;
select ' APPUSER_ALIAS : '||count(1) from APPUSER_ALIAS ;
select ' APPUSER_CLIENT_ROLE : '||count(1) from APPUSER_CLIENT_ROLE ;
select ' APPUSER_CONTACT : '||count(1) from APPUSER_CONTACT ;
select ' APPUSER_FACILITY : '||count(1) from APPUSER_FACILITY ;
select ' APPUSER_PASSWORD : '||count(1) from APPUSER_PASSWORD ;
select ' APPUSER_RECENT_MEMBER : '||count(1) from APPUSER_RECENT_MEMBER ;
select ' APPUSER_RECENT_PASSWORD : '||count(1) from APPUSER_RECENT_PASSWORD ;
select ' ASSISTANCE_CATEGORY_LOV : '||count(1) from ASSISTANCE_CATEGORY_LOV ;
select ' ASSOCIATION_CODE_LOV : '||count(1) from ASSOCIATION_CODE_LOV ;
select ' AUDIT_EVENT_TYPE : '||count(1) from AUDIT_EVENT_TYPE ;
select ' AUDIT_LOG : '||count(1) from AUDIT_LOG ;
select ' CARE_ASSESSMENT : '||count(1) from CARE_ASSESSMENT ;
select ' CARE_BARRIER_LOV : '||count(1) from CARE_BARRIER_LOV ;
select ' CARE_PLAN_DOMAIN_LOV : '||count(1) from CARE_PLAN_DOMAIN_LOV ;
select ' CARE_PROGRAM : '||count(1) from CARE_PROGRAM ;
select ' CLAIM : '||count(1) from CLAIM ;
select ' CLAIM_LINE : '||count(1) from CLAIM_LINE ;
select ' CLIENT : '||count(1) from CLIENT ;
select ' CLIENT_ADDRESS : '||count(1) from CLIENT_ADDRESS ;
select ' CLIENT_CONTACT : '||count(1) from CLIENT_CONTACT ;
select ' CLIENT_PRIMARY_ADDRESS : '||count(1) from CLIENT_PRIMARY_ADDRESS ;
select ' CLIENT_PRIMARY_CONTACT : '||count(1) from CLIENT_PRIMARY_CONTACT ;
select ' CLIENT_PRODUCT : '||count(1) from CLIENT_PRODUCT ;
select ' CLIENT_PROVIDER : '||count(1) from CLIENT_PROVIDER ;
select ' CLIENT_ROLE : '||count(1) from CLIENT_ROLE ;
select ' CLIENT_ROLE_LINK : '||count(1) from CLIENT_ROLE_LINK ;
select ' CLIENT_ROLE_PERMISSION : '||count(1) from CLIENT_ROLE_PERMISSION ;
select ' CLIENT_TYPE_LOV : '||count(1) from CLIENT_TYPE_LOV ;
select ' CONDITION : '||count(1) from CONDITION ;
select ' CONDITION_CLAIM : '||count(1) from CONDITION_CLAIM ;
select ' CONDITION_CLAIM_LINE : '||count(1) from CONDITION_CLAIM_LINE ;
select ' CONDITION_LOV : '||count(1) from CONDITION_LOV ;
select ' CONSENT_AUTHORITY_LOV : '||count(1) from CONSENT_AUTHORITY_LOV ;
select ' CONSENT_OVERRIDE : '||count(1) from CONSENT_OVERRIDE ;
select ' CONSENT_RESPONSE : '||count(1) from CONSENT_RESPONSE ;
select ' CONSULTATION_LOV : '||count(1) from CONSULTATION_LOV ;
select ' CONSULTATION_REASON_LOV : '||count(1) from CONSULTATION_REASON_LOV ;
select ' CONTACT_TYPE_LOV : '||count(1) from CONTACT_TYPE_LOV ;
select ' DATA_LOAD_DISPLAY_ITEM_LOV : '||count(1) from DATA_LOAD_DISPLAY_ITEM_LOV ;
select ' DATA_LOAD_HISTORY : '||count(1) from DATA_LOAD_HISTORY ;
select ' DATA_LOAD_SUBJECT_ITEM : '||count(1) from DATA_LOAD_SUBJECT_ITEM ;
select ' DATA_LOAD_SUBJECT_LOV : '||count(1) from DATA_LOAD_SUBJECT_LOV ;
select ' DIAGNOSIS : '||count(1) from DIAGNOSIS ;
select ' DISABILITY_CODE_LOV : '||count(1) from DISABILITY_CODE_LOV ;
select ' EDUCATION_LEVEL_LOV : '||count(1) from EDUCATION_LEVEL_LOV ;
select ' ELIGIBILITY_TYPE_LOV : '||count(1) from ELIGIBILITY_TYPE_LOV ;
select ' ENCOUNTER : '||count(1) from ENCOUNTER ;
select ' ENCOUNTER_DIAGNOSIS : '||count(1) from ENCOUNTER_DIAGNOSIS ;
select ' ENROLLMENT : '||count(1) from ENROLLMENT ;
select ' EPISODE : '||count(1) from EPISODE ;
select ' EPISODE_CLAIM_LINE : '||count(1) from EPISODE_CLAIM_LINE ;
select ' ETHNIC_GROUP_LOV : '||count(1) from ETHNIC_GROUP_LOV ;
select ' FACILITY : '||count(1) from FACILITY ;
select ' FACILITY_ADDRESS : '||count(1) from FACILITY_ADDRESS ;
select ' FACILITY_CONTACT : '||count(1) from FACILITY_CONTACT ;
select ' FACILITY_PRIMARY_ADDRESS : '||count(1) from FACILITY_PRIMARY_ADDRESS ;
select ' FACILITY_PRIMARY_CONTACT : '||count(1) from FACILITY_PRIMARY_CONTACT ;
select ' GCN : '||count(1) from GCN ;
select ' GENDER_LOV : '||count(1) from GENDER_LOV ;
select ' HEALTH_PLAN : '||count(1) from HEALTH_PLAN ;
select ' HOW_IDENTIFIED_LOV : '||count(1) from HOW_IDENTIFIED_LOV ;
select ' ICD : '||count(1) from ICD ;
select ' ICD_CONDITION : '||count(1) from ICD_CONDITION ;
select ' LANGUAGE_PREFERENCE_LOV : '||count(1) from LANGUAGE_PREFERENCE_LOV ;
select ' LEVEL_OF_CARE : '||count(1) from LEVEL_OF_CARE ;
select ' LEVEL_OF_CARE_LOV : '||count(1) from LEVEL_OF_CARE_LOV ;
select ' LIVING_ARRANGEMENT_LOV : '||count(1) from LIVING_ARRANGEMENT_LOV ;
select ' MARITAL_STATUS_LOV : '||count(1) from MARITAL_STATUS_LOV ;
select ' MEDICATION : '||count(1) from MEDICATION ;
select ' MEMBER : '||count(1) from MEMBER ;
select ' MEMBER_CONSENT : '||count(1) from MEMBER_CONSENT ;
select ' MEMBER_ELIGIBILITY : '||count(1) from MEMBER_ELIGIBILITY ;
select ' MEMBER_GENERAL_CONSENT : '||count(1) from MEMBER_GENERAL_CONSENT ;
select ' MEMBER_OPT_OUT : '||count(1) from MEMBER_OPT_OUT ;
select ' MEMBER_POPULATION : '||count(1) from MEMBER_POPULATION ;
select ' MEMBER_SOURCE_LINEAGE : '||count(1) from MEMBER_SOURCE_LINEAGE ;
select ' MEMBER_SPECIFIC_CONSENT : '||count(1) from MEMBER_SPECIFIC_CONSENT ;
select ' NDC : '||count(1) from NDC ;
select ' NETWORK : '||count(1) from NETWORK ;
select ' ORGANIZATION : '||count(1) from ORGANIZATION ;
select ' ORGANIZATION_ADDRESS : '||count(1) from ORGANIZATION_ADDRESS ;
select ' ORGANIZATION_CONTACT : '||count(1) from ORGANIZATION_CONTACT ;
select ' ORG_CONFIG : '||count(1) from ORG_CONFIG ;
select ' ORG_PRIMARY_ADDRESS : '||count(1) from ORG_PRIMARY_ADDRESS ;
select ' ORG_PRIMARY_CONTACT : '||count(1) from ORG_PRIMARY_CONTACT ;
select ' ORG_TELECOM_ADDRESS : '||count(1) from ORG_TELECOM_ADDRESS ;
select ' PERMISSION : '||count(1) from PERMISSION ;
select ' PERMISSION_TARGET : '||count(1) from PERMISSION_TARGET ;
select ' PERSON : '||count(1) from PERSON ;
select ' PERSON_ADDRESS : '||count(1) from PERSON_ADDRESS ;
select ' PERSON_ALIAS : '||count(1) from PERSON_ALIAS ;
select ' PERSON_ALIAS_TYPE_LOV : '||count(1) from PERSON_ALIAS_TYPE_LOV ;
select ' PERSON_ALTERNATE_ID : '||count(1) from PERSON_ALTERNATE_ID ;
select ' PERSON_ASSIST_CATEGORY : '||count(1) from PERSON_ASSIST_CATEGORY ;
select ' PERSON_CARE_BARRIER : '||count(1) from PERSON_CARE_BARRIER ;
select ' PERSON_CARE_PROBLEM : '||count(1) from PERSON_CARE_PROBLEM ;
select ' PERSON_CARE_PROGRAM : '||count(1) from PERSON_CARE_PROGRAM ;
select ' PERSON_CONDITION : '||count(1) from PERSON_CONDITION ;
select ' PERSON_CONSULTATION : '||count(1) from PERSON_CONSULTATION ;
select ' PERSON_CONTACT : '||count(1) from PERSON_CONTACT ;
select ' PERSON_DISABILITY : '||count(1) from PERSON_DISABILITY ;
select ' PERSON_MEDICAL_HOME : '||count(1) from PERSON_MEDICAL_HOME ;
select ' PERSON_PRIMARY_ADDRESS : '||count(1) from PERSON_PRIMARY_ADDRESS ;
select ' PERSON_PRIMARY_CONTACT : '||count(1) from PERSON_PRIMARY_CONTACT ;
select ' PERSON_PRODUCT : '||count(1) from PERSON_PRODUCT ;
select ' PERSON_PROVIDER : '||count(1) from PERSON_PROVIDER ;
select ' PERSON_SOURCE : '||count(1) from PERSON_SOURCE ;
select ' PERSON_STATUS : '||count(1) from PERSON_STATUS ;
select ' PERSON_STATUS_LOV : '||count(1) from PERSON_STATUS_LOV ;
select ' PHARMACY : '||count(1) from PHARMACY ;
select ' POPULATION : '||count(1) from POPULATION ;
select ' POPULATION_DETAIL : '||count(1) from POPULATION_DETAIL ;
select ' POPULATION_DET_TYPE_LOV : '||count(1) from POPULATION_DET_TYPE_LOV ;
select ' POPULATION_TYPE_LOV : '||count(1) from POPULATION_TYPE_LOV ;
select ' PRACTICE_GROUP : '||count(1) from PRACTICE_GROUP ;
select ' PRESCRIPTION : '||count(1) from PRESCRIPTION ;
select ' PRIMARY_SPECIALTY : '||count(1) from PRIMARY_SPECIALTY ;
select ' PROCEDURE : '||count(1) from PROCEDURE ;
select ' PRODUCT : '||count(1) from PRODUCT ;
select ' PRODUCT_CONDITION : '||count(1) from PRODUCT_CONDITION ;
select ' PRODUCT_TYPE_LOV : '||count(1) from PRODUCT_TYPE_LOV ;
select ' PROVIDER : '||count(1) from PROVIDER ;
select ' PROVIDER_ADDRESS : '||count(1) from PROVIDER_ADDRESS ;
select ' PROVIDER_ALTERNATE_ID : '||count(1) from PROVIDER_ALTERNATE_ID ;
select ' PROVIDER_CONTACT : '||count(1) from PROVIDER_CONTACT ;
select ' PROVIDER_LINEAGE : '||count(1) from PROVIDER_LINEAGE ;
select ' PROVIDER_NETWORK : '||count(1) from PROVIDER_NETWORK ;
select ' PROVIDER_PARTICIPATION : '||count(1) from PROVIDER_PARTICIPATION ;
select ' PROVIDER_POPULATION : '||count(1) from PROVIDER_POPULATION ;
select ' PROVIDER_PRACTICE_GROUP : '||count(1) from PROVIDER_PRACTICE_GROUP ;
select ' PROVIDER_SPECIALTY : '||count(1) from PROVIDER_SPECIALTY ;
select ' PROVIDER_TYPE : '||count(1) from PROVIDER_TYPE ;
select ' PROVIDER_TYPE_LOV : '||count(1) from PROVIDER_TYPE_LOV ;
select ' RACE_LOV : '||count(1) from RACE_LOV ;
select ' RELATIONSHIP_CODE_LOV : '||count(1) from RELATIONSHIP_CODE_LOV ;
select ' RELIGIOUS_AFFILIATION_LOV : '||count(1) from RELIGIOUS_AFFILIATION_LOV ;
select ' RISK_TYPE_LOV : '||count(1) from RISK_TYPE_LOV ;
select ' SECURITY_QUESTION : '||count(1) from SECURITY_QUESTION ;
select ' SELF_REPORTED_MEDICATION : '||count(1) from SELF_REPORTED_MEDICATION ;
select ' SERVICE_CODE : '||count(1) from SERVICE_CODE ;
select ' SERVICE_CODE_GROUP : '||count(1) from SERVICE_CODE_GROUP ;
select ' SERVICE_MEDICATION : '||count(1) from SERVICE_MEDICATION ;
select ' SERVICE_UNMAPPED : '||count(1) from SERVICE_UNMAPPED ;
select ' SEVERITY_CODE_LOV : '||count(1) from SEVERITY_CODE_LOV ;
select ' SOURCE_SYSTEM : '||count(1) from SOURCE_SYSTEM ;
select ' SPECIALTY_LOV : '||count(1) from SPECIALTY_LOV ;
select ' STATE_LOV : '||count(1) from STATE_LOV ;
select ' STATUS_REASON_LOV : '||count(1) from STATUS_REASON_LOV ;
select ' STD_PERSON_ADDRESS : '||count(1) from STD_PERSON_ADDRESS ;
select ' STD_PERSON_SOURCE : '||count(1) from STD_PERSON_SOURCE ;
select ' STD_PROVIDER : '||count(1) from STD_PROVIDER ;
select ' STD_PROVIDER_ADDRESS : '||count(1) from STD_PROVIDER_ADDRESS ;
select ' SUBSCRIBER : '||count(1) from SUBSCRIBER ;
select ' SUBSCRIBER_ENROLLMENT : '||count(1) from SUBSCRIBER_ENROLLMENT ;
select ' TCC : '||count(1) from TCC ;
select ' TEST : '||count(1) from TEST ;
select ' UNIT_OF_MEASURE_LOV : '||count(1) from UNIT_OF_MEASURE_LOV ;
select ' URI_REGISTRY : '||count(1) from URI_REGISTRY ;
select ' CLIENT_CARE_PROGRAM : '||count(1) from CLIENT_CARE_PROGRAM ;
select ' DATALOADHISTORYBYDISPLAYITEM : '||count(1) from DATALOADHISTORYBYDISPLAYITEM ;
select ' GETBARRIERTOCAREDATA : '||count(1) from GETBARRIERTOCAREDATA ;
select ' GETCAREMANAGEMENTPROGRAMDATA : '||count(1) from GETCAREMANAGEMENTPROGRAMDATA ;
select ' GETCAREPROBLEMDATA : '||count(1) from GETCAREPROBLEMDATA ;
select ' GETCLAIMCONDITION : '||count(1) from GETCLAIMCONDITION ;
select ' GETCLAIMLINECONDITION : '||count(1) from GETCLAIMLINECONDITION ;
select ' GETCONDITIONDATA : '||count(1) from GETCONDITIONDATA ;
select ' GETDIAGNOSESDATA : '||count(1) from GETDIAGNOSESDATA ;
select ' GETDIAGNOSTICTESTDATA : '||count(1) from GETDIAGNOSTICTESTDATA ;
select ' GETENCOUNTERDATA : '||count(1) from GETENCOUNTERDATA ;
select ' GETEPISODECONDITIONDETAIL : '||count(1) from GETEPISODECONDITIONDETAIL ;
select ' GETEPISODECONDITIONSUMMARY : '||count(1) from GETEPISODECONDITIONSUMMARY ;
select ' GETEPISODEDIAGNOSTICTESTDATA : '||count(1) from GETEPISODEDIAGNOSTICTESTDATA ;
select ' GETEPISODEENCOUNTERDATA : '||count(1) from GETEPISODEENCOUNTERDATA ;
select ' GETEPISODELABTESTDATA : '||count(1) from GETEPISODELABTESTDATA ;
select ' GETEPISODEPRESCRIPTIONDATA : '||count(1) from GETEPISODEPRESCRIPTIONDATA ;
select ' GETEPISODEPROCEDUREDATA : '||count(1) from GETEPISODEPROCEDUREDATA ;
select ' GETLABTESTDATA : '||count(1) from GETLABTESTDATA ;
select ' GETMEMBERDMPROGRAMDATA : '||count(1) from GETMEMBERDMPROGRAMDATA ;
select ' GETMEMBERHEALTHPLANDATA : '||count(1) from GETMEMBERHEALTHPLANDATA ;
select ' GETMEMBERPRESCRIPTIONDATA : '||count(1) from GETMEMBERPRESCRIPTIONDATA ;
select ' GETMEMBERPRESCRIPTIONDETAIL : '||count(1) from GETMEMBERPRESCRIPTIONDETAIL ;
select ' GETMEMBERPRESCRIPTIONSUMMARY : '||count(1) from GETMEMBERPRESCRIPTIONSUMMARY ;
select ' GETMEMBERPROVIDERDATA : '||count(1) from GETMEMBERPROVIDERDATA ;
select ' GETMEMBERSERVICEMEDDETAIL : '||count(1) from GETMEMBERSERVICEMEDDETAIL ;
select ' GETMEMBERSERVICEMEDSUMMARY : '||count(1) from GETMEMBERSERVICEMEDSUMMARY ;
select ' GETMEMBERSUBSCRIBERDATA : '||count(1) from GETMEMBERSUBSCRIBERDATA ;
select ' GETPROCEDUREDATA : '||count(1) from GETPROCEDUREDATA ;
select ' GETPROVIDERNETWORKDATA : '||count(1) from GETPROVIDERNETWORKDATA ;
select ' MEMBERCONDITIONSUMMARY : '||count(1) from MEMBERCONDITIONSUMMARY ;
select ' MEMBERDIAGNOSISCONDITIONDETAIL : '||count(1) from MEMBERDIAGNOSISCONDITIONDETAIL ;
select ' MEMBER_2_PROVIDER : '||count(1) from MEMBER_2_PROVIDER ;

 

[oradba101]

Greetings,

It would probably be better and highly recommended to start a new thread as your query (no pun intended) is not related to this thread at all.

Regards,



William Chadbourne
Oracle DBA