RE: Convert PL/SQL code to DBMS_SQL code

  • From: "Mindaugas Navickas" <mnavickas@xxxxxxxxx>
  • To: <gnahmed.c@xxxxxxxxxx>, "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Sun, 17 Jun 2007 23:01:23 -0400

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.

 

Other related posts: