ADO.CreateADOStoredProc(): cant return not numeric value

[OleksiiShyshkin]

Hi! 

Try to run Oracle stored procedure in TestComplete code: 
PROCEDURE TEST_PROCEDURE(IN_ID IN INT,
OUT_ID OUT INT,
OUT_ID1 OUT VARCHAR2)
IS
BEGIN
SELECT 1,
'2'
INTO OUT_ID,
OUT_ID1
FROM DUAL;
END TEST_PROCEDURE; 

JS code:

var SProc;
SProc = ADO.CreateADOStoredProc();
SProc.ConnectionString="DRIVER=...bla-bla-bla";

SProc.ProcedureName = "SHS_TEST_PKG.TEST_PROCEDURE";
// IN parameters
SProc.Parameters.CreateParameter("IN_ID", adInteger, adParamInput, 0, 21);
// OUT parameters
SProc.Parameters.CreateParameter("OUT_ID", DB.adInteger, adParamOutput, 0, 0);
SProc.Parameters.CreateParameter("OUT_ID1", DB.adInteger, adParamOutput, 0, 0);
SProc.ExecProc();
Log.Message("out1="+ SProc.Parameters.ParamByName("OUT_ID").Value+" out2=" + SProc.Parameters.ParamByName("OUT_ID1").Value);

////result

out1=1 out2=2 

Result OK, while out value is numeric or may be converted to numeric! 

But, when type of out value = varchar, "test 2" for example, sql - 
SELECT 1,
'test 2'
INTO OUT_ID,
OUT_ID1
FROM DUAL;
and JS code for this -  
SProc.Parameters.CreateParameter("OUT_ID", DB.adInteger, adParamOutput, 0, 0);
SProc.Parameters.CreateParameter("OUT_ID1", DB.adChar, adParamOutput, 0, 0);
no result with error - 
"Неправильно определен объект Parameter. Предоставлены несовместимые или неполные сведения"

[BenoitB]

Try DB.adVarChar instead of DB.adChar

Which driver do u use ? odp.net ?

[OleksiiShyshkin]

I tried with "Driver={Microsoft ODBC for Oracle}" and "Driver={Oracle in OraClient10g_home1}", with "DB.adVarChar" result the same - failed(( 

[sonya_m]

Thank you for the advice Benoit!

Let's see if there are more suggestions. Any ideas, Community?

@tristaanogre @Marsha_R @cunderw 

[OleksiiShyshkin · Accepted solution]

(!) Find solution in old topic - https://community.smartbear.com/t5/TestComplete-General-Discussions/CreateADOStoredProc-and-a-adVarC...

- When using ADO.CreateADOStoredProc, use the VCL data type constants:
http://docwiki.embarcadero.com/Libraries/en/Data.Win.ADODB.TDataType

So, 

SProc = ADO.CreateADOStoredProc();

//worked!

SProc.Parameters.CreateParameter("OUT_ID1", ftFixedChar, adParamOutput, 1000, null);

//NOT worked!

//SProc.Parameters.CreateParameter("OUT_ID1", adVarChar, adParamOutput, 1000, null);