locked
Error while calling Oracle Function from SQL Server RRS feed

  • Question

  • I am trying to call an oracle database function at the SQL Server that's have linked to the Oracle database. Below is the script I use:

     

    declare @OutputPara as float;
    EXECUTE ( 'BEGIN ? := get_exchange_rate(?,?); END;', 'USD', 'JPY', @OutputPara OUTPUT) at OracleTestDB
    select @OutputPara;

     

    And this is the error I got:

     

    OLE DB provider "MSDAORA" for linked server "OracleTestDB" returned message "ORA-06502: PL/SQL: numeric or value error: character string buffer too small

    ORA-06512: at line 1

    ".

    (1 row(s) affected)

    Msg 7215, Level 17, State 1, Line 2

    Could not execute statement on remote server 'OracleTestDB'.

     

     

    It's a dummy database function in Oracle Database that always return the same value:

     

    CREATE OR REPLACE FUNCTION get_exchange_rate 
    ( from_currency IN varchar2,
     to_currency IN varchar2)
    RETURN FLOAT IS
    tmpVar FLOAT;
    
    BEGIN
      tmpVar := 91.65;
      RETURN tmpVar;
      EXCEPTION
       WHEN NO_DATA_FOUND THEN
        NULL;
       WHEN OTHERS THEN
        -- Consider logging the error and then re-raise
        RAISE;
    END get_exchange_rate;
    /
    Please help, Thanks!! 

     

    Friday, June 18, 2010 8:58 AM

Answers

  • This error may occur in two situations

    1. NOT NULL column if you're trying to insert NULL value you will get this

    2. If you're inserting more than specified length this will occur

    check in the insert statement

    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)

    Friday, June 18, 2010 1:50 PM
  • >>OLE DB provider "MSDAORA" for linked server "OracleTestDB" returned message "ORA-06502: PL/SQL: numeric or >>value error: character string buffer too small

    JusticeFish, the error is coming from ORALCE and has nothing to do with SQL Server.

    You get this error when you attempt to assign a value to a PL/SQL variable which is not big enough for it

    Friday, June 18, 2010 2:14 PM

All replies

  • This error may occur in two situations

    1. NOT NULL column if you're trying to insert NULL value you will get this

    2. If you're inserting more than specified length this will occur

    check in the insert statement

    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)

    Friday, June 18, 2010 1:50 PM
  • >>OLE DB provider "MSDAORA" for linked server "OracleTestDB" returned message "ORA-06502: PL/SQL: numeric or >>value error: character string buffer too small

    JusticeFish, the error is coming from ORALCE and has nothing to do with SQL Server.

    You get this error when you attempt to assign a value to a PL/SQL variable which is not big enough for it

    Friday, June 18, 2010 2:14 PM
  • Hopefully one of the other responses here has answered the question, but if not, please post it on an Oracle forum - as Chirag Shah says, this has nothing to do with SQL Server.
    This post is provided 'as is' and confers no express or implied warranties or rights.
    Friday, June 18, 2010 4:38 PM