Sign in Join
Wiki copytemporary

Oracle: what changed

Revision 0 to 1. Removed lines have a minus sign; added lines have a plus sign.

Title: Oracle

OldNewLine
1+## Low level Oracle server interface
2+
3+The low level Oracle server interface exists of one unit, **oraoci**, which is a straight translation of the Oracle interface header files.
4+
5+There are 2 example programs:
6+
7+- **oraclew** contains some utility routines for the oracle interface, for easier management of result sets. Needs the classes unit from the FCL.
8+- **test01** a simple test program to demonstrate the interface.
9+
10+## Direct access to Oracle
11+
12+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.
13+
14+This [Lazarus component](https://www.devart.com/odac/download.html) is free to download.
15+
16+## OOP access to Oracle
17+
18+Built over the low-level interface, the SQLDB framework supplied with FPC supports accessing Oracle (using TOracleConnection); see also [[Lazarus Database Overview|Lazarus_Database_Overview#Lazarus_and_Oracle]]
19+
20+Lazarus also has a component: [[TOracleConnection]]
21+
22+- Hostname: as with other sqldb connectors, use hostname or IP address. Leave empty if you use a TNSNAMES.ORA net service name in DatabaseName
23+- Username/password: same as with other sqldb connectors
24+- DatabaseName:
25+ - instance/SID of the Oracle server you want to connect to **or**
26+ - net service name in a TNSNAMES.ORA file
27+
28+> **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
29+
30+## Troubleshooting
31+
32+### Client and server character sets
33+
34+To get info about what character set/NLS settings are active, you can run the following program.
35+
36+Please don't forget to fill out correct server name, username, password and database name before compiling.
37+
38+``` pascal
39+program oracharset;
40+
41+{ Shows client and server character set/NLS info}
42+{ PLEASE EDIT PASSWORDS ETC BELOW. }
43+
44+{$mode objfpc}{$H+}
45+
46+uses {$IFDEF UNIX} {$IFDEF UseCThreads}
47+ cthreads, {$ENDIF} {$ENDIF}
48+ Classes,
49+ SysUtils,
50+ sqldb,
51+ oracleconnection;
52+
53+var
54+ Col: integer;
55+ Conn: TOracleConnection;
56+ Tran: TSQLTransaction;
57+ Q: TSQLQuery;
58+begin
59+ Conn := TOracleConnection.Create(nil);
60+ Tran := TSQLTransaction.Create(nil);
61+ Q := TSQLQuery.Create(nil);
62+ try
63+ // * EDIT IDENTIFYING INFO AS NEEDED*
64+ Conn.HostName := '';
65+ Conn.UserName := 'system';
66+ Conn.Password := '';
67+ Conn.DatabaseName := 'XE';
68+ // *END IDENTIFIYING INFO*
69+ Conn.Transaction := Tran;
70+ Q.DataBase := Conn;
71+ Conn.Open;
72+ Tran.Active := true;
73+
74+ writeln('Server character set info:');
75+ Q.SQL.Text := 'SELECT value$ FROM sys.props$ WHERE name like ''NLS_%'' ';
76+ Q.Open;
77+ Q.First;
78+ while not (Q.EOF) do
79+ begin
80+ writeln('*****************');
81+ for Col := 0 to Q.Fields.Count - 1 do
82+ begin
83+ try
84+ writeln(Q.Fields[Col].DisplayLabel + ':');
85+ writeln(Q.Fields[Col].AsString);
86+ except
87+ writeln('Error retrieving field ', Col);
88+ end;
89+ end;
90+ Q.Next;
91+ end;
92+ Q.Close;
93+
94+ writeln('');
95+ writeln('Client character set info:');
96+ Q.SQL.Text := 'SELECT * FROM NLS_SESSION_PARAMETERS ';
97+ Q.Open;
98+ Q.First;
99+ while not (Q.EOF) do
100+ begin
101+ writeln('*****************');
102+ for Col := 0 to Q.Fields.Count - 1 do
103+ begin
104+ try
105+ writeln(Q.Fields[Col].DisplayLabel + ':');
106+ writeln(Q.Fields[Col].AsString);
107+ except
108+ writeln('Error retrieving field ', Col);
109+ end;
110+ end;
111+ Q.Next;
112+ end;
113+ Q.Close;
114+ // *END EXAMPLE BUG TESTING CODE*
115+ Conn.Close;
116+ finally
117+ Q.Free;
118+ Tran.Free;
119+ Conn.Free;
120+ end;
121+ writeln('Program complete. Press a key to continue.');
122+ readln;
123+end.
124+```
125+
126+### ORA-00911 : invalid character
127+
128+If you see this error message, you might want to try
129+
130+- removing a trailing semicolon - ; - if you have it in a SELECT statement
131+- adding a trailing semicolon (e.g. in CALL or EXECUTE statements)
132+
133+See this thread which applies to .Net but may apply to SQLDB as well: [1](http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/58a27505-a3fb-4cb1-9063-3946b3f26acd)
134+
135+Go back to [[Packages List]]
136+
137+---
138+*From the Free Pascal wiki, page "Oracle", as it stood on 2020-02-23 after a change by Trev, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's [[Licensing]] page. If you have concerns about this material, [message the admin](/wiki-contact).*

Back to history