Issue with update statements in pl/sql block. If table not exists, then it shouldn't execute update statements.

Update statements are appending 01 to startdate and last day of month to end date.

SET SQLBLANKLINES ON;
   
    DECLARE
    v_table_exists varchar2(2);
    BEGIN
        SELECT count(*) INTO v_table_exists FROM USER_TABLES WHERE TABLE_NAME = 'PRICECHANGE_FULL';
        IF (v_table_exists = '1') THEN
            UPDATE PRICECHANGE_FULL SET PS_START_MONTH=TO_CHAR(TO_DATE(PS_START_MONTH, 'YYYYMM'), 'YYYYMMDD') WHERE LENGTH(PS_START_MONTH)=6;
            UPDATE PRICECHANGE_FULL SET VC_ITEM_START_MONTH=TO_CHAR(TO_DATE(VC_ITEM_START_MONTH, 'YYYYMM'), 'YYYYMMDD') WHERE LENGTH(VC_ITEM_START_MONTH)=6;
            UPDATE PRICECHANGE_FULL SET CALC_ITEM_START_MONTH=TO_CHAR(TO_DATE(CALC_ITEM_START_MONTH, 'YYYYMM'), 'YYYYMMDD') WHERE LENGTH(CALC_ITEM_START_MONTH)=6;
            UPDATE PRICECHANGE_FULL SET ITEM_START_MONTH=TO_CHAR(TO_DATE(ITEM_START_MONTH, 'YYYYMM'), 'YYYYMMDD') WHERE LENGTH(ITEM_START_MONTH)=6;
    
            UPDATE PRICECHANGE_FULL SET PS_EXPIRY_MONTH=TO_CHAR(LAST_DAY(TO_DATE(PS_EXPIRY_MONTH, 'YYYYMM')), 'YYYYMMDD') WHERE LENGTH(PS_EXPIRY_MONTH)=6;
            UPDATE PRICECHANGE_FULL SET ITEM_END_MONTH=TO_CHAR(LAST_DAY(TO_DATE(ITEM_END_MONTH, 'YYYYMM')), 'YYYYMMDD') WHERE LENGTH(ITEM_END_MONTH)=6;
            UPDATE PRICECHANGE_FULL SET VC_ITEM_END_MONTH=TO_CHAR(LAST_DAY(TO_DATE(VC_ITEM_END_MONTH, 'YYYYMM')), 'YYYYMMDD') WHERE LENGTH(VC_ITEM_END_MONTH)=6;
            UPDATE PRICECHANGE_FULL SET CALC_ITEM_END_MONTH=TO_CHAR(LAST_DAY(TO_DATE(CALC_ITEM_END_MONTH, 'YYYYMM')), 'YYYYMMDD') WHERE LENGTH(CALC_ITEM_END_MONTH)=6;
            COMMIT;
        END IF;
    END;
    /
    
    exit;

Error: If table doesn't exists, I'm getting table not found error (update statements shouldn't get executed in this case).

CodePudding user response：

That code can't possibly work if table pricechange_full doesn't exist; it wouldn't compile. If you want to have such a code, you'll have to use dynamic SQL.

Apart from that:

• count function returns a NUMBER; why are you comparing it to a string? v_table_exists = '1' - should be = 1

• the whole idea here is to - as you said -

  append 01 to startdate

  It means that you are storing dates as strings. (Again strings? Do you love them that much?) That's really a bad, Bad, BAD, BAD idea. Store dates into DATE datatype columns. Once you do that, you won't need such a code (you wrote & posted in the question) and everything will work by itself

CodePudding user response：

The code seems right, even with count returning a number in a string variable. if you are getting table not found, should be it when compiling, not when executing. Do you have access to USER_TABLES?