?
Solved

Updating a data block's default query in Oracle Forms

Posted on 2012-03-29
8
Medium Priority
?
3,581 Views
Last Modified: 2012-08-14
I am trying to edit an Oracle Form.  I am using Oracle Form Builder v. 5, with Oracle 8.  I removed a column of a table in the database.  I went into the Form and used the Data Block Wizard to refresh the table items.  I removed the code from the Triggers that referenced that field of the table.  But when I run the form, I am getting an error "unable to perform query' and when I look at the Error, it shows the query is still requesting that field of the table.  In the WHEN-NEW-FORM-INSTANCE, this is the code:

  where_clause := 'rec_id  = '||''''|| :parameter.record_id ||'''';
 
    Set_Block_property(block_id, default_where, where_clause);
    Execute_query;

Where is it getting the query?  Why does the query still show the field that I removed?  How can I update the default query?
0
Comment
Question by:sjenks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 375 total points
ID: 37783280
In the Wizard you have to remove the field, behind which is the removed table column.
Go the that field Property palette and find the appropriate column of the table. Or remove the field from the list of the fields (irtems) or from canvas.
0
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 750 total points
ID: 37783290
Do you get the error immediate at startup of the form ?
Did you see the query in error (show error key or menu option) > 
is the removed column reference in the select or in the were part  ?
If in the select  > check the items of the block for an item with the column_name  or an item with another name but 'column name'-property with the column_name (maybe only in later versions of forms) that is marked as a database item.
If in the where > check the block-property default_where  and for other references to set_block_property where that might change the where clause.
0
 

Assisted Solution

by:HamidHelal
HamidHelal earned 375 total points
ID: 37783483
I went into the Form and used the Data Block Wizard to refresh the table items.

It doesn't work for removing item from block at form.
You need to manually identify the item in respective block navigator and remove the item.

Hope this works..
0
Independent Software Vendors: 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!

 

Author Comment

by:sjenks
ID: 37783516
schwertner: I have removed the field in the Data  Block Wizard, under the Items for the Data Block, and from the Layout Screen.

flow01: I get the error immediately at the startup of the form.  I see the query under the 'show error keys' selected from the Help menu.  The removed column is in the select list of the query.  I have checked all of the items, nothing refers to that column name.
0
 

Expert Comment

by:HamidHelal
ID: 37783546
From the object navigator, look for the item.
0
 
LVL 20

Accepted Solution

by:
flow01 earned 750 total points
ID: 37787895
Is it the only block referencing the query ?
Are you running the form from the formsbuilder ?
Can you verify  (for example by adding a
message('new version ',acknowledge);
in the when-new-form-instance trigger that you are running your changed form ?
0
 

Author Comment

by:sjenks
ID: 37788326
I tried running the form from the form builder, but it just hangs.  I have to build the whole project to run the form.  

I added a 'new version' message to the when-new-form-instance trigger, and I did not get that message when I ran the progarm. So that is telling me that it is not even getting to the when-new-form-instance trigger.  I am looking at the code in the previous screen.  When I click on the button to go to the next form, it calls the load_form program unit which has this code:

PROCEDURE load_form(form_name char, record_value number) IS
  pl_id   ParamList;
  pl_name VARCHAR2(13) := 'change';
begin
  pl_id := Get_Parameter_List(pl_name);
  IF Id_Null(pl_id) THEN
    pl_id := Create_Parameter_List(pl_name);
    IF Id_Null(pl_id) THEN
      Message('Error creating parameter list '||pl_name);
      RAISE Form_Trigger_Failure;
    end if;
  else
       Delete_Parameter(pl_id,'RECORD_ID');
       Delete_Parameter(pl_id,'E_DATE');
  end if;
  Add_Parameter(pl_id, 'RECORD_ID', TEXT_PARAMETER, record_value);        
  Add_Parameter(pl_id, 'E_DATE', TEXT_PARAMETER, :parameter.e_date);        
/*
** Now call the form, referencing the parameter list ID
** in the last argument to the CALL_FORM procedure
*/
   Open_Form(:parameter.form_location || form_name, ACTIVATE, NO_SESSION, pl_id);
end;

So where is the call to the query for the data being created?
0
 

Author Comment

by:sjenks
ID: 37788481
Ok. I got it.  The form_location passed in to the Open_Form Program unit was hard coded to the location of the production code environment.  I was making my changes in a development environment.

flow01: Thank you for recommending the debug messages that led me to see that I was looking at the wrong form.  Wow.  This was great!

Thanks everyone for your help.
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

764 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