partitioned tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: partitioned tables

  1. #1
    Join Date
    Oct 2000
    Posts
    16
    Folks,
    I need to do the following:

    Have a table that has 92 partitions. They are named as part001, part002..... part092.

    Want to do insert into the partition directly. The partition i want to insert into will be called based on the value of a parameter i send in to the stored procedure.
    A simple way is to use IF..THEN ..ELSE... but for 92 times its a little too repetitious.

    ie., if value = 1 then
    insert into emp partition (part001).....
    elsif value = 2 then
    insert into emp partition (part002)....
    elsif.....
    ....

    Can I build my insert string dynamically? ie.,

    create or replace procedure doinsert as
    i varchar2;
    v_part varchar2;

    So, if VALUE = 2, then

    if length(value) = 1 then
    i = '00' || to_char(VALUE)
    elsif length(calue) = 2 then
    i = '0' || to_char(VALUE)
    elsif length(value) = 3 then
    i = to_char(VALUE)
    end if;

    v_part = 'part' || i
    Insert into partition v_part ... ..........
    ...
    ...
    end;
    /


    The above doesn't seem to work. Any suggestions please....

    Thanks,
    Aruna

  2. #2
    Join Date
    Dec 2000
    Location
    Brazil / São Paulo
    Posts
    97

    Lightbulb

    Declare
    vTable Varchar2 (10);
    Begin
    vTable := 'part' || LPAD (value, 3, '0');
    Insert into partition vTable...
    End;
    Márcio de Souza Almeida
    DBA Oracle / SQLServer / PostgreSQL
    Rua Cupa, 139 Apto 85 A
    Penha - São Paulo - SP
    03640-000 - Brasil
    http://www.directory.com.br

Posting Permissions

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



Click Here to Expand Forum to Full Width