Error calling Oracle function on linked server

Question

I am calling an Oracle packaged function from SQL Server 2008 R2 and am getting the following error:

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

Here is the call I am attempting to make:

  DECLARE @p_pidm VARCHAR(8)
  DECLARE @p_emal_code VARCHAR(4)
  DECLARE @p_email_address VARCHAR(90)
  DECLARE @p_rowid VARCHAR(18) = NULL
  DECLARE @p_exists VARCHAR(1)

  SET @p_pidm = '11111'
  SET @p_emal_code = 'POA'
  SET @p_email_address = 'mytest@email.com'

  EXECUTE ('BEGIN ? := gb_email.f_exists(?,?,?,?);END;',@p_exists, @p_pidm, @p_emal_code, @p_email_address, @p_rowid)
  AT [TEST]

If I execute this call in SQL Developer it works fine. here's the query I'm executing there:

set serveroutput on ;
DECLARE rec_exists VARCHAR2(1) ;
begin
rec_exists := gb_email.f_exists(11111,'POA','mytest@email.com',NULL);
DBMS_OUTPUT.PUT_LINE(rec_exists);
end;

I've tried changing the size of @p_exists to VARCHAR(MAX) and that did nothing. I've verified that all of the variables I've declared match the definition for the package/function I'm calling.

This is not something I have to do often so any help in solving this problem would be greatly appreciated.

Here's the function definition:

FUNCTION f_exists(p_pidm          goremal.goremal_pidm%TYPE,
                  p_emal_code     goremal.goremal_emal_code%TYPE,
                  p_email_address goremal.goremal_email_address%TYPE,
                  p_rowid         gb_common.internal_record_id_type DEFAULT NULL)
  RETURN VARCHAR2


Checks to see if a record exists.   


Parameters  
 p_pidm The PIDM of the entity who owns this e-mail information. NUMBER(8) Required Key     
 p_emal_code The type of the e-mail address. VARCHAR2(4) Required Key     
 p_email_address The e-mail address. VARCHAR2(90) Required Key     
 p_rowid Database ROWID of record to be selected. VARCHAR2(18)     

Returns  
 Y if found, otherwise N.

Answer 1

Okay, I found the solution. I had failed to specify the parameter as OUTPUT. Here's the working query:

  DECLARE @p_pidm VARCHAR(8)
  DECLARE @p_emal_code VARCHAR(4)
  DECLARE @p_email_address VARCHAR(90)
  DECLARE @p_rowid_out VARCHAR(18) = NULL
  DECLARE @p_rec_exists VARCHAR(1)

  SET @p_pidm = '11111'
  SET @p_emal_code = 'POA'
  SET @p_email_address = 'mytest@email.com'

  EXECUTE ('BEGIN ? := gb_email.f_exists(?,?,?,?);END;',@p_rec_exists OUTPUT, @p_pidm, @p_emal_code, @p_email_address, @p_rowid_out)
  AT [TEST]
  SELECT @p_rec_exists