OraFAQ Forum: Forms » ORA-01747 on item from query

Home » Developer & Programmer » Forms » ORA-01747 on item from query (Oracle 10G forms)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01747 on item from query [message #432418]

Tue, 24 November 2009 01:48

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

Littlefoot
Messages: 21818
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.

Report message to a moderator

Re: ORA-01747 on item from query [message #432430 is a reply to message #432426]

Tue, 24 November 2009 03:23

wency
Messages: 450
Registered: April 2006
Location: Philippines

Senior Member

Thanks Littlefoot! Smile

Report message to a moderator

Previous Topic:	convert single byte characters to UTF8
Next Topic:	delete file from server side using webutil

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 09:32:27 CDT 2024