Difference between revisions of "Oracle"
From Lazarus wiki
Jump to navigationJump to searchLine 20: | Line 20: | ||
<syntaxhighlight> | <syntaxhighlight> | ||
program oracharset; | program oracharset; | ||
+ | |||
{ Shows client and server character set/NLS info} | { Shows client and server character set/NLS info} | ||
− | + | { PLEASE EDIT PASSWORDS ETC BELOW. } | |
+ | |||
{$mode objfpc}{$H+} | {$mode objfpc}{$H+} | ||
− | + | ||
− | uses | + | uses {$IFDEF UNIX} {$IFDEF UseCThreads} |
− | + | cthreads, {$ENDIF} {$ENDIF} | |
− | cthreads, | + | Classes, |
− | + | SysUtils, | |
− | Classes, | ||
sqldb, | sqldb, | ||
oracleconnection; | oracleconnection; | ||
− | + | ||
var | var | ||
Col: integer; | Col: integer; | ||
Line 38: | Line 39: | ||
Q: TSQLQuery; | Q: TSQLQuery; | ||
begin | begin | ||
− | Conn:=TOracleConnection. | + | Conn := TOracleConnection.Create(nil); |
− | Tran:=TSQLTransaction. | + | Tran := TSQLTransaction.Create(nil); |
− | Q:=TSQLQuery.Create(nil); | + | Q := TSQLQuery.Create(nil); |
try | try | ||
− | // * | + | // * EDIT IDENTIFYING INFO AS NEEDED* |
− | Conn.HostName:=''; | + | Conn.HostName := ''; |
− | Conn.UserName:='system'; | + | Conn.UserName := 'system'; |
− | Conn.Password:=''; | + | Conn.Password := ''; |
− | Conn.DatabaseName:='XE'; | + | Conn.DatabaseName := 'XE'; |
// *END IDENTIFIYING INFO* | // *END IDENTIFIYING INFO* | ||
− | Conn.Transaction:=Tran; | + | Conn.Transaction := Tran; |
− | Q.DataBase:=Conn; | + | 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.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; | + | Q.Close; |
− | + | writeln(''); | |
− | + | writeln('Client character set info:'); | |
− | Q.SQL.Text:='SELECT * FROM NLS_SESSION_PARAMETERS '; | + | 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; | Q.Close; | ||
// *END EXAMPLE BUG TESTING CODE* | // *END EXAMPLE BUG TESTING CODE* |
Revision as of 12:37, 20 July 2013
Introduction to Oracle server interface
The 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.
OOP access to Oracle
The SQLDB framework supplied with FPC supports accessing Oracle (using TOracleConnection); see Lazarus_Database_Overview#Lazarus_and_Oracle
Note: Released Lazarus x64 versions on Windows does not include an Oracle connector. If you enable it and test succesfully, 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