Oracle

│ English (en) │ español (es) │ français (fr) │ 日本語 (ja) │

Databases portal

References: General info Libraries Field types Controls FAQ SQL how-to Working With TSQLQuery In-memory database applications Tutorials/practical articles: Overview 0 - Database set-up 1 - Getting started 2 - Editing 3 - Queries 4 - Data modules SQLdb Programming Reference Databases Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

Low level Oracle server interface

The low level Oracle server interface exists of one unit, oraoci, which is a straight translation of the Oracle interface header files.

There are 2 example programs:

• oraclew contains some utility routines for the oracle interface, for easier management of result sets. Needs the classes unit from the FCL.
• test01 a simple test program to demonstrate the interface.

Direct access to Oracle

You can directly connect Lazarus and Oracle by using Oracle Data Access Components (ODAC). It is a library of components that provides native connectivity to Oracle from Lazarus (and Free Pascal) on Windows, Mac OS X, iOS, Android, Linux, and FreeBSD for both 32-bit and 64-bit platforms. The ODAC library is designed to help programmers develop faster and more native Oracle database applications.

This Lazarus component is free to download.

OOP access to Oracle

Built over the low-level interface, the SQLDB framework supplied with FPC supports accessing Oracle (using TOracleConnection); see also Lazarus_Database_Overview#Lazarus_and_Oracle

Lazarus also has a component: TOracleConnection

• Hostname: as with other sqldb connectors, use hostname or IP address. Leave empty if you use a TNSNAMES.ORA net service name in DatabaseName
• Username/password: same as with other sqldb connectors
• DatabaseName:
  • instance/SID of the Oracle server you want to connect to or
  • net service name in a TNSNAMES.ORA file

  Note: Released FPC/Lazarus x64 versions on Windows do not include an Oracle connector. If you enable it and test successfully, please submit a patch with the changes so it can be included

Troubleshooting

Client and server character sets

To get info about what character set/NLS settings are active, you can run the following program.

Please don't forget to fill out correct server name, username, password and database name before compiling.

program oracharset;

{ Shows client and server character set/NLS info}
{ PLEASE EDIT PASSWORDS ETC BELOW. }

{$mode objfpc}{$H+}

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

var
  Col: integer;
  Conn: TOracleConnection;
  Tran: TSQLTransaction;
  Q: TSQLQuery;
begin
  Conn := TOracleConnection.Create(nil);
  Tran := TSQLTransaction.Create(nil);
  Q := TSQLQuery.Create(nil);
  try
    // * EDIT IDENTIFYING INFO AS NEEDED*
    Conn.HostName := '';
    Conn.UserName := 'system';
    Conn.Password := '';
    Conn.DatabaseName := 'XE';
    // *END IDENTIFIYING INFO*
    Conn.Transaction := Tran;
    Q.DataBase := Conn;
    Conn.Open;
    Tran.Active := true;

    writeln('Server character set info:');
    Q.SQL.Text := 'SELECT value$ FROM sys.props$ WHERE name like ''NLS_%'' ';
    Q.Open;
    Q.First;
    while not (Q.EOF) do
    begin
      writeln('*****************');
      for Col := 0 to Q.Fields.Count - 1 do
      begin
        try
          writeln(Q.Fields[Col].DisplayLabel + ':');
          writeln(Q.Fields[Col].AsString);
        except
          writeln('Error retrieving field ', Col);
        end;
      end;
      Q.Next;
    end;
    Q.Close;

    writeln('');
    writeln('Client character set info:');
    Q.SQL.Text := 'SELECT * FROM NLS_SESSION_PARAMETERS ';
    Q.Open;
    Q.First;
    while not (Q.EOF) do
    begin
      writeln('*****************');
      for Col := 0 to Q.Fields.Count - 1 do
      begin
        try
          writeln(Q.Fields[Col].DisplayLabel + ':');
          writeln(Q.Fields[Col].AsString);
        except
          writeln('Error retrieving field ', Col);
        end;
      end;
      Q.Next;
    end;
    Q.Close;
    // *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.

ORA-00911 : invalid character

If you see this error message, you might want to try

• removing a trailing semicolon - ; - if you have it in a SELECT statement
• adding a trailing semicolon (e.g. in CALL or EXECUTE statements)

See this thread which applies to .Net but may apply to SQLDB as well: [1]

Go back to Packages List