Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    MA,USA
    Posts
    18

    Unanswered: swapping of data to history table

    I am trying to execute this procedure but getting error:
    ORA-14155: missing PARTITION or SUBPARTITION keyword

    Any help would be greatly appreciated:
    efdr_cash_handoff_stg is partitioned on day of week
    efdr_cash_handoff_hist table is partitioned by range on process date
    by range (months)

    ****************************

    CREATE OR REPLACE PROCEDURE Atrn_Cash_Handoff_Arch (
    p_day_of_week IN NUMBER

    IS

    v_string VARCHAR2(4000);
    v_date VARCHAR2(1);
    v_count1 NUMBER(28) := 0;
    v_count2 NUMBER(28) := 0;
    v_rowcount NUMBER(28) := 0;


    BEGIN
    --- check to see if any row exists for p_day_of_week row
    SELECT COUNT(*) INTO v_count1
    FROM efdr_cash_handoff_stg
    WHERE day_of_week = p_day_of_week;


    --- check to see if the rows in p_day_of_week have been processed
    SELECT COUNT(*) INTO v_count2
    FROM efdr_cash_handoff_stg
    WHERE day_of_week = p_day_of_week
    AND process_id IS NULL;

    dbms_output.put_line( 'Number of rows found: ' ||v_count2);


    IF ( v_count1 > 0 AND v_count2 != 0) THEN

    v_string := 'ALTER TABLE efdr_cash_handoff_stg EXCHANGE PARTITION'||
    'part_'||p_day_of_week||'WITH efdr_cash_handoff_tmp '||
    'UPDATE GLOBAL INDEXES ' ||
    'PARALLEL (DEGREE 4) ';



    EXECUTE IMMEDIATE v_string;

    INSERT/* + PARALLEL(hist,4) */ INTO efdr_cash_handoff_hist hist
    SELECT /* + PARALLEL (tmp,4) */ * FROM efdr_cash_handoff_tmp tmp ;

    v_rowcount := TO_CHAR(SQL%ROWCOUNT) ;


    dbms_output.put_line( 'Number of rows inserted : ' || v_rowcount);


    COMMIT;


    v_string := 'TRUNCATE TABLE efdr_cash_handoff_tmp';

    EXECUTE IMMEDIATE v_string;


    END IF;


    EXCEPTION

    WHEN NO_DATA_FOUND THEN
    dbms_output.put_line( 'No Data Found To Be Archived');

    WHEN OTHERS THEN

    RAISE_APPLICATION_ERROR(-20001,SQLCODE||SUBSTR(SQLERRM, 1, 2000));



    END Atrn_Cash_Handoff_Arch;

    /
    Thanks
    lucy

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I think you need to add a space character after the EXCHANGE PARTITION bit and also one before the WITH.

    With execute immediate I always find it a good idea to dbms_output the sql string as it is easy to miss a space.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •