Inserting new record in Oracle forms

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.
geeta_m9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jwahlCommented:
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
riazpkCommented:
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
geeta_m9Author Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

geeta_m9Author Commented:
Riazpk, I don't understand your solution. What do the +6 and -1 do?
0
riazpkCommented:
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
jwahlCommented:
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
geeta_m9Author Commented:
jwahl, how do I set the correct record status?
0
geeta_m9Author Commented:
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
jwahlCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
geeta_m9Author Commented:
I will try it out, thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.