Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2003
    Posts
    67

    Unanswered: invalid packages and functions

    The other day our DBA updated the data in the development box with current production data. As far as I can tell everything went fine. Then today I jumped back into a project I have not touched in a while on that development box. I found that all of the packages and functions are "invalid". Our DBA's are on vacation so I have a developer here that says he has seen this before and it has to do with the updates. Could this have something to do with the updates? If so how can I repair them?

    Thanks for you help.

  2. #2
    Join Date
    Oct 2003
    Posts
    71

    Re: invalid packages and functions

    Originally posted by timmoser
    The other day our DBA updated the data in the development box with current production data. As far as I can tell everything went fine. Then today I jumped back into a project I have not touched in a while on that development box. I found that all of the packages and functions are "invalid". Our DBA's are on vacation so I have a developer here that says he has seen this before and it has to do with the updates. Could this have something to do with the updates? If so how can I repair them?

    Thanks for you help.
    Has any database structure changed too from Prod to Development.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    recompile all the procedures/packages.

    you could do this in TOAD easily enough or you can write a quick little dynamic sql statement
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: invalid packages and functions

    If you have access to the schema owner you can run the script below to generate a listing of commands to recompile the invalid objects that were probably invalidated by the update.

    Spool the output to a file, and then execute the output file.

    select 'Alter '||owner||'.'||object_type||' '||object_name||' compile;'
    from user_objects
    where status = 'INVALID'
    and object_type in ('PROCEDURE','FUNCTION','PACKAGE');

    If your not sure what schemas have been affected, you can run the above query on ALL_OBJECTS and then recompile for each schema that you have access to.

    HIH

  5. #5
    Join Date
    Jan 2003
    Posts
    67
    carloa & The_Duck: Unfortunately I can't recompile anything the error I get is "unknown database error"; hehe, that's a big help! This is the SQL and results...

    SQL:
    Code:
    select 'Alter '||object_type||' '||owner||'.'||object_name||' compile;'
    from dba_objects
    where status = 'INVALID'
    and object_type in ('PROCEDURE','FUNCTION','PACKAGE');
    
    Results:
    Alter PROCEDURE SYS.DELEMELATERASBEGINDECLARE compile;
    Alter PROCEDURE TMOSER.C_ZIP_RL compile;
    Alter FUNCTION TMOSER.GET_ENGINEER compile;
    Alter FUNCTION TMOSER.GET_LATITUDE compile;
    Alter FUNCTION TMOSER.GET_LONGITUDE compile;
    Alter FUNCTION TMOSER.GET_ZIPCODE_DISTANCE compile;
    Alter PROCEDURE TMOSER.S_ZIPSEARCH_RL compile;
    Alter PACKAGE TMOSER.ZIPSEARCH compile;
    When I run the "recompile" I get the "unknown database error".

    The_Duck: "you could do this in TOAD easily enough or you can write a quick little dynamic sql statement" :: We use DBArtisan, I think it's a similar tool. When I tried to recompile individually using DBArtisan I got the same error.

    reemagupta: "Has any database structure changed too from Prod to Development" :: That was my first thought; I would hope someone didn't make a change directly in production. I'm checking that the fields in the packages and functions are the same names and types in the tables.

    Thanks everyone for your ideas and advice.

  6. #6
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    This could also have happened due to re-compilation of one of the stored procedures or package specification ...

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    run those recompile lines in sql*plus and post the exact output (cut/paste)

    I would like to see what it is doing.


    Also, do you have the create code to rebuild all the the procs/packages? That could be a better solution.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Jan 2003
    Posts
    67
    Thanks,
    run those recompile lines in sql*plus and post the exact output (cut/paste)
    Code:
    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
    With the Partitioning option
    JServer Release 8.1.7.4.1 - Production
    SQL> Alter PROCEDURE SYS.DELEMELATERASBEGINDECLARE compile;
    Warning: Procedure altered with compilation errors.
    SQL> Alter PROCEDURE TMOSER.C_ZIP_RL compile;
    Warning: Procedure altered with compilation errors.
    SQL> Alter FUNCTION TMOSER.GET_ENGINEER compile;
    Warning: Function altered with compilation errors.
    SQL> Alter PACKAGE TMOSER.ZIPSEARCH compile;
    Warning: Package altered with compilation errors.
    SQL>
    Also, do you have the create code to rebuild all the the procs/packages? That could be a better solution.
    The GET_ZIPCODE_DISTANCE function...
    Code:
    CREATE OR REPLACE FUNCTION TMOSER.GET_ZIPCODE_DISTANCE (zip1 varchar2,
       zip2 varchar2) RETURN NUMBER IS
    
    lat1 NUMBER;
    long1 NUMBER;
    lat2 NUMBER;
    long2 NUMBER;
    distance NUMBER := 0;
    pi number ;
    EARTH_RADIUS number;
    BEGIN
    --
    pi:=3.14159265379;
    EARTH_RADIUS := 3959; -- radius of Earth in miles
    --
    IF ZIP1=ZIP2 THEN
       distance := 0;
       RETURN distance;
    ELSE
    --
    -- get coordinates
       select distinct a.latitude, a.longitude, b.latitude, b.longitude
       into   lat1, long1, lat2, long2
       from   w6admin.w6zipdata a,
              w6admin.w6zipdata b
       where  a.zipcode = zip1 and
              b.zipcode = zip2;
       --
         distance := EARTH_RADIUS * acos( (cos(lat1 * (PI/180)) *
                           cos(long1 * (PI/180)) * cos(lat2 * (PI/180)) *
                           cos(long2 * (PI/180))) +
                           (cos(lat1 * (PI/180)) * sin(long1 * (PI/180)) *
                           cos(lat2 * (PI/180)) * sin(long2 * (PI/180))) +
                           (sin(lat1 * (PI/180)) * sin(lat2 * (PI/180))) );
       --
       return distance;
    END IF;
       EXCEPTION
         WHEN NO_DATA_FOUND THEN
    	 	  distance :='99999';
    		  return distance;
           dbms_output.put_line(sqlcode||' '|| sqlerrm);
         WHEN OTHERS THEN
    	 	 distance :='99999';
    		  return distance;
           dbms_output.put_line(sqlcode||' '||sqlerrm);
    
    END GET_ZIPCODE_DISTANCE;
    This is the ZIPSEARCH package head….
    Code:
    CREATE OR REPLACE PACKAGE TMOSER.ZIPSEARCH IS
    CURSOR ZIP_SEARCH_RECORD IS
    -- The "1 AS DISTANCE" sets up a number field in the return query.  It will be populated
    -- by the Get_ZipCode_Distance function.
     		SELECT W6KEY, NAME, ID, STREETNUMBER, STREETNAME, CITY, STATE, POSTCODE,
    			   1 AS DISTANCE
    		FROM W6ADMIN.W6ENGINEERS; 
    -- tsm
    CURSOR ZIP_SEARCH_RECORD_ASL IS
    		SELECT ASL, USL, NAME, ADDR1, ADDR2, ADDR3, CITY, STATE, ZIPCODE, VABA,
    			1 AS DISTANCE
    		FROM TMOSER.CZCS_ASLUSLADDR;
    --
    TYPE SEARCH_RESULTS_RECORD IS REF CURSOR RETURN ZIP_SEARCH_RECORD%ROWTYPE;
    TYPE SEARCH_RESULTS_RECORD_ASL IS REF CURSOR RETURN ZIP_SEARCH_RECORD_ASL%ROWTYPE;
    --
    PROCEDURE S_ZIPSEARCH (P_DISTANCE IN NUMBER, P_ZIPCODE IN VARCHAR2, P_MAXRETURNROWS IN NUMBER,
    		  			   P_RESULTS OUT SEARCH_RESULTS_RECORD);
    --
    PROCEDURE S_ZIPSEARCH_ASL (P_DISTANCE IN NUMBER, P_ZIPCODE IN VARCHAR2, P_MAXRETURNROWS IN NUMBER,
    		  			   P_RESULTS OUT SEARCH_RESULTS_RECORD_ASL);
    --
    END;
    This is the package body…
    Code:
    CREATE OR REPLACE PACKAGE BODY TMOSER.ZIPSEARCH AS
    --
    PROCEDURE S_ZIPSEARCH (P_DISTANCE IN NUMBER, P_ZIPCODE IN VARCHAR2, P_MAXRETURNROWS IN NUMBER,
    		  			   P_RESULTS OUT SEARCH_RESULTS_RECORD)
    AS
    --
      BEGIN
    	 OPEN P_RESULTS FOR
    	 SELECT * FROM
    	 (SELECT A.W6KEY, A.NAME, A.ID, A.STREETNUMBER, A.STREETNAME, A.CITY, A.STATE, A.POSTCODE, ROUND(GET_ZIPCODE_DISTANCE(A.POSTCODE, P_ZIPCODE)) AS DISTANCE
    	 		FROM W6ADMIN.W6ENGINEERS A
    	 		WHERE GET_ZIPCODE_DISTANCE(A.POSTCODE, P_ZIPCODE) < P_DISTANCE
    			ORDER BY DISTANCE)
    	  WHERE ROWNUM <= P_MAXRETURNROWS;
      END;
    W6ENGINEERS table…
    Code:
    CREATE TABLE W6ADMIN.W6ENGINEERS 
    (
        W6KEY              NUMBER        NOT NULL,
        REVISION           NUMBER        NOT NULL,
        CREATEDBY          VARCHAR2(128) NOT NULL,
        TIMECREATED        DATE          NOT NULL,
        CREATINGPROCESS    NUMBER        NOT NULL,
        MODIFIEDBY         VARCHAR2(128) NOT NULL,
        TIMEMODIFIED       DATE          NOT NULL,
        MODIFYINGPROCESS   NUMBER        NOT NULL,
        NAME               VARCHAR2(255)     NULL,
        ID                 VARCHAR2(64)      NULL,
        REGION             NUMBER            NULL,
        DISTRICT           NUMBER            NULL,
        POSTCODE           VARCHAR2(64)      NULL,
        CALENDAR           NUMBER            NULL,
        ENGINEERTYPE       NUMBER            NULL,
        ACTIVE             NUMBER            NULL,
        TRAVELSPEED        NUMBER            NULL,
        INTERNAL           NUMBER            NULL,
        EFFICIENCY         FLOAT             NULL,
        MOBILECLIENT       NUMBER            NULL,
        AVAILABILITYFACTOR NUMBER            NULL,
        BRANCH             NUMBER            NULL,
        STREETNUMBER       VARCHAR2(32)      NULL,
        STREETNAME         VARCHAR2(64)      NULL,
        CITY               VARCHAR2(64)      NULL,
        STATE              VARCHAR2(32)      NULL,
        PRIMARYPHONE       VARCHAR2(32)      NULL,
        BACKUPPHONE        VARCHAR2(32)      NULL,
        PAGER              VARCHAR2(32)      NULL,
        PAGERPIN           VARCHAR2(32)      NULL,
    CONSTRAINT W6FK8_18_70_
        FOREIGN KEY (BRANCH)
        REFERENCES W6ADMIN.W6BRANCH (W6KEY),
    CONSTRAINT W6FK8_5_50_
        FOREIGN KEY (REGION)
        REFERENCES W6ADMIN.W6REGIONS (W6KEY),
    CONSTRAINT W6FK8_6_53_
        FOREIGN KEY (DISTRICT)
        REFERENCES W6ADMIN.W6DISTRICTS (W6KEY),
    CONSTRAINT W6FK8_8_3_
        FOREIGN KEY (CALENDAR)
        REFERENCES W6ADMIN.W6CALENDARS (W6KEY),
    CONSTRAINT W6FK8_9_55_
        FOREIGN KEY (ENGINEERTYPE)
        REFERENCES W6ADMIN.W6ENGINEER_TYPES (W6KEY)
    )
    TABLESPACE W6
    LOGGING
    PCTFREE 15
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE(INITIAL 400K
            NEXT 200K
            MINEXTENTS 1
            MAXEXTENTS 640
            PCTINCREASE 0
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT)
    NOPARALLEL
    NOCACHE
    /
    ALTER TABLE W6ADMIN.W6ENGINEERS 
        ADD CONSTRAINT SYS_C003073
    PRIMARY KEY (W6KEY)
    USING INDEX PCTFREE 10
                INITRANS 2
                MAXTRANS 255
                TABLESPACE INDX
                STORAGE(INITIAL 52K
                       NEXT 52K
                       MINEXTENTS 1
                       MAXEXTENTS 640
                       PCTINCREASE 0
                       FREELISTS 1
                       FREELIST GROUPS 1
                       BUFFER_POOL DEFAULT)
    LOGGING ENABLE VALIDATE
    /
    The W6ZIPDATA table…
    Code:
    CREATE TABLE W6ADMIN.W6ZIPDATA 
    (
        ZIPCODE   VARCHAR2(64) NOT NULL,
        LONGITUDE FLOAT        NOT NULL,
        LATITUDE  FLOAT        NOT NULL
    )
    TABLESPACE W6
    LOGGING
    PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE(INITIAL 1200K
            NEXT 72K
            MINEXTENTS 1
            MAXEXTENTS 121
            PCTINCREASE 1
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT)
    NOPARALLEL
    NOCACHE
    /
    ALTER TABLE W6ADMIN.W6ZIPDATA 
        ADD CONSTRAINT SYS_C003315
    PRIMARY KEY (ZIPCODE)
    USING INDEX PCTFREE 10
                INITRANS 2
                MAXTRANS 255
                TABLESPACE INDX
                STORAGE(INITIAL 1456K
                       NEXT 104K
                       MINEXTENTS 1
                       MAXEXTENTS 121
                       PCTINCREASE 1
                       FREELISTS 1
                       FREELIST GROUPS 1
                       BUFFER_POOL DEFAULT)
    LOGGING ENABLE VALIDATE
    /
    The CZCS_ASLUSLADDR table...
    Code:
    CREATE TABLE TMOSER.CZCS_ASLUSLADDR 
    (
        ASL     CHAR(4)      NOT NULL,
        USL     CHAR(4)      NOT NULL,
        NAME    VARCHAR2(50)     NULL,
        ADDR1   VARCHAR2(50) NOT NULL,
        ADDR2   VARCHAR2(50)     NULL,
        ADDR3   VARCHAR2(50)     NULL,
        CITY    VARCHAR2(50) NOT NULL,
        STATE   CHAR(2)      NOT NULL,
        ZIPCODE VARCHAR2(10) NOT NULL,
        VABA    VARCHAR2(10) NOT NULL
    )
    TABLESPACE USERS
    LOGGING
    PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE(INITIAL 128K
            NEXT 148K
            MINEXTENTS 1
            MAXEXTENTS 505
            PCTINCREASE 1
            FREELISTS 1
            FREELIST GROUPS 1
            BUFFER_POOL DEFAULT)
    NOPARALLEL
    NOCACHE
    /
    ALTER TABLE TMOSER.CZCS_ASLUSLADDR 
        ADD CONSTRAINT CZCS_ASLUSL_PK
    PRIMARY KEY (ASL,USL)
    USING INDEX PCTFREE 10
                INITRANS 2
                MAXTRANS 255
                TABLESPACE USERS
                STORAGE(INITIAL 128K
                       NEXT 128K
                       MINEXTENTS 1
                       MAXEXTENTS 505
                       PCTINCREASE 1
                       FREELISTS 1
                       FREELIST GROUPS 1
                       BUFFER_POOL DEFAULT)
    LOGGING ENABLE VALIDATE
    /

  9. #9
    Join Date
    Jan 2003
    Posts
    67
    Oops you didn't want the code you just asked if I had it. I will start deleting and recreating to see if that works. Thanks

  10. #10
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Recompile 1 of the package (1 line)...
    after it comes back with a compilation error ...

    sql> show error

    if that doesn't show what the error is ...

    sql> select text from all_errors where name = 'GET_ZIPCODE_DISTANCE ';

    HTH
    gregg

  11. #11
    Join Date
    Jan 2003
    Posts
    67
    I wonder if they messed up my rights when they did the update. This is the error I get if I follow your instructions above:
    Code:
    SQL> Alter PACKAGE TMOSER.ZIPSEARCH compile;
    Warning: Package altered with compilation errors.
    SQL> show error
    Errors for PACKAGE TMOSER.ZIPSEARCH:
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    2/8      PLS-00341: declaration of cursor 'ZIP_SEARCH_RECORD' is
             incomplete or malformed
    5/10     PL/SQL: SQL Statement ignored
    7/14     PLS-00201: identifier 'W6ADMIN.W6ENGINEERS' must be declared
    14/1     PL/SQL: Declaration ignored
    SQL>
    W6ADMIN.W6ENGINEERS exist I can see it but maybe my rights have some restriction on it. In fact the DDL is posted for this table.

    Thanks

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

    Thumbs up

    HI,

    THis error arises because a cursor declaration is improper or an identifier referenced in the cursor declaration was not properly declared. A return type that does not refer to an existing database table or a previously declared cursor or cursor variable might have been specified.



    Originally posted by timmoser
    I wonder if they messed up my rights when they did the update. This is the error I get if I follow your instructions above:
    Code:
    SQL> Alter PACKAGE TMOSER.ZIPSEARCH compile;
    Warning: Package altered with compilation errors.
    SQL> show error
    Errors for PACKAGE TMOSER.ZIPSEARCH:
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    2/8      PLS-00341: declaration of cursor 'ZIP_SEARCH_RECORD' is
             incomplete or malformed
    5/10     PL/SQL: SQL Statement ignored
    7/14     PLS-00201: identifier 'W6ADMIN.W6ENGINEERS' must be declared
    14/1     PL/SQL: Declaration ignored
    SQL>
    W6ADMIN.W6ENGINEERS exist I can see it but maybe my rights have some restriction on it. In fact the DDL is posted for this table.

    Thanks
    SATHISH .

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

    Check the spelling and declaration of the cursor name and any
    identifiers referenced in the cursor declaration. Also confirm that the declaration is placed correctly in the block structure. If a return type was specified, make sure that it refers to an existing database table or a previously declared cursor or cursor variable.
    SATHISH .

  14. #14
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by timmoser
    I wonder if they messed up my rights when they did the update. This is the error I get if I follow your instructions above:
    Code:
    SQL> Alter PACKAGE TMOSER.ZIPSEARCH compile;
    Warning: Package altered with compilation errors.
    SQL> show error
    Errors for PACKAGE TMOSER.ZIPSEARCH:
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    2/8      PLS-00341: declaration of cursor 'ZIP_SEARCH_RECORD' is
             incomplete or malformed
    5/10     PL/SQL: SQL Statement ignored
    7/14     PLS-00201: identifier 'W6ADMIN.W6ENGINEERS' must be declared
    14/1     PL/SQL: Declaration ignored
    SQL>
    W6ADMIN.W6ENGINEERS exist I can see it but maybe my rights have some restriction on it. In fact the DDL is posted for this table.

    Thanks
    Perhaps you are correct. It is most probably a rights/permissions problem.

    Check:
    1. Is there a synonym for all tables in W6ADMIN?
    2. Do you, TMMOSER, have select privs on that schema??

    also, run a test by adding the schema-prefix onto the tablenames in the cursors. ie: select * from W6ADMIN.W6ENGINEERS;

    Run the proc and see what returns
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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