oracle cannot insert values into a nested table field

213 views Asked by At

I have the sample CUSTOMERS table. I exported the values from table as INSERT script. I took one row, modified the value from PK and re-executed the insert.

Insert into oe.customers (CUSTOMER_ID,
                          CUST_FIRST_NAME,
                          CUST_LAST_NAME,
                          CUST_ADDRESS,
                          PHONE_NUMBERS,
                          NLS_LANGUAGE,
                          NLS_TERRITORY,
                          CREDIT_LIMIT,
                          CUST_EMAIL,
                          ACCOUNT_MGR_ID,
                          CUST_GEO_LOCATION,
                          DATE_OF_BIRTH,
                          MARITAL_STATUS,
                          GENDER,
                          INCOME_LEVEL,
                          CREDIT_CARDS) 
values ( oe.customer_seq.nextval,
         'Donald',
         'Hunter',
         OE.CUST_ADDRESS_TYP('5122 Sinclair Ln','21206','Baltimore','MD','US'),
         OE.PHONE_LIST_TYP('+1 410 123 4795'),
         'us',
         'AMERICA',
         2600,
         '[email protected]',
         145,
         MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(-76.545732,39.322775,NULL),NULL,NULL),
         to_date('20-JAN-60','DD-MON-RR'),
         'married',
         'M',
         'G: 130,000 - 149,999',
         OE.TYP_CR_CARD_NST(OE.TYP_CR_CARD('Visa',100000000000011)));

and I have the following error message:

Error at Command Line : 32 Column : 29 Error report - SQL Error: ORA-00904: : invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause:
*Action:

which refers to line:

OE.TYP_CR_CARD_NST(OE.TYP_CR_CARD('Visa',100000000000011)));

The definition of types are:

create or replace type typ_cr_card as object
    ( card_type VARCHAR2(25)
    , card_num  NUMBER);

create or replace type typ_cr_card_nst as table of typ_cr_card;

Can someone tell me, please, what is wrong with this insert line as it is the one provided by SQL DEVELOPER?

NOTE: Also, I tried to use a procedure which inserts values in this table and the error refers to TYP_CR_CARD_NST datatype.

0

There are 0 answers