Re: Help on Insert with Select

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 5 Sep 2001 07:38:58 +0100
Message-ID: <999671820.6508.1.nnrp-13.9e984b29@news.demon.co.uk>

Try changing the position of the type-constructor:

INSERT INTO advfirma_obj

    (Id, Navn,adresse_ty,Retskreds)
SELECT
    Id, Navn,
    addresse_ty(Adresse1, Adresse2, Postnr, NULL, Telefon,

NULL, Telefax, Email,Hjemmeside ), Retskreds
FROM Advfirma;

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Magnus S. Petersen wrote in message <3b952be1$1_at_news.olivant.fo>...


>Can anyone help me with this syntax which i believe is correct but i keep

>getting an error. I am redesigning some om my tables to use object types.

It


>should be very

>simple but

>it looks like i am doing something wrong.

>

>SQL> desc adresse_ty

> Name                                      Null?    Type

> ----------------------------------------- -------- ------

> ADRESSE1                                     VARCHAR2(30)

> ADRESSE2                                     VARCHAR2(30)

> POSTNR                                         NUMBER(5)

> LAND                                             CHAR(3)

> TELEFON                                      VARCHAR2(15)

> MOBILTLF                                    VARCHAR2(15)

> TELEFAX                                       VARCHAR2(15)

> EMAIL                                            VARCHAR2(30)

> HOMEPAGE                                  VARCHAR2(40)

>

>SQL> desc advfirma

> Name                                      Null?    Type

> ----------------------------------------- -------- ------

> ID                        NOT NULL NUMBER(4)

> NAVN                                     VARCHAR2(40)

> ADRESSE1                             VARCHAR2(30)

> ADRESSE2                             VARCHAR2(30)

> POSTNR                                 NUMBER(5)

> TELEFON                               VARCHAR2(15)

> TELEFAX                               VARCHAR2(15)

> EMAIL                                    VARCHAR2(30)

> HJEMMESIDE                        VARCHAR2(30)

> RETSKREDS                          NUMBER(3)

>

>SQL> desc advfirma_obj

> Name                                      Null?    Type

> ----------------------------------------- -------- ------

> ID                     NOT NULL NUMBER(4)

> NAVN                                 VARCHAR2(40)

> ADRESSE                            ADRESSE_TY

> RETSKREDS                       NUMBER(3)

>

>SQL> INSERT

>  2    INTO   advfirma_obj

>  3           (Id, Navn,adresse_ty(Adresse1, Adresse2, Postnr, Land,

>Telefon,

>  4           Mobiltlf, Telefax, Email, Homepage), Retskreds)

>  5  SELECT

>  6           Id, Navn, Adresse1, Adresse2, Postnr, NULL, Telefon,

>  7           NULL, Telefax, Email,Hjemmeside, Retskreds

>  8  FROM

>  9     Advfirma;

>         (Id, Navn,adresse_ty(Adresse1, Adresse2, Postnr, Land, Telefon,

>                             *

>ERROR at line 3:

>ORA-00917: missing comma

>

>

>

>Kind regards

>

>Magnus S Petersen

>

>

Received on Wed Sep 05 2001 - 01:38:58 CDT