Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Location
    Istanbul
    Posts
    17

    Red face Unanswered: How to: Select from multiple schema's using a Stored procedure

    Hi people, Let me describe my problem briefly:

    I have two Schemas named:
    'GENERAL' and 'SOMESCHEMA'

    Below is a sp which is inside a package that has been defined in GENERAL schema. The thing is I am trying to access to some table fields which are inside another schema named SOMESCHEMA. But I am encountering error code ORA-00942 table or view does not exist . WHY ?
    Code:
    procedure LIST_USER(PCOMP_ID IN INTEGER,
                                 PCIT_COD IN INTEGER,
                                 RESULT OUT SYS_REFCURSOR)
    IS
    TMP SYS_REFCURSOR;
    
    BEGIN
        OPEN TMP FOR SELECT *
        FROM SOMESCHEMA.USERS a
        where a.COMP_ID = PCOMP_ID 
        AND a.CIT_COD= PCIT_COD;
        RESULT:= TMP;
    END;
    I know it seems pointless for that simple query why I bother that much. But this is just a small example that describes the big picture. I'll need to access to multiple schemas and insert update list from these schemas simulataneously using stored procedures...

    thanks.

  2. #2
    Join Date
    Jan 2005
    Posts
    362

    user

    Are you sure that the user that "own" the sp has access and privilege to that table?
    Dimis

  3. #3
    Join Date
    Aug 2004
    Location
    Istanbul
    Posts
    17
    it should be. Because whenever I execute only the SQL command without placing it into any SP it returns the list. I am using PL/Sql developer and TOAD at the same time. While writing the procedure,(logging onto GENERAL schema) whenever I type "SOMESCHEMA." after placing the dot all the tables can be seen under that tablespace, also the column names under USERS table can be seen after writing "USERS.".
    Isn't it wierd ? Or am I missing something ?

  4. #4
    Join Date
    Aug 2004
    Location
    Istanbul
    Posts
    17
    Sorry, I rechecked the privilages. Added grants to the table that is required to be queried from other schema. and it did fine.
    Thanks alot.

    I was thinking that I have a syntax or semantics error or something like that.
    The problem was all about table grants.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •