Home » Developer & Programmer » Forms » query-trigger problem
query-trigger problem [message #538217] |
Wed, 04 January 2012 13:11 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/47f6c5976516f84b8ebf8c38f0247b8b?s=64&d=mm&r=g) |
eddyno
Messages: 20 Registered: January 2012 Location: Beograd
|
Junior Member |
|
|
Hi,
I'm new to oracle forms, so I have a problem. I have a form called Nalog, with RBr,ID, Ulice... text fields,not in tabelar view, i preffered form view. Problem is how to display one row by entering ID of that row in the ID text item, normaly before execute query. I tried to put post-text-item trigger in the ID item, and query like
select nalog.datum, nalog.vozac into nalog from nalog where ID = '% %';
but it reports trigger unhanled exception. Could you help me how to do this. Thanks
[Updated on: Wed, 04 January 2012 13:16] Report message to a moderator
|
|
|
Re: query-trigger problem [message #538228 is a reply to message #538217] |
Wed, 04 January 2012 14:25 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
1) Please read and follow How to use [code] tags and make your code easier to read?
2) The exact error number would be helpful.
2) That code shouldn't compile since you're selecting two columns but listing one item in the into (is that the name of the datablock?). You must have as many items in the into as in the select.
3) That where clause looks like it'll return more than one row leading to a too_many_rows error.
4) Why are you writing this code at all? Why not just use the default query mechanism?
|
|
|
Re: query-trigger problem [message #538236 is a reply to message #538228] |
Wed, 04 January 2012 15:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
And you are using an equal sign, yet you are trying to match a pattern. Either use LIKE use percentage signs.
In any event, what cookiemonster says still applies; more than one row returned will give a TOO_MANY_ROWS exception.
|
|
|
Re: query-trigger problem [message #538237 is a reply to message #538228] |
Wed, 04 January 2012 16:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/47f6c5976516f84b8ebf8c38f0247b8b?s=64&d=mm&r=g) |
eddyno
Messages: 20 Registered: January 2012 Location: Beograd
|
Junior Member |
|
|
I changed code in:
select nalog.vozac, nalog.datum
into :nalog.vozac, :nalog.datum from nalog
where nalog.radni_nalog_br = ' ';
It reports: FRM-40735: POST-TEX-ITEM trigger reports unhanled exception ORA-01722.
I think that it should displau row with number of radni_nalog_br??
Yeah, nalog is block, datum and vocac are items.
Query mechanism that works when table is empty-before execution query, when form is started, i would like to know how to do that.
I tried to use like instead '=':
select nalog.vozac, nalog.datum
into :nalog.vozac, :nalog.datum from nalog
where radni_nalog_br like '% %';
FRM-40735: POST-TEX-ITEM trigger reports unhanled exception ORA-01403.
[Updated on: Wed, 04 January 2012 16:16] Report message to a moderator
|
|
|
Re: query-trigger problem [message #538241 is a reply to message #538237] |
Wed, 04 January 2012 16:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Default Forms querying mechanism (on data blocks) works as follows:
- enter query mode
- enter search criteria (for example, enter some ID into the ID item)
- execute query
That's all you need; why do you think you MUST write your own code to emulate a feature that works fine for decades?
Anyway: if you want to refer an item, you have to specify it. These lineswhere nalog.radni_nalog_br = ' ';
where radni_nalog_br like '% %'; don't do that, butwhere nalog.radni_nalog_br = :nalog.id; orwhere nalog.radni_nalog_br like :nalog.id || '%' is something different.
Besides, is equal to If that attempt returned NO-DATA-FOUND (which is ORA-01403), there's no row in NALOG table that matches the WHERE condition.
Now, you really need to specify what you are doing and what you have (a control block, a database block, which items are database ones, which are not, how exactly would you want to fetch data (what keys/buttons to press) etc.). Re-read Cookiemonster's reply once again. More details you specify, faster and more accurate answer you'll get.
|
|
|
Re: query-trigger problem [message #538242 is a reply to message #538241] |
Wed, 04 January 2012 17:21 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/47f6c5976516f84b8ebf8c38f0247b8b?s=64&d=mm&r=g) |
eddyno
Messages: 20 Registered: January 2012 Location: Beograd
|
Junior Member |
|
|
Thank you very much,
where nalog.radni_nalog_br = :radni_nalog.br;
that was what I actually needed. But now i can't list other rows in table?Why I wrote '= '% %' i don't know. The : sign is actually a bind variable, but i didn't remember what that variable holds. Now it fetch values but ask me to save those same values??
Database block is 'nalog', all items are datebase items, i mean they are in datablock nalog containing values i gave. I would like not to fetch just display other values in row- data simply by entering value of the nalog_br or something else, which i have in database, now when i enter some number in text item it displays when i press ENTER, of course i use post-text-item trigger.
Sorry to ask again, but where i should enter search enter query and search criteria? In the button enter query above or...?
[Updated on: Wed, 04 January 2012 17:37] Report message to a moderator
|
|
|
Re: query-trigger problem [message #538252 is a reply to message #538242] |
Thu, 05 January 2012 00:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Quote:Now it fetch values but ask me to save those same values??
Yes, because the way you fetched these records (using SELECT ... INTO) is interpreted as "hey, he entered some new records and now he wants to leave that screen. So ask him: do you want to save changes you made?"
You may try to "Save". Two outcomes are possible: if there are unique/primary keys on a table, INSERT (which is what would "Save" do in that case) would fail because of constraints' violation. If there are no unique/primary keys, a record would be saved but you'd then have two duplicate records.
That's why you shouldn't use that way of data fetch.
Quote:where i should enter search enter query and search criteria?
Run the form; it is empty. There are 3 ways I can think of to enter query mode:
- toolbar icon (save, print, ..., ENTER QUERY, EXECUTE QUERY, CANCEL QUERY, previous block, ...)
- Query menu (action, edit, QUERY, block, ...)
- keyboard shortcut (such as F7 - enter query mode, F8 - execute query, Ctrl + Q - cancel query mode)
Suppose you want to query all records where "Eddy No" was a driver: click ENTER QUERY green button on the toolbar. Enter "Eddy No" (without quotes!) into the VOZAC item. Click EXECUTE QUERY green button on the toolbar. The first record shows up. Scroll through all of them using UP/DOWN keys on the keyboard or PREVIOUS/NEXT RECORD buttons on the toolbar.
|
|
|
Re: query-trigger problem [message #538372 is a reply to message #538252] |
Thu, 05 January 2012 10:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/47f6c5976516f84b8ebf8c38f0247b8b?s=64&d=mm&r=g) |
eddyno
Messages: 20 Registered: January 2012 Location: Beograd
|
Junior Member |
|
|
[quote title=Littlefoot wrote on Thu, 05 January 2012 07:34]Quote:Now it fetch values but ask me to save those same values??
Yes, because the way you fetched these records (using SELECT ... INTO) is interpreted as "hey, he entered some new records and now he wants to leave that screen. So ask him: do you want to save changes you made?"
You may try to "Save". Two outcomes are possible: if there are unique/primary keys on a table, INSERT (which is what would "Save" do in that case) would fail because of constraints' violation. If there are no unique/primary keys, a record would be saved but you'd then have two duplicate records.
That's why you shouldn't use that way of data fetch.
Ok, as you said, when i choose to save i have double records, and put the record somewhere close to last record even if i don't have a primary/unique keys, still it insert's double record's, and again i can't do same query with same number i choose to display, because it has a two same numbers. Wondering now if i could use insert instead select, same result maybe.
Anyway seems that enter query mode is the best solution, but i would like to implement enter query, entering eddy no, end execute_query in some button or some triggger in a text field
[Updated on: Thu, 05 January 2012 10:59] Report message to a moderator
|
|
|
|
Re: query-trigger problem [message #538412 is a reply to message #538386] |
Thu, 05 January 2012 18:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/47f6c5976516f84b8ebf8c38f0247b8b?s=64&d=mm&r=g) |
eddyno
Messages: 20 Registered: January 2012 Location: Beograd
|
Junior Member |
|
|
- identifier 'ONETIME_WHERE' must be declared
I'm using Oracle forms 6, i thing 6 doesn't have 'onetime_where'. Any other way instead of onetime_where??
I made a two buttons, one for unos(enter_query), another for search(go_block; execute_query;). I would like to make it with just one button, or without buttons, but enter_query cannot be with-before execute_query.
[Updated on: Thu, 05 January 2012 19:09] Report message to a moderator
|
|
|
Re: query-trigger problem [message #538456 is a reply to message #538412] |
Fri, 06 January 2012 03:37 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
There's the default_where property but you would have to clear it afterwards.
However if you want the user to enter a value then press a single button to do all the querying then you are going to have to add a seperate non-database datablock for the user to enter the seach criteria in. Otherwise forms is going to think you're adding a new record. Unless you are in enter-query mode - and if you're making the users go into enter-query mode you might as well not bother adding any buttons and just teach them to use the default functionality.
|
|
|
Goto Forum:
Current Time: Sun Sep 08 07:21:08 CDT 2024
|