cross-schema queries in stored proc

[themikeaustin]

All,

I need to link in tables that are in a different schema. How can I do this in a stored proc (as opposed to SQL*Plus).

I'm coming back to PL/SQL after working with T-SQL and I'm a little rusty... In addition, I couldn't find how to do this in any of the 7 PL/SQL books available to me.

TIA,

Mike

 

[SantaMufasa]

Mike,

Following is PL/SQL code that I ran from Oracle user, "TEST", against tables owned by Oracle user "DHUNT":

set serveroutput on
begin
	for r in (select last_name, name from dhunt.s_emp e, dhunt.s_dept d
			where e.dept_id = d.id and rownum <= 5) loop
		dbms_output.put_line (r.last_name||': '||r.name);
	end loop;
end;
/

Velasquez: Administration
Ngao: Operations
Nagayama: Sales
Quick-To-See: Finance
Ropeburn: Administration

Does this give you enough to proceed?

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:20 (30Jun04) UTC (aka "GMT" and "Zulu"), 18:20 (29Jun04) Mountain Time)

 

[sbix]

Santa forgot to specify the obvious ... you should be granted to select into the other user's object
)

 

[themikeaustin]

If I try to execute the following:

GRANT SELECT ON DMART_TEST.ERACCOUNTDIM TO FINANCE_ML_QA;

I receive a "Table or View does not exist" message.

If I add the database:

GRANT SELECT ON MHBENG02.DMART_TEST.ERACCOUNTDIM TO FINANCE_ML_QA;

I receive a "Missing keyword" error message.

Any suggestions welcome.

Mike

 

[sem]

You should login as table owner and issue grant. You may omit fully qualified names.

Regards, Dima

http://seminihin.narod.ru