Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00904: : invalid identifier
icon6.gif  ORA-00904: : invalid identifier [message #314726] Thu, 17 April 2008 11:29 Go to next message
snow12
Messages: 9
Registered: April 2008
Junior Member
Hello:

I created the table

CREATE TABLE Name (
id NUMBER(10,0),
adminUser VARCHAR2(255),
adminPassword VARCHAR2(255),
access NUMBER(10,0)
);
I got (Error): ORA-00904: : invalid identifier

How to fix it.

Thanks

Snow Embarassed
Re: ORA-00904: : invalid identifier [message #314728 is a reply to message #314726] Thu, 17 April 2008 11:35 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Welcome to the forum. Spend some time in reading the forum guidelines and I cannot see any other statement apart from create table statement. Copy and paste your output from your sqlplus session something like this.
SQL> select wrongcolumn from user_tables;
select wrongcolumn from user_tables
       *
ERROR at line 1:
ORA-00904: "WRONGCOLUMN": invalid identifier

Regards

Raj
Re: ORA-00904: : invalid identifier [message #314731 is a reply to message #314726] Thu, 17 April 2008 11:53 Go to previous messageGo to next message
snow12
Messages: 9
Registered: April 2008
Junior Member
Hello:

Thanks for the reply

SQL> CREATE TABLE tbl_login (
2 id NUMBER(10,0),
3 adminUser VARCHAR2(255),
4 adminPassword VARCHAR2(255),
5 access NUMBER(10,0)
6 );
access NUMBER(10,0)
*
ERROR at line 5:
ORA-00904: : invalid identifier


Thanks
Re: ORA-00904: : invalid identifier [message #314735 is a reply to message #314731] Thu, 17 April 2008 12:04 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
It is because of the following :
SQL> select * from v$reserved_words where keyword = 'ACCESS';

KEYWORD                            LENGTH R R R R D
------------------------------ ---------- - - - - -
ACCESS                                  6 N N N Y N

  1  CREATE TABLE tbl_login (
  2   id NUMBER(10,0),
  3   adminUser VARCHAR2(255),
  4   adminPassword VARCHAR2(255),
  5   access_1 NUMBER(10,0)
  6*  )
SQL> /

Table created.


Regards

Raj
Re: ORA-00904: : invalid identifier [message #314767 is a reply to message #314735] Thu, 17 April 2008 13:36 Go to previous messageGo to next message
snow12
Messages: 9
Registered: April 2008
Junior Member
Hello:

Thanks for the help.

I have bad bind variable error.

Here is the trigger.

CREATE OR REPLACE TRIGGER tbl_tableName
BEFORE INSERT OR UPDATE OR DELETE
ON tbl_tableName
FOR EACH ROW
BEGIN
UPDATE tbl_tableName
SET ( yearsserved ) = ( SELECT sqlserver_utilities.convert('NUMBER(18,0)', SYSDATE - :NEW.viewName.hiredate) / 365
FROM viewName
:NEW.:OLD.EmpID = :NEW.viewName.empidWHERE :NEW.viewName.hiredate IS NOT NULL );

UPDATE tbl_tableName
SET color = 'Green'
WHERE :NEW.yearsserved <= 5.00;


I changed to:

UPDATE tbl_tableName
SET ( yearsserved ) = ( SELECT sqlserver_utilities.convert('NUMBER(18,0)', SYSDATE - :NEW.hiredate) / 365
FROM viewEmployee
:NEW.EmpID = :NEW.empid WHERE :NEW.viewName.hiredate IS NOT NULL );

I still got the error:

[1]: (Warning) PLS-00049: bad bind variable 'NEW.HIREDATE'
[1]: (Warning) PLS-00049: bad bind variable 'NEW.viewName'
[1]: (Warning) PL/SQL: ORA-00907: missing right parenthesis

Thanks for your help.

Re: ORA-00904: : invalid identifier [message #314769 is a reply to message #314767] Thu, 17 April 2008 13:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to:
1/ Use SQL*Plus
2/ Post code with line numbers
3/ Post errors with line numbers
4/ Format your post, for this read OraFAQ Forum Guide, "How to format your post?" section.

Regards
Michel

Re: ORA-00904: : invalid identifier [message #314775 is a reply to message #314769] Thu, 17 April 2008 14:06 Go to previous messageGo to next message
snow12
Messages: 9
Registered: April 2008
Junior Member
Hello:

Thanks for the reply.

I use SQL*PLUS, but only got: Warning: Trigger created with compilation errors.

It did not show line error. I used Navigator and the detailed error was shown.

Thanks
Re: ORA-00904: : invalid identifier [message #314776 is a reply to message #314775] Thu, 17 April 2008 14:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
After compilation, use "show errors" to get the errors.

Regards
Michel
Re: ORA-00904: : invalid identifier [message #314779 is a reply to message #314767] Thu, 17 April 2008 14:24 Go to previous messageGo to next message
snow12
Messages: 9
Registered: April 2008
Junior Member
Hello:

Here is code from sqlplus.

SQL> CREATE OR REPLACE TRIGGER tbl_tableName
  2     BEFORE INSERT OR UPDATE OR DELETE
  3     ON tbl_tableName
  4     FOR EACH ROW
  5  BEGIN
  6     UPDATE tbl_tableName
  7        SET ( yearsserved ) = ( SELECT sqlserver_utilities.convert('NUMBER(18,0)', SYSDATE - :NEW
.vieweName.hiredate) / 365
  8       FROM vieweName
  9       :NEW.:OLD.EmpID = :NEW.vieweName.empidWHERE :NEW.vieweName.hiredate IS NOT NULL );
 10  
 11     UPDATE tbl_tableName
 12        SET badgecolor = 'Green'
 13        WHERE :NEW.yearsserved <= 5.00;
 14  
 15  
 16  END;
 17  
 41  /

Warning: Trigger created with compilation errors.



The status of trigger is invalid.

Thanks
Re: ORA-00904: : invalid identifier [message #314781 is a reply to message #314779] Thu, 17 April 2008 14:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Execute "show errors" after your code to see the errors.

Regards
Michel
Re: ORA-00904: : invalid identifier [message #314797 is a reply to message #314779] Thu, 17 April 2008 15:51 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
WHERE clause missing.
Re: ORA-00904: : invalid identifier [message #314798 is a reply to message #314781] Thu, 17 April 2008 15:55 Go to previous messageGo to next message
snow12
Messages: 9
Registered: April 2008
Junior Member
Hello:

Thanks for the response.

Here is the error.


Errors for TRIGGER TBL_EMPLOYEE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/4      PL/SQL: SQL Statement ignored
3/92     PLS-00049: bad bind variable 'NEW.VIEWNAME'
5/6      PLS-00049: bad bind variable 'NEW.'
5/6      PL/SQL: ORA-00907: missing right parenthesis
5/24     PLS-00049: bad bind variable 'NEW.VIEWENAME'
5/53     PLS-00049: bad bind variable 'NEW.VIEWENAME'



Thanks,

H
Re: ORA-00904: : invalid identifier [message #314800 is a reply to message #314798] Thu, 17 April 2008 16:18 Go to previous messageGo to next message
snow12
Messages: 9
Registered: April 2008
Junior Member
Hello:

I have where clause, but still got the same error.


SQL> CREATE OR REPLACE TRIGGER tbl_tableName
  2     BEFORE INSERT OR UPDATE OR DELETE
  3     ON tbl_tableName
  4     FOR EACH ROW
  5  BEGIN
  6     UPDATE tbl_tableName
  7        SET ( yearsserved ) = ( SELECT sqlserver_utilities.convert('NUMBER(18,0)', SYSDATE - :NEW
.vieweName.hiredate) / 365
  8       FROM vieweName
  9       :NEW.:OLD.EmpID = :NEW.vieweName.empid WHERE :NEW.vieweName.hiredate IS NOT NULL );
 10  
 11     UPDATE tbl_tableName
 12        SET badgecolor = 'Green'
 13        WHERE :NEW.yearsserved <= 5.00;
 14  
 15  
 16  END;
 17  
 41  /

Warning: Trigger created with compilation errors.

LINE/COL ERROR
-------- -----------------------------------------------------
2/4      PL/SQL: SQL Statement ignored
3/92     PLS-00049: bad bind variable 'NEW.VIEWENAME'
5/6      PLS-00049: bad bind variable 'NEW.'
5/6      PL/SQL: ORA-00907: missing right parenthesis
5/24     PLS-00049: bad bind variable 'NEW.VIEWNAME'
5/54     PLS-00049: bad bind variable 'NEW.VIEWENAME'
SQL> 



Thanks,

H
Re: ORA-00904: : invalid identifier [message #314843 is a reply to message #314800] Fri, 18 April 2008 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
:NEW.vieweName.hiredate

What is "vieweName"?
:NEW is a record that contains a field for each "tbl_tableName" column.

Regards
Michel
Re: ORA-00904: : invalid identifier [message #314991 is a reply to message #314800] Fri, 18 April 2008 08:31 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
It doesn't take a rocket scientist to see that line 9 is completely shleprocked.
Re: ORA-00904: : invalid identifier [message #315014 is a reply to message #314800] Fri, 18 April 2008 10:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Your code is full of errors. Also, you should not try to update the same table that your trigger is on or you will end up with a mutating error. If your update fires a trigger that does an update that fires the trigger that does an update that fires the trigger and so on, you will have a never-ending loop. You need to re-think and describe what you are trying to accomplish.


Re: ORA-00904: : invalid identifier [message #315743 is a reply to message #315014] Tue, 22 April 2008 10:46 Go to previous messageGo to next message
snow12
Messages: 9
Registered: April 2008
Junior Member
Hello:

Thanks for the help.


Here is my orginal Sqlsever trigger. I would like change it to the Oracle.

CREATE TRIGGER [UpdateYears] ON dbo.tbl_TABLE
FOR INSERT, UPDATE, DELETE
AS UPDATE tbl_TABLE
SET yearsserved = CONVERT(numeric, GETDATE() - viewName.hiredate) / 365
FROM tbl_TABLE JOIN
viewName ON tbl_TABLE.id = viewName.pid
WHERE viewName.hiredate IS NOT NULL

UPDATE tbl_TABLE
SET color = 'Green'
WHERE yearsserved <= 5.00

UPDATE tbl_TABLE
SET color = 'Yellow'
WHERE yearsserved > 5.00 AND yearsserved <= 10.00

My question is that I would rewrite this trigger to the Oracle.

CREATE OR REPLACE TRIGGER UpdateYears BEFORE INSERT OR UPDATE OR OR DELETE
ON tbl_TABLE
FOR EACH ROW
BEGIN
...


How to do

AS UPDATE tbl_TABLE
SET yearsserved = CONVERT(numeric, GETDATE() - viewName.hiredate) / 365
FROM tbl_TABLE JOIN
viewName ON tbl_TABLE.id = viewName.pid
WHERE viewName.hiredate IS NOT NULL

after BEGIN in Oracle?

Your help is highly appreciated.
Re: ORA-00904: : invalid identifier [message #315752 is a reply to message #314726] Tue, 22 April 2008 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from v$reserved_words where keyword='ACCESS';
KEYWORD                        LENGTH R R R R D
------------------------------ ------ - - - - -
ACCESS                              6 N N N Y N

1 row selected.

Regards
Michel

Edit: Doh! I'm really late this day, I thought it was a new topic and answer to the first question. Time to have a rest.

[Updated on: Tue, 22 April 2008 10:59]

Report message to a moderator

Re: ORA-00904: : invalid identifier [message #316380 is a reply to message #315743] Thu, 24 April 2008 20:13 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Many of us Oracle folks are not familiar with SQL Server, so we might not know what your SQL Server code is supposed to do. You need to provide a create table statement with table name and column names that match those in your trigger and a sample insert statement and/or update statement and/or delete statement and explain what you want the result to be after the insert or update or delete has caused the trigger to fire. In general, when you assign a value to the row that is being processed when the trigger fired, you do so like:

:NEW.some_column := some_value;

not with an update statement.

[Updated on: Thu, 24 April 2008 20:13]

Report message to a moderator

Previous Topic: Piping results of an sql insert into a 2nd insert statement
Next Topic: Materialized view does not refresh when remote connection is dropped temporarily
Goto Forum:
  


Current Time: Sat Dec 10 18:27:13 CST 2016

Total time taken to generate the page: 0.08479 seconds