• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1938
  • Last Modified:

COPY DATA IN ORACLE FORM 10G

Dear Expert,
Iam developing a form in oracle 10g.

I have 2 database block.
block1 and block2.

In the block1 i have 1 nondatabase item(checkbox item);

when i execute data in block1.
I will choose check box for any record.
What ever i choose i should copy those records to block2.

 both r having similar column type except some fields.

My concern is what ever iam choosing in the check box .I need to copy those records and put it in another block.

Please help me
0
daprjo
Asked:
daprjo
  • 5
  • 3
  • 3
1 Solution
 
flow01Commented:
Is there a reason you need the records in form ?
If you  only want a copy  code the insert statements directly in the when-check-box-change trigger or build a procedure that you call from the when_check-box-changed-trigger.
The inserted records are committed/rollbacked  if you commit/rollback the changes of block 1.
(Is there always a change in block1 ?)
Making changes in 2 form-blocks at the same time is difficult.

Has the value of the checkbox a meaning in the process ? : otherwise i should prefer to use a button.
0
 
daprjoAuthor Commented:
dear Expert,
 I didn't mean in that way.both r of different table.
block 1 is empty
block2 has some records
Checkbox checked         1 ......
Checkbox not checked   2 .......
Checkbox checked         3 .....
(checkbox checked is a check box field
only those records checked  1 and 3 has to  insert into block1

thanks & regards
0
 
daprjoAuthor Commented:
Dear Expert ,

This copy will be on when i press a button in block2
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
flow01Commented:
I am confused:
is the copy ment to be from block1 to block2
or from block2 to block1.
Question remains in either case : is there anything special you want to do with the target records that you have to do in forms or is it just necessary to insert the records ?
0
 
daprjoAuthor Commented:
Dear expert,

This is urgent.

Block2 has so many records(Block2 is a database table).It has    1 checkbox field(nondata base).
and 1 button.
If say block2 has 5 records  .I will check only 3 records. Those 3 has to insert into block1(but not commit). When i press the button .
But block1 is detail block of another database block. It has master - detail relationsh
ip.
I think u understood now.

Please help me.

thanks & regards
0
 
flow01Commented:
-- you could try something like this:
-- walk true block2 an switch to block1 for each checked record to fill 1 record with copy-data and
--    come back to block1
-- pseudocode between <> you will have to work out in formscode
declare
  eof_block boolean := true;
first_record;
if <not a new record> then
    eof_block := false;
end if;
while not eof_block loop
   if <checkbox.checked> then
     go_block('block1');
     if <current_record is not a new_record> then
         create_record;
     end if;
     :block1.field1 := :block2.field1;
         ....
     post; -- on a post all changes will be applied but not committed
                 -- if you don't post you can't leave block1 without committing or rollback
      go_block('block2');
    end if;
     begin
       next_record;
       if < a new record> then
           eof_block := true;
       end if;
       exception when form_trigger_failure then
         -- if the block2 allows no insert of records then the next_record on the last
           -- record found will raise a failure or a warnig : maybe you will need
           -- to intercept that error in a the on-error or on-message trigger
           -- make eof_block := true;  -- to stop looping
     end;
end loop;
0
 
daprjoAuthor Commented:
Dear Expert,
     How can i use temp pl/table to copy selected checked records.and insert into the second block.
Both tables r  diff database tables )
0
 
schwertnerCommented:
see this:

http://www.dba-oracle.com/plsql/t_plsql_object_types.htm

http://www.dba-oracle.com/t_oracle_associative_arrays.htm
SET SERVEROUTPUT ON
DECLARE
  TYPE t_collection IS TABLE OF NUMBER(10)
    INDEX BY BINARY_INTEGER;  

  l_coll  t_collection;
  l_idx   NUMBER;
BEGIN
  -- Initialise the collection.
  << load_loop >>
  FOR i IN 1 .. 5 LOOP
    l_coll(i) := i;
  END LOOP load_loop;  

  -- Delete the third item of the collection.
  l_coll.DELETE(3);  

  -- Traverse sparse collection
  l_idx := l_coll.FIRST;
  << display_loop >>
  WHILE l_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || l_coll(l_idx));
    l_idx := l_coll.NEXT(l_idx);
  END LOOP display_loop;
END;
/
The number 1
The number 2
The number 4
The number 5

PL/SQL procedure successfully completed.

SQL> 

Open in new window

0
 
daprjoAuthor Commented:
Dear Expert,
How i can use the above in oracle forms
0
 
schwertnerCommented:
I see it in the following way:

1. In the block with populated rows before jumping to the second block (where the records should be copied)
create (using PL/SQL in the trigger WHEN-BUTTON_PRESSED) code that will store in a PL/SQL table the contents of the records in the block.

2. The PL/SQL table could be passed as a parameter to second block (it has its form).
In the WNEN_NEW_FORMS_INSTANCE you can code using PL/SQL a loop of statements
that will populate the rows of that block


Another idea would be to store the rows of the first block in a temporary table and to let
the second block to read them from the table.

I am sure there are many examples in the net how to do this.

The obstacle is that you need to be familiar with PL/SQL, but this particular assignment
is not so complex. I encourage you step by step to move toward the solution.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now