Solved

how to get solution for below requirement ?

Posted on 2015-01-19
6
52 Views
Last Modified: 2016-06-18
HI Expert,
i have below  four tables.
1-Source table
2-Target Table
3-Validation table
4-log table

i have source table  having all column datatypes are varchar2. but in my tar get all columns are not varchar2 and datatype size also not same with source table.

1- if data is proper  in source table as expected in target table then it will insert in target table and validation table.
2-if data is not proper and wht the errors it will insert in log table

below is my source table :
  CREATE TABLE  SOURCE_TABLE 
  (
     EMP_ID    VARCHAR2(20 BYTE),
     EMP_NAME  VARCHAR2(20 BYTE),
     EMP_LOC   VARCHAR2(20 BYTE),
     contactno    VARCHAR2(20 BYTE)
  );
 

Open in new window


input data:
Insert into  source_table (EMP_ID,EMP_NAME,EMP_LOC,CONTACTNO) values ('11','Robert','USA','777-889-000');
Insert into  source_table (EMP_ID,EMP_NAME,EMP_LOC,CONTACTNO) values ('22x','KING','USA','777-888-000');
Insert into  source_table (EMP_ID,EMP_NAME,EMP_LOC,CONTACTNO) values ('11','Robert','BLR','777-889-000');
Insert into  source_table (EMP_ID,EMP_NAME,EMP_LOC,CONTACTNO) values ('33','Robert thomson','SCR','888-765-900');
Insert into  source_table (EMP_ID,EMP_NAME,EMP_LOC,CONTACTNO) values ('35','king','BLR','779+888-000');
Insert into  source_table (EMP_ID,EMP_NAME,EMP_LOC,CONTACTNO) values ('36','king','BLR','777-886-000');

Open in new window



expected in target table :


  CREATE TABLE  TARGET_TABLE
  (
     EMP_ID     NUMBER NOT NULL ENABLE,
     EMP_NAME   VARCHAR2(7 BYTE),
     EMP_LOC    VARCHAR2(20 BYTE),
     CONTACTNO  VARCHAR2(200 BYTE),
    CONSTRAINT  TARGET_TABLE_PK  PRIMARY KEY ( EMP_ID ) );

input data:
Insert into target_table (EMP_ID,EMP_NAME,EMP_LOC,CONTACTNO) values (36,'king	','BLR','777-886-000');

Open in new window


expected in validation table:
CREATE TABLE   VALIDATION_TABLE 
  (
    EMP_ID NUMBER NOT NULL ENABLE,
    CONSTRAINT  VALIDATION_TABLE_PK  PRIMARY KEY ( EMP_ID )); 

Open in new window


input data:
Insert into VALIDATION_TABLE (EMP_ID) values (36);

Open in new window



expected in  log table
   CREATE TABLE   LOG_TABLE 
  (
     EMP_ID         VARCHAR2(20 BYTE),
     ERROR_DATE     VARCHAR2(20 BYTE),
     ERROR_COLUMN   VARCHAR2(20 BYTE),
     ERROR_MESSAGE  VARCHAR2(20 BYTE)
  );

Open in new window


Insert into log_table (EMP_ID,ERROR_DATE,ERROR_COLUMN,ERROR_MESSAGE) values ('22x','19-JAN-15','EMP_ID','invalid  id.data type should be number');
Insert into log_table (EMP_ID,ERROR_DATE,ERROR_COLUMN,ERROR_MESSAGE) values ('11','19-JAN-15','EMP_ID','Dupliacte id ');
Insert into log_table (EMP_ID,ERROR_DATE,ERROR_COLUMN,ERROR_MESSAGE) values ('33','19-JAN-15','EMP_NAME','dat type size is too lenghty');
Insert into log_table (EMP_ID,ERROR_DATE,ERROR_COLUMN,ERROR_MESSAGE) values ('35','19-JAN-15','CONTACTNO','not in proper format');

Open in new window


Regards
Thomos
0
Comment
Question by:deve_thomos
6 Comments
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
This look like a duplicate posting.

deve_thomos: can you delete this one?
0
 

Author Comment

by:deve_thomos
Comment Utility
no markgeer , its not duplicate  question ..
if it is duplicate then  can  you please tell me where it posted previously ?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
This question shows up twice for me in both Firefox and Internet Explorer.  This may be a bug on the web site, since the comment I posted in one of these, and your reply, now both show up in both copies of this question that I can see in my browsers.

I don't see multiple copies of other questions from today.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
I'm not clear on exactly what you are wanting to do.

I'm guessing some form of DML error logging.

Take a look at the online docs:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9014.htm#BCEGDJDJ

If you want something different please clarify your requirements.
0
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 500 total points
Comment Utility
You mean if data is not inserted in target table then insert in log_table

create validation table same as target table by this way you can identify those records which don't qualify the size of the target table, duplicate values also can be handled by dup_val_on_index exception, modify exceptions and add your own as per the need.. check below

a simple cursor can do this

declare
cursor c1 is
select rowid,* from source_table;
begin
for l1 in c1
loop
begin
insert into validation_table
select * from source_table where rowid=l1.rowid;
insert into target_table
select * from source_table where rowid=l1.rowid;
commit;
exception
when DUP_VAL_ON_INDEX
then
insert into log_table
select * from source_table where rowid=l1.rowid;
commit;
when others
then
insert into log_table
select * from source_table where rowid=l1.rowid;
commit;
end;
end loop;
end;

this is a generic approach which i thought, you can modify  it further based on your need.. if data is proper first it gets inserted in validation table and after that it would get inserted in target table(make sure the target table structure and validation table structure are same)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now