Results 1 to 2 of 2

Thread: add partiton

  1. #1
    Join Date
    Jul 2011
    Posts
    3

    Unanswered: add partiton

    i want to add partition to a table.

    EXECUTE IMMEDIATE 'ADD PARTITION' || 'P'||d || 'VALUES LESS THAN ('d')'

    here d is variable.

    i am using the above query but its showing invalid statement

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    A couple of observations:
    1) ADD PARTITION is not a valid SQL statement; it is a clause in ALTER TABLE statement though
    2) the second occurrence of N variable is not concatenated to the dynamic string as the first one, so even the static statement is wrong
    3) even if succeeded, some spaces are missing in the executed statement; you would easily reveal it if you printed it - next time, please do it yourself
    Code:
    with x as ( select 30 d from dual )
    select 'ADD PARTITION' || 'P'||d || 'VALUES LESS THAN ('||d||')'
    from x;
    
    'ADDPARTITION'||'P'||D||'VALUESLESSTH
    -------------------------------------
    ADD PARTITIONP30VALUES LESS THAN (30)
    "PARTITIONP30VALUES" is not a good reserved word name.

Posting Permissions

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