Home » Developer & Programmer » Forms » form prob
form prob [message #331921] Sun, 06 July 2008 10:55 Go to next message
acma
Messages: 20
Registered: July 2008
Location: mauritania
Junior Member

Hi everyone,
I wanna feed a table from another, but in the target table i will have the date both before and after update.

clearly: origin_tbl(ID, ref, posi)
target_tbl(id, a_ref,a_posi, ref, posi)

so, i tried to initialize the the target table ( a_ref:=origin_tbl.ref,a_posi:=origin_tbl.posi)

and i update th target table after the field update.
but i tried to insert into the target table with both pre-update and when-new-instance-block, but wasn't ok.

i hpe my problem is clear now .
thanks previously..
Re: form prob [message #331932 is a reply to message #331921] Sun, 06 July 2008 13:12 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you COMMIT?
Re: form prob [message #331997 is a reply to message #331921] Mon, 07 July 2008 03:46 Go to previous messageGo to next message
acma
Messages: 20
Registered: July 2008
Location: mauritania
Junior Member

Yeah, i did..!
Re: form prob [message #332334 is a reply to message #331997] Tue, 08 July 2008 04:02 Go to previous messageGo to next message
acma
Messages: 20
Registered: July 2008
Location: mauritania
Junior Member

any help please!
Re: form prob [message #332550 is a reply to message #332334] Tue, 08 July 2008 23:34 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
hi
try trigger WHEN-NEW-ITEM-INSTANCE or POST_CHANGE

{sorry for my mistake, becose i'm lerning english. Please, correct me if i don't right}
Re: form prob [message #332655 is a reply to message #332550] Wed, 09 July 2008 05:21 Go to previous messageGo to next message
acma
Messages: 20
Registered: July 2008
Location: mauritania
Junior Member

Thank you very much Mr. Ora_baby,

Now i have an idea.
This idea is to insert into target table directly in ON-UPDATE trigger using get_item_property() function to get the data before changing.

do you think this is good idea to solve my problem.
is ON-UPDATE the right trigger to do this INSERT.

My english is not good too Embarassed .
Re: form prob [message #332687 is a reply to message #332655] Wed, 09 July 2008 06:39 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

I would suggest you to use the POST-UPDATE trigger. The difference is .. the On-Update trigger works without commit being really applied. which means only when the block is marked for update the trigger fires. The POST-UPDATE trigger will fire after the commit has been applied for the update.
Re: form prob [message #333078 is a reply to message #332687] Thu, 10 July 2008 08:02 Go to previous messageGo to next message
acma
Messages: 20
Registered: July 2008
Location: mauritania
Junior Member

where i should declare the function 'Get_Item_Property'??..
I got this error : the fonction 'Get_Item_Property' is not declared..

[Updated on: Thu, 10 July 2008 08:23]

Report message to a moderator

Re: form prob [message #333096 is a reply to message #331921] Thu, 10 July 2008 09:01 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
get_item_property is a form built-in, you don`t have to declare it anywhere. You can check your form builder documentation on how to use it.
Re: form prob [message #333757 is a reply to message #331921] Mon, 14 July 2008 06:46 Go to previous messageGo to next message
acma
Messages: 20
Registered: July 2008
Location: mauritania
Junior Member

This error occured, and it's blocking me.
"The function 'GET_ITEM_PROPERTY' must not be used in SQL!!

anyone can help me???
  • Attachment: eroor.JPG
    (Size: 191.26KB, Downloaded 92 times)
Re: form prob [message #333758 is a reply to message #333757] Mon, 14 July 2008 06:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The error is clear, isn't it? You cannot use get_item_property in a SQL-statement.
So, pull it out of the SQL and use an intermediate variable.
Re: form prob [message #333760 is a reply to message #331921] Mon, 14 July 2008 06:51 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
Just assign it before using it in your sql.
something like

v_temp := get_item_property(...);

insert into ...
values (...,v_temp,...);
Re: form prob [message #333836 is a reply to message #333760] Mon, 14 July 2008 09:35 Go to previous messageGo to next message
acma
Messages: 20
Registered: July 2008
Location: mauritania
Junior Member

I did four tries:

1) v_temp :BAGENT.DATNAT1%TYPE := Get_Item_Property(:BAGENT.DATNAT1,Database_Value);

2)v_temp :BAGENT.DATNAT1%TYPE;
v_temp:= Get_Item_Property(:BAGENT.DATNAT1,Database_Value);

3) v_temp AGENT.DATNAT%TYPE := Get_Item_Property(:BAGENT.DATNAT1,Database_Value);

4)v_temp AGENT.DATNAT%TYPE;
v_temp:= Get_Item_Property(:BAGENT.DATNAT1,Database_Value);

AGENT : the table, BAGENT : the block

but i got this error:

Symbole '"' replaced one of the next symboles:
:=.(@%;
for 3) and 4) :
Symbole "AGENT" replaced one of the next symboles:
:=.(@%;
Symbole ":=" was substitued by "AGENT" to continue
Re: form prob [message #333842 is a reply to message #331921] Mon, 14 July 2008 10:01 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
You forgot the quote in your get_item_property command. If you check the documentation, first parameter is a varchar.

Try something like this

v_temp AGENT.DATNAT%TYPE := Get_Item_Property('BAGENT.DATNAT1',Database_Value);
Re: form prob [message #333844 is a reply to message #333842] Mon, 14 July 2008 10:10 Go to previous messageGo to next message
acma
Messages: 20
Registered: July 2008
Location: mauritania
Junior Member

the same error occurs Sad !!!
Re: form prob [message #333847 is a reply to message #331921] Mon, 14 July 2008 10:30 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
I don't know if it can make a difference, but what version of forms you are using?

I just tried it in Forms 10g and i got no compilation error.
Here is what i tried

declare 
    v_temp gav_utl_vehicule.prdass_id%type := get_item_property('gav_utl_veh.prdass_id',Database_value);
begin
    null;
end;	
Re: form prob [message #334030 is a reply to message #333847] Tue, 15 July 2008 04:28 Go to previous messageGo to next message
acma
Messages: 20
Registered: July 2008
Location: mauritania
Junior Member

Thank you very much,
Now it compiles well, but doesnt insert anything in the table!!!?

Regards!!
Re: form prob [message #334080 is a reply to message #334030] Tue, 15 July 2008 07:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It could help if you posted your changed code.
Re: form prob [message #334109 is a reply to message #334080] Tue, 15 July 2008 08:09 Go to previous messageGo to next message
acma
Messages: 20
Registered: July 2008
Location: mauritania
Junior Member

I didnt understand what do you mean!

So sorry to bother you brothers, i am begginer in PL-SQL..
Re: form prob [message #334144 is a reply to message #334109] Tue, 15 July 2008 09:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You say you changed your code, but now nothing gets inserted.
So I say: show us your new code. How else can we help you?
Post it here, enclose it with [code] and [/code] tags.
Re: form prob [message #334159 is a reply to message #334144] Tue, 15 July 2008 10:17 Go to previous messageGo to next message
acma
Messages: 20
Registered: July 2008
Location: mauritania
Junior Member

declare
v_temp0 AGENT.DATNAT%TYPE := Get_Item_Property('BAGENT.DATNAT1',Database_Value);	
v_temp1 AGENT.DTRECR%TYPE := Get_Item_Property('BAGENT.DTRECR1',Database_Value);
v_temp2 AGENT.CDCLAS%TYPE := Get_Item_Property('BAGENT.CDCLAS1',Database_Value);
v_temp3 AGENT.ECHLON%TYPE := Get_Item_Property('BAGENT.ECHLON1',Database_Value);
v_temp4 AGENT.CDPOSI%TYPE := Get_Item_Property('BAGENT.CDPOSI',Database_Value);
v_temp5 AGENT.DTPOSI%TYPE := Get_Item_Property('BAGENT.DTPOSI1',Database_Value);	
begin
-- Code Piéce 2000

	IF :BAGENT.DATNAT1=Get_Item_Property(:BAGENT.DATNAT1,Database_Value) Then
  --Champ Non Modifié
  null;
	Else --Champ Modifié
	 
INSERT INTO proto_agent_fp VALUES('2000',
:IDENT.MATRIC,
:BAGENT.REFNAT1,
:BAGENT.DATNAT1,
:IDENT.SEXEAG,
:IDENT.DTNAIS,
:IDENT.NOMAGL,
:IDENT.SITFAM,
:BAGENT.DTRECR1,
:BAGENT.DATNAT1,
:BAGENT.CDCOEM,
:BAGENT.CDCLAS1,
:BAGENT.ECHLON1,
:BAGENT.DATECH1,
:BAGENT.CDPOSI,
:BAGENT.DTPOSI,
:BAGENT.DTRECR1,
v_temp0,
:BAGENT.CDCOEM,
:BAGENT.CDCLAS1,
:BAGENT.ECHLON1,
:BAGENT.CDPOSI,
:BAGENT.DTPOSI,
'15/12/2007',
0,
0,
'12345',
'15/12/2008',
0,
'15/12/2006');
	end IF;
	--
	IF :BAGENT.DTRECR1=Get_Item_Property(:BAGENT.DTRECR1,Database_Value) Then
  --Champ Non Modifié
  null;
	Else --Champ Modifié
	 
INSERT INTO proto_agent_fp VALUES('2000',
:IDENT.MATRIC,
:BAGENT.REFNAT1,
:BAGENT.DATNAT1,
:IDENT.SEXEAG,
:IDENT.DTNAIS,
:IDENT.NOMAGL,
:IDENT.SITFAM,
:BAGENT.DTRECR1,
:BAGENT.DATNAT1,
:BAGENT.CDCOEM,
:BAGENT.CDCLAS1,
:BAGENT.ECHLON1,
:BAGENT.DATECH1,
:BAGENT.CDPOSI,
:BAGENT.DTPOSI,
v_temp1,
BAGENT.DATNAT1,
:BAGENT.CDCOEM,
:BAGENT.CDCLAS1,
:BAGENT.ECHLON1,
:BAGENT.CDPOSI,
:BAGENT.DTPOSI,
'15/12/2007',
0,
0,
'12345',
'15/12/2008',
0,
'15/12/2006');
	end IF;
--	--
-- Code Piéce 2015
-- --
IF :BAGENT.CDCLAS1=Get_Item_Property(:BAGENT.CDCLAS1,Database_Value)Then
  --Champ Non Modifié
  null;
Else --Champ Modifié

INSERT INTO proto_agent_fp VALUES('2015',
:IDENT.MATRIC,
:BAGENT.RFCLGR1,
:BAGENT.DTCLGR1,
:IDENT.SEXEAG,
:IDENT.DTNAIS,
:IDENT.NOMAGL,
:IDENT.SITFAM,
:BAGENT.DTRECR1,
:BAGENT.DATNAT1,
:BAGENT.CDCOEM,
:BAGENT.CDCLAS1,
:BAGENT.ECHLON1,
:BAGENT.DATECH1,
:BAGENT.CDPOSI,
:BAGENT.DTPOSI,
:BAGENT.DTRECR1,
:BAGENT.DATNAT1,
:BAGENT.CDCOEM,
v_temp2,
:BAGENT.ECHLON1,
:BAGENT.CDPOSI,
:BAGENT.DTPOSI,
'15/12/2007',
0,
0,
'12345',
'15/12/2008',
0,
'15/12/2006');
end IF;

IF :BAGENT.ECHLON1=Get_Item_Property(:BAGENT.ECHLON1,Database_Value) Then
  --Champ Non Modifié
  null;
Else --Champ Modifié

INSERT INTO proto_agent_fp VALUES('2015',
:IDENT.MATRIC,
:BAGENT.REFECH1,
:BAGENT.DATECH1,
:IDENT.SEXEAG,
:IDENT.DTNAIS,
:IDENT.NOMAGL,
:IDENT.SITFAM,
:BAGENT.DTRECR1,
:BAGENT.DATNAT1,
:BAGENT.CDCOEM,
:BAGENT.CDCLAS1,
:BAGENT.ECHLON1,
:BAGENT.DATECH1,
:BAGENT.CDPOSI,
:BAGENT.DTPOSI,
:BAGENT.DTRECR1,
:BAGENT.DATNAT1,
:BAGENT.CDCOEM,
:BAGENT.CDCLAS1,
v_temp3,
:BAGENT.CDPOSI,
:BAGENT.DTPOSI,
'15/12/2007',
0,
0,
'12345',
'15/12/2008',
0,
'15/12/2006');
end IF;
-- Code Piéce 2021
IF :BAGENT.CDPOSI=Get_Item_Property(:BAGENT.CDPOSI,Database_Value) Then
  --Champ Non Modifié
  null;
Else --Champ Modifié
INSERT INTO proto_agent_fp VALUES('2021',
:IDENT.MATRIC,
:BAGENT.RFPOSI1,
:BAGENT.DTPOSI1,
:IDENT.SEXEAG,
:IDENT.DTNAIS,
:IDENT.NOMAGL,
:IDENT.SITFAM,
:BAGENT.DTRECR1,
:BAGENT.DATNAT1,
:BAGENT.CDCOEM,
:BAGENT.CDCLAS1,
:BAGENT.ECHLON1,
:BAGENT.DATECH1,
:BAGENT.CDPOSI,
:BAGENT.DTPOSI1,
:BAGENT.DTRECR1,
:BAGENT.DATNAT1,
:BAGENT.CDCOEM,
:BAGENT.CDCLAS1,
:BAGENT.ECHLON1,
v_temp4,
v_temp5,
'15/12/2007',
0,
0,
'12345',
'15/12/2008',
0,
'15/12/2006');

end IF;
exception
  WHEN OTHERS THEN
       NULL;
end;

Re: form prob [message #334173 is a reply to message #334159] Tue, 15 July 2008 12:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
exception
  WHEN OTHERS THEN
       NULL;
end;



Who taught you that?
Why is that there?

Can you, before you do ANYTHING else, explain to us in very much detail what this piece of code actually does? (by "this piece of code", I mean the quoted exception-handler)
I mean it, I am very serious. Think and explain what YOU think this does.
After you did we will tell you the Big Truth.

[Updated on: Tue, 15 July 2008 12:05]

Report message to a moderator

Re: form prob [message #334375 is a reply to message #334173] Wed, 16 July 2008 05:57 Go to previous messageGo to next message
acma
Messages: 20
Registered: July 2008
Location: mauritania
Junior Member

Thank for taking care of my problem..

As i understand it, this piece of code work if any error happened in the insert above.

And i am waiting for the "BIG TRUTH".

As you saw,I am biggener in both Oracle and English.

[Updated on: Wed, 16 July 2008 05:58]

Report message to a moderator

Re: form prob [message #334392 is a reply to message #334375] Wed, 16 July 2008 06:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The exception handler will execute the code within, whenever ANY error occurs, not just with the insert, but ANY exception.
The code within is "NULL;", so guess what happens if you run into an error, ANY error...
It will just silently go on with the calling code.

When others is used wrongly in about 100% of the cases I see here at OraFAQ.

There's your Big Truth: Do not use when others until you understand why I tell you not to use it.

Re: form prob [message #334424 is a reply to message #334392] Wed, 16 July 2008 07:46 Go to previous messageGo to next message
acma
Messages: 20
Registered: July 2008
Location: mauritania
Junior Member

I deleted the exception and i replaced it by commit_form;

but no thing happens
Re: form prob [message #334428 is a reply to message #331921] Wed, 16 July 2008 07:53 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
Are you sure your trigger is executed?
Maybe you should put some trace in it to see what part of code is executed.

Tu put trace in your form use the following command:

message('Some Message here');

This will help you to debug your code.

Re: form prob [message #334433 is a reply to message #334428] Wed, 16 July 2008 08:08 Go to previous messageGo to next message
acma
Messages: 20
Registered: July 2008
Location: mauritania
Junior Member

yes, i did since long, i forgot to tell you
Re: form prob [message #334472 is a reply to message #334424] Wed, 16 July 2008 12:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
acma wrote on Wed, 16 July 2008 14:46
but no thing happens

Define "nothing". Does your cursor change into an hourglass?
Does your application hang? Do any messages that you added popup?
Which part of your code gets executed?

"Nothing happens" is what an end-user says, not what a developer says. (ok, unless really really really nothing happens, which is rarely the case)
Re: form prob [message #334583 is a reply to message #334472] Thu, 17 July 2008 04:10 Go to previous messageGo to next message
acma
Messages: 20
Registered: July 2008
Location: mauritania
Junior Member

Actually, i didnt catch anything executed!

I am thinking to treat every field in the POST-CHANGE instead to treat all in POST-UPDATE! how is it?
Re: form prob [message #334632 is a reply to message #334583] Thu, 17 July 2008 08:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
When do you thing the POST-UPDATE trigger fires? It's quite different from POST-CHANGE..
Re: form prob [message #334637 is a reply to message #334632] Thu, 17 July 2008 08:44 Go to previous messageGo to next message
acma
Messages: 20
Registered: July 2008
Location: mauritania
Junior Member

Can you explain the difference to me ??! please!

So sorry to bother you!
Re: form prob [message #334732 is a reply to message #334637] Thu, 17 July 2008 15:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No I won't
I have access to exactly the same (if not less, I have no hardcopies) documentation as you.
Time to do some research yourself. The assignment is quite easy: what's the difference between POST-UPDATE and POST-CHANGE.

What really baffles me is that you were about to switch from one to the other, apparently without even the slightest idea of what each does..
Re: form prob [message #335052 is a reply to message #334732] Sun, 20 July 2008 05:20 Go to previous messageGo to next message
acma
Messages: 20
Registered: July 2008
Location: mauritania
Junior Member

Thank you very much Mister Frank,
I give you the definition of both POST-UPDATE and POST-CHANGE

A Post-Update trigger fires during the Post and Commit Transactions
event, after a row is updated. It fires once for each row that is updated
in the database during the commit event.
-----
A Post-Change trigger fires when any of the following conditions occur:
1- the Validate the Field event determines that a field is marked
as Changed and is non-NULL
2- an operator reads a value into a field from a list of values
3- SQL*Forms reads a value into a field from a fetched record


I think from the definitions, the right choice to use in my problem is POST-UPDATE
Re: form prob [message #335064 is a reply to message #335052] Sun, 20 July 2008 11:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you say so, then it is. (anyway, that sounds more logical then a post-change)
Now did the fact that you found this yourself and that you now know and understand the difference between the two give far more satisfaction then when I simply told you which to use?
If you try to find out things by yourself, then
a) you will learn how to search
b) it is far less likely you will forget what you found.

Now, enjoy what you got and get back with any questions you may come up with.
(But remember to try to find the answers yourself first! And to assure yourself of better answers from the people here, tell us what you did to try to get to the answer)
Re: form prob [message #335202 is a reply to message #335064] Mon, 21 July 2008 07:30 Go to previous messageGo to next message
acma
Messages: 20
Registered: July 2008
Location: mauritania
Junior Member

Thank you very much for your nice recommendations,
I tried to debug the program by :
message('Post Upd old:'||v_temp0 ||' new:'||:BAGENT.DATNAT1);
message(' ');
 
IF :BAGENT.DATNAT1 = v_temp0 Then

to see if one of the element is null!

and still the message dosent appear!!!
Re: form prob [message #335214 is a reply to message #335202] Mon, 21 July 2008 08:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Excerpt from your code
v_temp0 AGENT.DATNAT%TYPE := Get_Item_Property('BAGENT.DATNAT1',Database_Value);
...
IF :BAGENT.DATNAT1=Get_Item_Property(:BAGENT.DATNAT1,Database_Value)


I see a difference there. Why is that?
Re: form prob [message #335246 is a reply to message #335214] Mon, 21 July 2008 10:41 Go to previous messageGo to next message
acma
Messages: 20
Registered: July 2008
Location: mauritania
Junior Member

It is just to simplify the code!!!

Instead to repeat the GET_ITEM_PROPERTY(:BAGENT.DATNAT1,Database_Value), it's enaugh to variable v_temp0 which contains its value.
Re: form prob [message #335255 is a reply to message #335214] Mon, 21 July 2008 11:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Frank wrote on Mon, 21 July 2008 15:35
Excerpt from your code
v_temp0 AGENT.DATNAT%TYPE := Get_Item_Property('BAGENT.DATNAT1',Database_Value);
...
IF :BAGENT.DATNAT1=Get_Item_Property(:BAGENT.DATNAT1,Database_Value)


I see a difference there. Why is that?

Reread what I say, not what you think I say.
I did not ask you why you duplicated it, I asked you why the two were different.
Re: form prob [message #335981 is a reply to message #335255] Thu, 24 July 2008 05:30 Go to previous message
acma
Messages: 20
Registered: July 2008
Location: mauritania
Junior Member

OHH, shut!
So sorry Mister Frank,
I think you mean that i should write it like this :
v_temp0 AGENT.DATNAT%TYPE := Get_Item_Property('BAGENT.DATNAT1',Database_Value);
...
IF :BAGENT.DATNAT1=Get_Item_Property('BAGENT.DATNAT1',Database_Value) 


this one was corrected in my code, just didnt take attention in the comments.
Previous Topic: how to access Parameter variables in Menus
Next Topic: Change the color of record base on condition
Goto Forum:
  


Current Time: Wed Dec 07 05:01:01 CST 2016

Total time taken to generate the page: 0.07007 seconds