Re: see if any / all / none of objects in schema are accessable

"Bernd Kochs" <Bernd.Kochs_at_T-Online.de> wrote in message news:3DEBBCEC.AA6C2409_at_T-Online.de...
> Hello erveryone,
>
> I'm new to Oracle but not new to Sql .
> Previosly I made some Stored Procedures in MS-Sql-Server 7.0.
> There was the need and there is the need to look if a table or view is
> accessable (exits).

Correct: there *was*. In Oracle there is *no* need as Oracle has exception handling. It will enable you to avoid writing spaghetti programs, which cater for seldom encountered problems.

> If I use a select on the all_objects , I noticed , that some of the
> objects in other schemas are not shown to me, because
> my schema/user hasn't the right to see objects of other schemas.

This is why they don't show in all_objects: all objects shows all the objects to which you have access, *not* *all* the objects in the database. Select * from dict where table_name = 'all_objects' will show you that.

>
> Writing a stored procedure in oracle with the ability to do things in
> other schemas brings up the need to look in
> the dictionary of other users, which causes problems if I cannot see, if
> there is a table or view, then I
> cannot decide in the stored procedure whether to delete or create or
> truncate it..

You shouldn't be capable to create objects in other users schema's or you will end up with a mess.

>
> The funny thing I noticed , that I had the right to create a table in
> another schema , but not to see it in ALL_OBJECTS.

Because the creating user didn't grant select on the newly created table.
>
> Regarding the ALL_OBJECTS View :
>
> 1.)If I see some Objects of a Schema that is'nt mine , I need a Function
> that tells me that this are all Objects in this Schema
> that exist.

You definitely don't. Most lazy programmers get around that by granting the DBA role to all their accounts. Thus can have access to the dba_objects view which shows all the objects in the database. select_catalog_role would have been sufficient for that purpose. However, you don't need that function as Oracle has exception handling.

> 2.)If I see no Objects in Schema , but the Schema exists (Select User
> from DUAL) , I need a Function that tells me that
> there is'nt any Object in this Schema.
>

You don't need that function.

> Greetings
> Bernd Kochs
>
>
>
>

Reading the Oracle Concepts manual wouldn't harm you

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address


Ceterum censeo: that all developers brainwashed by sql server should *first*
start reading Oracle manuals instead of just mucking around.