Home » SQL & PL/SQL » SQL & PL/SQL » how to debug package
how to debug package [message #582655] Sat, 20 April 2013 20:40 Go to next message
reenasharma
Messages: 5
Registered: December 2011
Location: pune
Junior Member
Hi all,

I have written a package including 13 procedures to insert data into a temp table but i am getting duplicate rows in dat table how to track from which procedure i am getting duplicate rows.
step 1 : procedures are inserting data using some joins into a temp table.
step 2: from temp table data will get stored into a target table where if the data already exist it will update the target table else will insert the data.(its a procedure where i used merge).
as i have duplicate data on temp table i am not able to update data on target table please suggest.
Thanks

Re: how to debug package [message #582656 is a reply to message #582655] Sat, 20 April 2013 21:12 Go to previous messageGo to next message
BlackSwan
Messages: 22897
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

the use of a UNIQUE index prevents duplicate rows.

Rarely, if ever, is a TEMP table required by Oracle.
Re: how to debug package [message #582657 is a reply to message #582656] Sat, 20 April 2013 21:25 Go to previous messageGo to next message
reenasharma
Messages: 5
Registered: December 2011
Location: pune
Junior Member
Hey Thanks for considering.

I am new to this forum and already googled alot.

should have specified i can delete the duplicate rows but need to find out from which procedure i am getting those.
as these procedures are fetching thousands of data so i am not able to identify what approach i should use.

by temp table i meant i have created a global temporary table named 'pending_adj_temp'.

i was getting error ORA: 30926
Re: how to debug package [message #582658 is a reply to message #582657] Sat, 20 April 2013 21:32 Go to previous messageGo to next message
BlackSwan
Messages: 22897
Registered: January 2009
Senior Member
You have table. We don't.
You have data. We don't.
You have code. We don't.

30926, 00000, "unable to get a stable set of rows in the source tables"
// *Cause:  A stable set of rows could not be got because of large dml 
//          activity or a non-deterministic where clause. 
// *Action: Remove any non-deterministic where clauses and reissue the dml.


Why do you expect assistance when you provide no actual details?

My car won't go.
Tell me how to make my car go.

UNIQUE index prevents duplicates.
Re: how to debug package [message #582659 is a reply to message #582658] Sat, 20 April 2013 22:00 Go to previous messageGo to next message
reenasharma
Messages: 5
Registered: December 2011
Location: pune
Junior Member

Thanks alot for providing the possible solution.


Re: how to debug package [message #583165 is a reply to message #582659] Fri, 26 April 2013 08:06 Go to previous messageGo to next message
gurusuryas
Messages: 49
Registered: March 2009
Location: Chennai
Member

Dear Friend,

Please find the below one of the debugging way.




CREATE TABLE TEMP_DEBUG(A VARCHAR2(3000),B NUMBER);

CREATE SEQUENCE TEMP_DEBUG_SEQ;

-- The below is the Debug procedure

CREATE OR REPLACE PROCEDURE TEMP_TRAN
(
A VARCHAR2 )
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO TEMP_DEBUG VALUES
(A,TEMP_DEBUG_SEQ.NEXTVAL
);
COMMIT;

END;
/

create or replace pacakge body pkg1
begin
TEMP_TRAN(' before p1 call');
Procedure p1;
TEMP_TRAN('after p1 call');
Procedure p2;
TEMP_TRAN('after p2 call');
Procedure p3;
TEMP_TRAN('after p3 call');
Procedure p4;
TEMP_TRAN('after p4 call');
Procedure p5;
TEMP_TRAN('after p5 call');
exception
when others then
TEMP_TRAN('Err Occured :'||Sqlerrm);
end pkg1;
/

begin
pkg1;
end;
/


-- Once executed the pkg pls select the below table,We can check how many procedures reached the control.

select * from TEMP_DEBUG;

[mod-edit] color removed.

[Updated on: Fri, 26 April 2013 12:42] by Moderator

Report message to a moderator

Re: how to debug package [message #583180 is a reply to message #583165] Fri, 26 April 2013 09:14 Go to previous message
Michel Cadot
Messages: 59399
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topics:

Michel Cadot wrote on Wed, 06 January 2010 16:04
1/
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
...


Michel Cadot wrote on Thu, 21 May 2009 09:56
1/
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
...


Do follow and apply the rules.

Quote:
exception
when others then
TEMP_TRAN('Err Occured :'||Sqlerrm);
RAISE;
end pkg1;
/


Quote:
begin
pkg1;
end;
/


This is an invalid call.

Regards
Michel
Previous Topic: adding number in sysdate
Next Topic: joining two tables
Goto Forum:
  


Current Time: Mon Oct 20 05:41:50 CDT 2014

Total time taken to generate the page: 0.14067 seconds