Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Referencing other schemas' tables in PL/SQL procedure

RE: Referencing other schemas' tables in PL/SQL procedure

From: Naveen Nahata <naveen_nahata_at_mindtree.com>
Date: Tue, 12 Aug 2003 04:06:07 -0800
Message-ID: <F001.005CA465.20030812040607@fatcity.com>


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:Paul.Vincent_at_uce.ac.uk]
>>>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: Paul.Vincent_at_uce.ac.uk
>>>
>>>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: ListGuru_at_fatcity.com (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: naveen_nahata_at_mindtree.com

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: ListGuru_at_fatcity.com (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).
Received on Tue Aug 12 2003 - 07:06:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US