Hi Nizar, Take a look at this function that I wrote a while ago to accomplish the same task... ---------------------------------------------------------------------------- ---- -- FUNCTION add_p -- generates dynamic DDL to add a partition. -- PARAMETERS: -- p_partition_table_name - partitioned table name -- p_partition_name - partition name to be added -- p_high_value - partition high value as a string with format YYYY-MM-DD HH24 FUNCTION add_p( p_partition_table_name VARCHAR2, p_partition_name VARCHAR2, p_high_value VARCHAR2 ) RETURN PLS_INTEGER IS v_cursor2 PLS_INTEGER; v_add_partition_stmt VARCHAR2(2000); v_ReturnCode PLS_INTEGER; v_subpartition_count PLS_INTEGER; v_default_tablespace VARCHAR2(100); err_code PLS_INTEGER; BEGIN /* -- Getting the number of subpartitions to use in the alter statement -- */ SELECT def_subpartition_count, def_tablespace_name INTO v_subpartition_count, v_default_tablespace FROM user_part_tables WHERE table_name = p_partition_table_name; /* -- Formattin the create partition statment -- */ v_add_partition_stmt := 'ALTER TABLE ' || p_partition_table_name || ' ADD PARTITION ' || p_partition_name || ' VALUES LESS THAN ( TO_DATE('''|| p_high_value || ''',''YYYY-MM-DD HH24''))' || ' TABLESPACE ' || v_default_tablespace; IF v_subpartition_count > 0 THEN v_add_partition_stmt := v_add_partition_stmt || ' SUBPARTITIONS ' || v_subpartition_count || ' STORE IN ' || '(' || v_default_tablespace || ')'; END IF; dbms_output.put_line(v_add_partition_stmt); -- UTL_FILE.PUT_LINE(file_handle, v_add_partition_stmt); v_cursor2 := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(v_cursor2, v_add_partition_stmt, dbms_sql.native); v_ReturnCode := DBMS_SQL.EXECUTE(v_cursor2); IF DBMS_SQL.IS_OPEN(v_cursor2) THEN DBMS_SQL.CLOSE_CURSOR(v_cursor2); END IF; RETURN 0; EXCEPTION WHEN OTHERS THEN err_code := SQLCODE; -- UTL_FILE.PUT_LINE(file_handle, 'ERR:ADD_P: '|| SQLERRM ); IF DBMS_SQL.IS_OPEN(v_cursor2) THEN DBMS_SQL.CLOSE_CURSOR(v_cursor2); END IF; RETURN err_code; END; Regards Mindaugas Navickas Oracle&DB2 DBA -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Nizar Ahmed Sent: June 17, 2007 5:16 AM To: oracle-l Subject: Convert PL/SQL code to DBMS_SQL code I have code whose purpose is to add 4 partitions to a table every day 6 hourly. I need to convert this code to one using DBMS_SQL. I am not a good programmer, appreciate any help.