| Insert to SQL Server from Oracle Forms Fails with (ORA-00904) [message #360625] |
Fri, 21 November 2008 12:06  |
akajiuwa
Messages: 3
Registered: November 2008
|
Junior Member |
|
|
Hello,
I am trying to insert data into an SQL Server based table using Oracle forms 10g.
I have successfully created the Heterogenous Services agent and configured all oracle configuration files and can confirm connection is working by
Database link to SQL Server : SQLSERVER2005
SQL> select * from student@SQLSERVER2005;
id sname
---------- --------------------------------------
1 NAME1
2 NAME2
3 NAME3
6 NAME4
SQL> insert into student@SQLSERVER2005 values (7,'NAME7');
1 row created.
SQL> select * from student@SQLSERVER2005;
id sname
---------- ------------------------------------------------
1 NAME1
2 NAME2
3 NAME3
6 NAME4
7 NAME7
But the problem is when i try inserting a record through oracle forms i get
INSERT INTO SQ_STUDENT(id,sname) VALUES (:1,:2)
ORA-00904: "SNAME": invalid identifier from the display error screen.
SQ_STUDENT is a synonym to student@SQLSERVER2005
Thanks
[Updated on: Sat, 22 November 2008 11:06] Report message to a moderator |
|
|
|
| Re: Insert to SQL Server from Oracle Forms Fails with (ORA-00904) [message #360702 is a reply to message #360625] |
Sat, 22 November 2008 13:37  |
akajiuwa
Messages: 3
Registered: November 2008
|
Junior Member |
|
|
Hello All,
Finally i got a solution to my problem and hopefully this could come in handy to anyone facing similar situation.
It all boils down to case sensitivity in Oracle.
I simply renamed all my objects and column names ins the SQLSERVER DB to UPPER CASE and it worked, not necessarily the best of options but i needed a quick fix alternatively go through this document and it will be clearer.
www.oracle.com/technology/products/forms/pdf/275201.pdf]
Take note of 6.8 Client-side PL/SQL
Cheers!
[Updated on: Sat, 22 November 2008 13:44] Report message to a moderator |
|
|
|
| Re: Insert to SQL Server from Oracle Forms Fails with (ORA-00904) [message #360710 is a reply to message #360702] |
Sun, 23 November 2008 01:12 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Thank you for the feedback.
Although you have already found the solution, here's a tip: Oracle really is case-sensitive regarding object names. It is possible to use lower or mixed case, but - in that case (case here, case there  ) - you'd have to enclose them in double quotes:SQL> create table "student"
2 (ID number,
3 "name" varchar2(20));
Table created.
SQL> insert into student (id, name) values (1, 'LF');
insert into student (id, name) values (1, 'LF')
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> insert into "student" (id, name) values (1, 'LF');
insert into "student" (id, name) values (1, 'LF')
*
ERROR at line 1:
ORA-00904: "NAME": invalid identifier
SQL> insert into "student" (id, "name") values (1, 'LF');
1 row created.
SQL>
After all, it appears that you'd rather leave Oracle to deal with such things and simply forget about upper and lower case (at least, when it comes to the naming conventions).
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
