?
Solved

Inserting new record in Oracle forms

Posted on 2006-04-10
10
Medium Priority
?
1,451 Views
Last Modified: 2008-01-09
I am new to Oracle forms and am faced with a problem. For records of a certain type, instead of updating the current record with the values in the form items, I am creating a new record in the table with the same values, but with a new sequence number (the primary key is a combination of the code and sequence number) . I can get this to work by using an insert statement in the on-update trigger at the form level. The table is updated, however,the form does not show this new version of the record. It still shows the previous one. How can I get the form to show this recently inserted record? I could set the where clause in the block property to the code and sequence number of the new record and do an execute_query so it displays on the form, but then the user would not be able to scroll up and down to view the other records.
0
Comment
Question by:geeta_m9
  • 5
  • 3
  • 2
10 Comments
 
LVL 12

Expert Comment

by:jwahl
ID: 16425003
what's your default_where in the block?
if  you want to display old records AND the new one (inserted by update) make sure that both criterias are included in the default_where after commiting.
0
 
LVL 13

Expert Comment

by:riazpk
ID: 16427015
t_where_pos := INSTR(UPPER(Get_Block_Property('BLK',LAST_QUERY)),'WHERE') + 6;
t_order_pos := INSTR(UPPER(Get_Block_Property('BLK',LAST_QUERY)),'ORDER') - 1;

Set_Block_Property('BLK',DEFAULT_WHERE,
                   SUBSTR(Get_Block_Property('BLK',LAST_QUERY),t_where_pos,(t_order_pos - t_where_pos))||'
                             OR (INVOICE_NO=<YourInvoioceNo>)'));
0
 

Author Comment

by:geeta_m9
ID: 16429433
Jwahl, I can display the old records and the new one, but  the recently inserted record must be showing on the form, and not some other record.
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!

 

Author Comment

by:geeta_m9
ID: 16429444
Riazpk, I don't understand your solution. What do the +6 and -1 do?
0
 
LVL 13

Assisted Solution

by:riazpk
riazpk earned 800 total points
ID: 16433407
I am getting previously executed query (without WHERE and ORDER by keywords) and adding an OR clause (including an invoice number) in order to display newly-inserted record + old records (so that user can navigate through the records).

t_where_pos := INSTR(UPPER(Get_Block_Property('BLK',LAST_QUERY)),'WHERE') + 6;

should be:

t_where_pos := INSTR(UPPER(Get_Block_Property('BLK',LAST_QUERY)),'WHERE') + 1;

Just get the conditions. For example:

from a query:

SELECT col1,col2,col3
from tab1
where col1=<SomeVal>
and col2 = <someVal>
ORDER BY col2

It will retrive:

col1=<SomeVal>
and col2 = <someVal>

and add addditional clause (like invoice_no in your case) so that the final where clause will become:

col1=<SomeVal>
and col2 = <someVal>
OR (col3 = <SomeVal>)

So +1 means just get the new charcater after WHERE clause but before ORDER BY clause (thats what  -1 means).
0
 
LVL 12

Expert Comment

by:jwahl
ID: 16433953
if you can make sure that the new record is the last one in your query (order_by ...)  execute the query as Riazpk described.

otherwise you have to loop through the queried records (FIRST_RECORD, NEXT_RECORD, ...) until you reach the new record.

or do NO execute query, but retrieve the values from the inserted record and overwrite the current record. don't forget to set the correct record_status.
0
 

Author Comment

by:geeta_m9
ID: 16437748
jwahl, how do I set the correct record status?
0
 

Author Comment

by:geeta_m9
ID: 16437763
Riazpk, what would the final query be? Would it be as follows:

t_where_pos := INSTR(UPPER(Get_Block_Property('BLK',LAST_QUERY)),'WHERE') + 1;
t_order_pos := INSTR(UPPER(Get_Block_Property('BLK',LAST_QUERY)),'ORDER') - 1;

Set_Block_Property('BLK',DEFAULT_WHERE,
                  SUBSTR(Get_Block_Property('BLK',LAST_QUERY),t_where_pos,(t_order_pos - t_where_pos)));

I don't have an invoice number. My primary key is a combination of the code and sequence number. If I execute this query, would the last record I inserted show up on the form or would it be showing the first record?
0
 
LVL 12

Accepted Solution

by:
jwahl earned 800 total points
ID: 16443429
assuming, your form has 3 fields: :BLOCK.ITEM1, :BLOCK.ITEM2, :BLOCK.ITEM3
you can execute this script after inserting the new record:

DECLARE
    v_item_1 VARCHAR2(200);
    v_item_2 VARCHAR2(200);
    v_item_3 VARCHAR2(200);
    v_rec_num NUMBER;
BEGIN
    --
    -- get the current record number in the block:
    --
    v_rec_num := GET_BLOCK_PROPERTY(:SYSTEM.cursor_block, CURRENT_RECORD);
    --
    -- retrieve data from the record you've just inserted:
    --
    SELECT <COL1>, <COL2>, <COL3>
    INTO v_item_1, item_2, v_item_3
    WHERE  <YOUR CONDITION TO RETRIEVE THE NEW RECORD, e.g. primary_key = ...>;
    --
    -- overwrite fields in the form with new values:
    --
    :BLOCK.COL1 := v_item_1;
    :BLOCK.COL2 := v_item_2;
    :BLOCK.COL3 := v_item_3;
    --
    -- set the fields valid to avoid the validate-item-trigger to fire:
    --
    SET_ITEM_PROPERTY('BLOCK.ITEM1', ITEM_IS_VALID, PROPERTY_TRUE);
    SET_ITEM_PROPERTY('BLOCK.ITEM2', ITEM_IS_VALID, PROPERTY_TRUE);
    SET_ITEM_PROPERTY('BLOCK.ITEM3', ITEM_IS_VALID, PROPERTY_TRUE);
    --
    -- set the record status to NEW to avoid a new insert/update:
    --
    SET_RECORD_PROPERTY(v_rec_num, :SYSTEM.cursor_block, STATUS, NEW_STATUS);
    --
END;
               
hope this helps ....
0
 

Author Comment

by:geeta_m9
ID: 16446594
I will try it out, thank you.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

850 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