Solved

how to get solution for below requirement ?

Posted on 2015-01-19
6
54 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 76

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Join SQL view with APEX item as the join condition 4 47
PL/SQL Two PAYER_IDs with PAYER_TYPES these two types 4 35
Need a replacement data type in Oracle 6 64
sql query 9 37
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

919 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

13 Experts available now in Live!

Get 1:1 Help Now