Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Mumbai, India
    Posts
    16

    Unanswered: Dbms_sql problem

    Hi
    i am using dbms_sql to create partition as dynamic, but it said parse error.
    pls go thru the following function and help me out pls

    here the function works like this

    its getting the high valuwe of the last partition and using next partition formula its creating the partition in dynamic

    FUNCTION create_partition(p_target_table IN VARCHAR2,
    p_date IN VARCHAR2) RETURN VARCHAR2 IS
    -- Cursors

    CURSOR db_instance IS
    SELECT GLOBAL_NAME FROM GLOBAL_NAME;

    CURSOR c_key_ranges IS
    SELECT high_value
    FROM ALL_TAB_PARTITIONS
    WHERE table_name = UPPER(p_target_table)
    ORDER BY partition_name DESC;

    CURSOR user_tablespace_cursor IS
    SELECT DISTINCT tablespace_name
    FROM USER_TAB_PARTITIONS
    WHERE table_name = UPPER(p_target_table);


    -- Variables

    instance_name VARCHAR2(30);
    high_value_long LONG;
    high_value_date DATE;
    tblspace_name DWH_LOAD.PARTITION_STORAGE.TABLESPACE_NAME%TYPE;
    partition_suffix_format DWH_LOAD.PARTITION_STORAGE.PARTITION_SUFFIX_FORMAT %TYPE;
    next_partition_formula DWH_LOAD.PARTITION_STORAGE.NEXT_PARTITION_FORMULA% TYPE;

    dynamic_stmt VARCHAR2(2000);
    dynamic_cursor INTEGER;
    dynamic_status INTEGER;
    errm varchar2(1000);

    BEGIN

    write_to_driver_log(TO_CHAR(SYSDATE, 'MM/DD/YY-HH24:MIS')||' - '||TO_CHAR(step_number)||'. Adding partitions to '||UPPER(p_target_table)||' table');

    -- Get database instance name

    OPEN db_instance;
    FETCH db_instance INTO instance_name;
    CLOSE db_instance;

    -- Get the high value of last partition from the target table


    OPEN c_key_ranges;

    LOOP
    FETCH c_key_ranges INTO high_value_long;
    IF c_key_ranges%FOUND THEN
    CLOSE c_key_ranges;
    EXECUTE IMMEDIATE 'SELECT ' || high_value_long || ' FROM DUAL' INTO high_value_date;
    EXIT; -- since it is ordered by descending we take the first row which will have the maximum high_value
    END IF;
    END LOOP;

    IF c_key_ranges%ISOPEN THEN
    CLOSE c_key_ranges;
    END IF;

    -- Create all partitions in between last partition and the partition that will satisfy p_date

    WHILE p_date > high_value_date
    LOOP

    -- Get the tablespace name

    OPEN user_tablespace_cursor;
    FETCH user_tablespace_cursor INTO tblspace_name;
    CLOSE user_tablespace_cursor;


    -- Get the partition suffix format and next partition formula

    IF UPPER(p_target_table) = 'DAILY_DETAIL_P' THEN
    partition_suffix_format := TO_CHAR(high_value_date,'_DDMONYY') || TO_CHAR(high_value_date + 6,'_DDMONYY');
    next_partition_formula := TO_CHAR(high_value_date + 7,'DD-MM-YYYY');
    ELSE
    partition_suffix_format := TO_CHAR(high_value_date,'_YYYY_MON');
    next_partition_formula := TO_CHAR(ADD_MONTHS(high_value_date,1),'DD-MM-YYYY');
    END IF;


    dynamic_cursor := DBMS_SQL.OPEN_CURSOR;
    dynamic_stmt := 'ALTER TABLE ' || p_target_table || ' ADD PARTITION ' || p_target_table ||
    partition_suffix_format || ' VALUES LESS THAN (' || high_value_date || ') TABLESPACE ' ||
    tblspace_name;
    /* dynamic_stmt := 'ALTER TABLE ' || p_target_table || ' ADD PARTITION ' || p_target_table ||
    partition_suffix_format || ' VALUES LESS THAN (TO_DATE(''14-03-2004'',''DD-MM-YYYY'' )) TABLESPACE ' ||
    tblspace_name;*/

    DBMS_SQL.PARSE(dynamic_cursor, dynamic_stmt, dbms_sql.native);
    DBMS_SQL.BIND_VARIABLE(dynamic_cursor,'high_value_ date',next_partition_formula);
    dynamic_status := DBMS_SQL.EXECUTE(dynamic_cursor);
    DBMS_SQL.CLOSE_CURSOR(dynamic_cursor);
    END LOOP;

    write_to_driver_log(TO_CHAR(SYSDATE, 'MM/DD/YY-HH24:MIS')||' - Partition Created Successfully for Table ' || p_target_table);
    RETURN('TRUE');
    EXCEPTION
    WHEN OTHERS THEN
    errm := sqlerrm;
    IF db_instance%isopen THEN
    CLOSE db_instance;
    END IF;
    IF c_key_ranges%isopen THEN
    CLOSE c_key_ranges;
    END IF;
    IF user_tablespace_cursor%isopen THEN
    CLOSE user_tablespace_cursor;
    END IF;
    /* Clean up on failure too. */
    DBMS_SQL.CLOSE_CURSOR(dynamic_cursor);
    write_to_driver_log('FAIL'||SQLERRM);
    RETURN('FAIL');
    END create_partition;
    G.Sakthi

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You don't give us much specific information about the error, but this is wrong:

    partition_suffix_format || ' VALUES LESS THAN (' || high_value_date || ') TABLESPACE ' || tblspace_name;

    That will translate to something like:

    VALUES LESS THAN (03-DEC-04) TABLESPACE XXX

    It needs to be more like:

    VALUES LESS THAN (TO_DATE('03-DEC-2004','DD-MON-YYYY')) TABLESPACE XXX

  3. #3
    Join Date
    Jan 2004
    Location
    Mumbai, India
    Posts
    16
    actually i have changed th ecode like this

    dynamic_stmt := 'ALTER TABLE ' || p_target_table || ' ADD PARTITION ' || p_target_table ||
    partition_suffix_format || ' VALUES LESS THAN (TO_DATE(' || high_value_date || ',''DD-MON-YY'')) TABLESPACE ' ||
    tblspace_name;

    but again same error happened,
    G.Sakthi

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Try writing the result of that assignment with DBMS_OUTPUT, and maybe you will spot your syntax error (I can see it!)

Posting Permissions

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