Link to home
Start Free TrialLog in
Avatar of maximus1974
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.

User generated image

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;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of maximus1974
maximus1974

ASKER

Thank you. How you suggest I do it? Need to copy the table daily without duplicating the data. Thought of deleting the rows and then copying the table.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Without a WHERE clause on the delete, I might suggest a TRUNCATE instead of a DELETE.
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.
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;

Open in new window

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.
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.


Without a WHERE clause on the delete, I might suggest a TRUNCATE instead of a DELETE.                                   
Unless there are DELETE-related triggers... Then you may check these beforehand ;-) (whatever they might do)
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial