Re: SQL Quest...

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Tue, 10 Feb 2004 12:19:53 GMT
Message-ID: <J%3Wb.50631$Wa.18435@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 '/'; Cheers ;)

Richard Received on Tue Feb 10 2004 - 06:19:53 CST