Not at all, I shall update my notes accordingly.

FI: SQL*Plus the oracle query tool will execute SQL either by
terminating with a ; or by a / on the next line. Both methods are in
common use (sometimes even in the same script).

Paul

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: 03 February 2003 09:42
To: IB-Conversions@yahoogroups.com
Subject: RE: [IB-Conversions] How to do basic Oracle-to-FB conversion


At 09:13 AM 3/02/2003 +0000, Paul Morris wrote:

A reasonably good summary. I hope you don't mind if I annotate it a
little!

>PL/SQL developer from allround automations
>http://www.allroundautomations.nl/index.html has facilities for
>exporting both DDL and data from oracle databases. The data can be
>exported as insert statements, which should work unchanged against
>firebird. The DDl will however require some tweaking, see below for

some

>of the major differences:
>
>Table Definitions
>-----------------
>� Change VARCHAR2 to VARCHAR
>� Change NUMBER(n,[n]) to NUMERIC(n,[n])

NUMERIC(s,p) where s is scale and p is precision. Precision is not
optional. See also DECIMAL(s,p).

>� Change NUMBER to FLOAT

FLOAT for single precision, DOUBLE PRECISION for...yup.

>� Change CLOB to either BLOB or VARCHAR (maximum size for VARCHAR

is

>32k)

Change CLOB to BLOB SUB_TYPE 1. BLOB without a sub_type defaults to
BLOB
SUB_TYPE 0, which is untyped. Sub_type 1 for character blobs.

>� Delete TABLESPACE clause
>� Delete STORAGE clause
>� Ensure all columns appearing as PRIMARY KEY or UNIQUE
>constraints have NOT NULL constraint.

And don't define a column or column set as both PRIMARY KEY and UNIQUE.

>� Remove any schema qualification from table names (e.g.
>IBERIS.ACKCODES becomes ACKCODES)
>� Change any table or column names that are Interbase reserved
>words:

For IB 6, 7 and for Firebird: Change or double-quote any table or
column
names that are InterBase reserved words. SQL-92 quoted identifiers are
supported.

>� Remove any table or column COMMENT ON definitions;

Enclose DDL comments between /* */ pairs.

>� Use ; instead of / to execute script.

Not sure what this means. Each statement in a script must be
terminated. The default terminator is a semicolon, but it can be any
string, provided it is declared using a SET TERM statement.
Within PSQL declarations, it is mandatory to SET TERM to something other

than a semicolon, because PSQL requires statements within procedure and
trigger declarations to be terminated with semicolons.

>Sequences
>---------
>These are known as generators under Interbase:
>CREATE GENERATOR .....
>GEN_ID(generator_name,n) - this increments generator_name by n and
>returns the new value;
>The following SQL will create a script to create a generator for every
>primary key
>select DISTINCT 'CREATE GENERATOR '||RTRIM(i.rdb$field_name)||'_SEQ;;'
>from rdb$relation_constraints RC,
>rdb$index_segments i
>where rc.rdb$index_name = i.rdb$index_name
>and rc.rdb$relation_name not like 'RDB%'
>and rc.rdb$constraint_TYPE = 'PRIMARY KEY';

Cute.
Note that RTRIM is not an internal function: it requires that the
IB_UDF
user-defined function library be present in the UDF directory and that
the
function be declared to the database.

cheers,
Helen

