Difference between revisions of "Oracle"

From Lazarus wiki
Jump to navigationJump to search
(Add character set test program)
Line 31: Line 31:
 
   sqldb,
 
   sqldb,
 
   oracleconnection;
 
   oracleconnection;
 
{
 
* ADD THE SQL NEEDED TO CREATE THE DATABASE HERE *
 
No DDL needed; can connect to any db and use dual
 
}
 
 
   
 
   
 
var
 
var
Line 77: Line 72:
 
end;
 
end;
 
     Q.Close;
 
     Q.Close;
+
 
 +
writeln('');
 
writeln('Client character set info:');
 
writeln('Client character set info:');
 
     Q.SQL.Text:='SELECT * FROM NLS_SESSION_PARAMETERS ';
 
     Q.SQL.Text:='SELECT * FROM NLS_SESSION_PARAMETERS ';
Line 108: Line 104:
 
end.
 
end.
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
 
=== ORA-00911 : invalid character ===
 
=== ORA-00911 : invalid character ===
 
If you see this error message, you might want to try
 
If you see this error message, you might want to try

Revision as of 11:35, 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

Light bulb  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}
 
{$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
    // *REMOVE IDENTIFYING INFO AND EDIT 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