Home » Other » Client Tools » DBMS_SQL And Commit Problem (Oracle 9i,9.2.0.6.0,Windows XP)
DBMS_SQL And Commit Problem [message #312255] Tue, 08 April 2008 05:19 Go to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Dear All,

In this code i have placed no Commit statement but still data is saved after successfull execution of the procedure. I don't wan't data to be saved.

CREATE OR REPLACE PROCEDURE Pr_Datamovementhisttomain(code IN VARCHAR2) IS
 
TYPE TableList IS TABLE OF TB_PURGINGTABLES%ROWTYPE;
purgTables TableList;
cursor_name INTEGER;
rows_processed INTEGER;
counter  NUMBER;
   
PROCEDURE PR_RECORDERRORS(mainTableId NUMBER,mainTable VARCHAR2,histTable VARCHAR2,err NUMBER,errmsg VARCHAR2,operType VARCHAR2,errtime DATE) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    NULL;
END PR_RECORDERRORS;
 
BEGIN
  BEGIN
   SELECT *  BULK COLLECT INTO  purgTables FROM   
   TB_PURGINGTABLES ORDER BY TB_SEQ ASC;
     FOR  ix  IN 1..purgTables.COUNT LOOP
      counter:=ix;
      cursor_name := dbms_sql.open_cursor;
      DBMS_SQL.PARSE(cursor_name, 'INSERT INTO  ' || purgTables(ix).TB_NAME  ||' (SELECT * FROM '||   purgTables(ix).TB_DEPNAME  || '@DBLINK  '|| purgTables(ix).Condition  ||' )', DBMS_SQL.NATIVE);
      DBMS_SQL.BIND_VARIABLE(cursor_name, ':var', code);
      rows_processed := DBMS_SQL.EXECUTE(cursor_name);
      DBMS_SQL.CLOSE_CURSOR(cursor_name);
     END LOOP;
	EXCEPTION WHEN OTHERS THEN
	      DBMS_SQL.CLOSE_CURSOR(cursor_name);
	      PR_RECORDERRORS(purgTables(counter).TB_ID,purgTables(counter).TB_NAME,purgTables(counter).TB_DEPNAME,SQLCODE,SQLERRM,'I',SYSDATE);
	      ROLLBACK;
	END;
	
END Pr_Datamovementhisttomain;
Re: DBMS_SQL And Commit Problem [message #312265 is a reply to message #312255] Tue, 08 April 2008 05:47 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
I found the problem.This is the problem with TOAD.In TOAD it automatically commit insert statement while execution of procedure.
If we execute procedure in sql plus procedure will not insert any data and working fine.

Thanks,
Rajat
Re: DBMS_SQL And Commit Problem [message #312269 is a reply to message #312265] Tue, 08 April 2008 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Ana's proverb
Those who live by the GUI, die by the GUI.

Regards
Michel
Re: DBMS_SQL And Commit Problem [message #312278 is a reply to message #312265] Tue, 08 April 2008 06:51 Go to previous message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
rajatratewal wrote on Tue, 08 April 2008 12:47
I found the problem.This is the problem with TOAD.In TOAD it automatically commit insert statement while execution of procedure.
If we execute procedure in sql plus procedure will not insert any data and working fine.

Thanks,
Rajat



As an avid TOAd-user I find this very hard to believe Smile

Maybe you should verify if you haven't checked the Autocommit checkbox.
Have a look at the attached image

/forum/fa/4116/0/
[mod: inserted image into body]

[Updated on: Tue, 08 April 2008 07:38] by Moderator

Report message to a moderator

Previous Topic: Toad Road Map
Next Topic: new tool
Goto Forum:
  


Current Time: Thu Mar 28 11:29:52 CDT 2024