>Triggers
>--------
>Remove OR REPLACE from definition
>Trigger definition format changes from
>
>CREATE OR REPLACE TRIGGER <trigger_name>
>BEFORE INSERT ON <table name> FOR EACH ROW
>to
>CREATE TRIGGER <trigger_name> FOR <table_name>
>BEFORE INSERT AS
>Change :OLD and :NEW to OLD and NEW respectively.
>
>
>Hope this helps
>
>Paul
>
>-----Original Message-----
>From: Todd Jonker [mailto:tvj@...]
>Sent: 24 January 2003 15:30
>To: IB-Conversions@yahoogroups.com
>Subject: Re: [IB-Conversions] How to do basic Oracle-to-FB conversion
>
>
>Claudio, I guess I wasn't as clear as I thought I was. Please forgive
>my
>lax terminology, I am not a DBA, just a developer with enough knowledge
>to
>be dangerous. :-)
>
>We need to clone both the table structures (= metadata?) and the data.
>
>The vendor of this legacy product is resistant to giving us (a
>third-party
>developer) the DDL, so we have no scripts to process. If they do give
>us
>the DDL, we will still need to get the data across somehow, and it's
>terribly unclear to me how to do that via an intermediate file format.
>So
>copying DB->DB directly seems to be the easiest route. (Note that this
>should be a one-time process.)
>
>The HSQL tool used JDBC to read the source database's metadata, create
>similar tables in the target, and then copy all the records over.
>That's
>what I'm looking for now. I will probably try the HSQL tool again, but
>was
>looking for something a bit more mature and reliable.
>
>Again, there's no stored procedures or other programmatic SQL, so I
>think
>there's not much more to worry about that the metadata and data.
>
>Thanks,
>.T.
>
>
>On 1/24/03 3:56 AM, cvalde@... wrote:
>
> > ----Original Message----
> > From: Todd <tvj@...> [mailto:tvj@...]
> > Sent: Jueves 23 de Enero de 2003 17:03
> >>
> >> This is all well and good, except for the fact that I need to
> >> bootstrap the process by cloning the oracle schema (and data) into
> >> Firebird.
> >>
> >> Can anyone suggest a tool that can do this? A year or so ago, I

used

> >> a simple migration tool that was part of the HypersonicSQL
> >> distribution, but it was a bit clumsy and had a hard time copying
> >> tables in the proper order. I'm hoping that Firebird/InterBase
> >> devlopers can suggest something appropriate.
> >
> > Do you want to clone only metadata?
> > Well, www.datanamic.com has a tool that can reverse engineer almost
>any
> > script to the format used by that CASE tool. Then, the CASE can
>generate a
> > script for other db engine.
> > For USD 149 + 41, it's not bad. I've used the two tools for over a
>year for
> > my needs and it's enough. I don't need the complexity of ER/Studio

and

>in
> > this way, I don't need a live connection to a db. I can reverse
>engineer any
> > supported engine's script without having that engine installed.

Hence,

> > someone could give me an Oracle script (I don't have Oracle), I
>convert it
> > with ImportER Scripts and then load the resulting file in Dezign.
>Finally, I
> > tell Dezign to generate the script for FB. Done. (I'm not associated
>with
> > Datanamic; I'm only a happy customer.)
> >
> > C.
>
>
>To unsubscribe from this group, send an email to:
>IB-Conversions-unsubscribe@egroups.com
>
>
>
>Your use of Yahoo! Groups is subject to
>http://docs.yahoo.com/info/terms/
>
>
>
>
>
>
>This mail has been scanned for viruses.
>
>
>
>
>
>
>
>
>******************************
>Confidentiality Notice
>******************************
>The information contained in this E-mail, and any attachments, is

intended

>for the named recipients only. It may contain confidential and/or
>privileged information.
>
>If you are not the intended recipient, you must not copy, distribute,

or

>take any action in reliance on it. Any views expressed do not

necessarily

>reflect the views of the company.
>
>If you receive this E-mail by mistake, please advise the sender by

using

>the reply facility in your E-mail software and then delete it.
>
>Please note that whilst the company takes steps to protect against

viruses

>it cannot accept liability for any virus accidentally transmitted.
>
>Aralia Systems Ltd, 13 North Parade, Horsham, West Sussex
>RH12 2BT, United Kingdom. Tel +44 (0)1403 240303
>
>
>
>
>
>To unsubscribe from this group, send an email to:
>IB-Conversions-unsubscribe@egroups.com
>
>
>
>Your use of Yahoo! Groups is subject to

http://docs.yahoo.com/info/terms/



To unsubscribe from this group, send an email to:
IB-Conversions-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/






This mail has been scanned for viruses.






******************************
Confidentiality Notice
******************************
The information contained in this E-mail, and any attachments, is intended for the named recipients only. It may contain confidential and/or privileged information.

If you are not the intended recipient, you must not copy, distribute, or take any action in reliance on it. Any views expressed do not necessarily reflect the views of the company.

If you receive this E-mail by mistake, please advise the sender by using the reply facility in your E-mail software and then delete it.

Please note that whilst the company takes steps to protect against viruses it cannot accept liability for any virus accidentally transmitted.

Aralia Systems Ltd, 13 North Parade, Horsham, West Sussex
RH12 2BT, United Kingdom. Tel +44 (0)1403 240303