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)
Post Reply
jfudickar
Posts: 202
Joined: Fri 10 Mar 2006 13:03
Location: Oberursel / Germany

Problem with DBMS_OUTPUT.GET_LINES

Post by jfudickar » Tue 07 Sep 2021 20:47

Hi,

I'm using the following code to get data from DBMS_OUTPUT for a long time:

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;
It's working with Oracle 12 without any problem, but with Oracle 18/19 I got an exception when executing the procedure.

The exception raised in the line StoredProc.Execute" and is:
'Unknown data type of parameter LINES'.
Environment Details:

Code: Select all

Odac-Version   	11.4.4
.
OCI-Lite       	False
OCI-Events     	True
OCI-Threaded   	True
OCI-Mutexed    	True
OCI-Shared     	False
OCI-Unicode    	True
.
.
Default Oracle Home 	OraClient19Home2
.
Oracle Home Count	3
Oracle Home Name        [1]	OraClient12Home1
   Path                 [1]	F:\Oracle\client\product\12.2.0\client_2
   TNS Path             [1]
   OCI-DLL              [1]
   OCI-Client-DLL       [1]
   OCI-Version          [1]
   OCI-CallStyle        [1]	None
   Possible CallStyles  [1]
Oracle Home Name        [2]	OraClient19Home1
   Path                 [2]	G:\Download\oracle\19c\WINDOWS.X64_193000_client_home
   TNS Path             [2]
   OCI-DLL              [2]
   OCI-Client-DLL       [2]
   OCI-Version          [2]
   OCI-CallStyle        [2]	None
   Possible CallStyles  [2]
Oracle Home Name        [3]	OraClient19Home2
   Path                 [3]	F:\Oracle\client\product\19.3\64bit
   TNS Path             [3]
   OCI-DLL              [3]	F:\Oracle\client\product\19.3\64bit\BIN\oci.dll
   OCI-Client-DLL       [3]	F:\Oracle\client\product\19.3\64bit\BIN\oraclient19.dll
   OCI-Version          [3]	19.3.0.0.0
   OCI-CallStyle        [3]	OCI80
   Possible CallStyles  [3]	OCI73; OCI80
.
Session 1	MainOracleSession (OratoolMainForm)
   Connect        	[email protected]
   HomeName
   Connected      	True
   OracleVersion  	19.0.0.0.0
   InTransaction  	False
   AutoCommit     	True
   Pooling        	False
   Schema         	BUCH
   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.EnableOraTimestamp	True
   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
Any idea what happens?

Regards
Jens

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

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by jfudickar » Sun 12 Sep 2021 13:47

Any help/news?

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

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by jfudickar » Tue 14 Sep 2021 20:27

The problem also exists with the latest release 12.0.1

So any ideas?

MaximG
Devart Team
Posts: 1816
Joined: Mon 06 Jul 2015 11:34

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by MaximG » Tue 21 Sep 2021 12:13

Thank you for the information. We have reproduced the issue and will investigate its origin. We will inform you about the results shortly.

MaximG
Devart Team
Posts: 1816
Joined: Mon 06 Jul 2015 11:34

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by MaximG » Fri 01 Oct 2021 14:07

We have identified the reason for the behavior you are describing! Please note that lower versions of Oracle return SQLT_CHR as the parameter type, while older versions return SQLT_TAB. We will try to make changes to ODAC in such a way as to maintain the familiar behavior when accessing any version of the Oracle server. Currently, as a workaround, you can explicitly specify the type ftString for the parameter.

Should you have any questions, do not hesitate to ask!

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

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by jfudickar » Sun 17 Oct 2021 14:55

Hi Maxim,
this solves the problem.
Hopefully you will find a better/more correct solution.

Regards
Jens

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

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by jfudickar » Sat 29 Jan 2022 15:52

Hi,

any news on that?
Would it help to implement version specific sql's?

Regards
Jens

MaximG
Devart Team
Posts: 1816
Joined: Mon 06 Jul 2015 11:34

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by MaximG » Fri 04 Feb 2022 11:08

Hi Jens!

Thanks for following up. We are currently investigating various ways of solving this issue. We will keep you posted.

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

Re: Problem with DBMS_OUTPUT.GET_LINES

Post by jfudickar » Tue 14 Jun 2022 20:03

Hi Maxim,

any news on this topic?

Regards
Jens

Post Reply