You Asked

User
I HAVE SUCCESSFULLY RUNNED A JOB WHICH HAS STORED PROCEDURE OF TRUNCATE AND INSERT STATEMENTS USING DBLINK, IF ERROR OCCURS, ROLLBACK STAEMENTS ALSO THERE IN ORACLE PLSQL. WHILE DOING SO, TRUNCATED DONE AND DBLINK COMMUNICATION INTERUPPTED AND GOT END. SO, DATA TRUNCATED BUT NEITHER INSERTED NOR ROLLBACK. NOW THERE IS NO DATA IN THE TABLE. IF I RUN THE SP IMMEDIATE I DONT FIND CONNECTION PROBLEM IN DBLINK AND DATA ARE INSERTED. WHAT COULD BE THE PROBLEM?

create or replace sp_dataload
begin
execute immediate 'TRUNCATE TABLE TABLE1';
INSERT INTO TABLE1
(
ID
,NAME)
SELECT * FROM TABLE2@DBLINK;
COMMIT;
dbms_output.put_line('complete');

Exception
when others then
dbms_output.put_line("error" || SQLERRm);
Rollback;
end
/

and Connor said...

I AM TYPING THIS IN CAPS BECAUSE THAT SEEMS TO BE HOW YOU LIKE IT :-)

YOUR EXECUTE IMMEDIATE IS NOT CORRECT WHICH MEANS IT IS NOT RUNNING

SQL> create table t ( x int );

Table created.

SQL> begin
  2    execute immediate 'truncate table t; insert into t values (1);';
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-03291: Invalid truncate option - missing STORAGE keyword
ORA-06512: at line 2

THIS IS WHY IT IS DANGEROUS TO HAVE A WHEN-OTHERS CATCHING EVERYTHING

Rating

(1 rating)

Comments

Sp_Truncate and insert the table_or Rollback using DBLink in a Procedure

Saranya, February 26, 2024 - 6:20 am UTC

Dear Connor, Thank you for your reply.. By the way, Sorry to type in Capital letters, somhow I have fixed to it while coding Caps on enabled and continued here too. :-)

I am ETL jobs in Oracle 19C. Could you give me a solution on to truncate the table and insert into the table, also handles the error like rollback everything in a Stored procedure?

I have tried with delete but it slow down the process, there are more than 17millions of data in a table.

February 28, 2024 - 5:41 am UTC

truncate is DDL so there is no way you can include that as *part* of a transaction - it is always a transaction in its own right.

To overcome this, you could look at a mechanism where you shift that out of the process. For example (using your "TABLE1" as the targert)

rename TABLE1 to HIDDEN_TABLE1;

create view TABLE1 as
select * from HIDDEN_TABLE1;

create table HIDDEN_TABLE2 as
select * from HIDDEN_TABLE1;

Now you have 2 copies of the table, and the view points to one of them.

Then your process becomes:

truncate HIDDEN_TABLE2
insert into HIDDEN_TABLE2 select * from XXX@dblink;

If this works to completion, you then do:

create or replace view TABLE1 as
select * from HIDDEN_TABLE2;

to now point to the new data. The next time you run the job, you do the reverse

truncate HIDDEN_TABLE1
insert into HIDDEN_TABLE1 select * from XXX@dblink;
create or replace view TABLE1 as
select * from HIDDEN_TABLE1;

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Sp_Truncate and insert the table_or Rollback using DBLink in a Procedure

More to Explore

PL/SQL demos

PL/SQL docs

DBLINK CONNECTION INETRUPTED_ROLLBACK NOT HAPPENS

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Sp_Truncate and insert the table_or Rollback using DBLink in a Procedure

More to Explore

PL/SQL demos

PL/SQL docs