| multi insert [message #656837] |
Thu, 20 October 2016 05:53  |
 |
premier10
Messages: 5
Registered: October 2016
|
Junior Member |
|
|
I want to be able to log duplicates from a insert procedure with just a dbms_output.put_line of duplicate records as shown below 'Dan' 'Morgan'.
I want to only show when both fields are duplicated. Can anyone advise?
I have the following tables
CREATE TABLE temp_table (
job_title VARCHAR2(20),
empname varchar2(30));
--------------------------
CREATE TABLE job (
job_id number,
job_title VARCHAR2(20));
------------------------------
CREATE TABLE emplyee (
empid number,
empname varchar2(30),
job_id number
);
------------------------------
INSERT INTO temp_table VALUES ( 'Dan', 'Morgan');
INSERT INTO temp_table VALUES ( 'Dan', 'Morgan');
INSERT INTO temp_table VALUES ( 'Helen', 'Lofstrom');
INSERT INTO temp_table VALUES ( 'Akiko', 'Toyota' );
INSERT INTO temp_table VALUES ( 'Jackie', 'Stough');
INSERT INTO temp_table VALUES ( 'Richard', 'Foote');
INSERT INTO temp_table VALUES ( 'Joe', 'Johnson');
INSERT INTO temp_table VALUES ( 'Clark', 'Urling');
-------------------------
create or replace procedure multi_insert
as
cursor cursor1
is
select * from temp_table;
begin
for rec in cursor1 loop
begin
insert into job values(job_id_seq.nextval,rec.job_title);
exception
when dup_val_on_index then
null;
log_staging_errors(SQLCODE,SQLERRM);
when others then
log_staging_errors(SQLCODE,SQLERRM);
null;
end;
begin
insert into emplyee values(empid_seq.nextval,rec.empname,rec.job_id);
exception
when dup_val_on_index then
log_staging_errors(SQLCODE,SQLERRM);
when others then
log_staging_errors(SQLCODE,SQLERRM);
null;
end;
end loop;
end;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: multi insert [message #656860 is a reply to message #656853] |
Thu, 20 October 2016 08:34  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
|
You do know that dbms_output will not be able to inform the user via a stored procedure in a 3-tier environment. If it is just for testing purposes, what do you plan to do in the real world scenario?
|
|
|
|
| Re: multi insert [message #656864 is a reply to message #656860] |
Thu, 20 October 2016 13:13 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
a simple query will show you duplicates. By the way your job titles are strange. What job title is a DAN, JOE, HELEN....?
select job_title,empname
from temp_table
group by job_title,empname
having count(*) > 1; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
