AV when using XMLTYPE params

[ac]

when executing the following method, an AV occurs. AFAICS I used it like described in the help. Am I doing something wrong or is there a bug?

Code: Select all

procedure TTestOraParamXML.TestXML;
var
  lXMLStr: String;
  I: Integer;
  lSession: TOraSession;
  lProc: TOraStoredProc;
  lXMLLob: TOraLob;
begin
  lXMLStr := '123';

  lSession := TOraSession.Create(nil);
  try
    lSession.LoginPrompt := False;
    lSession.Options.Net := False;
    lSession.Username := 'user';
    lSession.Password := 'password';
    lSession.Server := 'server';//some_server;//enter server address here;
    lSession.Connect;
    CheckTrue(lSession.Connected, 'connected');

    lSession.ExecSQL(
      'CREATE OR REPLACE FUNCTION RETURN_XML(ARG XMLTYPE) RETURN XMLTYPE IS ' + sLineBreak +
      'BEGIN RETURN ARG; END; '
    , []);

    lProc := TOraStoredProc.Create(nil);
    try
      lProc.StoredProcName := 'RETURN_XML';
      lProc.PrepareSQL;
// --- here lProc.ParamByName('ARG').AsXml is nil!!!
// --- lProc.ParamByName('ARG').DataType is dtADT, not dtXml!
      with lProc.ParamByName('ARG').AsXml do begin
        lXMLLob := TOraLob.Create(lSession.OCISvcCtx);
        try
          lXMLLob.CreateTemporary(ltClob);
          lXMLLob.Write(0, Length(lXMLStr), PChar(lXMLStr));
          lXMLLob.WriteLob;
          AllocObject(lSession.OCISvcCtx, lXMLLob);
// ---  here raises ACCESS VIOLATION Read of Address 000000. TOraXML Line:2836 'OraObjects'
        finally
          FreeAndNil(lXMLLob);
        end;
      end;
      lProc.Execute;
    finally
      FreeAndNil(lProc);
    end;
  finally
    FreeAndNil(lSession);
  end;
end;

[Plash]

When working with TOraStoredProc you should manually set DataType property to ftXml for XMLTYPE parameters. We have fixed this bug, and the fix will be included in next build of ODAC.

Creating XMLTYPE from LOB with method TOraXML.AllocObject(SvcCtx: pOCISvcCtx; OraLob: TOraLob) does not work on some versions of Oracle client. In that case you can assign value to XMLTYPE parameter using TOraXML.AsString property (if length of the XML text is not more than 4000 characters).

Or you can use temporary CLOB with TOraSQL component. Assign the following text to SQL property:

DECLARE
Result_xml XMLTYPE;
BEGIN
Result_xml := Return_xml(XMLTYPE(:arg));
:Result := Result_xml.GetClobVal;
END;

Set DataType property of parameters ARG and RESULT to ftOraClob. Set ParamType of ARG to ptInput and ParamType of RESULT to ptOutput.