OraFAQ Forum: Forms » query-trigger problem

Home » Developer & Programmer » Forms » query-trigger problem

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

query-trigger problem [message #538217]

Wed, 04 January 2012 13:11

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

cookiemonster
Messages: 13926
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?

Report message to a moderator

Re: query-trigger problem [message #538236 is a reply to message #538228]

Wed, 04 January 2012 15:56

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.

Report message to a moderator

Re: query-trigger problem [message #538237 is a reply to message #538228]

Wed, 04 January 2012 16:11

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

Littlefoot
Messages: 21813
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 lines

where nalog.radni_nalog_br = ' ';
where radni_nalog_br like '% %';

don't do that, but

where nalog.radni_nalog_br = :nalog.id;

where nalog.radni_nalog_br like :nalog.id || '%'

is something different.

Besides,

like '% %'

is equal to

like '%'

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.

Report message to a moderator

Re: query-trigger problem [message #538242 is a reply to message #538241]

Wed, 04 January 2012 17:21

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 '= '% %' Smile

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

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

Report message to a moderator

Re: query-trigger problem [message #538372 is a reply to message #538252]

Thu, 05 January 2012 10:53

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 #538386 is a reply to message #538372]

Thu, 05 January 2012 12:46

Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You could try the following: create a KEY-NEXT-ITEM trigger on the VOZAC item. Its code would be

set_block_property('nalog', onetime_where ,'vozac = :nalog.vozac');
execute_query;

- run the form
- enter "Eddy No" (without quotes) into the VOZAC item
- press <Enter> (it will cause KEY-NEXT-ITEM to fire)

Hopefully, record(s) where Eddy No was a driver will be displayed on the screen.

Report message to a moderator

Re: query-trigger problem [message #538412 is a reply to message #538386]

Thu, 05 January 2012 18:16

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. Sad

[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

cookiemonster
Messages: 13926
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.

Report message to a moderator

Previous Topic:	Error While running Oracle Look and feel Template Form - (LAF_TEMPLATE)
Next Topic:	Showing message when canvas changing

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 10 01:10:19 CDT 2024