Error in File to Jdbc.

[Former Member]

Hi all

I am trying to do a simple File to Jdbc scenario.table has 2 fields empno and empname.i am able to insert records into the database successfull without any data in empname ,but when trying to insert a record with value in empname it is giving me the error " Error when executing statement for table/stored proc. 'EMPLOYEE' (structure 'STATEMENT'): java.sql.SQLException: ORA-00984: column not allowed here ".

empname in database is of type varchar and length 100

and in IR it is of type string.

plz help me in resolving the issue

<It is against the rules of Engagement to post a question with the catch of awarding points. Please refrain from the same.>

Regards

Bhasker

Edited by: Bhavesh Kantilal on Dec 3, 2008 10:26 AM

[dharamveer_gaur2 · Accepted solution]

Hi

Check your generated SQL query format is correct

At runtime you can find the genereated sql statements by doing configuraitons in Receiver JDBC adapter.

In the JDBC Receiver adapter you have the Advanced Properties .

Over there enter the following

left column logSQLStatement

right column true

To see the query created ..

Login to adapter monitoring ..select the relevant jdbc adapter.

Now when any message is processed by the jdbc adapter in adapter monitoring at that time you will see a message link. When you click on that link a new window will open. In that window if you click on page down you will get to see the sql statement generated by the jdbc

[Former Member]

Hi,

I think most possibly, you have the incorrect table definition of your DB table, such as the inconsistent field name of emp_name, pls check whether it is same with the field name in your message type also lower or upper case.

regards,

Erick

[Former Member]

Hi,

I think your XML structure is not correkt. It should look like this one:

regards, Björn

Edited by: Björn Bokelmann on Dec 3, 2008 4:07 PM

Edited by: Björn Bokelmann on Dec 3, 2008 4:08 PM

[Former Member]

Hi all

Thank u for response and feedback.

my xml payload in adapter engine looks like this:

<?xml version="1.0" encoding="UTF-8" ?>

- <ns0:employee_MT xmlns:ns0="http://filetojdbc.com/employee">

- <STATEMENT>

- <EMPLOYEE action="INSERT">

<table>EMPLOYEE</table>

- <access>

<EMP_NO>2548</EMP_NO>

<EMP_NAME>abc</EMP_NAME>

</access>

</EMPLOYEE>

</STATEMENT>

</ns0:employee_MT>

is anything wrong in this payload.?

as mentioned earlier i am able to insert data without the any value in <EMP_NAME>

please help me in resolve this issue.

Regards

Bhasker

[Former Member]

Hi Bhaskar,

First try to insert the data in Database directly and check whether you are gettting any errors overthere?

And also check the generated SQL Statement like Dharmaveer said.

Regards

Goli Sridhar

[Former Member]

Hi all

Thank u for all responses.

problem is solved.Database should be accessed with username.database table name and i mapped this to the table field.

now it is accepting values in empname field

thank u Dharamveer Gaur u r tip really helped me.

thank u one and all

regards

bhasker

[Former Member]

Generated insert stmt is incorrect. can you give us the message structure and mapping used..

[GabrielSagaya]

An ORA-00984 will be thrown if a column name (like in the VALUES clause of an INSERT statement), is used in an expression where it is not permitted. You may have used a column name in an expression where it is not permitted. Typically, ORA-00984 occurs while including a column name in the VALUES clause of an INSERT statement.

To correct ORA-00984, you simply need to view the syntax of the SQL statement and only use column names where they are appropriate.

You may also find it appropriate to include a character value, in the INSERT statement, instead of the column name.

http://ora-00984.ora-code.com/