Home » Developer & Programmer » Forms » ORA-01747 on item from query (Oracle 10G forms)
ORA-01747 on item from query [message #432418] Tue, 24 November 2009 01:48 Go to next message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

Hi Guys:

This is the first time to encounter this design on existing module. On a block, my items were all from table_A aside from 1 item which is from query.

The item has these properties:
Database Item = Yes
Column Name = (select name from table_B B where C.myid = table_A.myid) myid

Then I get this error upon saving changes:
ORA-01747: invalid user.table.column, table.column, or column specification


along with this query:
UPDATE table_A SET fld1=:1,fld2=:2,fld3=:3,fld4=:4,fld5=:5,(select name from table_B B where B.myid = table_A.myid)  name=:6 WHERE ROWID=:7


Where should be wrong here?
When I set Database Item = No, it saves without problem.

[Updated on: Tue, 24 November 2009 01:52]

Report message to a moderator

Re: ORA-01747 on item from query [message #432426 is a reply to message #432418] Tue, 24 November 2009 03:18 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Obviously, you can't use items that way. It either belongs to a table you've based this data block on, or not. This item does NOT belong to that table, so you can't set its "database item" property to "Yes" (as Forms won't find it in that table). Therefore, it must be a non-database item.

If you want to store certain information into it, you'll have to write some code (for example, in PRE-INSERT trigger).

Or, create a view that will contain all columns from both tables and base form block on that view. All operations should then be done on a view (which includes use of additional triggers) instead of a table.
Re: ORA-01747 on item from query [message #432430 is a reply to message #432426] Tue, 24 November 2009 03:23 Go to previous message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

Thanks Littlefoot! Smile
Previous Topic: convert single byte characters to UTF8
Next Topic: delete file from server side using webutil
Goto Forum:
  


Current Time: Wed Dec 07 04:37:32 CST 2016

Total time taken to generate the page: 0.07049 seconds