September 11, 2013
I am having a problem to update into Oracle table. To be exact, I have created 2 separate form into 1 page. And those forms are actually binded with different table as well. My operation or design will be, when I click a ‘Submit’ button, all the data entered by user will be inserted into their respective table. Well, I got an error. What has been wrong?
Oracle APEX limitation?
After ‘googling’ (I wonder if this word has in dictionary..) around, I found that this is a known limitation for APEX. Well, obviously Oracle need to check further for this. You can only have one automated row fetch (DML) process per page. However, it does not means that we cannot add other form on the page. There are few a solution for that which works well for me.
Create Separate Region for separate Form
1. On Page Rendering, locate Region > Body and right click. Click ‘Create’ to create new region. This one will be your 1st Form.
2. Create Region, select ‘Form’ and press ‘Next’
3. Create Form, select ‘Table’ and press ‘Next’. Please just proceed until the wizard complete. The 1st Form will be straight forward.
The Important on Next Sequence Form
You may now see the new region successfully created from the wizard. The Form will populated text field or other field as per your selection.
Now, the next step we need to do will bit different.
4. Repeat same step 1 and 2, but for 3 select ‘SQL Query (fetch row) instead of ‘Table’
5. Press ‘Next’ until you found ‘SQL SELECT Statement’ page tab. Here will be SQL to populate the page item into the page. You can type it manually, or click [Query Builder] link.
6. The new page items will be populated according to your SQL. You can see the Form already created for you.
Setting the INSERT Operation
You may notice that there are few button automatically created inside the page. For me, I do not want it so many, so I created a new region for only 1 button. This button will do action INSERT for those 2 form above for 2 different table. Just make sure that the button action is ‘Submit Page’.
7. Since the 1st Form using Automatic Row Processing(DML), I just need to set the INSERT operation for Table #2 in Form #2. Go to Page Processing > Processes and right click on it.
8. Select PL/SQL and Next. Give your process a name and Next.
9. When reach at [*Enter PL/SQL Page Prosess] , enter you INSERT SQL statement. Remember to use page items in this SQL. For Example:
Oracle APEX limitation?
After ‘googling’ (I wonder if this word has in dictionary..) around, I found that this is a known limitation for APEX. Well, obviously Oracle need to check further for this. You can only have one automated row fetch (DML) process per page. However, it does not means that we cannot add other form on the page. There are few a solution for that which works well for me.
Create Separate Region for separate Form
1. On Page Rendering, locate Region > Body and right click. Click ‘Create’ to create new region. This one will be your 1st Form.
2. Create Region, select ‘Form’ and press ‘Next’
3. Create Form, select ‘Table’ and press ‘Next’. Please just proceed until the wizard complete. The 1st Form will be straight forward.
The Important on Next Sequence Form
You may now see the new region successfully created from the wizard. The Form will populated text field or other field as per your selection.
Now, the next step we need to do will bit different.
4. Repeat same step 1 and 2, but for 3 select ‘SQL Query (fetch row) instead of ‘Table’
5. Press ‘Next’ until you found ‘SQL SELECT Statement’ page tab. Here will be SQL to populate the page item into the page. You can type it manually, or click [Query Builder] link.
6. The new page items will be populated according to your SQL. You can see the Form already created for you.
Setting the INSERT Operation
You may notice that there are few button automatically created inside the page. For me, I do not want it so many, so I created a new region for only 1 button. This button will do action INSERT for those 2 form above for 2 different table. Just make sure that the button action is ‘Submit Page’.
7. Since the 1st Form using Automatic Row Processing(DML), I just need to set the INSERT operation for Table #2 in Form #2. Go to Page Processing > Processes and right click on it.
8. Select PL/SQL and Next. Give your process a name and Next.
9. When reach at [*Enter PL/SQL Page Prosess] , enter you INSERT SQL statement. Remember to use page items in this SQL. For Example:
insert into Table2 (column1, column2, column3) values ( : P1_Item1, : P1_Item2, : P1_Item3);Hopefully above steps works for you. If you got comment, leave your comment below.
No comments:
Post a Comment