How to implement SQL Query into Procedure Oracle Forms

Question

I created query to check validation data on table then insert into table. I want apply this query into Procedure Program unit Oracle Forms. I am a beginner and don't know how to implement this query into Procedure. I need procedure to apply on Validation Button Trigger When-Button-Pressed

Query:

INSERT INTO we_group_hof
(col1,col2,col3,col4,col5,col6
SELECT col1,col2,col3,col4,col5
FROM we_group_hof_k
WHERE col1 IS NOT NULL
AND col2 = 2
AND LENGTH(col3) <=13
AND col4 = 'Y'
AND col5 = 'A'
AND col6 <= sysdate
AND col6 IS NOT NULL;

Answer 1

Create a procedure, using appropriate node in Object Navigator (press the green "+" button in the vertical toolbar). Procedure code will be quite simple, without any parameters (as that's what your code suggests):

procedure p_myproc is
begin
  insert into we_group_hof
  ... the rest of your query goes here
  and col6 is not null;
end;

You'd then call it from the WHEN-BUTTON-PRESSED trigger by specifying its name:

p_myproc;

See whether you want to commit implicitly (by calling the STANDARD.COMMIT from within the procedure or the trigger), or let users decide (i.e. commit manually).

Answer 2

You can create such a stored or internal(under Program Units node) procedure such as

create or replace procedure pr_insert_grp_hof
                           (
                            i_col1 we_group_hof_k.col1%type,
                            i_col2 we_group_hof_k.col2%type,
                            i_col3 we_group_hof_k.col3%type,
                            i_col4 we_group_hof_k.col4%type,
                            i_col5 we_group_hof_k.col5%type,
                            i_col6 we_group_hof_k.col6%type
                           ) is
begin
  insert into we_group_hof_k
  select col1, col2, col3, col4, col5, col6
    from we_group_hof_k
   where i_col1 is not null
     and i_col2 = 2
     and length(i_col3) <= 13
     and i_col4 = 'Y'
     and i_col5 = 'A'
     and i_col6 <= sysdate
     and i_col6 is not null;
end;

where parameter names for procedure are prefixed with i_ and in which remove create or replace part in the beginning if you created an internal procedure.

After adding a button from the tool palette, right click on it and drag to Smart Triggers and select WHEN-BUTTON-PRESSED and write this simple code in it :

pr_insert_grp_hof;
commit;