Hi yall,
First time I have posted here. Happy to join this community. What I'm trying to do is create a three step stored procedure that inserts records into a table, deletes a different table then inserts records into that table I just deleted everything from. The stored procedure compiles but it says "with errors". The version of Oracle I'm using is at the bottom. I don't have any parameter to feed the stored procedure. I just want it do the three things I'm asking it to do. Thank you for the help!!
Here is the code I have written:
CREATE PROCEDURE customer_tier_trans_update ()
IS
BEGIN
INSERT INTO --step 1
TBL_CUST_TIER_TRANS_HISTORY
select
ttr.account_num,
ttr.customertier,
subselect.customer_tier,
sysdate - 1,
sysdate
from
TBL_TEMP_CUST_TIER ttr
inner join (SELECT dwhb.account_no, dwhb.customer_tier
FROM dwh_brm_accounts_d dwhb
GROUP BY dwhb.account_no, dwhb.customer_tier) subselect ON subselect.account_no=ttr.account_num
WHERE ttr.customertier <> subselect.customer_tier;
COMMIT;
DELETE FROM tbl_temp_cust_tier; --step 2
insert into --step 3
tbl_temp_cust_tier
select
dwhb.account_no,
dwhb.customer_tier
from
dwh_brm_accounts_d dwhb
group by
dwhb.account_no,
dwhb.customer_tier;
COMMIT ;
END;
/
Versions of Oracle I'm using:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production | |
PL/SQL Release 11.2.0.2.0 - Production | |
CORE 11.2.0.2.0 Production | |
TNS for Linux: Version 11.2.0.2.0 - Production | |
NLSRTL Version 11.2.0.2.0 - Production