Home » Developer & Programmer » Forms » Insert to SQL Server from Oracle Forms Fails with (ORA-00904) (ORacle 10g R2 and SQL Server 2005)
Insert to SQL Server from Oracle Forms Fails with (ORA-00904) [message #360625] Fri, 21 November 2008 12:06 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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 Smile) - 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).
Re: Insert to SQL Server from Oracle Forms Fails with (ORA-00904) [message #362520 is a reply to message #360710] Tue, 02 December 2008 11:21 Go to previous message
akajiuwa
Messages: 3
Registered: November 2008
Junior Member
Thanks a lot littlefoot.

Cheers
Previous Topic: how to open downloaded file
Next Topic: grey screen
Goto Forum:
  


Current Time: Sat Dec 10 22:45:31 CST 2016

Total time taken to generate the page: 0.07848 seconds