OraFAQ Forum: SQL & PL/SQL » ORA-00904: : invalid identifier

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00904: : invalid identifier

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-00904: : invalid identifier [message #314726]

Thu, 17 April 2008 11:29

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

Report message to a moderator

Re: ORA-00904: : invalid identifier [message #314728 is a reply to message #314726]

Thu, 17 April 2008 11:35

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

Report message to a moderator

Re: ORA-00904: : invalid identifier [message #314731 is a reply to message #314726]

Thu, 17 April 2008 11:53

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

Report message to a moderator

Re: ORA-00904: : invalid identifier [message #314735 is a reply to message #314731]

Thu, 17 April 2008 12:04

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

Report message to a moderator

Re: ORA-00904: : invalid identifier [message #314767 is a reply to message #314735]

Thu, 17 April 2008 13:36

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.

Report message to a moderator

Re: ORA-00904: : invalid identifier [message #314769 is a reply to message #314767]

Thu, 17 April 2008 13:38

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: ORA-00904: : invalid identifier [message #314775 is a reply to message #314769]

Thu, 17 April 2008 14:06

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

Report message to a moderator

Re: ORA-00904: : invalid identifier [message #314776 is a reply to message #314775]

Thu, 17 April 2008 14:11

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

After compilation, use "show errors" to get the errors.

Regards
Michel

Report message to a moderator

Re: ORA-00904: : invalid identifier [message #314779 is a reply to message #314767]

Thu, 17 April 2008 14:24

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

Report message to a moderator

Re: ORA-00904: : invalid identifier [message #314781 is a reply to message #314779]

Thu, 17 April 2008 14:30

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Execute "show errors" after your code to see the errors.

Regards
Michel

Report message to a moderator

Re: ORA-00904: : invalid identifier [message #314797 is a reply to message #314779]

Thu, 17 April 2008 15:51

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

WHERE clause missing.

Report message to a moderator

Re: ORA-00904: : invalid identifier [message #314798 is a reply to message #314781]

Thu, 17 April 2008 15:55

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

Report message to a moderator

Re: ORA-00904: : invalid identifier [message #314800 is a reply to message #314798]

Thu, 17 April 2008 16:18

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

Report message to a moderator

Re: ORA-00904: : invalid identifier [message #314843 is a reply to message #314800]

Fri, 18 April 2008 00:39

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: ORA-00904: : invalid identifier [message #314991 is a reply to message #314800]

Fri, 18 April 2008 08:31

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

It doesn't take a rocket scientist to see that line 9 is completely shleprocked.

Report message to a moderator

Re: ORA-00904: : invalid identifier [message #315014 is a reply to message #314800]

Fri, 18 April 2008 10:47

Barbara Boehmer
Messages: 9106
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.

Report message to a moderator

Re: ORA-00904: : invalid identifier [message #315743 is a reply to message #315014]

Tue, 22 April 2008 10:46

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.

Report message to a moderator

Re: ORA-00904: : invalid identifier [message #315752 is a reply to message #314726]

Tue, 22 April 2008 10:57

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

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

Barbara Boehmer
Messages: 9106
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Aug 04 11:35:38 CDT 2025