CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle11g
BadCoder
BadCoder

Reputation: 13

How to overcome a persistent oracle 'invalid identifier' error on a basic insert?

I am trying to create a basic table using subtypes and insert some data into this in Oracle Express 11g.

My table is successfully created but i am having issues with inserting data.

The result of my insert statement always throws back an error 'SQL Error: ORA-00904: "BRANCH_PHONE": invalid identifier'.

The column which shows up in the error message is always the column which is at the end of the insert statement, despite the column existing in the table. I have tried the following code:

create type addressType as object(
street varchar2(20),
city varchar2(20),
postCode varchar2(8))
not final
/
create type branchType as object(
branchID int,
branch_address addressType,
branch_phone int(11))
not final
/
create table Branch of branchType(
    constraint branch_pk primary key(branchID));
/
insert into Branch values (
branchID('2364'), 
addressType('12 Rooster','Atlantis','A13 4UG'),
branch_phone('01316521311'));

I would really appreciate any ideas.

Upvotes: 1

Views: 404

Answers (1)

OldProgrammer
OldProgrammer

Reputation: 12169

I made some changes, including changing the branch_phone to varchar2. A Phone number, while is "numbers" is not a data type of number. it is a string of characters. Also you were passing branchID as a string, but you are declaring it as a number, so changed that also. BranchID and branch_phone are primitive data types, so no constructor needed.

create type addressType as object(
street varchar2(20),
city varchar2(20),
postCode varchar2(8))
not final
/
create type branchType as object(
branchID int,
branch_address addressType,
branch_phone varchar2(11))
not final
/
create table Branch of branchType(
    constraint branch_pk primary key(branchID));
/
insert into Branch values (
branchtype(2364,
           addressType('12 Rooster','Atlantis','A13 4UG'),
           '01316521311') )

Upvotes: 1

Related Questions