Solved

how to get solution for below requirement ?

Posted on 2015-01-19
6
69 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

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…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

689 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