[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to do this in PL/SQL?

Posted on 2009-02-09
12
Medium Priority
?
614 Views
Last Modified: 2013-12-19
This is maybe simple, I have a form(block). One of the fields named batch# shows -1 for some product_ids.

I want to do
if the front-end interface the field batch#= -1, the front-end interface batch# field will be displayed as blank. But the nothing changed to the back-end batch#= -1 in date if I query it.

I have an On-dated trigger like blow, how to modify or there is any simple way to do that, which trigger?

How to
.....
if :media_request_info.batch_number='-1' then
	:media_request_info.batch_number = '';
else
	,xuid.ship_batch_id =:media_request_info.batch_number;
end if;
.....

Open in new window

0
Comment
Question by:wasabi3689
  • 6
  • 3
  • 3
12 Comments
 
LVL 22

Accepted Solution

by:
Helena Marková earned 240 total points
ID: 23598753
I don't know ON-DATED trigger in Oracle Forms, it seems, that it is user-defined trigger.
However you can add another block items to the code:
if :media_request_info.batch_number='-1' then
      :media_request_info.batch_number = '';
:media_request_info.another_item_1:='';
...
:media_request_info.another_item_n:='';
else
      ,xuid.ship_batch_id =:media_request_info.batch_number;
end if;

Or you can use POST-QUERY trigger:
if :media_request_info.batch_number='-1' then
      :media_request_info.batch_number = '';
:media_request_info.another_item_1:='';
...
:media_request_info.another_item_n:='';
...
end if;
SET_RECORD_PROPERTY(:system.trigger_record, :system.trigger_block, STATUS, QUERY_STATUS);
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 240 total points
ID: 23603264
Be careful with using a Post-Query trigger to change the value!  If you do that and the user saves the record, this value will be changed in the back-end database!

I don't recognize that trigger name (On-dated) as being a valid Oracle Forms trigger.  Do you mean: "on-update"?  That is too late though, since you want this to happen post-query.

Another option that may be safer: create a named Visual Attribute in the form that is very similar to the Visual Attribute used by this field (:media_request_info.batch_number) but set the Foreground Color to the same color as the Background Color.  That can be used to make the contents of the field invisible.  Then in the Post-Query trigger use: Set_Item_Instance_Property to assign this named visual attribute to the field if the value = "-1".  You maywant to add a when-validate-item trigger to set the named visual attribute back to the standard one if the user changes this to something other than -1 (so they can see what they did).
0
 

Author Comment

by:wasabi3689
ID: 23603400
I don't want to save blank in the back end. All I need is when -1, invisible, other numbers will be shown.

in the Post-Query trigger use: Set_Item_Instance_Property to assign this named visual attribute to the field if the value = "-1".

how to write this line in Post-Query trigger?

 You may want to add a when-validate-item trigger to set the named visual attribute back to the standard one if the user changes this to something other than -1 (so they can see what they did).

how to write this in Post-Query trigger?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 240 total points
ID: 23604173
"how to write this line in Post-Query trigger?"

Here is part of a Post-Qurty trigger in our of our Forms that uses:  set_item_instance_property

declare
    v_attrib varchar2(30);
begin
if :edi_bmw_detail.bmw_date > (:control.today + 14) then
    v_attrib := 'DATA_BLUE';
elsif :edi_bmw_detail.bmw_date >= (:control.today + 7) then
    if :edi_bmw_detail.bmw_qty = 0 then
      v_attrib := 'DATA_ROSE';
    else
      v_attrib := 'DATA_OK';
    end if;
  elsif :edi_bmw_detail.bmw_date < (:control.today + 7) then
    v_attrib := 'DATA_ROSE';
  else
    null;
  end if;
  set_item_instance_property('edi_bmw_detail.bmw_date',current_record,visual_attribute,v_attrib);
...

"how to write this in Post-Query trigger?"
No, you don't write a when-validate-item trigger in a Post-Query trigger.  Those are separate triggers.  In the when-validate-item trigger you just call: set_item_instance_property to set the value back to the standard named visual attribute.
0
 

Author Comment

by:wasabi3689
ID: 23604772
First, I have this

Compiling POST-QUERY trigger on MEDIA_REQUEST_INFO block...
Compilation error on POST-QUERY trigger on MEDIA_REQUEST_INFO block:
PL/SQL ERROR 313 at line 31, column 3
'SET_ITEM_INSTANCE_PROPERTY' not declared in this scope
PL/SQL ERROR 0 at line 31, column 3
Statement ignored

Second, after I changed line to

 SET_ITEM_PROPERTY('media_request_info.batch_number',current_record, visual_attribute,v_attrib);

I can create the form. But, I don't see the data. I have error message in the toolbar
"frm-40735:post query trigger raised unhandled exception VALUE_ERROR"

Here is the code I modified in Post-query trigger




 
declare
    v_attrib varchar2(30);
begin
	if :media_request_info.batch_number= -1 then
    		v_attrib := ' ';
 
	end if;
 
  SET_ITEM_PROPERTY('media_request_info.batch_number',current_record, visual_attribute,v_attrib);
 
end;

Open in new window

0
 

Author Comment

by:wasabi3689
ID: 23604785
my oracle form version is 11.0.300
0
 

Author Comment

by:wasabi3689
ID: 23606854
I try



if :media_request_info.batch_number = -1 then
      set_item_property(':media_request_info.batch_number',visible,property_false);


end if;

I got a message

'frm-41045 cannot find item: invalid ID'
0
 
LVL 22

Assisted Solution

by:Helena Marková
Helena Marková earned 240 total points
ID: 23608628
This
set_item_property(':media_request_info.batch_number',visible,property_false);
ought to be:
set_item_property('media_request_info.batch_number',visible,property_false);

This is wrong:
SET_ITEM_PROPERTY('media_request_info.batch_number',current_record, visual_attribute,v_attrib);
ought to be:
SET_ITEM_PROPERTY('media_request_info.batch_number',visual_attribute,v_attrib);

0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 240 total points
ID: 23610876
I haven't worked in Forms11 yet, so maybe the "SET_ITEM_INSTANCE_PROPERTY" built-in that was available in earlier versions of Forms has been dropped.  If that's true, that's too bad.   I don't think you want to use SET_ITEM_PROPERTY, because this will apply to all records in the block, not just the current record.  Check your documentation for Oracle Forms11 to see if SET_ITEM_INSTANCE_PROPERTY is still supported, or not.
0
 
LVL 22

Assisted Solution

by:Helena Marková
Helena Marková earned 240 total points
ID: 23610981
As I know SET_ITEM_INSTANCE_PROPERTY is still there, but asker has changed
SET_ITEM_INSTANCE_PROPERTY('media_request_info.batch_number',current_record, visual_attribute,v_attrib);
to
SET_ITEM_PROPERTY('media_request_info.batch_number',current_record, visual_attribute,v_attrib);
without changing number of arguments.
0
 

Author Comment

by:wasabi3689
ID: 23612816
solution is


      if :media_request_info.batch_number= -1 then
            v_attrib := 'white_prompt';
      end if;
      
      SET_ITEM_PROPERTY('media_request_info.batch_number',visual_attribute,v_attrib);
0
 

Author Closing Comment

by:wasabi3689
ID: 31544735
Thank you so much.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses
Course of the Month19 days, 11 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question