ODAC

Connecting to Oracle

Contents

Requirements

In order to connect to Oracle server you need the server itself running, ODAC installed and IDE running. If you have Oracle Client Software installed and want to use it, you need to know TNS alias name, login and password. If you do not wish to use OCI, you have to know host name or IP address, Oracle System Identifier (SID) or Oracle Servise Name, port, login and password.

General information

To establish a connection to server you have to provide some connection parameters to ODAC. This information is used by OraSession component to find the server and login with credentials of your account. The parameters are represented as connection string. You can compose the connection string manually or have ODAC construct it for you.

There are two ways to connect to server: with and without Oracle Client Interface. This is controlled by Direct property. It indicates whether the Oracle Client Interface will be used for connecting to server. By default Direct mode is disabled to preserve maximal functionality. Switch to Direct mode if you want to work in a system without Oracle Client Software installed.

Creating OraSession

Design time creation

The following assumes that you have IDE running, and you are currently focused on a form designer.

  1. Find the OraSession component on the ODAC tab of the component palette.
  2. Double-click the component. Notice that new object appears on the designer underneath the form. If this is first time you create the OraSession in this application it is named OraSession1.
  3. Click on the OraSession1 object and press F11 to focus on object's properties. Or double-click on OraSession1 to open the dialog.
  4. If you connect through OCI, in the Server property provide TNS alias of the server.
  5. If you use Direct mode, perform the following assignments:
    • set Direct to true
    • Set the Server property to a string that contains the host address of the database server, port number, and the Oracle System Identifier (SID) or Oracle Servise Name in the following format: Host:Port:SID or Host:Port:sn=ServiceName
  6. In the Username property specify your login. For example, scott.
  7. In the Password property specify your password. For example, tiger.

Run time creation

Same operations performed in runtime look as follows (note that you have to add DB, DBAccess, Ora units to the uses clause):

[Delphi OCI]


uses DB, DBAccess, Ora;
...
var
  OraSession1: TOraSession;
begin
  OraSession1 := TOraSession.Create(nil);
  OraSession1.Server := 'ORASERVER';
  OraSession1.Username := 'SCOTT';
  OraSession1.Password := 'TIGER';

[Delphi Direct]


uses DB, DBAccess, Ora;
...
var
  OraSession1: TOraSession;
begin
  OraSession1 := TOraSession.Create(nil);
  OraSession1.Options.Direct := True;
  OraSession1.Server := 'LOCALHOST:1521:ORASERVER';
  OraSession1.Username := 'SCOTT';
  OraSession1.Password := 'TIGER';

[C++ Builder OCI]


#pragma link "DBAccess"
#pragma link "Ora"
...
  TOraSession *OraSession1 = new TOraSession(NULL);
  OraSession1->Server = "ORASERVER";
  OraSession1->Username = "SCOT";
  OraSession1->Password = "TIGER";

[C++ Builder Direct]


#pragma link "DBAccess"
#pragma link "Ora"
...
  TOraSession *OraSession1 = new TOraSession(NULL);
  OraSession1->Options->Direct = True;
  OraSession1->Server = "LOCALHOST:1521:ORASERVER";
  OraSession1->Username = "SCOT";
  OraSession1->Password = "TIGER";

You can do this all in single assignment. It actually does not matter whether connection string is assigned directly or composed with particular properties. After you assign a value to ConnectionString property all other properties are populated with parsed values. So you can choose what is more convenient for you.

[Delphi OCI]


  OraSession1.ConnectString := 'SCOTT/[email protected]';

[Delphi Direct]


  OraSession1.ConnectString := 'SCOTT/[email protected]:1521:ORASERVER';

[C++ Builder OCI]


  OraSession1->ConnectString = "SCOTT/[email protected]';

[C++ Builder Direct]


  OraSession1->ConnectString = "SCOTT/[email protected]:1521:ORASERVER';

Opening connection

Opening a connection is as simple as that:

[Delphi]


  OraSession1.Connect;

[C++ Builder]


  OraSession1->Connect();

Of course, the OraSession1 must have valid connection string assigned earlier. When you call Connect, ODAC tries to find the host and connect to server. If any problem occurs it raises an exception with brief explanation on what is wrong. Finally, when connection is established, the Connect method returns and Connected property is changed to True.

In design time you can connect to server in few steps:

  1. In the dialog window provide necessary logon information.
  2. Click Connect button to establish connection.

Or you can simply change Connected property to True in Properties window to establish connection using current connection string.

Closing connection

To close a connection call its Disconnect method, or set its Connected property to False.

The following example summarizes aforementioned information and shows how to create, setup, open, use and then close the connection.

[Delphi]


var
  OraSession1: TOraSession;
begin
  OraSession1 := TOraSession.Create(nil);
  OraSession1.ConnectString := 'SCOTT/[email protected]';
  OraSession1.Connect;
  ShowMessage(OraSession1.OracleVersion);
  OraSession1.Disconnect;

[C++ Builder]

#pragma link "DBAccess"
#pragma link "Ora"
...
  TOraSession *OraSession1 = new TOraSession(NULL);
  OraSession1->Options->Direct = True;
  OraSession1->Server = "LOCALHOST:1521:ORASERVER";
  OraSession1->Username = "SCOT";
  OraSession1->Password = "TIGER";

Modifying connection

You can modify connection by changing properties of OraSession object. Keep in mind that while some of the properties can be altered freely, most of them close connection when new value is assigned. For example, if you change Server property, it gets closed immediately, and you have to reopen it manually.

Additional information

ODAC has wide set of features you can take advantage of. The following list enumerates some of them so you can explore the advanced techniques to achieve better performance, balance network load or enable additional capabilities.

Asynchronous connection opening Connection pooling (refer to MSDN documentation for information about connection pooling).

See Also

© 1997-2022 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback