10 Replies Latest reply: May 23, 2012 1:57 AM by andrewmy RSS

    Stored procedure issue

    406212
      Hi

      I have two users (appDBA, appUser) to access my oracle DB(11g). I have updated existing stored procedure thru SQL developer in appDBA login credential. But stored procedure changes are not reflecting in appUser login. Should I need to change any permissions (or anything else)?

      Regards
      Chamu
        • 1. Re: Stored procedure issue
          sybrand_b
          Kindly explain what 'not reflecting' means.
          Can Appuser execute this procedure?
          Yes?
          So you have no problem and everything is working as designed and intended.

          ----------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: Stored procedure issue
            sb92075
            cmohan99 wrote:
            Hi

            I have two users (appDBA, appUser) to access my oracle DB(11g). I have updated existing stored procedure thru SQL developer in appDBA login credential. But stored procedure changes are not reflecting in appUser login. Should I need to change any permissions (or anything else)?
            I would not be surprised if said procedure had EXCEPTION handler that masks some error.


            I don't know what you have.
            I don't know what you do.
            I don't know what you see.
            It is really, Really, REALLY difficult to fix a problem that can not be seen.
            use COPY & PASTE so we can see what you do & how Oracle responds.
            • 3. Re: Stored procedure issue
              406212
              I have login as "appDBA" (i.e owner of the Database, it has all the rights).

              I have updated stored procedure logic (changed table name) and complied no errors, executed, working fine. No issues.

              Then I have login same database but different user as "appUser". When I view the stored procedure, the stored procedure changes (i.e updated table names) are not present in the "appUser" login. The stored procedure has old table names and my changes are not present in the "appUser" login.

              Same database and same stored procedure, updated in "appDBA" login, but changes are NOT reflecting in "appuser" login.

              Why stored procedure changes are not reflecting the "appUser" login ?

              Any idea ???
              • 4. Re: Stored procedure issue
                EdStevens
                cmohan99 wrote:
                I have login as "appDBA" (i.e owner of the Database, it has all the rights).

                I have updated stored procedure logic (changed table name) and complied no errors, executed, working fine. No issues.

                Then I have login same database but different user as "appUser". When I view the stored procedure, the stored procedure changes (i.e updated table names) are not present in the "appUser" login. The stored procedure has old table names and my changes are not present in the "appUser" login.

                Same database and same stored procedure, updated in "appDBA" login, but changes are NOT reflecting in "appuser" login.

                Why stored procedure changes are not reflecting the "appUser" login ?

                Any idea ???
                As dba
                select owner
                ,      object_name
                ,      object_type
                from dba_objects
                where object_name='NAMEOFMYSTERYPROCEDURE'
                ;
                • 5. Re: Stored procedure issue
                  sybrand_b
                  Again no explanation what 'no reflecting' means.
                  Sadly, this is not a chat room, your webcam is off and this is also not a riddle forum.

                  Good day to you

                  -------------
                  Sybrand Bakker
                  Senior Oracle DBA
                  • 6. Re: Stored procedure issue
                    406212
                    Better you should read my post once again. I have mention very clearly in my message. My stored procedure changes (I have updated table name in the stored procedure) are not updating in the another user login for the same database.
                    • 7. Re: Stored procedure issue
                      sb92075
                      cmohan99 wrote:
                      Better you should read my post once again. I have mention very clearly in my message. My stored procedure changes (I have updated table name in the stored procedure) are not updating in the another user login for the same database.
                      please post SQL & results that show above is valid & true
                      • 8. Re: Stored procedure issue
                        sybrand_b
                        This is *100* percent irrelevant.

                        Can you run the 'updated' procediure? Does it function correctly?

                        Yes, you have NO ISSUE and you are only out here to annoy people.

                        -----------
                        Sybrand Bakker
                        Senior Oracle DBA
                        • 9. Re: Stored procedure issue
                          Mark Malakanov (user11181920)
                          My stored procedure changes (I have updated table name in the stored procedure) are not updating in the another user login for the same database.
                          Have you specified schema for "My stored procedure" ?
                          It may be you have "My stored procedure" and other user has its own "My stored procedure".
                          • 10. Re: Stored procedure issue
                            andrewmy
                            You are making the assumption that both users are referring to the same stored procedure object just because the name is the same.

                            This may not be true. Each user can have their own procedure with the same name which are completely independent of the others.

                            If you can access the dba_objects view you can check how many objects with the same name as your stored procedure there are:
                            select owner, object_name, object_type
                            from dba_objects
                            where object_name = 'yourprocedurename'
                            /