| Alter table statement for Oracle ORA-00904: invalid column name [message #40218] |
Tue, 17 September 2002 14:29  |
Rich D
Messages: 2
Registered: September 2002
|
Junior Member |
|
|
I'm modifying SQl for SQL Server to run in Oracle.
I successfully created a table in SQL + with this SQL:
CREATE TABLE Survey_Series
(
Survey_series_id NUMBER ,
Survey_series_sequence_id int NOT NULL ,
Survey_series_name char (128) NOT NULL ,
Survey_series_shortform char (32) NOT NULL ,
Create_username char (64) NOT NULL ,
Create_date date NOT NULL ,
Update_username char (64) NOT NULL ,
Update_date date NOT NULL,
PRIMARY KEY (Survey_series_id)
);
Now I wanna alter this SQL to work in Oracle. I always get an error saying ORA-00904: invalid column name
ALTER TABLE Survey_Series ADD
CONSTRAINT DF_Survey_Series_seqence_id DEFAULT (0) FOR SURVEY_SERIES_SEQUENCE_ID,
CONSTRAINT DF_Groups_Survey_shortform DEFAULT ('') FOR Survey_series_shortform,
CONSTRAINT DF_Groups_Create_username DEFAULT (app_name()) FOR Create_username,
CONSTRAINT DF_Groups_Create_date DEFAULT (SYSDATE) FOR Create_datetime,
CONSTRAINT DF_Groups_Update_usename DEFAULT (app_name()) FOR Update_username,
CONSTRAINT DF_Groups_Update_date DEFAULT (SYSDATE) FOR Update_datetime,
CONSTRAINT PK_Groups PRIMARY KEY CLUSTERED
(
Survey_series_id
);
Any thoughts? ideas? or fixed code send to rdalfonso@nyc.rr.com
|
|
|
|
| Re: Alter table statement for Oracle ORA-00904: invalid column name [message #40219 is a reply to message #40218] |
Tue, 17 September 2002 15:08  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
First off, you should be using the VARCHAR2 datatype instead of CHAR. Unless you like storing lots of spaces and dealing with all kinds of query comparison problems.
You will be able to set default values for 3 of the columns as:
alter table survey_series
modify (survey_series_id default 0,
create_date default sysdate,
update_date default sysdate);
The default value of '' (empty string) for survey_series_shortform is meaningless since an empty string is the same as NULL in Oracle. Since your column is defined as NOT NULL here, you have an issue...
The two columns with a default of app_name() - I'm assuming this is a custom function - will have to be populated in a trigger because DDL cannot refer to function calls. You could handle the sysdate defaults in the trigger, too. The trigger would look like:
create or replace trigger survey_series_trg
before insert or update on survey_series
for each row
begin
if inserting then
:new.create_username := app_name();
:new.create_date := sysdate;
end if;
:new.update_username := app_name();
:new.update_date := sysdate;
end;
/ |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
