Re: Dbms_metadata experts?
From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Wed, 18 Mar 2015 13:37:50 -0500
Message-Id: <CAB1B8BD-D27B-486B-A9E6-7FA4960CDD41_at_gmail.com>
Below is what I have so far. I would like to have a neater way of changing char to varchar2, and I would like to change the table names to tablename_int.
>>> On Mar 18, 2015, at 11:47 AM, Stefan Knecht <knecht.stefan_at_gmail.com> wrote:
>>>
>>> Andrew
>>>
>>> If that's the only change you need to do - change char() to varchar2() - you won't need to bother with dbms_metadata nor dbms_redefinition:
>>>
>>>
>>> SYS_at_DEV > create table t (x char(10));
>>>
>>> Table created.
>>>
>>> SYS_at_DEV > alter table t modify ( x varchar2(10));
>>>
>>> Table altered.
>>>
>>> Stefan
>>>
>>>
Date: Wed, 18 Mar 2015 13:37:50 -0500
Message-Id: <CAB1B8BD-D27B-486B-A9E6-7FA4960CDD41_at_gmail.com>
Below is what I have so far. I would like to have a neater way of changing char to varchar2, and I would like to change the table names to tablename_int.
select 'select replace(dbms_metadata.get_ddl(object_type=>'||''''||'TABLE'||''''||',name=>'||''''||table_name||''''||',schema=>'|| ''''||owner||''''||')'||'||'||''''||';'||''''|| ','||''''||'CHAR('||''''||','||''''||'varchar2('||''''||') from dual;' from dba_tables where owner='SCOTT';
Sent from my iPad
> On Mar 18, 2015, at 1:15 PM, Steve Harville <steve.harville_at_gmail.com> wrote: > > I'm interested in this too if you find what you are looking for. > >> On Wed, Mar 18, 2015 at 1:57 PM Andrew Kerber <andrew.kerber_at_gmail.com> wrote: >> Well, it does look I can use the replace function on the get_ddl call. That works, but just doesn't seem very neat to me. Does anyone know of a way to do this using the functionality of the dbms_metadata package? Or maybe I am just being a little OCD. :) >> >> Sent from my iPad >>
>>> On Mar 18, 2015, at 11:47 AM, Stefan Knecht <knecht.stefan_at_gmail.com> wrote:
>>>
>>
>>> Andrew
>>>
>>> If that's the only change you need to do - change char() to varchar2() - you won't need to bother with dbms_metadata nor dbms_redefinition:
>>>
>>>
>>> SYS_at_DEV > create table t (x char(10));
>>>
>>> Table created.
>>>
>>> SYS_at_DEV > alter table t modify ( x varchar2(10));
>>>
>>> Table altered.
>>>
>>> Stefan
>>>
>>>
>>>> On Wed, Mar 18, 2015 at 11:42 PM, Andrew Kerber <andrew.kerber_at_gmail.com> wrote: >>>> Ok. I am looking to find help with dbms_metadata. This may not be possible, but I have a bunch of tables I need to change the layout on with dbms_redefinition. I need to change all the char data types to varchar2. Is there any way to set a transformation in dbms_metadata to do that transformation as the DDL is generated for the interim table? >>>> >>>> Sent from my iPad-- >>>> http://www.freelists.org/webpage/oracle-l
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 18 2015 - 19:37:50 CET