Hi everybody,

Is there any method using which I can search some numbers in all
numeric fields in all the tables of a particular schema ????

This is urgent. Thnaks in advance.

"Ratan" <ratan.nitrkl@gmail.com> wrote in message
news:d75c8bf5-a9ca-4ee5-9d6b-5e3955b1fc7f@s8g2000prg.googlegroups.com...
> Hi everybody,
>
> Is there any method using which I can search some numbers in all
> numeric fields in all the tables of a particular schema ????
>
> This is urgent. Thnaks in advance.

Use sql to generate sql and run the result.

eg something like:
spool tmp.sql

select 'select * from '||owner||'.'||table_name||' where
'||column_name||'=myvalue;' from dba_tab_columns dtc
where dtc.owner='THESCHEMA' and dtc.column_type='NUMBER' order by
table_name, column_name;

spool off
@tmp.sql

You have to debug the above a little bit, but it gives you the idea.
Jim

On Jan 16, 3:44*pm, "news.verizon.net" <kenned...@verizon.net> wrote:
> "Ratan" <ratan.nit...@gmail.com> wrote in message
>
> news:d75c8bf5-a9ca-4ee5-9d6b-5e3955b1fc7f@s8g2000prg.googlegroups.com...
>
> > Hi everybody,
>
> > Is there any method using which I can search some numbers in all
> > numeric fields in all the tables of a particular schema ????
>
> > This is urgent. Thnaks in advance.
>
> Use sql to generate sql and run the result.
>
> eg something like:
> spool tmp.sql
>
> *select 'select * from '||owner||'.'||table_name||' where
> '||column_name||'=myvalue;' from dba_tab_columns dtc
> where dtc.owner='THESCHEMA' and dtc.column_type='NUMBER' order by
> table_name, column_name;
>
> spool off
> @tmp.sql
>
> You have to debug the above a little bit, but it gives *you the idea.
> Jim


Thanks a lot Jim. I have got my answer.

But the problem is, I have to login as SYSDBA to execute this query.
As an ordinary user, I cant execute, because, then it cant find the
dba_tab_columns table. So, what is the alternative?

On 17 Jan, 07:55, Ratan <ratan.nit...@gmail.com> wrote:
> Thanks a lot Jim. I have got my answer.
>
> But the problem is, I have to login as SYSDBA to execute this query.
> As an ordinary user, I cant execute, because, then it cant find the
> dba_tab_columns table. So, what is the alternative?- Hide quoted text -
>
> - Show quoted text -

You don't have to log in as SYS dba to see the DBA_TAB_COLS view
(note, not DBA_TAB_COLUMNS). SYSTEM can see this view.

Also, as well as the DBA_* views, there are equivalent USER_* and
ALL_* views.

HTH

-g

On Jan 17, 3:35*pm, gazzag <gar...@jamms.org> wrote:
> On 17 Jan, 07:55, Ratan <ratan.nit...@gmail.com> wrote:
>
> > Thanks a lot Jim. I have got my answer.
>
> > But the problem is, I have to login as SYSDBA to execute this query.
> > As an ordinary user, I cant execute, because, then it cant find the
> > dba_tab_columns table. So, what is the alternative?- Hide quoted text -
>
> > - Show quoted text -
>
> You don't have to log in as SYS dba to see the DBA_TAB_COLS view
> (note, not DBA_TAB_COLUMNS). *SYSTEM can see this view.
>
> Also, as well as the DBA_* views, there are equivalent USER_* and
> ALL_* views.
>
> HTH
>
> -g

Thanks a ot. Now its completely working.