This discussion is archived
6 Replies Latest reply: Mar 19, 2012 7:09 PM by 84959 RSS

sp_getmessage equivalent in oracle

srpa01red - oracle Newbie
Currently Being Moderated
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
    84959 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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
    84959 Explorer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points