Devart Forums

jfudickar

Hi Maxim,

any news on this topic?

Regards
Jens

jfudickar

Hi,

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

Regards
Jens

jfudickar

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

Regards
Jens

jfudickar

The problem also exists with the latest release 12.0.1

So any ideas?

jfudickar

Any help/news?

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

Hi Maxim,
did you have any news about this issue?
Regards
Jens

jfudickar

Hi,

a customer shows me the following error, which is easy to reproduce.
When you include an invalid IFILE definition in the tnsnames.ora (files or folder not valid)
IFILE=Q:\ORANT\NET80\ADMIN\VKD\tnsnames.ora
IFILE=Q:\ORANT\NET80\ADMIN\EDWH\tnsnames.ora

the following exception is raised then the GetServerList function is used:

Code: Select all

exception number   : 1
exception class    : EStringListError
exception message  : List index out of bounds (4596).

main thread ($2440):
00535494 +018 OraToolStatementRepository.exe System.Classes                            TStringList.Get
011ee842 +196 OraToolStatementRepository.exe OraServices                               TOraServerEnumerator.GetServerList
01795999 +03d OraToolStatementRepository.exe JvDBLogonDialogOdac               275  +4 TJvDBOdacLogonDialog.FillDatabaseComboBoxValues

Can this be fixed?

Regards
Jens

jfudickar

Hi Both of you:-)

Yes this solves the problem.

Would be nice if such classes are documented in the helpfile.

Regards
Jens

jfudickar

Good to see that the problem is realy solved now.
And that someone was able to reproduce it.

I was really frustrated that I was not able to build a sample which shows it.

But my problem is solved now also.

Regards
Jens

jfudickar

Hi,

I'm new on ODAC 10.

Now my old code is no longer working because the TOraServerEnumerator is unknown.

In ODAC 9 it was in the OraServices.pas.

There is no reference in the help files and I have no source code.

Any help would be welcome.

Kind regards
Jens

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

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

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

jfudickar

But why is it working in SQL*Plus?

Devart Forums

Search found 202 matches

Re: Problem with DBMS_OUTPUT.GET_LINES

Re: Problem with DBMS_OUTPUT.GET_LINES

Re: Problem with DBMS_OUTPUT.GET_LINES

Re: Problem with DBMS_OUTPUT.GET_LINES

Re: Problem with DBMS_OUTPUT.GET_LINES

Problem with DBMS_OUTPUT.GET_LINES

Re: Exception in TOraServerEnumerator.GetServerList with Invalid IFILE definition in tnsnames.ora

Exception in TOraServerEnumerator.GetServerList with Invalid IFILE definition in tnsnames.ora

Re: After updating to ODAC 10 where do I find TOraServerEnumerator

Re: Problem with DBMS_OUTPUT.GET_LINES

After updating to ODAC 10 where do I find TOraServerEnumerator

Re: Problem with DBMS_OUTPUT.GET_LINES

Re: Problem with DBMS_OUTPUT.GET_LINES

Re: Problem with DBMS_OUTPUT.GET_LINES

Re: Problem with DBMS_OUTPUT.GET_LINES