Home » SQL & PL/SQL » SQL & PL/SQL » Merge over Multiple Schemas
icon5.gif  Merge over Multiple Schemas [message #264012] Fri, 31 August 2007 09:07 Go to next message
jaylrob
Messages: 6
Registered: August 2007
Junior Member
ProcedureA and TableA are located in SchemaA. ProcedureA performs a merge between View B of SchemaB and TableA. ProcedureA is giving a ORA-00942(table or view does not exist) for the following line ... USING (SELECT * FROM B.VIEWB) D

ORA version 9.2.0.6

MERGE INTO A.TableA C
USING (SELECT * FROM B.ViewB) D
ON (C.dealerid = D.di_dealer_id)
WHEN MATCHED THEN UPDATE SET

C.dealername = D.di_dealer_name,
C.dealerno = D.di_dealer_no,
C.fedid = D.di_fed_id,
C.oldate = D.di_online_dte, 


Permissions given are as follows

Schema A
----------------
ProcedureA - execute on User A
TableA- Insert, Delete, Update, Select on User A

Schema B
----------------
ViewB - Select on User A, Select on UserARole


[Updated on: Fri, 31 August 2007 09:16]

Report message to a moderator

Re: Merge over Multiple Schemas [message #264014 is a reply to message #264012] Fri, 31 August 2007 09:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

If you'd posted a test case that shows what you did, then we can:
1/ see what and where it's wrong
2/ test by ourself to find how to fix it

Regards
Michel
Re: Merge over Multiple Schemas [message #264018 is a reply to message #264012] Fri, 31 August 2007 09:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Privileges acquired via ROLE do not apply within PL/SQL procedures.


GRANT SELECT ON B.UNDERLYING_TABLE TO A.
Re: Merge over Multiple Schemas [message #264039 is a reply to message #264012] Fri, 31 August 2007 09:51 Go to previous messageGo to next message
jaylrob
Messages: 6
Registered: August 2007
Junior Member
Thanks for help. The View had already granted select privilege to A. To verify, the table from which the view is created should also grant select to A?

[Updated on: Fri, 31 August 2007 09:51]

Report message to a moderator

Re: Merge over Multiple Schemas [message #264054 is a reply to message #264012] Fri, 31 August 2007 10:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>To verify, the table from which the view is created should also grant select to A?
YES!

You can test how PL/SQL will behave from SQL*PLUS by 1st doing
SQL> SET ROLE NONE
now test any & all DML statements which exist within PL/SQL procedure.
Re: Merge over Multiple Schemas [message #264056 is a reply to message #264054] Fri, 31 August 2007 10:25 Go to previous messageGo to next message
jaylrob
Messages: 6
Registered: August 2007
Junior Member
Many thanks.
Re: Merge over Multiple Schemas [message #264077 is a reply to message #264054] Fri, 31 August 2007 12:06 Go to previous message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
anacedent wrote on Fri, 31 August 2007 08:21
SQL> SET ROLE NONE
now test any & all DML statements which exist within PL/SQL procedure.


Great idea...I never thought about testing it in that way
Previous Topic: LEFT JOIN works wrong?
Next Topic: nested table
Goto Forum:
  


Current Time: Thu Mar 28 16:39:38 CDT 2024