Home » SQL & PL/SQL » SQL & PL/SQL » I am getting Primary key vialotion error (ORACLE 10g ,XP (OS))
I am getting Primary key vialotion error [message #381659] Mon, 19 January 2009 02:52 Go to next message
baulraj
Messages: 48
Registered: January 2009
Member
Hi,

I have a ORACLE script file with .sql and i have list of alter table commands and some CREATE TABLE ,CREATE SEQUENCE and then some INSERT commands for different table.

My problem is i am getting Primary key violation from the last insert statement.

Because the last INSERT command alone executed twice,but i have checked and the script is correct.

Also i checked for the additional / if i mistakenly made but every thing is seems to be correct?

Note : If i removed the all the INSERT commands then what ever statement i am giving instead of INSERT command is executed twice.

what could be the cause of the problem?

can any one advice me?

Thanks in advance.
Baulraj.




Re: I am getting Primary key vialotion error [message #381664 is a reply to message #381659] Mon, 19 January 2009 03:07 Go to previous messageGo to next message
parag_narkhede
Messages: 110
Registered: January 2008
Location: Pune
Senior Member
Can you please attach the .sql script?
Re: I am getting Primary key vialotion error [message #381665 is a reply to message #381659] Mon, 19 January 2009 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Because the last INSERT command alone executed twice,but i have checked and the script is correct.

I bet your insert ends with ";" and you have a "/" after.
Do I win?

Regards
Michel
Re: I am getting Primary key vialotion error [message #381669 is a reply to message #381665] Mon, 19 January 2009 03:21 Go to previous messageGo to next message
baulraj
Messages: 48
Registered: January 2009
Member
Hi,

Thank you for your valuable reply.

Yes.
I accept your point.

Is it the problem?

One more doubt i have this line in the middle of the script i have the following...

set serveroutput on size 1000000
declare

cn number ;
begin
select count(*) into cn from user_indexes where index_name = 'IDX_OP_IN_SUITE_DATE_TIME' ;

some alter statements according to the cn value
/


While executing this set of commands are executed first then at the last also executed.....

Re: I am getting Primary key vialotion error [message #381670 is a reply to message #381665] Mon, 19 January 2009 03:25 Go to previous messageGo to next message
baulraj
Messages: 48
Registered: January 2009
Member
Hi,

Thank you for your valuable reply.

i have similar script INSERT command ends with ; and then '/' after.


also in the middle of the script i am having something like....
set serveroutput on size 1000000
declare

cn number ;
begin
select count(*) into cn from user_indexes where index_name = 'IDX_OP_IN_SUITE_DATE_TIME' ;

Here some alter statements based on 'cn' value.Then
/


While executing this part is automatically executed as very first line in script and no error is generated .

At end also same block is generated ....
Re: I am getting Primary key vialotion error [message #381671 is a reply to message #381665] Mon, 19 January 2009 03:26 Go to previous messageGo to next message
baulraj
Messages: 48
Registered: January 2009
Member
Yes you are correct.


Thank you ....


Regards
Baulraj.V
Re: I am getting Primary key vialotion error [message #381672 is a reply to message #381671] Mon, 19 January 2009 03:28 Go to previous messageGo to next message
baulraj
Messages: 48
Registered: January 2009
Member
Hi,

I also have some commands in the middle of the script file....

set serveroutput on size 1000000
declare

cn number ;
begin
select count(*) into cn from user_indexes where index_name = 'IDX_OP_IN_SUITE_DATE_TIME' ;


While executing the script file , the above part is executed first eventhough i have written in the mid of the script.

At the end also the same script is executed.....

Can you pls advice me.

Thank you.
Re: I am getting Primary key vialotion error [message #381674 is a reply to message #381659] Mon, 19 January 2009 03:33 Go to previous messageGo to next message
parag_narkhede
Messages: 110
Registered: January 2008
Location: Pune
Senior Member
I am not able to understand your problem. But if you want to see any errors during execution, you can use SHOW ERRORS;
Re: I am getting Primary key vialotion error [message #381676 is a reply to message #381674] Mon, 19 January 2009 03:37 Go to previous messageGo to next message
baulraj
Messages: 48
Registered: January 2009
Member
Hi,
I tried that also.
show errors ....it displays no error.

I am not able to upload the script file because it is large one.

Re: I am getting Primary key vialotion error [message #381678 is a reply to message #381676] Mon, 19 January 2009 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove the "/"

Regards
Michel
Re: I am getting Primary key vialotion error [message #381682 is a reply to message #381678] Mon, 19 January 2009 03:55 Go to previous messageGo to next message
baulraj
Messages: 48
Registered: January 2009
Member
Hi,

Can you pls go through the script file and let me know the error.

I already attached the spool result.

Thank you.

Regards
Baulraj
Re: I am getting Primary key vialotion error [message #381683 is a reply to message #381676] Mon, 19 January 2009 04:08 Go to previous messageGo to next message
baulraj
Messages: 48
Registered: January 2009
Member
Hi,

Some how i managed to update the script file.

Can you please look unto it and let me know the error.
(Kindly find the attachment in the previous reply)

Thank you.


Regards
Baulraj.v
Re: I am getting Primary key vialotion error [message #381704 is a reply to message #381682] Mon, 19 January 2009 06:02 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
baulraj wrote on Mon, 19 January 2009 10:55
Can you pls go through the script file and let me know the error.

Do you really expect anybody to go through nearly 6000 lines of (unintended) code? Which seems to be a patch script of a system you did not mention anything about. So nobody here has any idea, whether e.g. table APATS contained COUNTER column before running this script and what e.g. SP_TAB_EXISTS procedure does.

You know the current system and the state it shall be after running this script. It is your work to check it on a test (non-production) system. If you do not have any, have a good luck.

Just one remark: as all your procedures contain this exception handling
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');
, they are unnecessarily complex; you may easily replace them with that PUT_LINE command, as it result satisfies you.
Re: I am getting Primary key vialotion error [message #382034 is a reply to message #381659] Tue, 20 January 2009 13:52 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
yOUR PROBLEM IS with the difference with a block and a statement. If you have the following

declare
junk number;
begin
junk:=1;
end;
/


The block (declare..begin..end) is not executed until the / is hit.

A statement is outside a block. The following will be executed once.

select sysdate from dual
/


because the select is not ended with a semi colon. The following statement will be run twice because the statement is ended with a semi colon.

select sysdate from dual;
/

Previous Topic: Sequence
Next Topic: ORA-00942 Errors (merged 3)
Goto Forum:
  


Current Time: Sat Dec 03 07:46:27 CST 2016

Total time taken to generate the page: 0.05784 seconds