VARCHAR2(10 CHAR) problem
Posted: Wed 02 Sep 2009 07:44
Dear Support,
We have a problem with object attribute type VARCHAR2(n CHAR). The NLS_CHARACTERSET is set to AL32UTF8 in the database. If we declare the attribute to type VARCHAR2(10) then everything is OK. But we need to declare it to VARCHAR2(10 CHAR) to store exacly 10 character. If the attribute type is VARCHAR2(10 CHAR) then we get ORA-21525 error when passing an object parameter to a stored procedure or an update statement. In PL/SQL code we have no problem, only using the object from ODAC component.
Below I've appended some sample code. Please help us because this is a critical problem for our project!
Regards,
Balazs Miereisz
--- Sample code ---
SQL code:
Delphi Code (SQLSelect and SQLUpdate is TOraSQL):
--- Saple code end ---
We have a problem with object attribute type VARCHAR2(n CHAR). The NLS_CHARACTERSET is set to AL32UTF8 in the database. If we declare the attribute to type VARCHAR2(10) then everything is OK. But we need to declare it to VARCHAR2(10 CHAR) to store exacly 10 character. If the attribute type is VARCHAR2(10 CHAR) then we get ORA-21525 error when passing an object parameter to a stored procedure or an update statement. In PL/SQL code we have no problem, only using the object from ODAC component.
Below I've appended some sample code. Please help us because this is a critical problem for our project!
Regards,
Balazs Miereisz
--- Sample code ---
SQL code:
Code: Select all
CREATE OR REPLACE TYPE TP_DEFOBJ AS OBJECT (
ID NUMBER(9),
MSG VARCHAR2(10 CHAR)
) NOT FINAL;
/
CREATE TABLE T_MSG OF TP_DEFOBJ;
INSERT INTO T_MSG (ID, MSG) VALUES (1, '123');
COMMIT;Code: Select all
procedure TForm1.Button1Click(Sender: TObject);
var
tpMsg: TOraObject;
begin
SQLSelect.SQL.Clear;
SQLSelect.SQL.Add('begin');
SQLSelect.SQL.Add('select value(m) into :tpm from t_msg m where m.ID = 1;');
SQLSelect.SQL.Add('end;');
SQLUpdate.SQL.Clear;
SQLUpdate.SQL.Add('UPDATE T_MSG M');
SQLUpdate.SQL.Add('SET VALUE(M) = :iMSG');
SQLUpdate.SQL.Add('WHERE M.ID = 1;');
tpMsg := TOraObject.Create(TOraType.Create(OraSession1.OCISvcCtx, 'TP_DEFOBJ'));
SQLSelect.ParamByName('tpm').DataType := ftObject;
SQLSelect.ParamByName('tpm').ParamType := ptOutput;
SQLSelect.ParamByName('tpm').AsObject := tpMsg;
tpMsg.AllocObject(OraSession1.OCISvcCtx, 'TP_DEFOBJ');
SQLSelect.Execute;
tpMsg.AttrAsString['MSG'] := '1234567890';
SQLUpdate.ParamByName('iMSG').DataType := ftObject;
SQLUpdate.ParamByName('iMSG').ParamType := ptInput;
SQLUpdate.ParamByName('iMSG').AsObject := tpMsg;
SQLUpdate.Execute; {Here we got the ORA-21525 }
end;