Re: migrating data from SQL 7.0 to ORACLE

From: Klaus Zeuch <KZeuchnospam_at_hotmail.com>
Date: Thu, 3 May 2001 13:54:22 +0200
Message-ID: <9crki0$a2q$1@papyrus.erlm.siemens.de>

Dan Phillips <dphillips_at_instantforms.com> schrieb in im Newsbeitrag: ZRVH6.233841$Z2.2470161_at_nnrp1.uunet.ca...
> We are migrating our data tables from SQL 7.0 to Oracle and have run into
 a
> slight setback. The conversion process tells us that we are not allowed
 to
> have more than 1 column of type LONG in a table. Is ther a possible work
> around for this restriction.

Use columns of type clob

>
> Also we had one column in the SQL 7.0 tables that was an identity
> column(auto-incrementing numeric column). We noticed that on the
 conversion
> to ORALCE the field was converted to a column of type NUMBER . Is there a
> method of creating a column that is and auto-incrementing column aside
 from
> ROWID?
>

CREATE SEQUENCE sequence_name
 INCREMENT BY 1
 START WITH 1
 MINVALUE 1

 MAXVALUE 999999999999999999999999999

 NOCYCLE
 NOORDER
NOCACHE; CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT
ON table_name
FOR EACH ROW
DECLARE
    v_nextid NUMBER;
BEGIN
  IF ( :new.id IS NULL ) THEN -- id = name of primary key column

      SELECT
           sequence_name.NEXTVAL
      INTO
           v_nextid
      FROM
        dual;

    :new.id := v_nextid;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END trigger_name;

Hth

Klaus Received on Thu May 03 2001 - 06:54:22 CDT