Oracle Forms

I would not do it this way.

I would create the second block based on table B and then "build" a WHERE clause, set the block property DEFAULT_WHERE  of block B to that built WHERE clause, go to block B and then do EXECUTE_QUERY.

Build a Database Block from your table A and create a Pre-Query trigger and write go_block and execute_query.

You can pass your parameters in Pre-Query trigger.

Pre-Query trigger Code for your reference.
DECLARE
    v_Query Varchar2(2000);
BEGIN
    v_Query  :=  '1=1';
    IF :Parameter.Param1 IS NOT NULL THEN
        v_Query := v_Query ||' AND Your_Column_Name = '|| :Parameter.Param1  ;
    END IF;
 
    IF :Parameter.Param2 IS NOT NULL THEN
        v_Query := v_Query ||' AND Your_Column_Name = '|| :Parameter.Param2  ;
    END IF;
    ---So .................on.........
   Set_Block_Property('Your_Block_Name',Default_Where,v_Query);
END;

Hope it helps you.

Thanks for your inputs.

I will elaborate more on my requirement. I will have query/search form with 4 parameters, once i select and click on FIND button,i have to pull data from different tables, then i need to populate that in data block which is based on table B. I will have check box in data block (table B), i will select records and click on SUBMIT button (i have submit button in data block B) then i need to store this data in table B. That's why i cant' append dynamic WHERE clause in PRE-QUERY trigger of data block (Table B).

Please advise me best approach.

Thanks

My approach is:

Avoid explicit DML (and even explicit SELECT) on the client side (the application server is also a database client)

in any tool / language (Forms, Java ...), ie write explicit DML (and even explicit SELECT) in database procedures / functions.

SQL on the client is always a dynamic SQL (although Forms has a SQL parser, and a PL/SQL parser & executor).

So, I recommend using the stored procedures / functions, which (if needed) return the ref cursor (dynamic, if needed).

Of course, perhaps in your case there is a much simpler solution.

Regards,

Zlatko

Hi,

Thanks for your advise. I will write database procedure/function to pull data from different tables and return that result. You mentioned much simpler solution, could you explain that.

Thanks

Actually, I do not know if there is a simpler solution because I do not know all the details of your request.

Regards,

Zlatko

Hi,

My requirement is, I will have search/query form with 4 parameters and FIND button, once i select parameters and click on FIND button, need to pull data from different tables and show in a block. In that block i will have check box and SUBMIT button as well, user will select check box for his/her wish records and clicks on SUBMIT button, then these selected records should be pushed to table. Requirement is always query through Search/query form and save selected records in table. No query or update or delete on block which contains search results, only insert of selected records.

Please advise.

Thanks

Maybe it could be like this:

1. Based on the entered parameters, Forms calls the database procedure that reads the original tables and updates the GTT table

(Global Temporary Table is not necessary, it could be a "standard table", but then we need "tricks" if we want multiple users to work simultaneously).

2. The Forms block is loaded from the GTT table.

The user selects the rows (thus changing the GTT table) and launches Submit.

3. Forms calls the database procedure that reads the GTT table and insert rows into the destination table.

Regards,

Zlatko

Hi,

  What i understand is that what you need is a block referencing table A, that can find records in that table, and when you push submit button, need that some or all of the fields of the selected records go to a table B. In my opinion the easiest way to do that is the following:

     1 - Create a Data Block A that references table A in query mode (no insert or update or delete allowed). In the form, with a button find, search the required records.

     2 - Add in the Data Block a non data base referenced check field.

     3 - In de button Submit, scroll the cursor of the data block A, and where find a check marked put a simple insert clause.

  If want to use another block, create a block referenced to the table A, and make the same that in step 3, but making an equals field by field form a block to other (blockA.field1 : blockB.field1) and a commit_form before.

i hope that can help you.

Regards.

Hi,

Thanks for your advise. problem is query form will pick data from more than one table, so i can't create data block A on table A, i need to go for view and select 4 columns what i required in block A and clicking on FIND button, do execute_query and show in separate block B. But block B should be on table B, not on table A since when i select records on block B (search results of block A), i need to push them to table B. My question here is, how to display records in block B (search results of block A)?

Please advise.

Thanks

Hi,

Thanks for your advise. I have some doubts on design part.

1. query/search form will contain 4 parameters and FIND button. (as far as i understand it's control block)

2. when i select parameters and click on FIND , it loads GTT/standard table

3. block B where i need to display  records is based on GTT/standard table

here question is, when i click on FIND button on query form, it will load GTT table, so after that to show results on block B, do we need write below code in WHEN_BUTTON_PRESSED trigger of FIND.

1. call procedure and load data into GTT table

2. go to block B

3. execute_query

Am i right or missing anything.

After that, when i select check box for some records and click submit button, i can write in WHEN_BUTTON_PRESSED of block B to insert data into destination table. what piece of code do we need to write here to insert selected records and insert into destination table. do we need to loop through all the records in block B and look for selected records like this way or any other way?

please advise.

> 1. call procedure and load data into GTT table

> 2. go to block B

> 3. execute_query

> Am i right or missing anything.

That is good.

> After that, when i select check box for some records and click submit button, i can write in WHEN_BUTTON_PRESSED of block B to insert data into destination table.

> what piece of code do we need to write here to insert selected records and insert into destination table.

> do we need to loop through all the records in block B and look for selected records like this way or any other way?

You can loop through all the records in block B and look for selected records, but I prefer to use the database procedure.

GTT table could have column eg. "selected number(1) default 0 not null" (0 = unselected, 1 = selected).

In WHEN_BUTTON_PRESSED of block B, write:

1. POST - updates "selected" column in the GTT table (you can use COMMIT_FORM instead of POST, but then GTT table must be created using "... ON COMMIT PRESERVE ROWS", not "... ON COMMIT DELETE ROWS")

2. call the (second) database procedure, that reads selected rows from the GTT table and inserts into the destination table

3. COMMIT_FORM

Regards,

Zlatko

Thanks for your help. I will try and let you know if i face any issue.