Home » SQL & PL/SQL » SQL & PL/SQL » Call multiple schema in Function
Call multiple schema in Function [message #409147] Fri, 19 June 2009 13:17 Go to next message
Bonita
Messages: 32
Registered: June 2008
Member
Oracle 10g 10.1

Learning Oracle as newbie.

There are two schema A,B. Created new user X as DBA, and log in as X.

In user schema X, the following SQL wroks well.
select a.y0,b.z0 from a,b where a.c0 = b.c0;

However, when I try to build function in schema X using the same SQL as body, it shows me error message

Error(10,12): PL/SQL: ORA-00942: table or view does not exist

Just in case, I grant all tables of A, B to X but has the error.

I am wondering if user X doesn't have Procedure right but it is DBA roles.

Can someone help me out, please.

Thanks for your time and reply.
Re: Call multiple schema in Function [message #409149 is a reply to message #409147] Fri, 19 June 2009 13:23 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

privileges acquired via ROLE do NOT apply within PL/SQL named procedures or functions.

Explicit GRANT on objects is required

BTW this is a FAQ!
Re: Call multiple schema in Function [message #409150 is a reply to message #409147] Fri, 19 June 2009 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a FAQ please search before posting.
Also you should also read the prerequisites in documentation when you use something.

http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html

Regards
Michel
Re: Call multiple schema in Function [message #409152 is a reply to message #409150] Fri, 19 June 2009 14:04 Go to previous messageGo to next message
Bonita
Messages: 32
Registered: June 2008
Member
Thanks.

The problem is that after I use following command,

 grant create any procedure to x;
 grant execute any procedure to x;


It still show error message.

Error(10,12): PL/SQL: ORA-00942: table or view does not exist


What did I miss ? Please advise.
Re: Call multiple schema in Function [message #409153 is a reply to message #409147] Fri, 19 June 2009 14:10 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
from Posting Guidelines!

Practice


* Show us what you did (if you tried it yourself) and how Oracle responded (COPY & PASTE your SQL*Plus session), including errors and/or why the result is not what you want.
Do not describe, explain or report - show us!
* Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure. It'll help people setting up a test set (yes, some people really do that)
* Provide INSERT statements for sample data instead of pasting in or mocking up the results of a SELECT statement.
* Provide your expected result set and explain the rules/reasons that lead to it.
* If you want to post error codes or SQL*Plus output, just copy everything that is on your screen when the error occurred, for instance:
Re: Call multiple schema in Function [message #409154 is a reply to message #409152] Fri, 19 June 2009 14:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How do you expect we can give any help without the code?
The only answer we can give is:
Quote:
table or view does not exist

Prove us this is not true.

Regards
Michel
Re: Call multiple schema in Function [message #409156 is a reply to message #409147] Fri, 19 June 2009 14:27 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>There are two schema A,B. Created new user X as DBA, and log in as X.
>In user schema X, the following SQL wroks well.
>select a.y0,b.z0 from a,b where a.c0 = b.c0;

I do not believe SELECT above works against schema A & B tables.
Please post proof by CUT & PASTE whole sqlplus session.
>two schema A,B.
>from a,b?
Really?

[Updated on: Fri, 19 June 2009 14:28]

Report message to a moderator

Re: Call multiple schema in Function [message #409159 is a reply to message #409147] Fri, 19 June 2009 14:57 Go to previous messageGo to next message
Bonita
Messages: 32
Registered: June 2008
Member
Many thanks.

OK. I quickly built one to describe my Q better ( Sorry if I confuse you before ).

Oracle 10g.

In HR schemam built sample function

CREATE OR REPLACE
FUNCTION fnSalesDeptID (id IN NUMBER)
   RETURN VARCHAR2 
IS
      TempVar HR.DEPARTMENTS.DEPARTMENT_NAME%TYPE;
   BEGIN
	 
SELECT DEPARTMENTS.DEPARTMENT_NAME INTO TempVar
  FROM HR.EMPLOYEES EMPLOYEES
       ,HR.DEPARTMENTS DEPARTMENTS
       WHERE (EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
                 AND EMPLOYEES.EMPLOYEE_ID = id );
      RETURN TempVar;
   END fnSalesDeptID;


Create user SA user, and
GRANT CREATE ANY PROCEDURE TO SA;
GRANT EXECUTE ANY PROCEDURE TO SA;


Log in as User SA

Following SQL works well after grant above HR schema sample function to SA

SELECT EMPLOYEE_ID, HR.fnsalesdeptid(EMPLOYEE_ID) FROM HR.employees;


In user X schema, build exact the same function ( only the name is different )

CREATE OR REPLACE
FUNCTION fnSalesDeptID_0 (id IN NUMBER)
   RETURN VARCHAR2 
IS
      TempVar HR.DEPARTMENTS.DEPARTMENT_NAME%TYPE;
   BEGIN
	 
SELECT DEPARTMENTS.DEPARTMENT_NAME INTO TempVar
  FROM HR.EMPLOYEES EMPLOYEES
       ,HR.DEPARTMENTS DEPARTMENTS
       WHERE (EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
                 AND EMPLOYEES.EMPLOYEE_ID = id );

      RETURN TempVar;

   END fnSalesDeptID_0;


To do Complie, get folowing messge

    FUNCTION SA.FNSALESDEPTID_0@SA
        Error(5,15): PLS-00201: identifier 'HR.DEPARTMENTS' must be declared
        Error(5,15): PL/SQL: Item ignored
        Error(8,1): PL/SQL: SQL Statement ignored
        Error(10,12): PL/SQL: ORA-00942: table or view does not exist
        Error(14,7): PL/SQL: Statement ignored
        Error(14,14): PLS-00320: the declaration of the type of this expression is incomplete or malformed


Pretty simple but it drives me nuts.

Once again, thank you.
Re: Call multiple schema in Function [message #409162 is a reply to message #409147] Fri, 19 June 2009 15:03 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
GRANT SELECT ON HR.EMPLOYEES TO SA;

Explicit GRANT on object is required
Explicit GRANT on object is required
Explicit GRANT on object is required
Re: Call multiple schema in Function [message #409163 is a reply to message #409159] Fri, 19 June 2009 15:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you read what I said you?
Why don't you read the link I posted you from top to bottom?

The last sentence is:
Quote:
To be able to perform that operation in a typical procedure, you need to have that privelege granted directly to you.


Regards
Michel
Re: Call multiple schema in Function [message #409167 is a reply to message #409162] Fri, 19 June 2009 15:33 Go to previous messageGo to next message
Bonita
Messages: 32
Registered: June 2008
Member
o..ops.

I suppose I had it done already.

Because there are more than 570 tables in scheme X, and Y. I use following procedure to do the job. It works.

CREATE OR REPLACE 
PROCEDURE pGRANT_TABLES
   IS
   BEGIN
      FOR tab IN (SELECT table_name
                  FROM   all_tables
                  WHERE  owner = 'HR') LOOP
         EXECUTE IMMEDIATE 'GRANT SELECT ON '||tab.table_name||' TO other_user';
      END LOOP;
   END;


Once again, thanks.
Re: Call multiple schema in Function [message #409169 is a reply to message #409159] Fri, 19 June 2009 15:36 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OP
GRANT CREATE ANY PROCEDURE TO SA;
GRANT EXECUTE ANY PROCEDURE TO SA;

vs.

ORA-00942: table or view does not exist


See the difference? "Procedure" is different from "table or view".

[EDIT] Oh, so you've solved the problem.

[Updated on: Fri, 19 June 2009 15:37]

Report message to a moderator

Re: Call multiple schema in Function [message #409213 is a reply to message #409147] Sat, 20 June 2009 10:03 Go to previous message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>I suppose I had it done already.
So you say.
You don't know, what you don't know.

>Because there are more than 570 tables in scheme X, and Y.
The number of tables owned by schema X or Y has NOTHING to do with them accessing table in HR schema.


>I use following procedure to do the job. It works.
I am not so sure "It works".
It depends upon which user actually invoked the pGRANT_TABLES procedure.
Previous Topic: how to separate mobileno and landlineno in oracle(input is in single field)
Next Topic: trigger has :new is a collection type
Goto Forum:
  


Current Time: Tue Dec 06 04:42:41 CST 2016

Total time taken to generate the page: 0.44597 seconds