maximus1974
asked on
Need help with a stored procedure
Need to create a stored procedure that deletes the lines of a table and copies the data. How would I properly use the where clause in this statement? I am receiving this error when executing.
create or replace procedure BA_PROC_AUDIT_TRAIL_WOO(
audit_trail varchar2,
audit_trail_woo varchar2)
is
begin
DELETE FROM AUDIT_TRAIL_WOO;
execute immediate 'insert into '||audit_trail||' (select * from '||audit_trail_woo||' where source_table = 'WOO')';
end;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Without a WHERE clause on the delete, I might suggest a TRUNCATE instead of a DELETE.
ASKER
Thank you Sean. I need to basically refresh AUDIT_TRAIL_WOO daily from AUDIT_TRAIL without duplicates. Copy the data from AUDIT_TRAIL --> AUDIT_TRAIL_WOO. Will this accomplish this?
>>>> AUDIT_TRAIL --> AUDIT_TRAIL_WOO.
no, it copies the other direction based on your original code.
But that's easy enough to fix, just swap ~AUDIT_TRAIL~ and ~AUDIT_TRAIL_WOO~ in the v_insert_sql declaration (not in the REPLACE calls)
slightwv's comment about truncate is also applicable if you want to commit immediately after the delete.
If you do not want to commit then you can't use truncate.
no, it copies the other direction based on your original code.
But that's easy enough to fix, just swap ~AUDIT_TRAIL~ and ~AUDIT_TRAIL_WOO~ in the v_insert_sql declaration (not in the REPLACE calls)
slightwv's comment about truncate is also applicable if you want to commit immediately after the delete.
If you do not want to commit then you can't use truncate.
ASKER
Like so?
CREATE OR REPLACE PROCEDURE ba_proc_audit_trail_woo(audit_trail VARCHAR2, audit_trail_woo VARCHAR2)
IS
v_insert_sql VARCHAR2(32767)
:= q'[insert into ~AUDIT_TRAIL_WOO~ (select * from ~AUDIT_TRAIL~ where source_table = 'WOO')]';
v_delete_sql VARCHAR2(32767) := 'TRUNCATE TABLE ~AUDIT_TRAIL_WOO~';
BEGIN
v_insert_sql := REPLACE(v_insert_sql, '~AUDIT_TRAIL~', audit_trail);
v_insert_sql := REPLACE(v_insert_sql, '~AUDIT_TRAIL_WOO~', audit_trail_woo);
v_delete_sql := REPLACE(v_delete_sql, '~AUDIT_TRAIL_WOO~', audit_trail_woo);
EXECUTE IMMEDIATE v_insert_sql;
EXECUTE IMMEDIATE v_delete_sql;
Reading the code again and trying to decipher the requirements:
truncate and the delete in your original code removes ALL rows in a table.
You are inserting base on a WHERE clause. So, you might not want truncate or a delete without a WHERE.
truncate and the delete in your original code removes ALL rows in a table.
You are inserting base on a WHERE clause. So, you might not want truncate or a delete without a WHERE.
it also doesn't make sense to insert into audit_trail_woo and then delete everything from audit_trail_woo
let's stop worrying about the syntax - take a moment to think about what you really want to happen
and then describe that goal (don't use sql, just use english)
Also - think about the truncate vs delete comments slightwv has pointed out.
let's stop worrying about the syntax - take a moment to think about what you really want to happen
and then describe that goal (don't use sql, just use english)
Also - think about the truncate vs delete comments slightwv has pointed out.
Unless there are DELETE-related triggers... Then you may check these beforehand ;-) (whatever they might do)
Without a WHERE clause on the delete, I might suggest a TRUNCATE instead of a DELETE.
ASKER
I need the table AUDIT_TRAIL_WOO to be wiped clean daily and then updated from data that resides in table AUDIT_TRAIL. My goal was to first delete the rows within table AUDIT_TRAIL_WOO and then insert new data from table AUDIT_TRAIL daily.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER