Home » SQL & PL/SQL » SQL & PL/SQL » Schema Name.
Schema Name. [message #39493] Fri, 19 July 2002 08:22 Go to next message
sverch
Messages: 582
Registered: December 2000
Senior Member
I am perplexed. It looks like Oracle easily recognizes schema name in an annonimous block, but not in the named procedure.
For example:
SQL> show user
USER is "ITTR_TEST"
SQL> begin
2
3 UPDATE mo_case MC
4 SET i_status='TC'
5 WHERE c_id IN (SELECT c_id
6 FROM ittr_test3.m_case);
7
8 END;
9 /

PL/SQL procedure successfully completed.

SQL> select count(*) from mo_case where i_status='TC';

COUNT(*)
---------
2257
Everything is fine. But when I try to create stored procedure with the same code it fails:

SQL> CREATE OR REPLACE PROCEDURE TEST AS
2
3 begin
4
5
6 UPDATE mo_case MC
7 SET i_status='TC'
8 WHERE c_id IN (SELECT c_id
9 FROM ittr_test3.m_case);
10
11 END;
12 /
Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3 PL/SQL: SQL Statement ignored
9/11 PLS-00201: identifier 'ITTR_TEST3.M_CASE
must be declared
SQL>
If anybody knows why I beg you to please enlighten me!
Re: Schema Name. [message #39497 is a reply to message #39493] Fri, 19 July 2002 09:03 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
as per your postings
SQL> show user
USER is "ITTR_TEST"

so the current user is ITTR_TEST.
but in the code you are using ITTR_TEST3.
which is another schema. 
Is this a typo?
if not, and u intend to select from another schema,
make sure, you have an explicitly granted select privilige over that table.

Re: Schema Name. [message #39499 is a reply to message #39497] Fri, 19 July 2002 09:05 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
More info on why you need a direct grant (as opposed to a grant through a role) to create a procedure referencing that table:

http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html
Previous Topic: to_date from character field yyyymmdd
Next Topic: how to delete all the rows from a table?
Goto Forum:
  


Current Time: Thu Mar 28 15:11:55 CDT 2024