Oracle Insert into Table2 Then Delete from Table1, Exception If Fail

insert into table1 select * from another table2 , then delete the row from table2

As per our discussion, i propose this solution for you. Even though i dont find any need of having a FUNCTION am showing the exmaple using a FUNCTION. Also I had written your delete logic to a Procedure and depending on the success of delete statement, a Return value is caputured in variable var and sending as OUT paramater which you can use.

Function:

CREATE OR REPLACE FUNCTION confirm_ckh
   RETURN VARCHAR
AS
BEGIN
   RETURN ('#');
END;

Procedure:

CREATE OR REPLACE PROCEDURE DELETEROW (
   GODOWN_DIST_CODE   IN TEST_TABLE.GODOWN_DIST_CODE%TYPE,
   var OUT varchar2) <-- Out parameter to get the return value
AS

BEGIN
   BEGIN
      INSERT INTO TEST_TABLE_BACKUP
         SELECT *
           FROM TEST_TABLE
          WHERE GODOWN_DIST_CODE = GODOWN_DIST_CODE;

      IF SQL%ROWCOUNT > 0
      THEN
         DBMS_OUTPUT.put_line ('Insert Success TEST_TABLE_BACKUP');
         var:=confirm_ckh;
      END IF;

      DELETE TEST_TABLE
       WHERE GODOWN_DIST_CODE = GODOWN_DIST_CODE;

      IF SQL%ROWCOUNT > 0
      THEN
         DBMS_OUTPUT.put_line ('Deleted records from TEST_TABLE ');
         var:=confirm_ckh;
      END IF;

      INSERT INTO NEW_TABLE_BACKUP
         SELECT *
           FROM NEW_TABLE
          WHERE GODOWN_DIST_CODE = GODOWN_DIST_CODE;

      IF SQL%ROWCOUNT > 0
      THEN
         DBMS_OUTPUT.put_line ('Insert Success for NEW_TABLE_BACKUP');
         var:=confirm_ckh;
      END IF;

      DELETE FROM NEW_TABLE
            WHERE GODOWN_DIST_CODE = GODOWN_DIST_CODE;

      IF SQL%ROWCOUNT > 0
      THEN
         DBMS_OUTPUT.put_line ('Deleted records from NEW_TABLE ');
         var:=confirm_ckh;
      END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         null;
   END;
END;

handling exception for insert into select from

You can create an error log table, and then use a single insert:

exec dbms_errlog.create_error_log(dml_table_name => 'TXN_HEADER_REPORTS');

insert into TXN_HEADER_REPORTS ( txn_id, txn_Date ,product_id,company_id ,
  dealer_id , tran_amt)
select txn_id, txn_Date ,product_id,company_id , dealer_id , tran_amt
from schema1.TXN_HEADER
log errors into ERR$_TXN_HEADER_REPORTS reject limit unlimited;

Any rows that can't inserted will be recorded in the ERR table. Note that this is plain SQL, it doesn't need to be in a PL/SQL block.

Read more in the documentation.

Oracle Merge Sql with insert in destination and delete from source

MERGE is useful to do manipulation on the destination table, not source.

You can use an anonymous PLSQL block:

begin
    delete from my_data_backup;
    insert into my_data_backup
    select *
    from my_data;
    delete from my_data;
    commit;
exception
    when others then
        rollback;
        -- handle here
end;
/

You can also put the above in a procedure and call the procedure.

You can think about using truncate statement instead of delete which will be faster when the table size is larger but be careful that it, being a DDL, will do an implicit commit.

execute immediate 'truncate table tablename';

How to FETCH perticular records from table1 and INSERT into table2?

how this is possible? with procedure or with trigger?

Neither, I guess. UPDATE will do.

update user_detail d set
  (d.purchased_item, d.amount_Total, d.due_Date) =
  (select p.p_name, p.p_amount, sysdate
   from product p
   where p.p_id = :par_p_id
  )
where d.u_id = :par_u_id;

---

If it has to be a procedure, then

create or replace procedure p_iud (par_p_id in product.p_id%type,
                                   par_u_id in user_detail.u_id%type
                                  )
as
begin
  update user_detail d set
    (d.purchased_item, d.amount_Total, d.due_Date) =
    (select p.p_name, p.p_amount, sysdate
     from product p
     where p.p_id = par_p_id
    )
  where d.u_id = par_u_id;
end;
/

Call it as

begin
   p_iud(101, 3676);
end;
/

---

If you'd want to pass several products at the same time, one option is to pass them as a string with comma-separated values. The procedure would then be

create or replace procedure p_iud (par_p_id in varchar2,
                                   par_u_id in user_detail.u_id%type
                                  )
as
begin
  update user_detail d set
    (d.purchased_item, d.amount_total, d.due_Date) =
    (select listagg(p.p_name, ',') within group (order by p.p_name), 
            sum(p.p_amount), 
            sysdate
     from product p
     where p.p_id in (select to_number(regexp_substr(par_p_id, '[^,]+', 1, level))
                      from dual
                      connect by level <= regexp_count(par_p_id, ',') + 1
                     )
    )
  where d.u_id = par_u_id;
end;
/

and you'd call it as

begin
   p_iud('101,301', 3676);
end;
/

Possible problem: as LISTAGG concatenates all those product names, it'll fail if such a "long" string is too long for PURCHASED_ITEM column (you could SUBSTR it, if that's an option)

If oracle table1 is empty then output table2 else output table1 itself; No columns on both the tables are common

Accordingly to your phrase:

if Table1 is empty/null then output Table2 else output table1

I think the solution is (I briefed Table1, Table2 by A, B respectively ):

--I created this tables to test the solution
create table A( id number, val varchar2(5));
create table B( code varchar2(5), event_dt date);

insert into b(code, event_dt)
  values ('test', sysdate);
--query(1)
select b.code, to_char(b.event_dt,'yyyy-mm-dd') 
  from b
 where not exists (select 1 from a)
union 
 select to_char(id), to_char(val)
   from a
; 
--now insert data on the other table (to test purposes)
insert into A(id, val)
  values(1, 'TestA');

--run the query(1) again

The key is "union", kind of repeat your query when the first portion deals to no data
found.
Please remember to CAST your columns to achieve the same DATA-TYPES required by UNION

Best regards.

---

Related Topics