RE: Convert PL/SQL code to DBMS

From: Mindaugas Navickas <mnavickas_at_yahoo.com>
Date: Sun, 17 Jun 2007 23:01:23 -0400
Message-ID: <000001c7b154$f3f83e10$6401a8c0@MN>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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.

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Jun 17 2007 - 22:01:23 CDT

RE: Convert PL/SQL code to DBMS_SQL code