Renaming Columns


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Renaming Columns

  1. #1
    Laura Guest

    Renaming Columns


    Why is it that you can't simply rename a column in Oracle? I can't believe
    one actualy has to drop and recreate the column...does anyone have a logical
    explanation of why?!

  2. #2
    Vince Guest

    Re: Renaming Columns


    "Laura" <laurarosati@hotmail.com> wrote:
    >
    >Why is it that you can't simply rename a column in Oracle? I can't believe
    >one actualy has to drop and recreate the column...does anyone have a logical
    >explanation of why?!


    You must be new to Oracle.
    This is just one of the pains. You'll discover more.

  3. #3
    Luiz Guest

    Re: Renaming Columns


    Hi, Laura.

    Run this follow script at SQLPlus to rename a column:

    set serveroutput on
    set verify off
    --clear screen
    prompt Renomeando coluna:
    prompt
    accept user prompt 'Informe o owner da tabela: '
    accept table_name prompt 'Informe a tabela: '
    accept old_name prompt 'Informe a coluna a ser renomeada: '
    accept new_name prompt 'Informe o novo nome para coluna: '
    prompt
    prompt Aguarde....
    prompt
    declare
    id number;
    col_id number;
    cursor_name1 INTEGER;
    cursor_name2 INTEGER;
    ret1 INTEGER;
    ret2 INTEGER;
    begin
    select object_id into id from dba_objects
    where object_name=UPPER('&table_name')
    and owner=UPPER('&user')
    and object_type='TABLE';

    select col#
    into col_id
    from sys.col$
    where obj#=id
    and name=UPPER('&old_name');

    update sys.col$ set name=UPPER('&new_name')
    where obj#=id
    and col#=col_id;

    dbms_output.put_line('A coluna '||'&old_name'||' da tabela
    '||'&user'||'.&table_name'||' foi renomeada para '||'&new_name'||'.');

    commit;
    cursor_name1 := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cursor_name1, 'ALTER SYSTEM FLUSH SHARED_POOL',DBMS_SQL.native);
    ret1 := DBMS_SQL.EXECUTE(cursor_name1);
    DBMS_SQL.CLOSE_CURSOR(cursor_name1);
    cursor_name2:= DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cursor_name2, 'ALTER SYSTEM CHECKPOINT',DBMS_SQL.native);
    ret2:= DBMS_SQL.EXECUTE(cursor_name2);
    DBMS_SQL.CLOSE_CURSOR(cursor_name2);
    end;
    /


    "Vince" <vruan@yahoo.com> wrote:
    >
    >"Laura" <laurarosati@hotmail.com> wrote:
    >>
    >>Why is it that you can't simply rename a column in Oracle? I can't believe
    >>one actualy has to drop and recreate the column...does anyone have a logical
    >>explanation of why?!

    >
    >You must be new to Oracle.
    >This is just one of the pains. You'll discover more.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center