Why cut the value of the field

[Stark II]

ODAC version 6.25 or 8.6
Why cut the value of the field sSelectionName after we do refresh?

Code: Select all

    oraDataSet.Session := FOraSession;
    oraDataSet.SQL.Text := 'begin open :cur for PRL_DOCTYPEAPI.getsKPITabGST(:iddoctype); end;';
    oraDataSet.Params.ParamByName('cur').DataType := ftCursor;

    oraDataSet.OptionsDS.AutoClose := true;
    oraDataSet.Execute;
    oraDataSet.Open;

    if oraDataSet.RecordCount > 0 then
    begin
      field := oraDataSet.FieldByName('sSelectionName');
      Assert(SameText(field.AsString,'SEL_PRL_QuotientKPIDet')); // !!! Correctly
    end;

    oraDataSet.Params.ParamByName('iddoctype').DataType := ftFloat;
    oraDataSet.Params.ParamValues['iddoctype'] := '172606130001';

    oraDataSet.Refresh;

    if oraDataSet.RecordCount > 0 then
    begin
      field := oraDataSet.FieldByName('sSelectionName');
      Assert(SameText(field.AsString,'SEL_PRL_QuotientPRCKPIDet')); // !!! Wrong
    end;

Really, after refresh, field.AsString = 'SEL_PRL_QuotientPRCKPI' instead of 'SEL_PRL_QuotientPRCKPIDet' and field.Size=Length('SEL_PRL_QuotientKPIDet')=22

function code:

Code: Select all

 static function GetsKPITabGST(idpSelf in number) return varchar2
 as
 begin

   for xvRow in (select t.sKPITabGST
                   from prl_doctype t
                  where t.id = idpSelf
                    and t.sKPITabGST is not null
   )loop
     return xvRow.sKPITabGST;
   end loop;

   return 'Select null as id'||chr(10)||
          '     ,''SEL_PRL_QuotientKPIDet'' as sSelectionName'||chr(10)||
          '     ,''CLList_idQuotientKPI'' as sRepresentationName'||chr(10)||
          '     ,''Показатели'' as sDisplayName'||chr(10)||
          '     ,null as nImage'||chr(10)||
          ' from dual';
 end;

In table prl_doctype for id = 172606130001 exist only one row where
sKPITabGST = Select null as id
,'SEL_PRL_QuotientPRCKPIDet' as sSelectionName
,'CLList_idQuotientKPI' as sRepresentationName
,'Показатели' as sDisplayName
,null as nImage
from dual

[Stark II]

This test code that is completely error repeats:

Code: Select all

procedure TForm1.btnBtnTestClick(Sender: TObject);
const
  strSQLText = 'begin'#13#10+
                 'if (:id = 1) then'#13#10+
                   'open :cur for select ''00000000000'' as Text from dual;'#13#10+
                 'else'#13#10+
                   'open :cur for select ''11111'' as Text from dual;'#13#10+
                 'end if;'#13#10+
               'end;';
var
  oraSession : TOraSession;
  oraDataSet : TOraDataSet;
  field      : TField;
begin
  oraSession := TOraSession.Create(nil);
  try
    oraSession.ConnectString := '**************';
    oraSession.Open;
    oraDataSet := TOraDataSet.Create(nil);
    try
      oraDataSet.Session := oraSession;
      oraDataSet.SQL.Text := strSQLText;
      oraDataSet.Params.ParamByName('id').DataType := ftFloat;
      oraDataSet.Params.ParamByName('cur').DataType := ftCursor;

      oraDataSet.OptionsDS.AutoClose := true;
      oraDataSet.Execute;
      oraDataSet.Open;

      if oraDataSet.RecordCount > 0 then
      begin
        field := oraDataSet.FieldByName('Text');
        Assert(SameText(field.AsString,'11111'),'Text = "'+field.AsString+'" instead of "11111"');
      end;

      oraDataSet.Params.ParamValues['id'] := '1';

      oraDataSet.Refresh;

      if oraDataSet.RecordCount > 0 then
      begin
        field := oraDataSet.FieldByName('text');
        Assert(SameText(field.AsString,'00000000000'),'Text = "'+field.AsString+'" instead of "00000000000"');
      end;
    finally
      FreeAndNil(oraDataSet);
    end;
  finally
    FreeAndNil(oraSession);
  end;
end;

Triggered a second Assert.

[AlexP]

Hello,

This is correct behaviour, as when opening the cursor, required fields are created in the DataSet, and since the field type is unknown, then the field is created on the basis of the data, in your case - a string field with size 5 is created for "11111". As you don't close the DataSet, but just do refresh, fields in the DataSet are not re-created - and, respectively, a string longer than 5 characters is cropped to the current field length. To solve the problem, you should either reopen the cursor by explicit calling of the close/open methods of TOraDataSet or map the string in the query to varchar2 and specify maximum possible length. For example, in your query:

Code: Select all

  strSQLText = 'begin'#13#10+
                 'if (:id = 1) then'#13#10+
                   'open :cur for select cast(''00000000000'' as varchar2(11)) as Text from dual;'#13#10+
                 'else'#13#10+
                   'open :cur for select cast(''11111'' as varchar2(11)) as Text from dual;'#13#10+
                 'end if;'#13#10+
               'end;';

[Stark II]

Ok thanks.