| Cursor over two schemas? [message #369796] |
Fri, 13 October 2000 05:15  |
Fabian
Messages: 19
Registered: October 2000
|
Junior Member |
|
|
Hello I'm a beginner in PL/SQL,so I need your help.
How is it possible to fetch a cursor over two schemas?
My Procedure is:
Procedure P_HERST_ADR (VER_NR IN VARCHAR2)
IS
cursor cHerstAdr is
SELECT *
FROM HERST_ADR;
cHerst_rec cHerstAdr%ROWTYPE;
BEGIN
delete from M_HERST_ADR where VERSIONS_NR=VER_NR;
open cHerstAdr;
loop
fetch cHerstadr into cHerst_rec;
exit when cHerstadr%NOTFOUND;
-- insert into M_HERST_ADR VALUES(VER_NR);
.....
end loop;
close cHerstadr;
END; -- Procedure HERST_ADR
The Proc. is in the schema SIV , the table M_HERST_ADR is in the schema MEDIA.
The Exeption-SQL is:
BEGIN
p_herst_adr;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
RAISE;
END;
The Error Message is:
PLS-00201 type "MEDIA.M_HERST_ADR" mus be declared
Oracle notices that the table is in another schema but the procedure is not compilable. How have I to to declare the table?
(Answers please with Code and Syntax, because I'm a beginner)
Thanks a lot for your help
Fabian
|
|
|
|
| Re: Cursor over two schemas? [message #369797 is a reply to message #369796] |
Fri, 13 October 2000 05:24  |
Markus Delhofen
Messages: 1
Registered: October 2000
|
Junior Member |
|
|
In order to select tables from schema MEDIA in the schema SIV the user must have the akurat priviliges.
this can be done by GRANT SELECT ON
TO <USER_NAME> in the schema MEDIA to give the user in teh schema SIV access to the table.
Look this up in the oracle documentation in the SQL Reference (see GRANT)
Markus
|
|
|
|
| Re: Cursor over two schemas? [message #369799 is a reply to message #369796] |
Fri, 13 October 2000 06:05 |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
In summary, If you have a table called Table_1 in a schema called Schema_1 belonging to User_1, then to be able to see this table from Schema_2, logged on as User_2, you must:
1) Have at least SELECT access to the table
(type GRANT SELECT ON TABLE_1 TO USER_2
or GRANT SELECT ON TABLE_1 TO PUBLIC, while logged on as User_1)
2) Prefix your references to Table_1 with the name of the schema it lives in
Ie SELECT * FROM Schema_1.Table_1 WHERE stuff...
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
