Naveen - many thanks! I had a vague recollection that this may be the case, rattling around in the back of my mind, but I couldn't find confirmation in the manuals. Thanks for the definitive answer! It's an annoying restriction, but now we can cope with it!
Paul -----Original Message----- Sent: 12 August 2003 13:06 To: Multiple recipients of list ORACLE-L Any privileges granted through roles are not enabled in PL/SQL procedures. You need to have the privilege granted directly not through a ROLE. Regards Naveen >>>-----Original Message----- >>>From: Paul Vincent [mailto:[EMAIL PROTECTED] >>>Sent: Tuesday, August 12, 2003 5:29 PM >>>To: Multiple recipients of list ORACLE-L >>>Subject: Referencing other schemas' tables in PL/SQL procedure >>> >>> >>>Hi, >>> >>>one of our developers is having a problem. His userid has >>>the DBA role on a test database, and he's written a PL/SQL >>>procedure, in his schema, which is referencing (via SELECT) >>>and updating a table in another schema, so he's coding the select as: >>> >>> CURSOR c1 >>> IS >>> select distinct ORIG_MODULE >>> from QLDBA.GENTRAN >>> where TRANS_DT = to_date('15/07/2003','dd/mm/yyyy'); >>> >>>...however, when he tries to compile the procedure, he gets >>>several error messages including: >>> >>>Line # = 16 Column # = 11 Error Text = PLS-00201: identifier >>>'QLDBA.GENTRAN' must be declared >>> >>> >>>Now, the table GENTRAN certainly exists in the QLDBA schema, >>>so there must be some rule being broken here. I thought >>>anyone with the DBA role could do any DML on any table in >>>any schema? Indeed, when the guy runs the select in a >>>SQL*Plus window, it works fine, so can any PL/SQL guru shed >>>some light on this? My PL/SQL skills are pretty rudimentary, >>>and a rummage through the PL/SQL User Guide didn't turn >>>anything up... >>> >>>Hope someone can help! >>> >>>Regards, >>> >>>Paul >>>-- >>>Please see the official ORACLE-L FAQ: http://www.orafaq.net >>>-- >>>Author: Paul Vincent >>> INET: [EMAIL PROTECTED] >>> >>>Fat City Network Services -- 858-538-5051 http://www.fatcity.com >>>San Diego, California -- Mailing list and web hosting services >>>--------------------------------------------------------------------- >>>To REMOVE yourself from this mailing list, send an E-Mail message >>>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >>>the message BODY, include a line containing: UNSUB ORACLE-L >>>(or the name of mailing list you want to be removed from). You may >>>also send the HELP command for other information (like subscribing). >>> DISCLAIMER: This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Vincent INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
