+1-888-365-2779
Try Now
More in this section

Forums / Bugs & Issues / Multiple INVALID Procedures in orcacle. Why?

Multiple INVALID Procedures in orcacle. Why?

4 posts, 0 answered
  1. Herman
    Herman avatar
    6 posts
    Registered:
    22 Jun 2009
    04 Dec 2009
    Link to this post
    The DBA reported som problems with the sitefinity database we are using. There were a couple of Procedures that where empty and oracle marked them as invalid. Here is the list:
     - SF_NOTIF_GROUPNEWSLETTER_UPD
     - SF_NOTIF_RECIPIENTGROUP_UPD
     - SF_READCOMMENTS_UPD
     - TELERIK_USERSINROLES_UPD
     - SF_LOC_ITEM_UPD
     - SF_NOTIF_RECIEPIENTROLE_UPD
     - SF_ACTIONSTRACKER_UPD

    What could be the cause of this? We noticed other invalid procedures in other enviroment. Is this s bug?
  2. Georgi
    Georgi avatar
    3583 posts
    Registered:
    28 Oct 2016
    08 Dec 2009
    Link to this post
    Hi Herman,

    This is generally not a bug. 

    The ORM that we are using is generating the stored procedures automatically, based on our code. Unfortunately we cannot remove them, but these stored procedures are not doing anything. The same procedures are empty in SQL Server environment as well. 

    I hope this is not against some of your rules in your corporate DB-Management.

    Regards,
    Georgi
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
  3. Herman
    Herman avatar
    6 posts
    Registered:
    22 Jun 2009
    08 Dec 2009
    Link to this post
    The problem is that these procedures are invalid according to oracle rules. Our DB admins don't allow invalid procedures in our production enviroment. If you simply fill them with NULL; they are valid but do nothing. Would this be an option?

    So don't clear them like this:
    BEGIN

    END

    but lik this:

    BEGIN
      NULL;
    END

    Then they would be valid oracle procedures and still do nothing.
  4. Radoslav Georgiev
    Radoslav Georgiev avatar
    3370 posts
    Registered:
    01 Feb 2016
    09 Dec 2009
    Link to this post
    Hello Herman,

    Thank you for getting back to us.

    Since your DB Admins do not allow invalid procedures you can do something like this. In fact the ORM that we use when it generates those procedures for other DB types such as MS SQL or MySQL it simply creates a dummy variable there. For example this is how the stored procedue sf_Notif_GroupNewsletter_upd looks like those two DB types.

    MS SQL:

    USE [Blank37SP1]
    GO
    /****** Object:  StoredProcedure [dbo].[sf_Notif_GroupNewsletter_upd]    Script Date: 12/09/2009 13:40:27 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[sf_Notif_GroupNewsletter_upd] (@PO_NewsletterId uniqueidentifier, @PO_GroupId uniqueidentifier, @PO_Application nvarchar(50))
    AS BEGIN
        DECLARE @dummy int
     
    END;

    MySQL:

    CREATE DEFINER=`root`@`localhost` PROCEDURE `sf_Notif_GroupNewsletter_upd`(in PO_NewsletterId varchar(39), in PO_GroupId varchar(39), in PO_Application varchar(54))
    BEGIN
        DECLARE _msg TINYINT;
     
    END

    So the ORM has built in methods for handling this in other DB engines. If you declare a dummy variable there will be no problem. One think to note however - if you upgrade your version of Sitefinity the DB schema will be automatically upgraded too. This means that the ORM will overwrite all stored procedures to match the ones for the new version, therefore you will have to go to the invalid ones again and put the dummy variable.

    In Sitefinity 4.0 we will not have this problem as we are using a different ORM software which will not generate invalid procedures.

    Greetings,
    Radoslav Georgiev
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
Register for webinar
4 posts, 0 answered