Problem with DBMS_OUTPUT.GET_LINES

Discussion of open issues, suggestions and bugs regarding ODAC (Oracle Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
jfudickar
Posts: 202
Joined: Fri 10 Mar 2006 13:03
Location: Oberursel / Germany

Problem with DBMS_OUTPUT.GET_LINES

Post by jfudickar » Mon 02 Nov 2015 18:23

Hi,

I've a realized a problem with DBMS_OUTPUT.GET_LINES.

I'm using the following code get the results from DBMS_OUTPUT:

Code: Select all

procedure TDBMS_Output.Get_Lines(out Lines: string; var NumLines: Integer);
var i: Integer;
  StoredProc : TOraStoredProc;
begin
  lines := '';
  StoredProc := TOraStoredProc.Create(nil);
  try
    StoredProc.Session := Session;
    StoredProc.StoredProcName := 'sys.dbms_output.get_lines';
    StoredProc.PrepareSQL;
    StoredProc.ParamByName('numlines').AsInteger := NumLines;
    StoredProc.ParamByName('lines').Length := NumLines;
    StoredProc.Execute;
    NumLines:= StoredProc.ParamByName('numlines').AsInteger;
    for i := 1 to NumLines do
      if i <= 1 then
        Lines := StoredProc.ParamByName('lines').ItemAsString[i]
      else
        Lines := Lines + #13#10 + StoredProc.ParamByName('lines').ItemAsString[i];
  finally
    FreeAndNil(StoredProc);
  end;
end;
Now I'm using the following SQL:

Code: Select all

begin
  dbms_output.put_line('-A
-B');
END ;
This leads to the following result:
-A
-
Using:

Code: Select all

begin
  dbms_output.put_line('-A
-B
-C
-D
-E
-F');
END ;
leads to
-A
-B
-C
-D
-
Each CRLF in the original line leads to one missing character at the end of the result.

The environment and session settings looks like:
Odac-Version 9.5.15
.
OCI-Lite False
OCI-Events True
OCI-Threaded True
OCI-Mutexed True
OCI-Shared False
OCI-Unicode True
.
.
Default Oracle Home InstantClient1
.
Oracle Home Count 2
Oracle Home Name [1] Oracle_Client10g
Path [1] c:\PROGRA~2\Oracle102
TNS Path [1]
OCI-DLL [1]
OCI-Client-DLL [1]
OCI-Version [1]
OCI-CallStyle [1] None
Possible CallStyles [1]
Oracle Home Name [2] InstantClient1
Path [2] c:\Program Files (x86)\Oracle102
TNS Path [2]
OCI-DLL [2] c:\Program Files (x86)\Oracle102\BIN\oci.dll
OCI-Client-DLL [2] c:\Program Files (x86)\Oracle102\BIN\oraclient10.dll
OCI-Version [2] 10.2.0.5.0
OCI-CallStyle [2] OCI80
Possible CallStyles [2] OCI73; OCI80
.
Session 1 MainOracleSession (OratoolMainForm)
Connect *****
HomeName
OracleVersion 10.2.0.5.0
InTransaction False
Connected True
AutoCommit True
Pooling False
Schema *******
Options.CharLength 0
Options.Charset
Options.ClientIdentifier
Options.ConvertEOL True
Options.ConnectionTimeOut 0
Options.DateFormat DD.MM.RR
Options.DateLanguage GERMAN
Options.DefaultSortType stBinary
Options.Direct False
Options.EnableBCD False
Options.EnableFMTBCD False
Options.EnableIntegers True
Options.EnableLargeInt False
Options.EnableNumbers False
Options.LocalFailOver False
Options.Net False
Options.NeverConnect False
Options.OptimizerMode omDefault
Options.StatementCache False
Options.StatementCacheSize 20
Options.SubscriptionPort 0
Options.UseOCI7 False
Options.UnicodeEnvironment True
Options.UseUnicode True
The same in SQL*Plus seams to work fine.

Any ideas???

With kind regards
Jens Fudickar

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by AlexP » Tue 03 Nov 2015 11:36

Hello,

Please send the code of the TDBMS_Output.Get_Lines method call and specify the exact Oracle server version.

jfudickar
Posts: 202
Joined: Fri 10 Mar 2006 13:03
Location: Oberursel / Germany

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by jfudickar » Tue 03 Nov 2015 19:19

Hi Alex,

the code outside is:

Code: Select all

procedure GetDBMSOutputFromSession;
  var
    OutPut: tStringList;
    dbms_output_result: string;
    NumLines: Integer;
  const cMaxLines : Integer = 1000;
  begin
    if not (spsfServerOutput in SQLParser.SQLPlusState.SetFlags) then
      Exit;
    NumLines := cMaxLines;
    OutPut := tStringList.Create;
    try
      try
        if FormSession.Connected then
          repeat
            FormSession.DBMS_Output.Get_Lines(dbms_output_result, NumLines);
            if NumLines > 0 then
            begin
              OutPut.Text := dbms_output_result;
              Statement.StatementDBMSOutPut.Add(dbms_output_result);
            end;
          until NumLines < cMaxLines;
      except
        on e: exception do
          FormSession.CheckConnection(False);
      end;
    finally
      FreeAndNil(OutPut);
    end;
  end;
But ou can see the problem directly when you debug the Get_Lines implementation.
After the line

Code: Select all

Lines := StoredProc.ParamByName('lines').ItemAsString[i]
the characters are not returned into the lines variable.
And Numlines is "1".

The problem exists on multiple database versions.

The first test as shown in the original reply was on 10.2.0.5.0.
My dev tests are on "Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production"
The NLS Settings on this database are:

Code: Select all

Type	Parameter	Value
DATABASE	NLS_CALENDAR	GREGORIAN
DATABASE	NLS_CHARACTERSET	AL32UTF8
DATABASE	NLS_COMP	BINARY
DATABASE	NLS_CURRENCY	$
DATABASE	NLS_DATE_FORMAT	DD-MON-RR
DATABASE	NLS_DATE_LANGUAGE	AMERICAN
DATABASE	NLS_DUAL_CURRENCY	$
DATABASE	NLS_ISO_CURRENCY	AMERICA
DATABASE	NLS_LANGUAGE	AMERICAN
DATABASE	NLS_LENGTH_SEMANTICS	BYTE
DATABASE	NLS_NCHAR_CHARACTERSET	AL16UTF16
DATABASE	NLS_NCHAR_CONV_EXCP	FALSE
DATABASE	NLS_NUMERIC_CHARACTERS	.,
DATABASE	NLS_RDBMS_VERSION	12.1.0.1.0
DATABASE	NLS_SORT	BINARY
DATABASE	NLS_TERRITORY	AMERICA
DATABASE	NLS_TIMESTAMP_FORMAT	DD-MON-RR HH.MI.SSXFF AM
DATABASE	NLS_TIMESTAMP_TZ_FORMAT	DD-MON-RR HH.MI.SSXFF AM TZR
DATABASE	NLS_TIME_FORMAT	HH.MI.SSXFF AM
DATABASE	NLS_TIME_TZ_FORMAT	HH.MI.SSXFF AM TZR
The application is build with Delphi XE6 and Odac-Version 9.5.15.

Regards
Jens

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by AlexP » Wed 04 Nov 2015 10:06

When using put_line, one string will be sent not depending on whether it contains line breaks or not. If you need to send several strings, you should call put_line several times.

jfudickar
Posts: 202
Joined: Fri 10 Mar 2006 13:03
Location: Oberursel / Germany

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by jfudickar » Wed 04 Nov 2015 10:11

But why is it working in SQL*Plus?

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by AlexP » Wed 04 Nov 2015 11:55

The same results in SQLPlus

Code: Select all

SQL>   create table test_get_lines(lines_count number);

Table created.

SQL> declare
  2    l  dbmsoutput_linesarray;
  3    numlines  integer default 10;
  4  begin
  5    dbms_output.put_line('-A
  6                          -B
  7                          -C
  8                          -D
  9                          -E
 10                          -F');
 11    dbms_output.get_lines(l, numlines);
 12    insert into test_get_lines values (numlines);
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> select * from test_get_lines;

LINES_COUNT
-----------
          1

jfudickar
Posts: 202
Joined: Fri 10 Mar 2006 13:03
Location: Oberursel / Germany

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by jfudickar » Wed 04 Nov 2015 12:29

Hi Alex,

the numlines is not my problem.

The problem is that the line is shortend in delphi.
The numlines will always be "1".

This script

Code: Select all

spool dbms_output_short.LOG
SET serveroutput ON
SET linesize 2000
BEGIN
dbms_output.put_line('-A
-B
-C
-D
-E
-F');
END;
/
spool off
will create the following log file:

Code: Select all

-A
-B
-C
-D
-E
-F                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               

PL/SQL procedure successfully completed.

When I do the same using my OraTool (using the shown delphi code) the result looks like:

Code: Select all

-A
-B
-C
-D
-


Statement successfully executed
Duration : 00:00:00,002

You see that the last characters are missing. NumLines will always be "1".

Any further ideas?

Regards
Jens

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by AlexP » Wed 04 Nov 2015 13:24

We can't reproduce the issue on the latest ODAC version 9.6.20. The below sample returns correct data. Please modify it so that the problem can be reproduced and send it back to us.

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, System.Classes, Ora;

var
  OraSession: TOraSession;


procedure Get_Lines(out Lines: string; var NumLines: Integer);
var i: Integer;
  StoredProc : TOraStoredProc;
begin
  lines := '';
  StoredProc := TOraStoredProc.Create(nil);
  try
    StoredProc.Session := OraSession;
    StoredProc.StoredProcName := 'sys.dbms_output.get_lines';
    StoredProc.PrepareSQL;
    StoredProc.ParamByName('numlines').AsInteger := NumLines;
    StoredProc.ParamByName('lines').Length := NumLines;
    StoredProc.Execute;
    NumLines:= StoredProc.ParamByName('numlines').AsInteger;
    for i := 1 to NumLines do
      if i <= 1 then
        Lines := StoredProc.ParamByName('lines').ItemAsString[i]
      else
        Lines := Lines + #13#10 + StoredProc.ParamByName('lines').ItemAsString[i];
  finally
    FreeAndNil(StoredProc);
  end;
end;

procedure GetDBMSOutputFromSession;
var
  OutPut: tStringList;
  dbms_output_result: string;
  NumLines: Integer;
const cMaxLines : Integer = 1000;
begin
  NumLines := cMaxLines;
  OutPut := tStringList.Create;
  try
    try
      if OraSession.Connected then
        repeat
          Get_Lines(dbms_output_result, NumLines);
          if NumLines > 0 then
          begin
            OutPut.Text := dbms_output_result;
            writeln(dbms_output_result);
          end;
        until NumLines < cMaxLines;
    except
    end;
  finally
    FreeAndNil(OutPut);
  end;
end;

begin
  OraSession := TOraSession.Create(nil);
  try
    OraSession.ConnectString := 'scott/tiger@orcl';
    OraSession.Options.ConvertEOL := True;
    OraSession.Options.UseUnicode := True;
    OraSession.Options.UnicodeEnvironment := True;
    OraSession.Connect;
    OraSession.ExecSQL('begin' + #13#10 +
                       'dbms_output.enable;' + #13#10 +
                       'dbms_output.put_line(''-A' + #13#10 +
                       '-B' + #13#10 +
                       '-C' + #13#10 +
                       '-D' + #13#10 +
                       '-E' + #13#10 +
                       '-F'');' + #13#10 +
                       'END ;');
    GetDBMSOutputFromSession;
  finally
    OraSession.Free;
    readln;
  end;
end.

jfudickar
Posts: 202
Joined: Fri 10 Mar 2006 13:03
Location: Oberursel / Germany

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by jfudickar » Fri 06 Nov 2015 22:07

Hi Alex,

I'm totally frustrated!!!

I can't reproduce the problem also.
Neither with you're sample, nor with an test application where I included all the necesarry code from my own application.
I've compared all session properties and so on.

The sample is working, the app not.
The dbms_monitor says for both way, that the same sql statements where executed in the same way.

Did you have any idea where I can look into.
I'm totally clueless :-(

With kind regards
Jens

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by AlexP » Mon 09 Nov 2015 10:01

The detailed description and samples of work with the DBMS_OUTPUT package are available in the Oracle documentation.
Perhaps, the string you retrieve in your code is trimmed at some moment - and you get incorrect result.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by AlexP » Mon 09 Nov 2015 10:01

The detailed description and samples of work with the DBMS_OUTPUT package are available in the Oracle documentation.
Perhaps, the string you retrieve in your code is trimmed at some moment - and you get incorrect result.

jfudickar
Posts: 202
Joined: Fri 10 Mar 2006 13:03
Location: Oberursel / Germany

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by jfudickar » Mon 09 Nov 2015 10:22

Hi Alex,

I've checked the statements which are executed with the failing application and with the working sample using the DB-Monitor.

The statmenent containing the dbms_output.put_line is exact the same (in DB-Monitor).
I can't look into the results of get_line using the DB-Monitor, because the result is a collection and then the values are not shown in the monitor.

Did you see/know any other way to look more detailed into the communication (any log files)??

Regards
Jens

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by AlexP » Tue 10 Nov 2015 12:33

Unfortunately, we can't advise you a utility to show such parameter values. Try contacting your DBA - such a utility may be available in the server.

sinys
Posts: 186
Joined: Tue 21 Feb 2012 03:44

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by sinys » Tue 20 Jun 2017 03:33

Hello, AlexP,
I tried to use your code example and I have next problem:
When I'm trying to call dbms_output.put_line('..long string(32000 chars)...') I'm getting error like buffer string to small but when I set Session.UseUnicode := true it is works fine. And I don't know what is it. Is it ODAC bug (or TStoredProcedure component bug) or is it right behavior? Can session set UseUnicode automatically when it is need?
Thank you.

sinys
Posts: 186
Joined: Tue 21 Feb 2012 03:44

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by sinys » Tue 20 Jun 2017 06:55

And one more question:
if I change const cMaxLines : Integer = 1000; to 100 is it reduce memory usage or what affect if I change it? Exclude size of set of course.

Post Reply