stored procedure become invalid
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: stored procedure become invalid

  1. #1
    Join Date
    Jun 2000
    Posts
    315
    What would cause a stored procedure to become invalid if I did not upgrade, install patchset, or run catolag?

    Thanks!

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Any change on any object that the procedure is depending on (any table, view, function, etc...) would cuse the procedure to become invalid. But it will recompile automatcaly the first time it is used again, so you can ignore this behavior.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2001
    Posts
    34
    it script generate all objects invalid in your database:

    rem *********************************************************
    rem * file: comp_all.sql
    rem * purpose: compile all database stored objects
    rem * to use: log in using the appropriate account then
    rem * execute this script using the following syntax:
    rem *
    rem * SQL> @comp_all
    rem *
    rem * NOTE: You should not have to run this script more
    rem * than once since it uses
    rem * order_object_by_dependency table to compile
    rem * objects in the proper order. Any
    rem * compilation errors generated should be
    rem * investigated.
    rem *********************************************************
    rem ====================== script for 8.1.5 =========================
    rem If the above script fails when run on 8.1.5 with:
    rem ERROR at line 1:
    rem ORA-01436: CONNECT BY loop in user data.
    rem This is due to [BUG:895238] fixed in 8.1.7.
    rem
    rem Use the following modification instead. It will run successfully, rem but it will
    rem not order by dependencies:
    rem References:
    rem ===========
    rem [NOTE:1014072.102] SCRIPT TO FIND INVALID OBJECTS IN THE DATABASE
    rem
    rem ============================================================
    set echo off
    set heading off
    set pagesize 0
    set linesize 79
    set verify off
    set recsep off
    spool /tmp/compile_all.sql
    select
    decode( OBJECT_TYPE, 'PACKAGE BODY',
    'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
    'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
    from
    dba_objects
    where
    STATUS = 'INVALID' and OBJECT_TYPE in
    ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'VIEW' )
    order by OWNER, OBJECT_TYPE, OBJECT_NAME;
    spool off



    CZULU
    --------
    Senior DBA Oracle

  4. #4
    Join Date
    Jun 2000
    Posts
    315
    Jmodic:

    Thank you! I appreciate it!

    Lisa

  5. #5
    Join Date
    Jun 2000
    Posts
    315


    How come sometimes they do not recompile automatically?

    Thanks!


  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by lgaorcl
    How come sometimes they do not recompile automatically?
    It can be either:

    a) PL/SQL procedure has errors in it, so it won't compile even if you manually recompile it

    b) It did recompile automaticaly, but after that your aplication could couse some other procedure or function to recompile automaticaly which in turn invalidated your first procedure again, according to dependancy tree

    c) If neither of the above two options are true in your case, you must provide us some more details about the circumstances in which you encountered that behavior.

    I remembered once we had an isue with one of our Delphi applications, which consistantly compaining about an invalid function, but to my amasement the function did not recompile automaticaly when invoked by the aplication (I'm not a Delphi programer, so I have not had a clue about what could be causing this). Only after ve recompiled the function manually the application worked again, but only untill something made that function invalid again. At the end it turned out that in Delphi you can set some switch (or object attribute or something) which prevent automatic recompilation of stored database code. Once we set that switch back to the default setting everithing went back to normal.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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