Solved

Updating a data block's default query in Oracle Forms

Posted on 2012-03-29
8
3,154 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 125 total points
Comment Utility
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 250 total points
Comment Utility
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 125 total points
Comment Utility
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
 

Author Comment

by:sjenks
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Expert Comment

by:HamidHelal
Comment Utility
From the object navigator, look for the item.
0
 
LVL 20

Accepted Solution

by:
flow01 earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

771 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now