6 Replies Latest reply: Mar 19, 2012 9:09 PM by Pnauduri-Oracle RSS

    sp_getmessage equivalent in oracle

    srpa01red - oracle
      hi

      we are migrating from sybase to oracle11g.

      As a part of it migration , a stored procedure is failing on sp_getmessage(sybase).

      Is there any equivalent function/procedure in oracle like sp_getmessage in sybase.

      sp_getmessage:
      Description:
      Retrieves stored message strings from sysmessages and sysusermessages for print and raiserror statements.



      Regards...
        • 1. Re: sp_getmessage equivalent in oracle
          Pnauduri-Oracle
          There is no equivalent functionality in Oracle. Error messages generated during the execution of stored procedures because of RAISE commands or otherwise are not persisted in the database that can be retrieved later. So you need to handle the error conditions in the procedure and then display it immediately when it occurs. Don't think many users actually store the error messages in a table for later use. Although some may log them for auditing purposes based on error condition.

          DECLARE ...
          BEGIN
          ....
          EXCEPTION WHEN ....
          DBMS_PUTLINE('Error occurred');
          END;

          Regards

          Prakash
          • 2. Re: sp_getmessage equivalent in oracle
            srpa01red - oracle
            hi

            doing sybase to oracle migration.

            sybase code:

            CREATE PROCEDURE mufin.xxx
            (@p_error_code     char(6),
                 @p_error_msg     varchar(254) = "" OUTPUT)
            AS

            DECLARE     @int_errcode     integer
            IF     (@p_error_code LIKE "[0123456789 ][0123456789 ][0123456789 ][0123456789 ][0123456789 ] "     
            AND     CONVERT(integer, SUBSTRING(@p_error_code, 1, 5)) <= 20000)                         
            BEGIN
                 SELECT     @int_errcode = CONVERT(integer, SUBSTRING(@p_error_code, 1, 5))
                 EXEC     sp_getmessage     @int_errcode,@p_error_msg OUTPUT
            END
            ELSE
            BEGIN
                 SELECT     @p_error_msg = libmsg
                 FROM     .mufin.tstbdbmsgstd
                 WHERE     coderr = @p_error_code
                 IF     @@rowcount != 1
                      SELECT     @p_error_msg = "Undefined error proceed"
            END
            RETURN 0

            oracle code:


            CREATE OR REPLACE FUNCTION mufin.xxx
            (
            v_p_error_code IN CHAR DEFAULT NULL ,
            v_p_error_msg OUT VARCHAR2
            )
            RETURN NUMBER
            AS
            v_int_errcode NUMBER(10,0);

            BEGIN
            IF ( v_p_error_code LIKE A0123456789_0123456789_0123456
            AND CAST(SUBSTR(v_p_error_code, 1, 5) AS NUMBER) <= 20000 ) THEN

            BEGIN
            v_int_errcode := CAST(SUBSTR(v_p_error_code, 1, 5) AS NUMBER) ;
            sp_getmessage(v_int_errcode,v_p_error_msg);
            END;
            ELSE

            BEGIN
            SELECT libmsg

            INTO v_p_error_msg
            FROM tstbdbmsgstd
            WHERE coderr = v_p_error_code;
            IF SQL%ROWCOUNT != 1 THEN
            v_p_error_msg := Undefined_error_proceed ;
            END IF;
            END;
            END IF;
            RETURN 0;
            END;

            i am doing the migration using sql devloper3.1

            the oracle code erroring out at :
            IF ( v_p_error_code LIKE A0123456789_0123456789_0123456 and at sp_getmessage.

            can any one plz correct it....


            regards
            • 3. Re: sp_getmessage equivalent in oracle
              Mkirtley-Oracle
              Hi,
              You have not said what error you get at - IF ( v_p_error_code LIKE A0123456789_0123456789_0123456 and at sp_getmessage.
              However, have you checked that you have a sp_getmessage procedure created during the migration ?
              There is a bug for the 3.0.0.1 version of the workbench because sp_message is not converted but it isn't clear if it was fixed in later versions.

              Regards,
              Mike
              • 4. Re: sp_getmessage equivalent in oracle
                srpa01red - oracle
                hi mike


                you check that sybase code was not perfectly converted into oracle code
                the sybase code

                IF     (@p_error_code LIKE "[0123456789 ][0123456789 ][0123456789 ][0123456789 ][0123456789 ] "

                is not converted into oracle code

                IF ( v_p_error_code LIKE A0123456789_0123456789_0123456


                what can i use instead of sp_getmessage in oracle.
                any equivalent procedure or function is there

                more over i am using 3.1.06 version of sql dev.


                regards
                • 5. Re: sp_getmessage equivalent in oracle
                  Mkirtley-Oracle
                  Hi,
                  Okay, it looks like the string -

                  IF (@p_error_code LIKE "[0123456789 ][0123456789 ][0123456789 ][0123456789 ][0123456789 ] "

                  is not converted correctly, and should probably look like -

                  IF ( v_p_error_code LIKE "[0123456789 ][0123456789 ][0123456789 ][0123456789 ][0123456789 ] "

                  which looks like a bug in Developer.
                  As said earlier there is no direct equivalent in Oracle of sp_getmessage. Can you confirm if there is a procedure equivalent to sp_getmessage created during the migration ?

                  PL/SQL error handling is discussed in the documentation -

                  Oracle® Database
                  PL/SQL Language Reference
                  11g Release 2 (11.2)

                  In Chapter 11 - 11PL/SQL Error Handling

                  It is available here -

                  http://docs.oracle.com/cd/E11882_01/appdev.112/e25519.pdf - PL/SQL languages

                  If sp_getmessage is not migrated then we can follow up with development to see if it should be, otherwise you will have to write something yourself to handle what it does.

                  What happens if you put the Sybase procedure -

                  CREATE PROCEDURE mufin.xxx
                  (@p_error_code char(6),
                  @p_error_msg varchar(254) = "" OUTPUT)
                  AS

                  DECLARE @int_errcode integer
                  IF (@p_error_code LIKE "[0123456789 ][0123456789 ][0123456789 ][0123456789 ][0123456789 ] "
                  AND CONVERT(integer, SUBSTRING(@p_error_code, 1, 5)) <= 20000)
                  BEGIN
                  SELECT @int_errcode = CONVERT(integer, SUBSTRING(@p_error_code, 1, 5))
                  EXEC sp_getmessage @int_errcode,@p_error_msg OUTPUT
                  END
                  ELSE
                  BEGIN
                  SELECT @p_error_msg = libmsg
                  FROM .mufin.tstbdbmsgstd
                  WHERE coderr = @p_error_code
                  IF @@rowcount != 1
                  SELECT @p_error_msg = "Undefined error proceed"
                  END
                  RETURN 0

                  into the 'Translation Scratch Editor' -

                  Tools - Migration - Translation Scratch Editor

                  does that give errors ?

                  YOu could also try manually editting the Oracle output to have the correct syntax.
                  However, if there is no sp_getmessage procedure then this will not work in any case.

                  Regards,
                  Mike
                  • 6. Re: sp_getmessage equivalent in oracle
                    Pnauduri-Oracle
                    You will have to come up with a custom implementation of sp_getmessage functionality in Oracle in the form of a function or a procedure. But before that you will have to come up with a error logging routine incorporated in all stored procedures/triggers that will store all error messages and error nos (application errors) in a table . In Oracle , the application error messages/codes are not persisted for them to be retrieved later. SQL developer does not convert all system functions provided by Sybase/SQL Server althought it does convert quite a few of them in the form of the UTILS package in the EMULATION schema.

                    With regards to sp_getmessage() functionality in Oracle, you can use SQLERRM system function in Oracle that takes an error code as the input and returns the actual Oracle message corresponding to that error code. More information can be found here: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/sqlerrm_function.htm#i38980

                    For consistency, you can create a sp_getmessage function in the UTILS package.

                    Regards

                    Prakash