Procedure not working
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Procedure not working

  1. #1
    Join Date
    Sep 2003
    Posts
    49

    Procedure not working

    hI
    Iam not able to compile procedure sucessfully . it says errors pls-00302 foo must be declared. But same thing working in different user (same instance,same database).

    Scenario as follows

    1) connect alumni/o@test

    create table foo (id number);

    insert into foo values(1);

    commit;

    grant all on foo to faismgr;


    2) connect faismgr/o@test

    create procedure as follows


    create procedure bogus is
    my_values alumni.foo.id%TYPE;
    cursor c1 is
    select id from alumni.foo;
    begin
    open c1;
    fetch c1 into my_values;
    close c1;
    end;
    /
    show error



    SQL> show error
    Errors for PROCEDURE BOGUS:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    2/13 PL/SQL: Item ignored
    2/20 PLS-00302: component 'FOO' must be declared
    7/3 PL/SQL: SQL Statement ignored
    7/17 PLS-00320: the declaration of the type of this expression is
    incomplete or malformed


    but same thing worked in some other user called SATURN.

    What could be problem? Any privs or roles missing in FAISMGR schema

    any help would be appreiciated

  2. #2
    Join Date
    Jan 2004
    Posts
    10

    Post AUTHID clause

    as you are trying to access a table of another user, try using the AUTHID clause, brother,

    -- stand-alone procedure
    CREATE [OR REPLACE] PROCEDURE [schema_name.]procedure_name
    [(parameter_list)]
    [AUTHID {CURRENT_USER | DEFINER}] {IS | AS}

  3. #3
    Join Date
    Sep 2003
    Posts
    49
    hi
    From another user , it is working perfectly fine.

  4. #4
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    As user faismgr what does this return?

    Code:
    SELECT object_name, object_type, owner
    FROM all_objects
    WHERE object_name IN ('FOO', 'ALUMNI')
    John

  5. #5
    Join Date
    Sep 2003
    Posts
    49
    Object_name Object_type Owner
    ------------------------------ ------------------ --------

    Foo Table Alumni
    Alumni Package Faismgr
    Alumni Package Body Faismgr

  6. #6
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    So, does the package alumni in faismrg's schema contain a function or procedure foo?

    This is your situation:

    Code:
    SQL> SHOW USER
    USER is "OPS$ORACLE"
    SQL> create user a identified by a
      2  /
     
    User created.
     
    SQL> grant connect, create procedure to a
      2  /
     
    Grant succeeded.
     
    SQL> grant select on t to a;
     
    Grant succeeded.
     
    SQL> connect a/a
    Connected.
    SQL> CREATE package ops$oracle AS
      2  procedure p;
      3  end;
      4  /
     
    Package created.
     
    SQL> CREATE PACKAGE BODY ops$oracle AS
      2  PROCEDURE p IS
      3  BEGIN
      4     NULL;
      5  END;
      6  END;
      7  /
     
    Package body created.
      
    SQL> DECLARE
      2  l_v ops$oracle.t.id%TYPE;
      3  BEGIN
      4     SELECT id INTO l_v FROM ops$oracle.t;
      5  END;
      6  /
    l_v ops$oracle.t.id%TYPE;
                   *
    ERROR at line 2:
    ORA-06550: line 2, column 16:
    PLS-00302: component 'T' must be declared
    ORA-06550: line 2, column 5:
    PL/SQL: Item ignored
    ORA-06550: line 4, column 19:
    PLS-00320: the declaration of the type of this expression is incomplete or malformed
    ORA-06550: line 4, column 23:
    PL/SQL: ORA-00904: : invalid identifier
    ORA-06550: line 4, column 4:
    PL/SQL: SQL Statement ignored
     
    SQL> CREATE SYNONYM t for ops$oracle.t;
     
    Synonym created.
     
    SQL> DECLARE
      2  l_v t.id%TYPE;
      3  BEGIN
      4     SELECT id INTO l_v FROM t;
      5  END;
      6  /
    
    PL/SQL procedure successfully completed.
    TTFN
    John

  7. #7
    Join Date
    Sep 2003
    Posts
    49
    Dear john
    Thanks for the wonderful DEMO. (solved my problem)
    BTW, why oracle is behaving like this?

  8. #8
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    It is a result of the way Oracle does name resolution when you access a database object. Oracle will first look at "real" database objects (tables, views, packages etc.) in the caller's schema. If it does not find an appropriate object it will look at the caller's private synonyms, it no private synonyms exist, Oracle will look for a public synonym. Only after all this, does Oracle check to see if it might be an object owned by another user.

    HTH
    John

Posting Permissions

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


Click Here to Expand Forum to Full Width