DBMS output

[wjm4]

I have a stored procedure in oracle that puts information on dbms_output. How can I access this information in within Delphi after I execute this procedure ?

[MaximG]

You can use the DBMS_Output package in the following way. Create a test procedure with the following content:

Code: Select all

CREATE OR REPLACE procedure Pet(StringParameter IN VARCHAR2) is
begin
  DBMS_Output.Enable(1024);
  DBMS_Output.Put_Line(StringParameter);
end;

Then data retrieving with the DBMS_Output package may look like the following:

Code: Select all

var
  OutPutSQL: TOraSQL;
begin
   ...
  OraStoredProc.StoredProcName := 'Pet';
  OraStoredProc.Prepare;
  OraStoredProc.ParamByName('StringParameter').AsString := 'Hello, World !';
  OraStoredProc.ExecProc;
   ...
  OutPutSQL := TOraSQL.Create(Self);
  try
    OutPutSQL.Session := OraSession;
    OutPutSQL.SQL.Add('begin');
    OutPutSQL.SQL.Add('  dbms_output.get_line(:LINE, :STATUS);');
    OutPutSQL.SQL.Add('end;');
  OutPutSQL.ParamByName('LINE').DataType:= ftWideMemo;
  OutPutSQL.ParamByName('LINE').ParamType := ptOutput;
  OutPutSQL.ParamByName('STATUS').DataType := ftInteger;
  OutPutSQL.ParamByName('STATUS').ParamType := ptOutput;
  OutPutSQL.Execute;
  if OutPutSQL.ParamByName('STATUS').AsInteger = 0 then
    ShowMessage(OutPutSQL.ParamByName('Line').AsString);
  finally
    OutPutSQL.Free;
  end;
end;