Page 1 of 2
Problem with DBMS_OUTPUT.GET_LINES
Posted: Mon 02 Nov 2015 18:23
by jfudickar
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
Re: Problem with DBMS_OUTPUT.GET_LINES
Posted: Tue 03 Nov 2015 11:36
by AlexP
Hello,
Please send the code of the TDBMS_Output.Get_Lines method call and specify the exact Oracle server version.
Re: Problem with DBMS_OUTPUT.GET_LINES
Posted: Tue 03 Nov 2015 19:19
by jfudickar
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
Re: Problem with DBMS_OUTPUT.GET_LINES
Posted: Wed 04 Nov 2015 10:06
by AlexP
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.
Re: Problem with DBMS_OUTPUT.GET_LINES
Posted: Wed 04 Nov 2015 10:11
by jfudickar
But why is it working in SQL*Plus?
Re: Problem with DBMS_OUTPUT.GET_LINES
Posted: Wed 04 Nov 2015 11:55
by AlexP
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
Re: Problem with DBMS_OUTPUT.GET_LINES
Posted: Wed 04 Nov 2015 12:29
by jfudickar
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
Re: Problem with DBMS_OUTPUT.GET_LINES
Posted: Wed 04 Nov 2015 13:24
by AlexP
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.
Re: Problem with DBMS_OUTPUT.GET_LINES
Posted: Fri 06 Nov 2015 22:07
by jfudickar
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
Re: Problem with DBMS_OUTPUT.GET_LINES
Posted: Mon 09 Nov 2015 10:01
by AlexP
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.
Re: Problem with DBMS_OUTPUT.GET_LINES
Posted: Mon 09 Nov 2015 10:01
by AlexP
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.
Re: Problem with DBMS_OUTPUT.GET_LINES
Posted: Mon 09 Nov 2015 10:22
by jfudickar
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
Re: Problem with DBMS_OUTPUT.GET_LINES
Posted: Tue 10 Nov 2015 12:33
by AlexP
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.
Re: Problem with DBMS_OUTPUT.GET_LINES
Posted: Tue 20 Jun 2017 03:33
by sinys
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.
Re: Problem with DBMS_OUTPUT.GET_LINES
Posted: Tue 20 Jun 2017 06:55
by sinys
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.