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 = '[email protected]'

  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','[email protected]',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.

What type does gb_email.f_exists() returns?

Anjan Biswas
– Anjan Biswas

2012-10-11 16:34:48 +00:00
Commented Oct 11, 2012 at 16:34 — Anjan Biswas
– Anjan Biswas, Commented Oct 11, 2012 at 16:34
I added the function description to my question.

user1921
– user1921

2012-10-11 17:24:24 +00:00
Commented Oct 11, 2012 at 17:24 — user1921
– user1921, Commented Oct 11, 2012 at 17:24

user1921 · Accepted Answer · 2012-10-11 17:45:44Z

3

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 = '[email protected]'

  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

answered Oct 11, 2012 at 17:45

user1921

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error calling Oracle function on linked server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related