Home » SQL & PL/SQL » SQL & PL/SQL » Invalid Identifier (Oracle 10G)
Invalid Identifier [message #381210] Thu, 15 January 2009 18:32 Go to next message
tojassl0l
Messages: 12
Registered: January 2009
Junior Member
I have the below code. And I got this error:
Error at Command Line:12 Column:20
Error report:
SQL Error: ORA-00904: "CO"."CO": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:

insert into users values (
    MyUser(
        PersonName ('Bela', 'Joska'),
        Address ('MIttom', 23),
        TelephoneNumberList (
            TelephoneNumber_T(
                0040,
                254,
                356643
            )
        ),
        (Select REF(co.co) From Companies co Where co.co.CompanyName = 'Company1'),
        2312342,
        'lol',
        'pass'
    )
);


At line "(Select REF(co.co) From Companies co Where co.co.CompanyName = 'Company1')" my object constructor is waiting for a reference. I got a tutorial where it is done like I did it... but it's not working. Anybody know the solution? Sad
Re: Invalid Identifier [message #381215 is a reply to message #381210] Thu, 15 January 2009 19:53 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
As the conspicuous lack of reply to your post should clue you, not many people use REFs. I also do not.

I would suggest that you break your query up into more lines and more parts in an attempt to see exactly what short piece of this query is causing the error.

Additionally, might I ask, why are you attempting to use REFs anyway? I admit not understanding them very well, but still, I have never found a use for them.

Maybe someone can give us a good reason to use them when they answer your question.

Kevin
Re: Invalid Identifier [message #381251 is a reply to message #381215] Fri, 16 January 2009 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As the conspicuous lack of reply to your post should clue you, not many people use REFs.

Or maybe we sleep. Smile

@OP,
Post a Test case: create table and insert statements.
Use SQL*Plus and copy and paste your session.
Also always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Fri, 16 January 2009 01:43]

Report message to a moderator

Re: Invalid Identifier [message #381272 is a reply to message #381251] Fri, 16 January 2009 01:23 Go to previous messageGo to next message
tojassl0l
Messages: 12
Registered: January 2009
Junior Member
So here is my code and a test case...

create or replace type Address as object (
    StreetName varchar2(50),
    Nr number
);
/
 
create or replace type TelephoneNumber_t as object (
    CountryCode number,
    CountyCode number,
    Nr number
);
/
 
create or replace type TelephoneNumberList as table of TelephoneNumber_t;
/
 
create or replace type Company as object (
    CompanyName varchar2(50),
    Address_ Address,
    TelephoneNumbers TelephoneNumberList
);
/
 
create or replace type PersonName as object (
    FirstName varchar2(50),
    LastName varchar2(50)
);
/
 
create or replace type Person as object (
    PersonName_ PersonName,
    Address_ Address,
    TelephoneNumbers TelephoneNumberList,
    Company_ REF Company,
    Salary number
) NOT FINAL;
/
 
create or replace type MyUser UNDER Person (
    UserName varchar2(50),
    MyPassword varchar2(50)
);
/
 
create table Users (
    u MyUser,
    constraint Users_pk PRIMARY KEY(u.UserName)
) NESTED TABLE u.TelephoneNumbers STORE AS UsTelephoneNumbersTab;
/
 
insert into users values (
    MyUser(
        PersonName ('Bela', 'Joska'),
        Address ('MIttom', 23),
        TelephoneNumberList (
            TelephoneNumber_T(
                0040,
                254,
                356643
            )
        ),
        (Select REF(co.co) From Companies co Where co.co.CompanyName = 'Company1'),
        2312342,
        'lol',
        'pass'
    )
);


The insert command is my test case, and for this I got the above error...
Re: Invalid Identifier [message #381273 is a reply to message #381272] Fri, 16 January 2009 01:39 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
the create of table companies is still missing.

Assuming
CREATE OR REPLACE TYPE CompanyType AS OBJECT
  ( CNR		   NUMBER
  , CO  	   REF Company
  );

CREATE TABLE Companies OF CompanyType
  ( CNR  PRIMARY KEY )
  OBJECT IDENTIFIER PRIMARY KEY;


use:
  
select co.co.CompanyName from Companies co
  
select ref(co) from companies co where co.co.CompanyName='Comp1'

[Updated on: Fri, 16 January 2009 01:56]

Report message to a moderator

Re: Invalid Identifier [message #381276 is a reply to message #381210] Fri, 16 January 2009 02:11 Go to previous messageGo to next message
tojassl0l
Messages: 12
Registered: January 2009
Junior Member
In this line was my fault...
CREATE TABLE Companies OF CompanyType


Thanks a lot man!!!
Re: Invalid Identifier [message #381286 is a reply to message #381276] Fri, 16 January 2009 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> CREATE TABLE Companies OF CompanyType
  2  /
CREATE TABLE Companies OF CompanyType
                          *
ERROR at line 1:
ORA-00902: invalid datatype


SQL> CREATE TABLE Companies OF Company
  2  /
CREATE TABLE Companies OF Company
*
ERROR at line 1:
ORA-22913: must specify table name for nested table column or attribute

Regards
Michel
Re: Invalid Identifier [message #381292 is a reply to message #381273] Fri, 16 January 2009 02:59 Go to previous messageGo to next message
tojassl0l
Messages: 12
Registered: January 2009
Junior Member
Ok Man, now it's working well.

But I got an other bas situation.

So Companies table looks like this
create or replace type Company as object (
    CompanyName varchar2(50),
    Address_ Address,
    TelephoneNumbers TelephoneNumberList
);
/

create table Companies of Company (
    CompanyName PRIMARY KEY
) OBJECT IDENTIFIER PRIMARY KEY
  NESTED TABLE TelephoneNumbers STORE AS CoTelephoneNumbersTab;
/


And I created another table with the below definition

create or replace type Truck as object (
    TruckID number,
    HP number,
    WeightCapacity number,
    Company_ REF Company
);
/

create table Trucks of Truck (
    TruckID PRIMARY KEY
) OBJECT IDENTIFIER PRIMARY KEY;
/


And the problem comes when I try to insert. Here is the insert code:

insert into Trucks values (
    trucks_sequence.nextval,
    385,
    7500,
    Company (Select REF(co) From Companies co Where co.CompanyName = 'Company1')
);


And the error message:


Error starting at line 1 in command:
insert into Trucks values (
    trucks_sequence.nextval,
    385,
    7500,
    Company (Select REF(co) From Companies co Where co.CompanyName = 'Company1')
)
Error at Command Line:5 Column:13
Error report:
SQL Error: ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:    
*Action:
Re: Invalid Identifier [message #381295 is a reply to message #381292] Fri, 16 January 2009 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Ok Man, now it's working well.

Post the solution you found.

Quote:
But I got an other bas situation.

SQL> insert into Trucks 
  2  select trucks_sequence.nextval,
  3         385,
  4         7500,
  5         REF(co) From Companies co Where co.CompanyName = 'Company1'
  6  /
select trucks_sequence.nextval,
       *
ERROR at line 2:
ORA-02289: sequence does not exist


SQL> create sequence trucks_sequence;

Sequence created.

SQL> insert into Trucks 
  2  select trucks_sequence.nextval,
  3         385,
  4         7500,
  5         REF(co) From Companies co Where co.CompanyName = 'Company1'
  6  /

0 rows created.

Regards
Michel
Re: Invalid Identifier [message #381340 is a reply to message #381210] Fri, 16 January 2009 07:30 Go to previous messageGo to next message
tojassl0l
Messages: 12
Registered: January 2009
Junior Member
So my solution:

create or replace type Address as object (
    StreetName varchar2(50),
    Nr number
);
/
 
create or replace type TelephoneNumber_t as object (
    CountryCode number,
    CountyCode number,
    Nr number
);
/
 
create or replace type TelephoneNumberList as table of TelephoneNumber_t;
/
 
create or replace type Company as object (
    CompanyName varchar2(50),
    Address_ Address,
    TelephoneNumbers TelephoneNumberList
);
/

create table Companies of Company (
    CompanyName PRIMARY KEY
) OBJECT IDENTIFIER PRIMARY KEY
  NESTED TABLE TelephoneNumbers STORE AS CoTelephoneNumbersTab;
/
 
create or replace type PersonName as object (
    FirstName varchar2(50),
    LastName varchar2(50)
);
/
 
create or replace type Person as object (
    PersonName_ PersonName,
    Address_ Address,
    TelephoneNumbers TelephoneNumberList,
    Company_ REF Company,
    Salary number
) NOT FINAL;
/
 
create or replace type MyUser UNDER Person (
    UserName varchar2(50),
    MyPassword varchar2(50)
);
/
 
create table Users (
    u MyUser,
    constraint Users_pk PRIMARY KEY(u.UserName)
) NESTED TABLE u.TelephoneNumbers STORE AS UsTelephoneNumbersTab;
/
 
insert into users values (
    MyUser(
        PersonName ('Bela', 'Joska'),
        Address ('MIttom', 23),
        TelephoneNumberList (
            TelephoneNumber_T(
                0040,
                254,
                356643
            )
        ),
        (Select REF(co.co) From Companies co Where co.co.CompanyName = 'Company1'),
        2312342,
        'lol',
        'pass'
    )
);

create or replace type Truck as object (
    TruckID number,
    HP number,
    WeightCapacity number,
    Company_ REF Company
);
/


And the below code isn't working for me Sad

SQL> insert into Trucks 
  2  select trucks_sequence.nextval,
  3         385,
  4         7500,
  5         REF(co) From Companies co Where co.CompanyName = 'Company1'
  6  /
Re: Invalid Identifier [message #381341 is a reply to message #381340] Fri, 16 January 2009 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"isn't working" is NOT an Oracle error.

Regards
Michel


Re: Invalid Identifier [message #381342 is a reply to message #381341] Fri, 16 January 2009 07:41 Go to previous messageGo to next message
tojassl0l
Messages: 12
Registered: January 2009
Junior Member
Yes, you are right! Smile

So the error is:

Error starting at line 1 in command:
insert into Trucks 
  select trucks_sequence.nextval,
  385,
  7500,
  REF(co) From Companies co Where co.CompanyName = 'Company1'
  
Error report:
SQL Error: ORA-22979: cannot INSERT object view REF or user-defined REF
22979. 00000 -  "cannot INSERT object view REF or user-defined REF"
*Cause:    Attempt to insert an object view REF or user-defined REF in a
           REF column created to store system generated REF values"
*Action:   Make sure the REF to be inserted is not from an object view
           or from a user-defined REF column

Re: Invalid Identifier [message #381344 is a reply to message #381340] Fri, 16 January 2009 07:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
insert into Trucks
            *
ERROR at line 1:
ORA-00942: table or view does not exist

Re: Invalid Identifier [message #381345 is a reply to message #381210] Fri, 16 January 2009 07:47 Go to previous messageGo to next message
tojassl0l
Messages: 12
Registered: January 2009
Junior Member
What do you want to say with this?

For me it does exist.
Re: Invalid Identifier [message #381346 is a reply to message #381345] Fri, 16 January 2009 07:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You provided an incomplete testcase.
This makes it impossible to replay your scenario and trace your error.

[Updated on: Fri, 16 January 2009 07:51]

Report message to a moderator

Re: Invalid Identifier [message #381348 is a reply to message #381210] Fri, 16 January 2009 07:57 Go to previous messageGo to next message
tojassl0l
Messages: 12
Registered: January 2009
Junior Member
I posted my code, my test insert command, and the error, do I need any other things? Please say it what you need to advance in this problem.
Re: Invalid Identifier [message #381352 is a reply to message #381342] Fri, 16 January 2009 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you create Trucks with the same statement you previously posted?
create table Trucks of Truck (
    TruckID PRIMARY KEY
) OBJECT IDENTIFIER PRIMARY KEY;
/

Regards
Michel
Re: Invalid Identifier [message #381354 is a reply to message #381210] Fri, 16 January 2009 08:04 Go to previous messageGo to next message
tojassl0l
Messages: 12
Registered: January 2009
Junior Member
Yes.

I created as you said.
Re: Invalid Identifier [message #381357 is a reply to message #381354] Fri, 16 January 2009 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Drop all stuff, use SQL*Plus and post the full script execution.
Add your Oracle version with 4 decimals.

Regards
Michel
Re: Invalid Identifier [message #381361 is a reply to message #381210] Fri, 16 January 2009 08:15 Go to previous messageGo to next message
tojassl0l
Messages: 12
Registered: January 2009
Junior Member
Full script execution
/* Products */
create or replace type ProductName_t as object (
    ProductName varchar2(50),
    Country varchar2(50)
);
/

create type ProductNameList as table of ProductName_t;
/

create or replace type Product as object (
    ProductID number,
    Weight number,
    Price number,
    ProductNames ProductNameList
);
/

create table Products of Product (
    ProductID PRIMARY KEY
) OBJECT IDENTIFIER PRIMARY KEY
  NESTED TABLE ProductNames STORE AS ProductNamesTab;
/

commit;
/* Products End */

/* Companies */

create or replace type Address as object (
    StreetName varchar2(50),
    Nr number
);
/

create or replace type TelephoneNumber_t as object (
    CountryCode number,
    CountyCode number,
    Nr number
);
/

create or replace type TelephoneNumberList as table of TelephoneNumber_t;
/

create or replace type Company as object (
    CompanyName varchar2(50),
    Address_ Address,
    TelephoneNumbers TelephoneNumberList
);
/

create table Companies of Company (
    CompanyName PRIMARY KEY
) OBJECT IDENTIFIER PRIMARY KEY
  NESTED TABLE TelephoneNumbers STORE AS CoTelephoneNumbersTab;
/

commit;
/* Companies End */

/* Persons */

create or replace type PersonName as object (
    FirstName varchar2(50),
    LastName varchar2(50)
);
/

create or replace type Person as object (
    PersonID number,
    PersonName_ PersonName,
    Address_ Address,
    TelephoneNumbers TelephoneNumberList,
    Company_ REF Company,
    Salary number
) NOT FINAL;
/

create table Persons of Person (
    PersonID PRIMARY KEY
) OBJECT IDENTIFIER PRIMARY KEY
  NESTED TABLE TelephoneNumbers STORE AS PeTelephoneNumbersTab;
/

commit;
/* Persons End */

/* Trucks */

create or replace type Truck as object (
    TruckID number,
    HP number,
    WeightCapacity number,
    Company_ REF Company
);
/

create table Trucks of Truck (
    TruckID PRIMARY KEY
) OBJECT IDENTIFIER PRIMARY KEY;
/

commit;
/* Trucks End */

/* Drivers */

create or replace type Order__t as object (
    coFrom REF Company,
    coTo REF Company,
    Product_ REF Product
)
/

create or replace type Order_List as table of Order__t;
/

create or replace type Driver as object (
    DriverID number,
    PersonName_ PersonName,
    Truck_ Truck,
    Orders Order_List
);
/

create table Drivers of Driver (
    DriverID PRIMARY KEY
) OBJECT IDENTIFIER PRIMARY KEY
  NESTED TABLE Orders STORE AS OrdersTab;
/

commit;
/* Drivers End */

/* Users */

create or replace type MyUser UNDER Person (
    UserName varchar2(50),
    MyPassword varchar2(50)
);
/

create table Users of MyUser (
    UserName PRIMARY KEY
) OBJECT IDENTIFIER PRIMARY KEY
  NESTED TABLE TelephoneNumbers STORE AS UsTelephoneNumbersTab;
/

commit;
/* Users End */

create sequence products_sequence
start with 1
increment by 1
nomaxvalue;

create sequence trucks_sequence
start with 1
increment by 1
nomaxvalue;

/* Products */

insert into Products values (
    Product (
        products_sequence.nextval,
        500,
        300,
        ProductNameList (
            ProductName_t ('Product1', 'Romania'),
            ProductName_t ('Product2', 'England'),
            ProductName_t ('Product3', 'Hungary')
        )
    )
);
/

insert into Products values (
    Product (
        products_sequence.nextval,
        100,
        400,
        ProductNameList (
            ProductName_t ('Product4', 'Romania'),
            ProductName_t ('Product5', 'England'),
            ProductName_t ('Product6', 'Hungary')
        )
    )
);
/


insert into Products values (
    Product (
        products_sequence.nextval,
        500,
        350,
        ProductNameList (
            ProductName_t ('Product7', 'Romania'),
            ProductName_t ('Product8', 'England'),
            ProductName_t ('Product9', 'Hungary')
        )
    )
);
/

insert into Products values (
    Product (
        products_sequence.nextval,
        50,
        300,
        ProductNameList (
            ProductName_t ('Product10', 'Romania'),
            ProductName_t ('Product11', 'England'),
            ProductName_t ('Product12', 'Hungary')
        )
    )
);
/

insert into Products values (
    Product (
        products_sequence.nextval,
        100,
        50,
        ProductNameList (
            ProductName_t ('Product13', 'Romania'),
            ProductName_t ('Product14', 'England'),
            ProductName_t ('Product15', 'Hungary')
        )
    )
);
/

commit;
/* Products End */

/* Companies */

insert into Companies values (
    Company(
        'Company1',
        Address (
            'Street A',
            1
        ),
        TelephoneNumberList (
            TelephoneNumber_T(
                0040,
                254,
                356643
            )
        )
    )
);
/

insert into Companies values (
    Company(
        'Company2',
        Address (
            'Street A',
            1
        ),
        TelephoneNumberList (
            TelephoneNumber_T(
                0040,
                254,
                356643
            )
        )
    )
);
/

insert into Companies values (
    Company(
        'Company3',
        Address (
            'Street A',
            1
        ),
        TelephoneNumberList (
            TelephoneNumber_T(
                0040,
                254,
                356643
            )
        )
    )
);
/

insert into Companies values (
    Company(
        'Company4',
        Address (
            'Street A',
            1
        ),
        TelephoneNumberList (
            TelephoneNumber_T(
                0040,
                254,
                356643
            )
        )
    )
);
/

insert into Companies values (
    Company(
        'Company5',
        Address (
            'Street A',
            1
        ),
        TelephoneNumberList (
            TelephoneNumber_T(
                0040,
                254,
                356643
            )
        )
    )
);
/

commit;
/* Companies End */

insert into Trucks 
  select trucks_sequence.nextval,
  385,
  7500,
  REF(co) From Companies co Where co.CompanyName = 'Company1'
  /


Oracle Version:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


Now it's ok?
Re: Invalid Identifier [message #381365 is a reply to message #381361] Fri, 16 January 2009 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a script but not the execution of the script.
Please post the execution, use SQL*Plus.

Regards
Michel
Re: Invalid Identifier [message #381367 is a reply to message #381210] Fri, 16 January 2009 08:25 Go to previous messageGo to next message
tojassl0l
Messages: 12
Registered: January 2009
Junior Member
I can't download Sql *Plus... Maybe you know a good download link?

I use other developer tool. So when I run the above script I got the following:

type ProductName_t Compiled.
type ProductNameList Compiled.
type Product Compiled.
create table succeeded.
commit succeeded.
type Address Compiled.
type TelephoneNumber_t Compiled.
type TelephoneNumberList Compiled.
type Company Compiled.
create table succeeded.
commit succeeded.
type PersonName Compiled.
type Person Compiled.
create table succeeded.
commit succeeded.
type Truck Compiled.
create table succeeded.
commit succeeded.
type Order__t Compiled.
type Order_List Compiled.
type Driver Compiled.
create table succeeded.
commit succeeded.
type MyUser Compiled.
create table succeeded.
commit succeeded.
create sequence succeeded.
create sequence succeeded.
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
commit succeeded.
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
1 rows inserted
commit succeeded.
Error starting at line 1 in command:
insert into Trucks 
  select trucks_sequence.nextval,
  385,
  7500,
  REF(co) From Companies co Where co.CompanyName = 'Company1'
  
Error report:
SQL Error: ORA-22979: cannot INSERT object view REF or user-defined REF
22979. 00000 -  "cannot INSERT object view REF or user-defined REF"
*Cause:    Attempt to insert an object view REF or user-defined REF in a
           REF column created to store system generated REF values"
*Action:   Make sure the REF to be inserted is not from an object view
           or from a user-defined REF column


That's all..
Re: Invalid Identifier [message #381375 is a reply to message #381367] Fri, 16 January 2009 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is on client download.
If you have a thick client, you have it in bin directory.

Regards
Michel
Re: Invalid Identifier [message #381388 is a reply to message #381375] Fri, 16 January 2009 10:08 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
You have to add bevor INSERT:
ALTER TABLE Trucks
  ADD (SCOPE FOR (COMPANY_) IS Companies);

Good luck
Re: Invalid Identifier [message #381404 is a reply to message #381388] Fri, 16 January 2009 12:09 Go to previous message
tojassl0l
Messages: 12
Registered: January 2009
Junior Member
Yes _jum, you are right, it is working with your answer. Thanks a lot! Smile
Previous Topic: Determining which tables are external tables
Next Topic: Trim & Instr
Goto Forum:
  


Current Time: Thu Dec 08 04:19:10 CST 2016

Total time taken to generate the page: 0.26962 seconds