Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2009
    Posts
    4

    Unanswered: How to get OLE DB related errors in T-SQL

    If I run a insert sql sentece to a Oracle Linked server I got a lot of errors messages in the Management Studio, for example:

    insert into LK_ORACLE_PRODUCCION..SCHM1.DE_123 VALUES('982', 'CO', '1')

    Error reported: OLE DB provider "OraOLEDB.Oracle" for linked server "LK_ORACLE_PRODUCCION" returned message "ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "SCHM1.DATAINP", line 199 ORA-01403: no data found ORA-01403: no data found Msg 7343, Level 16, State 2, Line 1 The OLE DB provider "OraOLEDB.Oracle" for linked server "LK_ORACLE_PRODUCCION" could not INSERT INTO table "[LK_ORACLE_PRODUCCION]..[SCHM1].[DE_123]".

    If I use a try/catch block, I can get the last message, but not all of them, example:

    BEGIN TRY
    insert into LK_ORACLE_PRODUCCION..SCHM1.DE_123 VALUES('982', 'CO', '1')
    END TRY

    BEGIN CATCH
    SELECT ERROR_NUMBER() AS ERR_NUM, ERROR_MESSAGE() AS ERR_MSG
    END CATCH

    I get: 7343 The OLE DB provider "OraOLEDB.Oracle" for linked server "LK_ORACLE_PRODUCCION" could not INSERT INTO table "[LK_ORACLE_PRODUCCION]..[SANPRO].[DE_982]".

    Is there any way to get ALL the error messages (including the "OLE DB provider "OraOLEDB..."") using T-SQL?

    Thanks

    Rafael Becerra

  2. #2
    Join Date
    Aug 2009
    Posts
    262
    it is said in manual , you cannot insert into an oracle table using ole-db driver ( meaing u cannot insert into oracle table from sqlserver . however u can select data from it .

  3. #3
    Join Date
    Dec 2009
    Posts
    4
    I'm sorry for not being specific on this topic... this is the long history:

    1. I have already made a sucessful transactional scheme between MS SQL Server 2005 and Oracle 10g. I have jobs in SQL Server which inserts some data into Oracle, using distributed transactions. This is working fine.

    2. The Oracle tables have some triggers, to process some information I'm inserting. For example, the info I insert contains information about a material, and that materials is associated with a work order.

    3. The 99% of the times, the insert is successful. But in some cases, the Oracle trigger rejects the insertion (for example, when the work order associated with the material is deleted from Oracle).

    4. So, what I have done, is a error validation logic, to log those errors in a SQL Table (say, tbl_Errors). When I use the SQL function ERROR_MESSAGE(), I only get the last line of the error (the error associated with SQL Server). What I like to have, is some function to also get the specific error messages provided by the OraOLEDB driver (in the previous post, it's the message that says "ORA-06502: PL/SQL: numeric or value error").

    Thanks

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you trap the error on the Oracle side of things?
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2009
    Posts
    4
    Quote Originally Posted by gvee View Post
    Can you trap the error on the Oracle side of things?
    Yes, I know that the Oracle guys can track the errors (Oracle is not part of my scope, there are specialized guys doing the Oracle stuff). This is why I need to provide them, in the SQL Server side, with the error message provided by the OraOLEdb driver (so they can match the errors they found in Oracle with the errors I need to report in SQL Server).

    Thanks

  6. #6
    Join Date
    Aug 2009
    Posts
    262
    the error codes in oracle are the complete reference to the problem occurred, i am an oracle lil dba myself so u can take a fraction of my word for it.
    there is no last line ... an error code is complete .




    ORA-06502: PL/SQL: numeric or value error string
    Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
    Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.


    2. The Oracle tables have some triggers, to process some information I'm inserting. For example, the info I insert contains information about a material, and that materials is associated with a work order.

    3. The 99% of the times, the insert is successful. But in some cases, the Oracle trigger rejects the insertion (for example, when the work order associated with the material is deleted from Oracle).

    either it is the data or the constraints causing the problem,
    while inserting one can ask the oracle guys to disable the constraints on a desired partition of the table.

    you can make sure the numeric inserts you are sending are not in form of INT , but in numeric (10 or any number , 0)

  7. #7
    Join Date
    Dec 2009
    Posts
    4
    Quote Originally Posted by mishaalsy View Post
    the error codes in oracle are the complete reference to the problem occurred, i am an oracle lil dba myself so u can take a fraction of my word for it.
    there is no last line ... an error code is complete .




    ORA-06502: PL/SQL: numeric or value error string
    Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
    Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.


    2. The Oracle tables have some triggers, to process some information I'm inserting. For example, the info I insert contains information about a material, and that materials is associated with a work order.

    3. The 99% of the times, the insert is successful. But in some cases, the Oracle trigger rejects the insertion (for example, when the work order associated with the material is deleted from Oracle).

    either it is the data or the constraints causing the problem,
    while inserting one can ask the oracle guys to disable the constraints on a desired partition of the table.

    you can make sure the numeric inserts you are sending are not in form of INT , but in numeric (10 or any number , 0)
    Yes, and I'm sure you are right...
    Again the summary of my question:
    SQL Server Management Studio shows the error in this way:
    1. Shows the OLEDB specific error number (like the one you noted).
    2. Shows the SQL related error number

    The error_number() function returns the SQL related error, but NO the OLEDB specific error. When I said "last line", I meaning this. What I need is a way to get (to have a function) to get the OLEDB specific error.

    Thanks

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •