Solved

how to get solution for below requirement ?

Posted on 2015-01-19
6
61 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 35

Expert Comment

by:Mark Geerlings
ID: 40558189
This look like a duplicate posting.

deve_thomos: can you delete this one?
0
 

Author Comment

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

Expert Comment

by:Mark Geerlings
ID: 40558410
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40559767
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
ID: 40563612
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

726 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