PLSQL Help - using loop values in a new query

[Kaos]

I'm trying to do the following:

• select all of the users on the system
• loop over that list of users and see if a certain table exists
• if it does print out the username

Pertinent Info - using Oracle11g, accessing via "All Around Automations PL/SQL Developer" right now, but later intending to use PHP to pull the data.

Here's how I'm trying right now

DECLARE
  cnt NUMBER;
BEGIN
  FOR myusers in ( select username from all_users ) 
    LOOP
     SELECT COUNT(*) INTO cnt from myusers.username.table_name; 
     IF ( cnt > 0 )THEN 
         dbms_output.put_line(myusers.username);
     END IF;
   END LOOP;
END;
/

The part I'm having trouble with is either A) my approach is wrong, or B) I can't use "myusers.username.table_name" - most likely because it doesn't exist, or at least it doesn't exist like I'm trying to use it.

My background is mostly in shell scripting and php so some of the PLSQL conventions are just a little different than what I'm used to. Do I perhaps need another variable declaration to hold the value of the username?

Thanks!

 

[PTNL]

I suspect you can accomplish this with an INNER JOIN statement, but....

What exact condition(s) mean that a matching table is found for a specific user? (Please give column and table names, if possible.)

 

[v6maro]

You're writing too much code in SQL, why not write a script to grab the data, then parse it how you want it. And as PTNL said above, a join will probably format it how you want it before its given back to your script.

 

[Kaos]

the existence of the table itself is enough - the table is "system_properties" - however there are any other number of unique tables that it could be.

so lets say I have a few schemas: "ninja", "pirate", and "cyborg"

If ninja.system_properties exist I want to print "ninja"
if pirate.system_properties exists I don't want to print pirate
etc

Please let me know if I can provide any more info, thanks for the help too!

-

I'm just trying to get a valid list of schemas that I care about in one shot instead of getting all the schemas and then connecting over and over again to check if the table exists.

What I'm trying to accomplish in the end is something to help a group out here at work. They keep track of what schemas are on what db servers via spreadsheet. However when they create our user accounts everyone gets their own schema - so those exist on the server along with client schemas which are the only ones we care about.

 

[PTNL]

Perhaps something like this...

SELECT    username
FROM      all_users
WHERE     username + '.system_properties' IN (SELECT table_name FROM user_tables);

 

[Kaos]

Hm, the query itself complains that '.system_properties' is not a number, I figured you were trying to do string concatenation so I changed it to

select username
from all_users where username || '.system_properties' in (SELECT table_name from user_tables);

Which is valid as far as syntax goes, but doesnt return anything.

If I run the sub select as one of the schema owners that I'm concerned about, I do get a full list of their table names though.

 

[PTNL]

Hm, the query itself complains that '.system_properties' is not a number, I figured you were trying to do string concatenation so I changed it to

select username
from all_users where username || '.system_properties' in (SELECT table_name from user_tables);

Which is valid as far as syntax goes, but doesnt return anything.

If I run the sub select as one of the schema owners that I'm concerned about, I do get a full list of their table names though.

Yes, string concatenation was intended. I haven't done much at all with PLSQL


Try weighing that inner SELECT statement against the results of:

SELECT username || '.system_properties' FROM all_users;

Does the above give you the list of table names formatted properly that you are looking for? Are you seeing entries from the above query within the earlier inner SELECT query?

 

[Kaos]

Try weighing that inner SELECT statement against the results of:

SELECT username || '.system_properties' FROM all_users;

Does the above give you the list of table names formatted properly that you are looking for? Are you seeing entries from the above query within the earlier inner SELECT query?

So if I run

select username || '.SYSTEM_PROPERTIES' from all_users;

It returns all the users with ".SYSTEM_PROPERTIES" one way or the other, I think where the problem boils down to is that when it runs "select table_name from user_tables" that it's doing it form the user I'm currently logged in as.

 

[Kaos]

And actually it turns out I had an approach that wasn't optimal. I was looking through one of the oreilly oracle books we had here over breakfast and found that there's a table/view called "all_all_tables" that gives you every table that exists for all schemas along with some other useful columns (count etc)

in the end I was able to make the query simply be

select owner from all_all_tables where table_name = 'SYSTEM_PROPERTIES' de

I still want to figure out the plsql stuff - the implementation of arrays etc isn't what Im used to but Im sure it could be useful to learn.