Subject RE: [Firebird-general] OT Firebird and Oracle
Author Thomas Steinmaurer
Hi Ian,

> Just some OT questions regarding Oracle and Firebird. I have been
> asked by a (large) client if I can produce our application running
> on Oracle. It currently runs on Firebird. My initial thoughts are
> that it should be possible, but a number of things cause me concern.
> 1. Selectable Stored Procedures (SSP)
> We use SSPs in a number of places. Does oracle have similar
> functionality, if so what is it.

AFAIK, Oracle 9i introduced something to execute a stored
procedure that returns a result set, but the usage isn't
that easy, as it is in Firebird with select * from myproc.

> 2. Before and After Triggers.
> I believe oracle has these, but would appreciate any
> differences/pitfalls to watch out for.

Firebird and Oracle triggers are somewhat similar. You have
to be aware that Oracle triggers can be row or statement-
level triggers. If you use the FOR EACH ROW clause in the
create trigger, then it is a row-level trigger (like the
ones in Firebird). If you omit that clause, then it is a
statement-level trigger.

The difference is, how often a trigger fires, if e.g. one
UPDATE statement changes, let's say, 1000 records. A row
level trigger fires 1000 times, where as a statement-level
trigger only fires once.

Like Firebird 1.5, Oracle also allows multi-action triggers,
that means, one trigger for different DML operations.

Oracle has BEFORE and AFTER triggers, and since 8i system-
level triggers. Read here:

> 3. Development Tools.
> Do you need to buy any development tools for Oracle, or does it come
> with some. ie I use IBExpert mainly to develop Firebird Databases,
> is there similar functionality in the Oracle database or do I need
> to buy an add on.

Usually, the installed tools SQL+ and SQL Worksheet are far
away from being that capable as it is Database Workbench or
IBExpert, ... A product called TOAD is something comparable
for Oracle.

Thomas Steinmaurer
LogManager Serie - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database and MS SQL Server
Upscene Productions