10 Replies - 8197 Views - Last Post: 10 May 2010 - 06:34 AM Rate Topic: **--- 1 Votes

#1 DRMS  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 08-May 10

ORA-00904: invalid identifier

Posted 09 May 2010 - 02:01 AM

Hello......
I'm trying to write the data from file to any database.& i'm getting this exception. I have checked spellings & have left with no clue to what to do next!

any clue?
Is This A Good Question/Topic? 0
  • +

Replies To: ORA-00904: invalid identifier

#2 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: ORA-00904: invalid identifier

Posted 09 May 2010 - 03:03 AM

Post your code that's giving you the error. Without that, it's going to be virtually impossible to provide you with any kind of assistance...
Was This Post Helpful? 0
  • +
  • -

#3 DRMS  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 08-May 10

Re: ORA-00904: invalid identifier

Posted 09 May 2010 - 03:30 AM

code is like that:
[import java.io.*;
import java.sql.*;
import java.io.*;
class FileRead
{
public static void main(String args[])
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException cnf)
{
System.out.println("Can't load the driver"+cnf.getMessage());
System.exit(0);
}
try
{
Connection conn=DriverManager.getConnection("jdbc:odbc:dili","system","xxx");
Statement st=conn.createStatement();

// Open the file that is the first
// command line parameter
FileInputStream fstream = new FileInputStream("adil.txt");
// Get the object of DataInputStream
DataInputStream in = new DataInputStream(fstream);
BufferedReader br = new BufferedReader(new InputStreamReader(in));
String strLine;

//Read File Line By Line
while ((strLine = br.readLine()) != null) {
String query="strLine";
int row=st.executeUpdate(strLine);
}
//Close the input stream
in.close();
}
catch (Exception e){//Catch exception if any
System.err.println("Error: " + e.getMessage());
}
}
}
]
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5770
  • View blog
  • Posts: 12,582
  • Joined: 16-October 07

Re: ORA-00904: invalid identifier

Posted 09 May 2010 - 03:39 AM

Looks like you're trying the execute SQL statements in adil.txt. The problem would be in that file.

The error is a generic, "we can't parse what you're giving us" kind of thing. Echo the lines to the screen so you can see which one throws the error.

I suspect your file has extra line feeds in it. Your code doesn't allow for that, executing each line as a complete statement.
Was This Post Helpful? 0
  • +
  • -

#5 DRMS  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 08-May 10

Re: ORA-00904: invalid identifier

Posted 09 May 2010 - 04:24 AM

THE CONTENT OF THAT FILE IS
Create table emp(EMPNO NUMBER (6),
ENAME VARCHAR2 (10),
JOB VARCHAR2 (9),
MGR NUMBER (6),
HIREDATE DATE,
SAL NUMBER (9),
COMM NUMBER (9),
DEPTNO NUMBER (4));]
Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5770
  • View blog
  • Posts: 12,582
  • Joined: 16-October 07

Re: ORA-00904: invalid identifier

Posted 09 May 2010 - 10:23 AM

So...
st.executeUpdate("ENAME VARCHAR2 (10),");


means what to a database?

Rule number one, you must pass valid SQL. The string you pass must be a complete statement.
Was This Post Helpful? 1
  • +
  • -

#7 DRMS  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 08-May 10

Re: ORA-00904: invalid identifier

Posted 09 May 2010 - 03:07 PM

Ohk 10x!
But when i pass a complete query in one line, it gives me another error "right parenthesis required" .
what shall i do to write a query from file to sql?
Was This Post Helpful? 0
  • +
  • -

#8 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5770
  • View blog
  • Posts: 12,582
  • Joined: 16-October 07

Re: ORA-00904: invalid identifier

Posted 09 May 2010 - 04:19 PM

You can check that the SQL runs in SQL Plus first. Also, I don't recall if the oracle jdbc is tolerant of semicolons.

Try something like:
String sql = "Create table EMP("
   + " EMPNO NUMBER(6),"
   + " ENAME VARCHAR2(10),"
   + " JOB VARCHAR2(9),"
   + " MGR NUMBER(6),"
   + " HIREDATE DATE,"
   + " SAL NUMBER(9),"
   + " COMM NUMBER(9),"
   + " DEPTNO NUMBER(4)"
   + " )"
   ;


Was This Post Helpful? 1
  • +
  • -

#9 DRMS  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 08-May 10

Re: ORA-00904: invalid identifier

Posted 09 May 2010 - 08:30 PM

Hello
i tried using addBatch() & it works.
Create command executed successfully from file to oracle.Now There is some problem executing 'insert'.
it giving me error" ora-01861: Literal does not match format string"

the code i tried is:-
[
Connection conn=DriverManager.getConnection("jdbc:odbc:dili","system","malasinha");
conn.setAutoCommit(false);
Statement st=conn.createStatement();
FileReader fr=new FileReader("adil.txt");
BufferedReader br=new BufferedReader(fr);
String s;
while((s=br.readLine()) !=null)
{
st.addBatch(s);
}
int[] upCounts=st.executeBatch();
conn.commit();
fr.close( );
]
and data in file for insert is:-
[
insert into emp values( 7876.0,'ADAMS','CLERK',7788.0,'1987-05-23',1100.0,0.0,20.0)
insert into emp values( 7900.0,'JAMES','CLERK',7698.0,'1981-12-03',950.0,0.0,30.0)
insert into emp values( 7902.0,'FORD','ANALYST',7566.0,'1981-12-03',3000.0,0.0,20.0)
insert into emp values( 7934.0,'MILLER','CLERK',7782.0,'1982-01-23',1300.0,0.0,10.0)
]

the problem lies with DATE column.
what to do next?
Was This Post Helpful? 0
  • +
  • -

#10 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5770
  • View blog
  • Posts: 12,582
  • Joined: 16-October 07

Re: ORA-00904: invalid identifier

Posted 10 May 2010 - 05:24 AM

[quote name='DRMS' date='09 May 2010 - 09:30 PM' timestamp='1273462239' post='1013942']
ora-01861: Literal does not match format string
[/code]

Most likely the date. You're giving it a string. Oracle will convert a strict, if it matches the default format. YYYY-MM-DD is likely the throw it. The default format is an awkward DD-MON-RR.

You two options are conforming to your default format, with no real guarantees. Or explicit conversion.
e.g.
-- insert into emp values( 7876.0,'ADAMS','CLERK',7788.0,'1987-05-23',1100.0,0.0,20.0)
insert into emp values( 7876.0,'ADAMS','CLERK',7788.0,'23-MAY-87',1100.0,0.0,20.0)
insert into emp values( 7876.0,'ADAMS','CLERK',7788.0,TO_DATE('1987-05-23','YYYY-MM-DD'),1100.0,0.0,20.0)


Was This Post Helpful? 1
  • +
  • -

#11 DRMS  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 08-May 10

Re: ORA-00904: invalid identifier

Posted 10 May 2010 - 06:34 AM

Many 10X
It really works!
i have successfully converted data from file to DB!
now i'll try for another DBs.
:)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1