Re: add column to many tables at once
From: Michael Austin <maustin_at_firstdbasource.com>
Date: Sat, 18 Apr 2009 18:40:52 -0500
Message-ID: <uwtGl.29362$ZP4.17814_at_nlpi067.nbdc.sbc.com>
student4life wrote:
> On Apr 18, 5:35 pm, johnbhur..._at_sbcglobal.net wrote:
>
> ok where I work they have tables prefixed with owner's name. Don't ask
> me why because I don't know. I use the following script that is
> supposed to add column on tables based on owner name
>
> select 'alter table '||table_name|| ' add (column_name varchar2);'
> from all_tables
> where owner = 'some_name';
>
> but I got error 'no rows selected' even though the name matched, so I
> thought I might try a different approach. I am also looking at this
> plsql :
>
> http://www.oracle.com/technology/oramag/code/tips2004/011104.html
>
> to use part of it but my plsql IDE is messed up now so I can really
> test it yet.
Date: Sat, 18 Apr 2009 18:40:52 -0500
Message-ID: <uwtGl.29362$ZP4.17814_at_nlpi067.nbdc.sbc.com>
student4life wrote:
> On Apr 18, 5:35 pm, johnbhur..._at_sbcglobal.net wrote:
>> On Apr 18, 6:25 pm, student4life <student4li..._at_gmail.com> wrote: >> >>> Could someone show me how I can add a column to many tables that have >>> names starting with some similar characters? TIA. >> Why would you want to do that exactly? Give us a convincing business >> case that this is some kind of real world business need and you might >> get some help. >> >> Otherwise it just sounds like homework ... perhaps your homework? >> >> Back to you for a question ... what would your sql or plsql look like >> to find a list of table names that start with some similar characters?
>
> ok where I work they have tables prefixed with owner's name. Don't ask
> me why because I don't know. I use the following script that is
> supposed to add column on tables based on owner name
>
> select 'alter table '||table_name|| ' add (column_name varchar2);'
> from all_tables
> where owner = 'some_name';
>
> but I got error 'no rows selected' even though the name matched, so I
> thought I might try a different approach. I am also looking at this
> plsql :
>
> http://www.oracle.com/technology/oramag/code/tips2004/011104.html
>
> to use part of it but my plsql IDE is messed up now so I can really
> test it yet.
are you sure you are selecting the correct thing (upper case maybe)? TABLE_NAME and OWNER are generally all uppercase when evaluating "equals".
Do you have access to DBA_TABLES? Received on Sat Apr 18 2009 - 18:40:52 CDT