Home » SQL & PL/SQL » SQL & PL/SQL » multi insert
multi insert [message #656837] Thu, 20 October 2016 05:53 Go to next message
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 #656841 is a reply to message #656837] Thu, 20 October 2016 06:47 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to rhe forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html

Re: multi insert [message #656842 is a reply to message #656837] Thu, 20 October 2016 06:52 Go to previous messageGo to next message
John Watson
Messages: 7212
Registered: January 2010
Location: Global Village
Senior Member
Would insert error logging be an alternative?
http://docs.oracle.com/database/121/SQLRF/statements_9014.htm#sthref7110
Re: multi insert [message #656843 is a reply to message #656842] Thu, 20 October 2016 06:54 Go to previous messageGo to next message
premier10
Messages: 5
Registered: October 2016
Junior Member
Thanks for your reply, I just want to be able to display a message with the duplicate row, is this possible?
Re: multi insert [message #656845 is a reply to message #656843] Thu, 20 October 2016 06:57 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Read the article I gave you, it explains and learns you how to fulfill such issues.
It contains WITH EXAMPLE OF CODE, the error logging John mentioned.

Re: multi insert [message #656846 is a reply to message #656843] Thu, 20 October 2016 06:58 Go to previous messageGo to next message
John Watson
Messages: 7212
Registered: January 2010
Location: Global Village
Senior Member
You haven't bothered to follow any references, have you?
Re: multi insert [message #656847 is a reply to message #656846] Thu, 20 October 2016 07:00 Go to previous messageGo to next message
premier10
Messages: 5
Registered: October 2016
Junior Member
Yes, different to what I am asking.
Re: multi insert [message #656849 is a reply to message #656847] Thu, 20 October 2016 07:24 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
use of UNIQUE constraint will automatically prevent duplicates in the table.
Re: multi insert [message #656850 is a reply to message #656849] Thu, 20 October 2016 07:27 Go to previous messageGo to next message
premier10
Messages: 5
Registered: October 2016
Junior Member
thank you, I have the unique constraints created. Is there a way I can (dbms_output.put_line) a message that will show when there is a duplicate of BOTH job_title and emp_name
Re: multi insert [message #656852 is a reply to message #656850] Thu, 20 October 2016 07:34 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
is application 3-tier?
Re: multi insert [message #656853 is a reply to message #656852] Thu, 20 October 2016 07:55 Go to previous messageGo to next message
premier10
Messages: 5
Registered: October 2016
Junior Member
Yes just running at application level at the moment
Re: multi insert [message #656858 is a reply to message #656847] Thu, 20 October 2016 08:30 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
premier10 wrote on Thu, 20 October 2016 14:00
Yes, different to what I am asking.
No exactly what you are asking.

Re: multi insert [message #656859 is a reply to message #656850] Thu, 20 October 2016 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
premier10 wrote on Thu, 20 October 2016 14:27
thank you, I have the unique constraints created. Is there a way I can (dbms_output.put_line) a message that will show when there is a duplicate of BOTH job_title and emp_name
Yes, read what we have told you.

Re: multi insert [message #656860 is a reply to message #656853] Thu, 20 October 2016 08:34 Go to previous messageGo to next message
joy_division
Messages: 4817
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 Go to previous message
Bill B
Messages: 1709
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;
Previous Topic: Interval in DBMS_JOB
Next Topic: calculate working days not beween date - merged
Goto Forum:
  


Current Time: Tue Jan 16 20:13:24 CST 2018

Total time taken to generate the page: 0.01813 seconds