TOraStoredProc (how to pass a char parameter in C++)

JFlo · Post by **JFlo** » Mon 16 Jun 2008 09:45

I want to pass a single char (in C++) as a parameter to an Oracle stored procedure with parameter type CHAR. Since there is no type ftChar i tried others like ftString or ftFixedChar but i will get an error ORA-12899 (actual 2 expected 1)
What can i do?

Challenger · Post by **Challenger** » Tue 17 Jun 2008 08:02

We could not reproduce this problem using the TOraStoredProc component. Please specify the versions of Oracle server and client you are using and send us a small sample to demonstrate this problem.

JFlo · Post by **JFlo** » Tue 17 Jun 2008 08:52

// Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
// ODAC 5.80.0.42 for C++ Builder 6

bool TAslLdb::LDB_MF_Error(const char severity, const String msg)
{
bool ok;
String str;

try {
oraProc->StoredProcName = "STVA_LDB.MF_Error";
oraProc->Params->Clear();
oraProc->Params->CreateParam(ftString,"Severity",ptInput); // char does not work!
oraProc->Params->ParamValues["Severity"]=severity;
oraProc->Params->CreateParam(ftString,"Message",ptInput); // String is ok
oraProc->Params->ParamValues["Message"]=msg;
oraProc->Params->CreateParam(ftInteger, "SQL_Ok", ptOutput);
oraProc->ExecProc();
ok = oraProc->ParamByName("SQL_Ok")->AsBoolean;
}
catch (const EDAError &err) {
printf(err.Message); // ORA-12899: Wert zu groß für Spalte "STVA"."PROTOKOLL"."P_STUFE" (aktuell: 2, maximal: 1)
ok = false;
}
return ok;
}

PROCEDURE MF_Error(Severity IN CHAR,
Message IN STRING,
SQL_Ok OUT NUMBER); /* BOOLEAN */

Challenger · Post by **Challenger** » Wed 18 Jun 2008 08:44

We still could not reproduce the problem. According to the reference of this error code it concerns only columns. Maybe the problem is in the code of the stored procedure.

JFlo · Post by **JFlo** » Wed 18 Jun 2008 09:09

The stored procedure works fine (is existing code and can be verified when called from sqlplus) It looks like (esential stuff):

PROCEDURE MF_Error(Severity IN CHAR, Message IN STRING, SQL_Ok OUT NUMBER) AS
BEGIN
INSERT INTO PROTOKOLL(P_STUFE, P_MELDUNG) VALUES(UPPER(Severity), Message);
COMMIT;
SQL_Ok:=0;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
SQL_Ok:=0;
END MF_Error;

Table:
CREATE TABLE PROTOKOLL(
P_STUFE CHAR(1) DEFAULT 'I', -- Severity
P_MELDUNG VARCHAR2(128), -- Meldungstext
P_ERF_TIME DATE DEFAULT SYSDATE); -- Erfassungs-Zeit

You are right: The error comes from the insert into the CHAR(1) field,
but since the passed parameter is not one CHAR as it shoud be, but 2 chars!
I think this would also happen if the insert is executed by a OraQuery.

Challenger · Post by **Challenger** » Thu 19 Jun 2008 14:38

We have reproduced the problem and now we are investigating it. As soon as we get any results we will let you know

Challenger · Post by **Challenger** » Fri 20 Jun 2008 09:14

This problem is caused by the peculiarity of converting char value to variant in C++Builder. C++Builder converts it to the SmallInt value and when converting back to string you get the code of the character. You can avoid this problem by explicit converting the char variable:

Code: Select all

oraProc->Params->ParamValues["Severity"]= String(severity);

Devart Forums

TOraStoredProc (how to pass a char parameter in C++)

TOraStoredProc (how to pass a char parameter in C++)

Example code

Bad value passed if a single char