12 Replies Latest reply: Jul 10, 2012 7:34 AM by 6363 RSS

    function flow

    751828
      Can anyone help me explain the flow what exactly is the below function do in detail.
       FUNCTION  Get_RmtUsr_rec_FUNC (p_MsgType IN VARCHAR2)
          RETURN    RemoteUser_Rec_Type;
      FUNCTION  Get_RmtUsr_rec_FUNC (   p_MsgType IN VARCHAR2)
      RETURN    RemoteUser_Rec_Type
      IS
         l_RmtUsr_rec     RemoteUser_Rec_Type;
      
      BEGIN
         l_RmtUsr_rec.UserLogin          := 'ERPAdmin';
         l_RmtUsr_rec.UserFullName       := 'ERPAdmin';
         l_RmtUsr_rec.UserAuthenticator  := 'ERPAdmin';
      
         RETURN l_RmtUsr_rec;
      EXCEPTION
         WHEN OTHERS THEN
             so0solodebug_pkg.AddDebugMessage(p_PackageInfo  => 'Get_RmtUsr_rec_FUNC',
                                        p_DebugMessage =>  SUBSTR(SQLERRM,1,240),
                                        p_MessageLevel =>  0,
                                        p_ErrorCode    =>  SQLCODE);
         RETURN l_RmtUsr_rec;
      END  Get_RmtUsr_rec_FUNC;
      PROCEDURE AddDebugMessage(p_PackageInfo    in VARCHAR2  default NULL,
                                p_RecordType     in VARCHAR2  default NULL,
                       p_RecordKey      in VARCHAR2  default NULL,
                                 p_DebugMessage   in VARCHAR2,
                       p_MessageLevel   in NUMBER,
                          p_ErrorCode      in VARCHAR2  default NULL);
      
      PROCEDURE PostDebugMessage;
      
      END so0solodebug_pkg;
        • 1. Re: function flow
          Purvesh K
          user9093700 wrote:
          Can anyone help me explain the flow what exactly is the below function do in detail.
          FUNCTION  Get_RmtUsr_rec_FUNC (p_MsgType IN VARCHAR2)
          RETURN    RemoteUser_Rec_Type;
          Function Definition
          FUNCTION  Get_RmtUsr_rec_FUNC (   p_MsgType IN VARCHAR2)
          RETURN    RemoteUser_Rec_Type
          IS
          l_RmtUsr_rec     RemoteUser_Rec_Type;
          
          BEGIN
          l_RmtUsr_rec.UserLogin          := 'ERPAdmin';
          l_RmtUsr_rec.UserFullName       := 'ERPAdmin';
          l_RmtUsr_rec.UserAuthenticator  := 'ERPAdmin';
          
          RETURN l_RmtUsr_rec;
          EXCEPTION
          WHEN OTHERS THEN
          so0solodebug_pkg.AddDebugMessage(p_PackageInfo  => 'Get_RmtUsr_rec_FUNC',
          p_DebugMessage =>  SUBSTR(SQLERRM,1,240),
          p_MessageLevel =>  0,
          p_ErrorCode    =>  SQLCODE);
          RETURN l_RmtUsr_rec;
          END  Get_RmtUsr_rec_FUNC;
          Function Body of Get_RmtUsr_rec_FUNC, accepting a Varchar2 variable (which is not used anywhere in function); Returning a Collection populated with User info. Exception part looks like some UDPackage adding the Debug information.
          On other notes, usage of Others should not be advocated. IMV, use Others for debug purpose only.
          PROCEDURE AddDebugMessage(p_PackageInfo    in VARCHAR2  default NULL,
          p_RecordType     in VARCHAR2  default NULL,
                           p_RecordKey      in VARCHAR2  default NULL,
                              p_DebugMessage   in VARCHAR2,
                           p_MessageLevel   in NUMBER,
                              p_ErrorCode      in VARCHAR2  default NULL);
          
          PROCEDURE PostDebugMessage;
          
          END so0solodebug_pkg;
          Definition of AddDebugMessage procedure.


          I did not see any code that is exceptional enough not to understand. Please give some efforts to understand and do post the efforts so people can find easier to explain.


          Regards,
          P
          • 2. Re: function flow
            BluShadow
            user9093700 wrote:
            Can anyone help me explain the flow what exactly is the below function do in detail.
            It assigns some values to a structured variable and returns it (not actually using the passed in parameter anywhere)
            If it get's an exception (which is highly unlikely from what little it does) then it appears to log the error and return the structured variable anyway, so regardless of the exception, it's effectively ignored and the calling code will not know that any exception has happened.

            Use of WHEN OTHERS exceptions should not be done. You should only capture exceptions that you expect and can handle, and allow all others to propagate back up to the calling code. Where logging of exceptions is required it should always be followed by a RAISE statement if the exception hasn't been handled.
            • 3. Re: function flow
              751828
              >
              Definition of AddDebugMessage procedure.


              I did not see any code that is exceptional enough not to understand. Please give some efforts to understand and do post the efforts so people can find easier to explain.


              Regards,
              Actually i didnot understand what this package is doing so0solodebug_pkg.AddDebugMessage in the below code.
              FUNCTION  Get_RmtUsr_rec_FUNC (   p_MsgType IN VARCHAR2)
              RETURN    RemoteUser_Rec_Type
              IS
                 l_RmtUsr_rec     RemoteUser_Rec_Type;
               
              BEGIN
                 l_RmtUsr_rec.UserLogin          := 'ERPAdmin';
                 l_RmtUsr_rec.UserFullName       := 'ERPAdmin';
                 l_RmtUsr_rec.UserAuthenticator  := 'ERPAdmin';
               
                 RETURN l_RmtUsr_rec;
              EXCEPTION
                 WHEN OTHERS THEN
                     so0solodebug_pkg.AddDebugMessage(p_PackageInfo  => 'Get_RmtUsr_rec_FUNC',
                                                p_DebugMessage =>  SUBSTR(SQLERRM,1,240),
                                                p_MessageLevel =>  0,
                                                p_ErrorCode    =>  SQLCODE);
                 RETURN l_RmtUsr_rec;
              END  Get_RmtUsr_rec_FUNC;
              below is the full package
              create or replace
              PACKAGE so0solodebug_pkg
              AS
              
               TYPE Debug_Rec_type IS RECORD (
                    PackageInfo        VARCHAR2(60),
                    RecordType         VARCHAR2(50),
                    RecordKey          VARCHAR2(200),
                    Message            VARCHAR2(500),
                    MessageLevel       NUMBER,
                    ErrorCode          VARCHAR2(200));
              
               TYPE Debug_tbl_type IS TABLE OF Debug_Rec_Type INDEX BY BINARY_INTEGER;
              
              /************************************************************/
              /* Global variable definitions
              /************************************************************/
               G_Debug_Tbl               Debug_Tbl_Type;
               G_Debug_Level             NUMBER           := 0;
               G_Debug_Mode              NUMBER           := 0 ;
               G_Debug_Count             NUMBER           := 0;
               G_Prog_Name               VARCHAR2(50);
               G_Debug_Len               CONSTANT NUMBER  := 500;
               G_ProgramAppId           NUMBER;
               G_ProgramId          NUMBER;
               G_RequestId          NUMBER;
              
              
              
              /************************************************************/
              /* Procedure interface definitions
              /************************************************************/
              PROCEDURE  InitializeDebugMessage(
                                    p_DebugLevel      NUMBER default 0,
                                    p_DebugMode       NUMBER default 0,
                                    p_Progname        VARCHAR2,
                                    p_ProgramAppId      NUMBER default NULL,
                                    p_ProgramId     NUMBER default NULL,
                                    p_RequestId     NUMBER default NULL);
              
              PROCEDURE AddDebugMessage(p_PackageInfo    in VARCHAR2  default NULL,
                                        p_RecordType     in VARCHAR2  default NULL,
                               p_RecordKey      in VARCHAR2  default NULL,
                                         p_DebugMessage   in VARCHAR2,
                               p_MessageLevel   in NUMBER,
                                  p_ErrorCode      in VARCHAR2  default NULL);
              
              PROCEDURE PostDebugMessage;
              
              END so0solodebug_pkg;
              • 4. Re: function flow
                Purvesh K
                What you have provided is a Package Definition. It would contain the Function/Procedure, Global Variable definitions.

                The said procedure looks like a Custom/User Defined code to create Logs. Just storing the Exception Information generated during Code Execution.
                If you see the Procedure, you might find it inserting certain data into a Table.

                To see the procedure, visit the Package Body section and the procedure.
                • 5. Re: function flow
                  751828
                  where can i see the procedure.
                  • 6. Re: function flow
                    Purvesh K
                    If you are using tools such as SQL Navigator, Toad; you will find it in Database Objects>Packages/Package Body.
                    I am using SQL Developer, Packages>Package_name>Package_Name Body.

                    Another way of Viewing it.
                    create or replace package test_pkg
                    as
                      procedure test_proc(var   in    varchar2);
                    
                    end test_pkg;
                    /
                    
                    create or replace package body test_pkg
                    as
                    
                      procedure test_proc (var    in    varchar2)
                      is
                      begin
                        dbms_output.put_line('Into Test_pkg.test_proc');
                      end test_proc;
                    
                    end test_pkg;
                    /
                    
                    select * 
                      from user_source
                     where name = 'TEST_PKG'
                     order by TYPE, LINE;
                    Regards,
                    P.
                    • 7. Re: function flow
                      751828
                      i need this exception part what it is doing

                      EXCEPTION
                         WHEN OTHERS THEN
                             so0solodebug_pkg.AddDebugMessage(p_PackageInfo  => 'Get_RmtUsr_rec_FUNC',
                                                        p_DebugMessage =>  SUBSTR(SQLERRM,1,240),
                                                        p_MessageLevel =>  0,
                                                        p_ErrorCode    =>  SQLCODE);
                         RETURN l_RmtUsr_rec;
                      END  Get_RmtUsr_rec_FUNC;
                      • 8. Re: function flow
                        Purvesh K
                        Did you do what I asked you in my earlier post?


                        "If you are using tools such as SQL Navigator, Toad; you will find it in Database Objects>Packages/Package Body.
                        I am using SQL Developer, Packages>Package_name>Package_Name Body.

                        Another way of Viewing it.
                        "

                        You must understand, it is not possible to Spoon-Feed anybody. You must learn to give your efforts.

                        Now, tell which Development Tool are you using? If you are able to get the procedure, then look for the execution flow. It must be picking up some Sequence and logging into a Log/Trace Table.


                        Regards,
                        P.
                        • 9. Re: function flow
                          751828
                          Sorry dude, acutally i am using SQL Developer itself

                          here is the package body


                          create or replace
                          PACKAGE BODY so0solodebug_pkg
                          AS
                          /************************************************************/
                          /* Forward declarations
                          /************************************************************/
                          PROCEDURE DumpDebugMessage;
                          PROCEDURE InsertDebugMessage;
                          
                          
                          /*==============================================================
                          **   PROCEDURE NAME:  InitializeDebugMessage
                          **
                          **   PURPOSE Of PROCEDURE:
                          **       Initializes a Global PL/SQL Table G_Debug_Tbl with passed parameters.
                          **
                          **   Input Parameters : p_DebugLevel
                          **                      p_DebugMode
                          **                      p_Progname
                          ==============================================================*/
                          
                          PROCEDURE  InitializeDebugMessage(
                                              p_DebugLevel      NUMBER default 0,
                                               p_DebugMode      NUMBER default 0,
                                              p_Progname      vARCHAR2,
                                              p_ProgramAppId      NUMBER default NULL,
                                              p_ProgramId     NUMBER default NULL,
                                              p_RequestId     NUMBER default NULL
                          ) AS
                          BEGIN
                           G_Debug_Tbl.DELETE;
                           G_Debug_Level       := p_DebugLevel;
                           G_Debug_Mode        := p_DebugMode;
                           G_Debug_Count       := 0;
                           G_Prog_Name         := p_Progname;
                           G_ProgramAppId      := p_ProgramAppId;
                           G_ProgramId     := p_ProgramId;
                           G_RequestId     := p_RequestId;
                          END InitializeDebugMessage;
                          
                          /*==============================================================
                          **   PROCEDURE NAME:  AddDebugMessage
                          **
                          **   PURPOSE Of PROCEDURE:
                          **       Inserts the passed parameters into the PL/SQL table G_Debug_Tbl.
                          **
                          **   Input Parameters : p_PackageInfo
                          **                      p_RecordType
                          **                      p_RecordKey
                          **                      p_DebugMessage
                          **                      p_ErrorCode
                          ==============================================================*/
                          
                          PROCEDURE AddDebugMessage(p_PackageInfo  in VARCHAR2  default NULL,
                                           p_RecordType   in VARCHAR2  default NULL,
                                           p_RecordKey    in VARCHAR2  default NULL,
                                                    p_DebugMessage in VARCHAR2,
                                                    p_MessageLevel in NUMBER,
                                                    p_ErrorCode    in VARCHAR2  default NULL) AS
                          BEGIN
                             IF( G_Debug_Level >= p_MessageLevel ) THEN
                                 G_Debug_Count                           := G_Debug_Count +1;
                                 G_Debug_Tbl(G_Debug_Count).PackageInfo  := p_PackageInfo;
                                 G_Debug_Tbl(G_Debug_Count).RecordType   := p_RecordType;
                                 G_Debug_Tbl(G_Debug_Count).RecordKey    := p_RecordKey;
                                 G_Debug_Tbl(G_Debug_Count).Message      := SUBSTR(p_DebugMessage,1,G_Debug_Len);
                                 G_Debug_Tbl(G_Debug_Count).MessageLevel := p_MessageLevel;
                                 G_Debug_Tbl(G_Debug_Count).ErrorCode    := p_ErrorCode;
                             END IF;
                          END AddDebugMessage;
                          
                          
                          /*==============================================================
                          **   PROCEDURE NAME:  PostDebugMessage
                          **
                          **   PURPOSE Of PROCEDURE:
                          **        As per the Debug Mode passed when InitializeDebugMessage
                          **        was called this procedure either shows the debug messages
                          **        on screen or inserts them into a table or both.
                          ***       Debug mode =0 (On screen)
                          ***                  =1 (Into Table)
                          **                   =2 (Both)
                          **
                          **   Input Parameters : none .
                          ==============================================================*/
                          
                          PROCEDURE PostDebugMessage IS
                          BEGIN
                            IF G_Debug_Mode = 0 THEN
                              DumpDebugMessage ;
                            ELSIF G_Debug_Mode=1 THEN
                              InsertDebugMessage;
                            ELSIF G_Debug_Mode=2 THEN
                              DumpDebugMessage;
                              InsertDebugMessage;
                            END IF;
                          END PostDebugMessage;
                          
                          
                          /*==============================================================
                          **   LOCAL PROCEDURE NAME:  DumpDebugMessage
                          **
                          **   PURPOSE Of PROCEDURE:
                          **      Shows the Debug Messages on the screen.
                          **
                          **   Input Parameters : none .
                          ==============================================================*/
                          
                          PROCEDURE DumpDebugMessage AS
                          l_Var    VARCHAR2(2000);
                          l_Var1   VARCHAR2(2000);
                          l_length NUMBER;
                          BEGIN
                             DBMS_OUTPUT.ENABLE(1000000);
                             IF G_Debug_Count > 0
                             THEN
                             FOR i in 1..G_Debug_Count
                             LOOP
                                l_Var      := To_char(i)||': '||Rtrim(G_Debug_Tbl(i).RecordType)
                                                    ||': '||Rtrim(G_Debug_Tbl(i).RecordKey)
                                                   ||': '||Rtrim(G_Debug_Tbl(i).Message) ;
                                l_Var1      := REPLACE(REPLACE(l_Var,': :',':'),': :',':');
                                l_length      := LENGTH(l_Var1);
                          
                               /************************************************************/
                               /* Dbms put_line can't put the lines properly, If the length
                               /* of message is more than 256. Hence the below processing
                               /************************************************************/
                          
                                IF l_length > 255 THEN
                                  DBMS_OUTPUT.PUT_LINE(SUBSTR(l_Var1,1,255));
                                  IF LENGTH(SUBSTR(l_Var1,256,l_length)) > 255 THEN
                                     DBMS_OUTPUT.PUT_LINE(SUBSTR(l_Var1,256,510));
                                     DBMS_OUTPUT.PUT_LINE(SUBSTR(l_Var1,511,l_length));
                                  ELSE
                                     DBMS_OUTPUT.PUT_LINE(SUBSTR(l_Var1,256,l_length));
                                  END IF;
                                ELSE
                                   DBMS_OUTPUT.PUT_LINE(l_Var1);
                                END IF;
                             END LOOP;
                             END IF;
                          END DumpDebugMessage;
                          
                          
                          /*==============================================================
                          **   LOCAL PROCEDURE NAME:  InsertDebugMessage
                          **
                          **   PURPOSE Of PROCEDURE:
                          **      Inserts the Debug Messages into the Table seasolo_message_log.
                          **
                          **   Input Parameters : none .
                          ==============================================================*/
                          
                          PROCEDURE InsertDebugMessage AS
                          l_Stmnt     VARCHAR2(500);
                          l_Sequence      Number;
                          l_GetSeq      VARCHAR2(1) :='Y';
                          l_StartSeq      Number;
                          l_EndSeq        Number;
                          
                          BEGIN
                             DBMS_OUTPUT.ENABLE(200000);
                             IF G_Debug_Count > 0
                            THEN
                          
                            l_Stmnt :='Inserting into seasolo_message_log';
                            FOR i in 1..G_Debug_Count
                            LOOP
                                INSERT into seasolo_message_log (
                                    Sequence_Id,
                                    Program_Name,
                                     PROGRAM_APPLICATION_ID,
                                     PROGRAM_ID,
                                     REQUEST_ID,
                                    Package_Info,
                                    Record_Type,
                                    Record_key,
                                    Message,
                                    Message_Level,
                                    Error_Code,
                                    Creation_date,
                                    Created_by,
                                    Last_update_date,
                                    Last_updated_by
                               ) VALUES (
                                      SEASOLO_MESSAGE_LOG_S1.NEXTVAL,
                                      G_Prog_Name,
                                      G_ProgramAppId,
                                      G_ProgramId,
                                      G_RequestId,
                                      G_Debug_Tbl(i).PackageInfo,
                                      G_Debug_Tbl(i).RecordType,
                                      G_Debug_Tbl(i).RecordKey,
                                      G_Debug_Tbl(i).Message,
                                      G_Debug_Tbl(i).MessageLevel,
                                      G_Debug_Tbl(i).ErrorCode ,
                                      SYSDATE,
                                      -1, -- NVL(fnd_profile.value('USER_ID'), -1),
                                      SYSDATE,
                                      -1 -- NVL(fnd_profile.value('USER_ID'), -1)
                               );
                          
                               IF l_GetSeq='Y' THEN
                                         Select SEASOLO_MESSAGE_LOG_S1.CURRVAL into l_StartSeq from dual;
                                         l_GetSeq :='N';
                               END IF;
                          
                            END LOOP;
                          
                            Select SEASOLO_MESSAGE_LOG_S1.CURRVAL into l_EndSeq from dual;
                          
                            Dbms_output.put_line('Inserted into Seasolo_Message_Log with program_name as : '||
                               G_Prog_Name ||' starting with sequence '||l_StartSeq ||' and ending at '||l_EndSeq);
                            END IF;
                          EXCEPTION
                            WHEN OTHERS THEN
                                Dbms_output.put_line('so0solodebug_pkg.InsertDebugMessage errored while : '
                               ||l_Stmnt||' : '||SQLCODE||' :'||SQLERRM);
                          END InsertDebugMessage;
                          
                          END so0solodebug_pkg;
                          Edited by: user9093700 on Jul 10, 2012 3:53 PM
                          • 10. Re: function flow
                            Purvesh K
                            It does not matter if a Package has 1Million lines, you have to study the procedure.

                            So, just open the package Body and navigate to the procedure and understand the logic(which I bet would be there for a Logging Procedure).
                            • 11. Re: function flow
                              Purvesh K
                              InitializeDebugMessage
                              It is initializing the variables to default values

                              AddDebugMessage
                              Populating a Record with the Debug Information viz. package name, error code etc.

                              DumpDebugMessage
                              Displaying the Debug message on the Console

                              InsertDebugMessage
                              Insert the Record into a Database table.
                              --I would suggest to use FORALL instead of FOR Loop. If you can, remove the Exception WHEN OTHERS. Perform correct exception handling viz. NO_DATA_FOUND, TOO_MANY_ROWS etc.

                              PostDebugMessage
                              Controls the call of DumpDebugMessage and/or InsertDebugMessage depending on Global Variable mode set.


                              I hope this clarifies your package structure.

                              If this clarifies, then mark the question as Answered.


                              Regards,
                              P.

                              Edited by: Purvesh K on Jul 10, 2012 5:06 PM
                              • 12. Re: function flow
                                6363
                                user9093700 wrote:

                                i need this exception part what it is doing
                                Hiding errors and significantly increasing application maintenance costs.

                                http://tkyte.blogspot.com/2006/09/classic-example-of-why-i-despise.html
                                http://tkyte.blogspot.com/2006/08/ouch-that-hurts.html
                                http://tkyte.blogspot.com/2008/06/when-others-then-null-redux.html
                                http://tkyte.blogspot.com/2007/03/dreaded-others-then-null-strikes-again.html
                                http://tkyte.blogspot.com/2012/05/pokemon-and-when-others.html

                                It is one of the cases where the code can be greatly improved simply by deleting it.