Forum Stats

  • 3,824,920 Users
  • 2,260,440 Discussions
  • 7,896,348 Comments

Discussions

Categories

Oracle Apex - Create Procedure to Insert Records from Another Table

SANPAT
SANPAT Member Posts: 1,090 Silver Badge
in APEX Discussions

Dear Friends

We want to insert data from A table to B table. How it will be written in Oracle Apex Forms.

In the form 12c i was writing the code in the following way, now in apex how to write.

Create Procedure Test1 as

Declare

Cursor a1 is select empno,name from employee;

begin

delete from test1;

commit;

for i in a1

loop

insert into test1(empno,name)

values (i.empno,i.name);

end loop;

COMMIT_FORM;

end;


sanjay

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown
    Answer ✓

    APEX is stateless. It automatically calls COMMIT as needed (or ROLLBACK ).

    I'd keep it simple, Sanjay (K.I.S.S.)

    BEGIN
  DELETE FROM TEST1;

  INSERT INTO TEST1 (empno,name)
    SELECT empno, name FROM EMPLOYEE;
END;
    · Share on TwitterShare on Facebook

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown

    It looks like table TEST1 is being used as a temp table.

    In Forms, this should probably have been a GTT.

    GTTs don't work in APEX dye to Connection Pooling. You'd use a Collection instead.

    But, I'm just guessing about the purpose of TEST1.

    (PS - you should probably have just done an INSERT... SELECT instead of parsing a cursor in a loop)

    · Share on TwitterShare on Facebook
  • SANPAT
    SANPAT Member Posts: 1,090 Silver Badge

    Dear Mike Kutz

    I want to delete all the old records from test1. then i want to update the table with the latest Query data from test table. can you suggest how to insert the records using the procedure in Oracle Apex .

    1. Delete all Data from A Table
    2. Query on Table B and get the data
    3. Insert the Query data into A Table.

    Sanjay

    · Share on TwitterShare on Facebook
  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown
    Answer ✓

    APEX is stateless. It automatically calls COMMIT as needed (or ROLLBACK ).

    I'd keep it simple, Sanjay (K.I.S.S.)

    BEGIN
  DELETE FROM TEST1;

  INSERT INTO TEST1 (empno,name)
    SELECT empno, name FROM EMPLOYEE;
END;
    · Share on TwitterShare on Facebook
  • SANPAT
    SANPAT Member Posts: 1,090 Silver Badge

    Dear Mike Kutz

    Thanks , your given Query is perfectly runing on Sql Command, but the same Query when i update in the Form at server side condition , it's not working . it truncate the data but not updating with fresh data from the called table.

    image.png


    image.png

    Kindly sugget .

    Sanjay

    · Share on TwitterShare on Facebook
  • AndyH
    AndyH Member Posts: 806 Bronze Trophy

    As you are running this from a Dynamic Action it will be running in a different database session to that of your page. If your page hasn't committed it's data, the session in your DA will not see that data.

    Is there a particular reason you are doing this work through a DA and not through a process?

    · Share on TwitterShare on Facebook
  • SANPAT
    SANPAT Member Posts: 1,090 Silver Badge

    Dear AndyH

    I am new to Oracle Apex and Learning , Can you please give me some tips , how to take such query from Process.

    Its getting executed properly , but i don't know how to display the message once the Procedure Query will update the table . Can you please suggest.

    Sanjay

    · Share on TwitterShare on Facebook