"Richard Foote" <richard.foote_at_bigpond.com> wrote in message
news:J%3Wb.50631$Wa.18435_at_news-server.bigpond.net.au...
> "Noel" <tbal_at_go2.pl> wrote in message news:c0afiv$ovl$1_at_inews.gazeta.pl...
> >
> > > SELECT *
> > > FROM ALL_OBJECTS
> > > WHERE OBJECT_NAME LIKE 'TAB/_%' ESCAPE '/';
> > >
> > > should do the trick...
> >
> > It did the trick, but, but...
> > It returns no rows since...
> >
> > SELECT *
> > FROM ALL_OBJECTS
> > WHERE OBJECT_NAME LIKE 'TAB%'
> > AND INSTR(OBJECT_NAME, CHR(95)) > 0; -- used also '_' instead of
CHR(95)
> >
> > returns 4 objects:
> > OWNER OBJECT_TYPE OBJECT_NAME
> > ------------------------------ ------------------ -------------------
> > SYS VIEW TABLE_PRIVILEGES
> > SYS TABLE TABLE_PRIVILEGE_MAP
> > PUBLIC SYNONYM TABLE_PRIVILEGES
> > PUBLIC SYNONYM TABLE_PRIVILEGE_MAP
> > --
> Hi Noel
>
> Hold on, you're not being fair !!
>
> Your original request was to find all tables with the "_" as the 4th
> character (i.e.. TAB_BOWIE). Your output above has the "_" as the 6th
> character.
>
> So did you really mean:
>
> SELECT *
> FROM ALL_OBJECTS
> WHERE OBJECT_NAME LIKE 'TABLE/_%' ESCAPE '/';
>
> or perhaps
>
> SELECT *
> FROM ALL_OBJECTS
> WHERE OBJECT_NAME LIKE 'TAB%/_%' ESCAPE '/';
This last should give the same results as the original query with chr(95).
Received on Tue Feb 10 2004 - 07:25:57 CST
