OraFAQ Forum: Forms » form prob

Home » Developer & Programmer » Forms » form prob

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

form prob [message #331921]

Sun, 06 July 2008 10:55

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

Report message to a moderator

Re: form prob [message #331932 is a reply to message #331921]

Sun, 06 July 2008 13:12

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

Senior Member
Account Moderator

Did you COMMIT?

Report message to a moderator

Re: form prob [message #331997 is a reply to message #331921]

Mon, 07 July 2008 03:46

acma
Messages: 20
Registered: July 2008
Location: mauritania

Junior Member

Yeah, i did..!

Report message to a moderator

Re: form prob [message #332334 is a reply to message #331997]

Tue, 08 July 2008 04:02

acma
Messages: 20
Registered: July 2008
Location: mauritania

Junior Member

any help please!

Report message to a moderator

Re: form prob [message #332550 is a reply to message #332334]

Tue, 08 July 2008 23:34

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}

Report message to a moderator

Re: form prob [message #332655 is a reply to message #332550]

Wed, 09 July 2008 05:21

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

Embarassed

.

Report message to a moderator

Re: form prob [message #332687 is a reply to message #332655]

Wed, 09 July 2008 06:39

mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait

Senior Member

mudabbir.tungekar@gmail.com

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.

Report message to a moderator

Re: form prob [message #333078 is a reply to message #332687]

Thu, 10 July 2008 08:02

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

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.

Report message to a moderator

Re: form prob [message #333757 is a reply to message #331921]

Mon, 14 July 2008 06:46

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 614 times)

Report message to a moderator

Re: form prob [message #333758 is a reply to message #333757]

Mon, 14 July 2008 06:49

Frank
Messages: 7901
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.

Report message to a moderator

Re: form prob [message #333760 is a reply to message #331921]

Mon, 14 July 2008 06:51

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,...);

Report message to a moderator

Re: form prob [message #333836 is a reply to message #333760]

Mon, 14 July 2008 09:35

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

Report message to a moderator

Re: form prob [message #333842 is a reply to message #331921]

Mon, 14 July 2008 10:01

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);

Report message to a moderator

Re: form prob [message #333844 is a reply to message #333842]

Mon, 14 July 2008 10:10

acma
Messages: 20
Registered: July 2008
Location: mauritania

Junior Member

the same error occurs Sad

Sad

!!!

Report message to a moderator

Re: form prob [message #333847 is a reply to message #331921]

Mon, 14 July 2008 10:30

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;

Report message to a moderator

Re: form prob [message #334030 is a reply to message #333847]

Tue, 15 July 2008 04:28

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!!

Report message to a moderator

Re: form prob [message #334080 is a reply to message #334030]

Tue, 15 July 2008 07:03

Frank
Messages: 7901
Registered: March 2000

Senior Member

It could help if you posted your changed code.

Report message to a moderator

Re: form prob [message #334109 is a reply to message #334080]

Tue, 15 July 2008 08:09

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

Report message to a moderator

Re: form prob [message #334144 is a reply to message #334109]

Tue, 15 July 2008 09:27

Frank
Messages: 7901
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.

Report message to a moderator

Re: form prob [message #334159 is a reply to message #334144]

Tue, 15 July 2008 10:17

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;

Report message to a moderator

Re: form prob [message #334173 is a reply to message #334159]

Tue, 15 July 2008 12:04

Frank
Messages: 7901
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

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

Frank
Messages: 7901
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.

Report message to a moderator

Re: form prob [message #334424 is a reply to message #334392]

Wed, 16 July 2008 07:46

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

Report message to a moderator

Re: form prob [message #334428 is a reply to message #331921]

Wed, 16 July 2008 07:53

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.

Report message to a moderator

Re: form prob [message #334433 is a reply to message #334428]

Wed, 16 July 2008 08:08

acma
Messages: 20
Registered: July 2008
Location: mauritania

Junior Member

yes, i did since long, i forgot to tell you

Report message to a moderator

Re: form prob [message #334472 is a reply to message #334424]

Wed, 16 July 2008 12:07

Frank
Messages: 7901
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)

Report message to a moderator

Re: form prob [message #334583 is a reply to message #334472]

Thu, 17 July 2008 04:10

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?

Report message to a moderator

Re: form prob [message #334632 is a reply to message #334583]

Thu, 17 July 2008 08:12

Frank
Messages: 7901
Registered: March 2000

Senior Member

When do you thing the POST-UPDATE trigger fires? It's quite different from POST-CHANGE..

Report message to a moderator

Re: form prob [message #334637 is a reply to message #334632]

Thu, 17 July 2008 08:44

acma
Messages: 20
Registered: July 2008
Location: mauritania

Junior Member

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

So sorry to bother you!

Report message to a moderator

Re: form prob [message #334732 is a reply to message #334637]

Thu, 17 July 2008 15:28

Frank
Messages: 7901
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..

Report message to a moderator

Re: form prob [message #335052 is a reply to message #334732]

Sun, 20 July 2008 05:20

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

Report message to a moderator

Re: form prob [message #335064 is a reply to message #335052]

Sun, 20 July 2008 11:40

Frank
Messages: 7901
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)

Report message to a moderator

Re: form prob [message #335202 is a reply to message #335064]

Mon, 21 July 2008 07:30

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!!!

Report message to a moderator

Re: form prob [message #335214 is a reply to message #335202]

Mon, 21 July 2008 08:35

Frank
Messages: 7901
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?

Report message to a moderator

Re: form prob [message #335246 is a reply to message #335214]

Mon, 21 July 2008 10:41

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.

Report message to a moderator

Re: form prob [message #335255 is a reply to message #335214]

Mon, 21 July 2008 11:28

Frank
Messages: 7901
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.

Report message to a moderator

Re: form prob [message #335981 is a reply to message #335255]

Thu, 24 July 2008 05:30

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.

Report message to a moderator

Previous Topic:	how to access Parameter variables in Menus
Next Topic:	Change the color of record base on condition

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 09 07:20:38 CST 2025