DBMS_SQL And Commit Problem [message #312255] |
Tue, 08 April 2008 05:19 |
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 |
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 #312278 is a reply to message #312265] |
Tue, 08 April 2008 06:51 |
|
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
Maybe you should verify if you haven't checked the Autocommit checkbox.
Have a look at the attached image
[mod: inserted image into body]
[Updated on: Tue, 08 April 2008 07:38] by Moderator Report message to a moderator
|
|
|