OracleConnection + instantclient = ORA-12541
lazarus / 16-Databases

----------------------------------------------------------------------
GolfCat · 2014-03-13 14:23

When using OracleConnection + instantclient I get ORA-12541: TNS:no
listener
When connecting to Oracle XE I have no problem.
I can connect with instantclient if I use zeoslib, but not with
OracleConnection.
Any setting that I have missed?

----------------------------------------------------------------------
BigChimp · 2014-03-13 14:26

Your post is not quite clear... do you have
Oracle XE => sqldb connection works ok
an Oracle server (which version)=>sqldb gives TNS no listener error?

Have you tried connecting with other tools (e.g. sqlworkbench/j,
sql/plus) to the server? Does that work?

Please specify Laz version, operating system - I guess 1.2, Windows 7?

----------------------------------------------------------------------
GolfCat · 2014-03-13 17:08

I usually connect with SQL Developer to the server and that has never
been any problem.
At home I have Oracle XE and no problem with Oracleconnection or
zeosconnection. At work I have a bigger server (64 bit) on Linux, but
SQL Developer works fine. I can connect with zeosconnection at work,
but get some strange errors when fetching data. with Oracleconnection
I cannot connect at all.
Lazarus version is 1.2.0 and 32-bit running on Win 7 (64-bit).
I have some Delphi 7 applications working like a charm with
zeosconnection and  instantclient.

----------------------------------------------------------------------
BigChimp · 2014-03-13 17:26

Mmm, nothing springs to mind...

----------------------------------------------------------------------
Edson · 2014-03-13 18:59

If you can connect locally with Oracleconnection, but not at a remote
Server, maybe you have a bad configuration for the connection.

Have you tested with the sqlplus?

----------------------------------------------------------------------
GolfCat · 2014-03-13 21:44

I cant see what good sqlplus would do?
I know instant client is OK, because my Delphi programs are working
OK.
It is the Oracleconnection that fails on a remote server. Interesting
would be to test another program that uses Oracleconnection over
instant client. I have assumed that I should configure
Oracleconnection the same way as I configure ZConnection for a remote
server, but I could be wrong, because I’ve never succeeded.

----------------------------------------------------------------------
sin_dragan · 2014-03-13 22:54

What is the version of Oracle server?

I use oraociei11.dll to connect to Oracle server, eventually you can
install oracle client (with admin options) and then try.

----------------------------------------------------------------------
BigChimp · 2014-03-14 06:19

Quote from: GolfCat on March 13, 2014, 10:44:49 pmI know instant
client is OK, because my Delphi programs are working OK.
It is the Oracleconnection that fails on a remote server. Interesting
would be to test another program that uses Oracleconnection over
instant client. I have assumed that I should configure
Oracleconnection the same way as I configure ZConnection for a remote
server, but I could be wrong, because I’ve never succeeded.

This code works for me, FPC x86, Windows, Oracle 12 instant client
with a remote server (I think Oracle 10):
Code: [Select]program oracletest;

{$mode objfpc}{$H+}

uses
  {$IFDEF UNIX}{$IFDEF UseCThreads}
  cthreads,
  {$ENDIF}{$ENDIF}
  Classes, sysutils,
  sqldb,
  oracleconnection;

var
  Conn: TOracleConnection;
  Tran: TSQLTransaction;
  Q: TSQLQuery;
begin
  Conn:=TOracleConnection.create(nil);
  Tran:=TSQLTransaction.create(nil);
  Q:=TSQLQuery.Create(nil);
  try
    // *REMOVE IDENTIFYING INFO AND EDIT AS NEEDED*
    Conn.HostName:='192.168.1.2'; //server name/ip address
    Conn.UserName:='scott';
    Conn.Password:='tiger';
    Conn.DatabaseName:='orcl'; //that's the instance, I think
    // *END IDENTIFIYING INFO*
    Conn.Transaction:=Tran;
    Q.DataBase:=Conn;
    Conn.Open;
    Tran.StartTransaction;
    Q.SQL.Text:='select ''working'' from dual ';
    Q.Open;
    Q.Last; //force recordcount update
    writeln('recordcount: '+inttostr(q.RecordCount));
    Q.Close;
    Tran.Commit;

    // *END EXAMPLE BUG TESTING CODE*
    Conn.Close;
  finally
    Q.Free;
    Tran.Free;
    Conn.Free;
  end;
  writeln('Program complete. Press a key to continue.');
  readln;
end.

----------------------------------------------------------------------
BigChimp · 2014-03-14 06:27

Updated
http://wiki.lazarus.freepascal.org/oracle#OOP_access_to_Oracle

Please feel free to update this page as needed...

----------------------------------------------------------------------
BigChimp · 2014-03-17 12:10

Just checked and when specifying the wrong SID in the database
property, I got
Code: [Select]EOraDatabaseError/ORA-12514: TNS:listener does not
currently know of service requested in connect descriptor

----------------------------------------------------------------------
Edson · 2014-03-17 15:38

@BigChimp

Do you really have a connection to Oracle, working OK?

I can connect, but I have experimented the problem of memory:

http://forum.lazarus.freepascal.org/index.php/topic,22590.msg133984.ht
ml
http://forum.lazarus.freepascal.org/index.php/topic,21561.msg127507.ht
ml

If it is related to the bug:
http://bugs.freepascal.org/view.php?id=24723, I understand that it's
solved. But it still fail for me with FPC 2.6.2.

Until now, I have not found a solution. So I'm given up for
"OracleConnection".

----------------------------------------------------------------------
BigChimp · 2014-03-17 15:44

Quote from: Edson on March 17, 2014, 04:38:37 pmDo you really have a
connection to Oracle, working OK?
Yes, but the FPC trunk test suite now crashes with my server. Haven't
had time/interest yet to try with another server.

