Problem with DBMS_OUTPUT.GET_LINES

[jfudickar]

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        	buch@ora19
   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]

Any help/news?

[jfudickar]

The problem also exists with the latest release 12.0.1

So any ideas?

[MaximG]

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

[MaximG]

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]

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

Regards
Jens

[jfudickar]

Hi,

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

Regards
Jens

[MaximG]

Hi Jens!

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

[jfudickar]

Hi Maxim,

any news on this topic?

Regards
Jens