Oracle Query with Schema in main table

I'm finding a lot of similar articles, but none quite covering what I need. I didn't design the table nor can I modify it. I am limited to a Read Only role.

I have an Info table in a root schema. Let's call it SchemaRoot. In the info table it contains a field with addition schema's for each record.

Info.RootSchema

Then all the project info is in it's own Schema.

ProjectInfo.Project1 or ProjectInfo.Project2

I need to be able to query the the RootSchema and SubSchema's in one query.

So I need something that'll give me.

| ProjectOwner | ProjectDetail1 | ProjectDetail2 | ProjectDetail3 |

for every project in Info.RootSchema.

Excuse me, but I'm not sure we speak Oracle here. What exactly do you call a "schema"? In an Oracle database, we create a user who then creates its tables, views, procedures etc., so user + all its objects represent a schema. I think that this is what you're trying to say.

There is one "master" schema (root), while every project has its own schema (project1, project2, etc.).

If any user wants to access data owned by another user - for example, in your case it is the root user who will select data owned by users named project1, project2, etc. - then these users have to grant certain privileges to root.

Therefore, you'd

connect project1/its_password
grant select on projectinfo to root;

connect project2/its_password
grant select on projectinfo to root;

Then, connect as root and access data owned by other users either by preceding table name with its owner (e.g. project1.projectinfo - not vice versa, which is what you posted - if I got you right), or by creating synonyms as

create synonym project1_projectinfo for project1.projectinfo;
create synonym project2_projectinfo for project2.projectinfo;

For sample data:

SQL> select * from info;

SCHEMANA PROJE
-------- -----
Project1 Bob
Project2 Larry

SQL> select * From project1_projectinfo;

PROJECTD PROJECTD
-------- --------
1 info 1 1 info 2
1 info 3 1 info 4

SQL> select * from project2_projectinfo;

PROJECTD PROJECTD
-------- --------
2 info 6 2 info 7

you'd get

SQL> select r.projectowner, a.projectdetail1, a.projectdetail2
  2  from info r cross join project1_projectinfo a
  3  where r.schemaname = 'Project1'
  4  union all
  5  select r.projectowner, b.projectdetail1, b.projectdetail2
  6  from info r cross join project2_projectinfo b
  7  where r.schemaname = 'Project2';

PROJE PROJECTD PROJECTD
----- -------- --------
Bob   1 info 1 1 info 2
Bob   1 info 3 1 info 4
Larry 2 info 6 2 info 7

SQL>

That's pretty much hardcoded solution. Can it be done "dynamically"? Yes; one option is to create a function which composes select statement to be used as a source for ref cursor. For example:

SQL> create or replace function f_test
  2    return sys_refcursor
  3  is
  4    l_str varchar2(10000);
  5    rc sys_refcursor;
  6  begin
  7    for cur_r in (select schemaname, projectowner from info) loop
  8      l_str := l_str ||
  9        'select ' || chr(39) || cur_r.projectowner || chr(39) || ' as projectowner, ' ||
 10        'projectdetail1, projectdetail2 from ' || cur_r.schemaname ||'_projectinfo' ||
 11        ' union all ';
 12    end loop;
 13    l_str := regexp_replace(l_str, ' union all $');
 14    open rc for l_str;
 15    return rc;
 16  end;
 17  /

Function created.

Testing:

SQL> select f_test from dual;

F_TEST
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

PROJE PROJECTD PROJECTD
----- -------- --------
Bob   1 info 1 1 info 2
Bob   1 info 3 1 info 4
Larry 2 info 6 2 info 7


SQL>

If that's not it, then I obviously didn't understand you speaking Oracle.