OraFAQ Forum: SQL & PL/SQL » Merge over Multiple Schemas

Home » SQL & PL/SQL » SQL & PL/SQL » Merge over Multiple Schemas

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Merge over Multiple Schemas [message #264012]

Fri, 31 August 2007 09:07

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

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Merge over Multiple Schemas [message #264018 is a reply to message #264012]

Fri, 31 August 2007 09:23

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.

Report message to a moderator

Re: Merge over Multiple Schemas [message #264039 is a reply to message #264012]

Fri, 31 August 2007 09:51

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

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.

Report message to a moderator