Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    3

    Unanswered: Oracle (1):PL/SQL: ORA-00942: table or view does not exist

    Hello,

    i'm trying to compile a Stored Proc like this:

    Procedure SP_PS_KH_TEST
    IS
    v_number number;
    cursor c_Stellplatz_Artikel is
    select gang from lvs.invk order by gang;
    BEGIN
    insert into kh_test values ('something');
    EXCEPTION
    WHEN others THEN
    raise_application_error( -20001, 'Fehler in Prozedur sp_ps_kh_test' );
    END;

    The error message ORA-00942: table or view does not exist
    means that the lvs.invk table in the cursor declaration is not known, although the (different) schema name is heading the table name. Even creating a public synonym won't help.

    Can anybody help me?
    Thanks

  2. #2
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    You might not have Privileges to access the invk table in lvs Schema.
    SATHISH .

  3. #3
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    3
    Originally posted by satish_ct
    Hi,

    You might not have Privileges to access the invk table in lvs Schema.
    The same select (select gang from lvs.invk order by gang works fine with my user with e.g. sqlplus. Only within the Procedure it doesn't work/compile.

  4. #4
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Can you test the following for us (from SQL*Plus) ?

    SQL>set role none;

    Role set

    SQL>select gang from lvs.invk order by gang;

    If the select still works, than I cannot help you more. If the select does no longer work (insufficient privileges), than you have been granted the priviliges to table lvs.invk through a role. To reference this object in a stored procedure, you need "direct" privilege to that particular object.

    More info at :
    http://asktom.oracle.com/~tkyte/Misc...rocedures.html

  5. #5
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    3
    You both were right!

    LVS had to set the 'direct privilege' (grant select ...) for the selecting user. It works now (with or without synonym).

    Tanks a lot for your help!

    Hermes

Posting Permissions

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