SqlNonQuery - Output parameter Value is too large to bind

Permanently deleted user

April 24, 2014 02:51

I am wondering if any one has success in retrieving out parameter value from a Sqlnonquery activity in GE 4.0?

I am using GE  4.0 and workflow designer 2.20.2.19. I have a workflow that takes users input and uses SQLNonQuery to insert a new record to an oracle database table. I keep getting the following error saying Parameter ':WORK_REQUEST_NO_OUT' value is too large to bind.

 

Here is how I set the parameters in my workflow:

/customer/servlet/servlet.FileDownload?file=00P6000000e88XTEAY

In side of the SQLNONQUERY activity command, I have:

 

DECLARE

 

  PLANT_IN VARCHAR2(3);

 

  DESCRIPTION_IN VARCHAR2(2000);

 

  ASSET_LIST_IN VARCHAR2(32767);

 

  REQUESTOR_IN VARCHAR2(100);

 

  WORK_REQUEST_NO_OUT VARCHAR2(7);

 

  ERROR_NO_OUT VARCHAR2(32767);

 

  ERROR_MESSAGE_OUT VARCHAR2(32767);

BEGIN

 

  PLANT_IN := '01';

 

  DESCRIPTION_IN := :DESCRIPTION_IN;

 

  ASSET_LIST_IN := :ASSET_LIST_IN;

 

  REQUESTOR_IN := :REQUESTOR_IN;

 

  WORK_REQUEST_NO_OUT := :WORK_REQUEST_NO_OUT;

 

  ERROR_NO_OUT :=:ERROR_NO_OUT;

 

  ERROR_MESSAGE_OUT := :ERROR_MESSAGE_OUT;

  SYNERGEN.SDBP_GIS_CREATE_WR (PLANT_IN, DESCRIPTION_IN, ASSET_LIST_IN, REQUESTOR_IN, WORK_REQUEST_NO_OUT, ERROR_NO_OUT, ERROR_MESSAGE_OUT);

  COMMIT;

 

END;

 

I don't understand why it complains about the size is too large to bind since in the command I have WORK_REQUEST_NO_OUT VARCHAR2(7) which match the field size in the oracle table. I tested the above command in TOAD with the following static value and

DECLARE

 

  PLANT_IN VARCHAR2(3);

 

  DESCRIPTION_IN VARCHAR2(2000);

 

  ASSET_LIST_IN VARCHAR2(32767);

 

  REQUESTOR_IN VARCHAR2(100);

 

  WORK_REQUEST_NO_OUT VARCHAR2(50);

 

  ERROR_NO_OUT VARCHAR2(32767);

 

  ERROR_MESSAGE_OUT VARCHAR2(32767);

BEGIN

 

  PLANT_IN := '01';

 

  DESCRIPTION_IN := 'Jin Test1 - value is too large to bind error from GIS';

 

  ASSET_LIST_IN := 'D BC00003';

 

  REQUESTOR_IN := 'ZHANGJ';

 

  WORK_REQUEST_NO_OUT := NULL;

 

  ERROR_NO_OUT :=NULL;

 

  ERROR_MESSAGE_OUT := NULL;

  SYNERGEN.SDBP_GIS_CREATE_WR (PLANT_IN, DESCRIPTION_IN, ASSET_LIST_IN, REQUESTOR_IN, WORK_REQUEST_NO_OUT, ERROR_NO_OUT, ERROR_MESSAGE_OUT);

  DBMS_OUTPUT.Put_Line('WORK_REQUEST_NO_OUT = ' || WORK_REQUEST_NO_OUT);

 

  DBMS_OUTPUT.Put_Line('ERROR_NO_OUT = ' || ERROR_NO_OUT);

 

  DBMS_OUTPUT.Put_Line('ERROR_MESSAGE_OUT = ' || ERROR_MESSAGE_OUT);

  DBMS_OUTPUT.Put_Line('');

 

  COMMIT;

 

END;

I was able to get expected result from the DBMS output window:

WORK_REQUEST_NO_OUT = W130160

 

ERROR_NO_OUT = 0

 

ERROR_MESSAGE_OUT = No errors found.

Any help is appreciated.

 

Regards,

 

Jin

0

• 

  Permanently deleted user

  April 28, 2014 22:29

  Can anyone who has successfully retrieved output parameter value from a SQLNONQUERY activity please offer some help? I keep getting this  "Parameter is too large to bind error" can could not find the cause.

   

  Thanks again.

   

  Jin

  0
• 

  Ryan Cooney

  May 15, 2014 01:44

  Hi Jin,

  We see this "value is too large to bind" error when attempting to use string and byte array output parameters with an Oracle database. Other output parameter types (int, double) are fine.

  Oracle requires additional information about the size of string and binary output parameters. Unfortunately there is no way for you to provide this information to the activity.

  We are looking in to some possible code workarounds that would handle these Oracle scenarios. 

  --Ryan

   

  0

Please sign in to leave a comment.