1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Compile Error ORA-00942 in Pl-SQL on dba_ or user_source

Discussion in 'SQL PL/SQL' started by getGoin, Jul 6, 2011.

  1. getGoin

    getGoin Guest

    I keep getting the error ORA-00942 (table or view does not exist) when compiling a package with the following code:
    it doesn't matter if I use "dba_source" or "user_source" for the view name.
    /*****************************
    ** IS_METHOD_DEFINED
    ** Determine if this optional procedure is defined and we should run it.
    ****************************/
    PROCEDURE IS_METHOD_DEFINED(pMethodType IN VARCHAR2, /* FUNCTION, PACKAGE,PROCEDURE*/
    pMethodName IN VARCHAR2, /* Name of function,package or procedure*/
    pSubMethodName IN VARCHAR2, /* if package, name of the procedure*/
    pMethodDefined OUT VARCHAR2) /* Y/N */
    IS
    vCount NUMBER := 0;
    BEGIN
    SELECT COUNT(*)
    INTO vCount
    FROM user_source a
    WHERE a.TYPE = upper(pMethodType) AND
    a.NAME = upper(pMethodName) AND
    (pSubMethodName IS NULL OR text LIKE '%' || pSubMethodName || '%');

    IF (vCount > 0) THEN
    pMethodDefined := 'Y';
    ELSE
    pMethodDefined := 'N';
    END IF;
    END IS_METHOD_DEFINED;

    If I open a new PL/SQL window and do a Select * from either view I do not get an error anyone have any ideas???
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I do not get such an error from your code:

    Code (SQL):
     
    SQL> CREATE OR REPLACE package mytest_pkg AS
      2  PROCEDURE IS_METHOD_DEFINED(pMethodType IN VARCHAR2, /* FUNCTION, PACKAGE,PROCEDURE*/
      3          pMethodName IN VARCHAR2, /* Name of function,package or procedure*/
      4          pSubMethodName IN VARCHAR2, /* if package, name of the procedure*/
      5          pMethodDefined OUT VARCHAR2); /* Y/N */
      6  END;
      7  /
    Package created.
    SQL>
    SQL> SHOW errors
    No errors.
    SQL>
    SQL> CREATE OR REPLACE package body mytest_pkg AS
      2  PROCEDURE IS_METHOD_DEFINED(pMethodType IN VARCHAR2, /* FUNCTION, PACKAGE,PROCEDURE*/
      3          pMethodName IN VARCHAR2, /* Name of function,package or procedure*/
      4          pSubMethodName IN VARCHAR2, /* if package, name of the procedure*/
      5          pMethodDefined OUT VARCHAR2) /* Y/N */
      6  IS
      7  vCount NUMBER := 0;
      8  BEGIN
      9          SELECT COUNT(*)
     10          INTO vCount
     11          FROM user_source a
     12          WHERE a.TYPE = UPPER(pMethodType) AND
     13          a.NAME = UPPER(pMethodName) AND
     14          (pSubMethodName IS NULL OR text LIKE '%' || pSubMethodName || '%');
     15
     16          IF (vCount > 0) THEN
     17                  pMethodDefined := 'Y';
     18          ELSE
     19                  pMethodDefined := 'N';
     20          END IF;
     21  END IS_METHOD_DEFINED;
     22  END;
     23  /
    Package body created.
    SQL>
    SQL> SHOW errors
    No errors.
    SQL>
    SQL> variable isdef varchar2(10)
    SQL>
    SQL> EXEC mytest_pkg.is_method_defined('procedure','my_proc',NULL, :isdef)
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> print :isdef
    ISDEF
    --------------------------------
    N
    SQL>

     
    I would check for possible typographical errors in your text or possibly the presence of unprintable characters in the table name; retype the table name in the code and try again.