Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Stored Procedure insert into, then delete from table then insert into

2815275Dec 16 2014 — edited Dec 17 2014

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  

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 14 2015
Added on Dec 16 2014
7 comments
2,515 views