Quote from: Edson on March 17, 2014, 04:38:37 pmIf it is related to
the bug: http://bugs.freepascal.org/view.php?id=24723, I understand
that it's solved. But it still fail for me with FPC 2.6.2.
That bug is not solved.
Looking there, you see:
Resolution   unable to reproduce

That means that it was not possible to see on our (developers')
systems the same problem that the bug reporter saw. It does not mean
the problem was fixed by changing code etc.
If you have the problem described in the bug, please feel free to
reopen the bug, but we must have a complete, compilable - including
the required table definitions, if any -  test program that shows the
bug (e.g. the oratest.lpr in the bug report).

By the way, please open a new forum thread for a new topic...

----------------------------------------------------------------------
Edson · 2014-03-17 16:01

So, it's some kind of bad luck for some systems with Oracle.

Uhmm. I've seen the bug like "resolved". Mantis is still some confused
for me.

QuoteBy the way, please open a new forum thread for a new topic...

I've exposed this problem here, because none of the mentioned  posts
have been answered at the end.

----------------------------------------------------------------------
BigChimp · 2014-03-17 16:12

Yes, it's also bad luck for the users and developers that spent time
trying to figure out the problem but were stuck because the problem
was not reproducible.

Bug status RESOLVED means that the bug will not be looked at any more.
You see the reason for why it was marked resolved in the Resolution
field.
Can't make it any clearer than that - it's just bug tracker work flow
terminology...

Sorry Edson, but I think the reason that at least one thread was not
answered was because nobody provided a compilable, complete example
program that demonstrated the problem.
(E.g. as asked here http://forum.lazarus.freepascal.org/index.php/topi
c,22590.msg133615.html#msg133615)

Note also that the behaviour may differ depending on operating system,
oracle client library version, server version, memory, etc. so it may
be hard to reproduce.

Please reopen the bug, specify your environment (as much detail as you
can e.g. at least what I mentioned above), and attach a compilable
example program [1] and we'll try and have a look.
Alternatively, open a new forum thread instead of reopening the bug
report.

[1] see e.g. http://wiki.lazarus.freepascal.org/Database_bug_reporting
and related pages

----------------------------------------------------------------------
Edson · 2014-03-17 18:44

OK. First I need to prepare a new enviroment of test for that.

----------------------------------------------------------------------
GolfCat · 2014-03-18 21:32

I have checked and when I supply the correct hostname I get ORA-12514,
when I set localhost as hostname I get ORA-12541. This is for
OracleConnection + instantclient. When using zeosconnection and
instantclient I can use the correct hostname or localhost as hostname
and connect OK. Strange that you guys get different results.

----------------------------------------------------------------------
BigChimp · 2014-03-21 08:36

If you get 12514 that matches my incorrect sid case. Not too much of
an Oracle guru but perhaps tnsnames.ora is interfering with a service
definition here?

Perhaps post the info/properties you use for zeos connection as well
as Oracleconnection could be useful?

----------------------------------------------------------------------
GolfCat · 2014-03-24 14:25

Code: [Select]procedure TForm1.btnOraClick(Sender: TObject);
begin
  OracleConnection1.DatabaseName:='UTV02';
  OracleConnection1.HostName:='utv-oradb234.utv.mycompany.se';
  OracleConnection1.UserName:='sbo';
  OracleConnection1.Password:='sbo';
  OracleConnection1.Connected:=True;
  if OracleConnection1.Connected then
  begin
    StatusBar1.SimpleText := 'Connected';
  end
  else begin
    StatusBar1.SimpleText := 'No Connection';
  end;
end;
This fails with ORA-12514
Code: [Select]procedure TForm1.btnZeosClick(Sender: TObject);
begin
  ZConnection1.Protocol := 'oracle';
  ZConnection1.Database := 'UTV02';
  ZConnection1.HostName := 'utv-oradb234.utv.mycompany.se'
  ZConnection1.User := 'sbo';
  ZConnection1.Password := 'sbo';
  ZConnection1.Connected := True;
  if ZConnection1.Connected then
  begin
    StatusBar1.SimpleText := 'Connected';
  end
  else begin
    StatusBar1.SimpleText := 'No Connection';
  end;
end;        
Here I get 'Connected'
All other properties are default.
In tnsnames.ora it looks like this:
Code: [Select]UTV02=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=utv-oradb234.utv.mycompany.se)
      (PORT=1523)
    )
    (CONNECT_DATA=
      (SERVICE_NAME=utv02.utv.mycompany.se)
    )
  )

----------------------------------------------------------------------
BigChimp · 2014-03-24 15:46

I vaguely remember having once used tnsnames.ora with FPC.... However,
too many scare memories for somebody who just wanted to test various
dbs...

Code: [Select]OracleConnection1.DatabaseName:='UTV02';
Perhaps try with the service name instead of the tnsnames entry:
Code: [Select]OracleConnection1.DatabaseName:='utv02.utv.mycompany.se'
also then you'll need to specify the port as it's not default 1521

----------------------------------------------------------------------
BigChimp · 2014-03-24 15:53

To specify port 1523 on host hostname you'd probably do something like
Code: [Select]OracleConnection1.hostname:='hostname:1523';
 but that's Oracle lower level stuff (OCI) which I know nothing
about...

----------------------------------------------------------------------
GolfCat · 2014-03-25 07:20

Tested all of this today and got ORA-12154
Searched the forum and found the solution:
I completely removed the hostname and IT FUNCTIONED.

----------------------------------------------------------------------
BigChimp · 2014-03-25 10:03

Great news!

I modified
http://wiki.lazarus.freepascal.org/oracle#OOP_access_to_Oracle
hope it's correct - corrections welcome.