Re: need help with script

From: Mohammed Mehraj Hussain <mhdmehraj_at_gmail.com>
Date: Thu, 24 Sep 2009 11:22:47 +0530
Message-ID: <b081c0a50909232252rf80635bo14c33276912dc002_at_mail.gmail.com>

Hi,
Please read the Mitchell QUESTION CAREFULLY.

<<<<I need a script that will select the owner and table names for the user-defined schemas that do not have any columns defined with a datatype = LONG. The script need to skip the table if any column is defined as a datatype = LONG.>>>>>>

On Thu, Sep 24, 2009 at 11:14 AM, Balakrishna Y <krishna000_at_gmail.com>wrote:

> Hi,
>
> But it works in the other way . ha ha ha
>
> SQL> select table_name from user_Tab_columns where data_type='LONG';
>
> TABLE_NAME
> ------------------------------
> BOB
> LONG_ONLY.
>
> Regards
>
> Bala
>
> On Wed, Sep 23, 2009 at 7:19 PM, Kevin Lidh <kevin.lidh_at_gmail.com> wrote:
>
>> The queries suggested similar to this would only work if the only column
>> in the table was a LONG.
>>
>> SQL> create table bob (col1 long,col2 varchar2(2));
>>
>> Table created.
>>
>> SQL> create table long_only(col1 long);
>>
>> Table created.
>>
>> SQL> select distinct(table_name) from user_tab_columns where data_type
>> <> 'LONG';
>>
>> TABLE_NAME
>> ------------------------------
>> BOB
>>
>> SQL>
>>
>>
>> On Wed, 2009-09-23 at 06:27 -0700, Joey D'Antoni wrote:
>> > This should work--you probably need to edit the schema list to reflect
>> > options installed in your database.
>> >
>> > select distinct owner,table_name
>> > from dba_Tab_cols where owner not in
>> > ('SYSTEM','XDB','SYS','TSMSYS','PERFSTAT','OUTLN','DBSNMP') and
>> > data_type <> 'LONG'
>> >
>> > Joseph D'Antoni
>> > Synthes USA
>> >
>> >
>> >
>> > ______________________________________________________________________
>> > From: Helen Mitchell <HelenJMitchell_at_Comcast.net>
>> > To: oracle-l <oracle-l_at_freelists.org>; Helen Mitchell
>> > <helen.j.mitchell_at_ulalaunch.com>
>> > Sent: Wednesday, September 23, 2009 9:11:18 AM
>> > Subject: need help with script
>> >
>> >
>> >
>> > I need a script that will select the owner and table names for the
>> > user-defined schemas that do not have any columns defined with a
>> > datatype = LONG. The script need to skip the table if any column is
>> > defined as a datatype = LONG.
>> >
>> >
>> >
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 24 2009 - 00:52:47 CDT