Thursday, July 14, 2011

Enabling Drag & Drop and Add / Edit Data for SQL Calendar in Oracle Application Express 4.1

Step 1: Click on the Create Page Button

Step 2: Select the Calendar Option

Step 3: Select SQL Calendar

Step 4: Just Select the Defaults

Step 5: Let us take the most common table for this Example ie EMP, Let the calendar source be

select ROWID,
from EMP

Step 6: Select ROWID as primary key and select Date & Time option in the following page with ENAME as display column and HIREDATE as Date Column.

Step 6: Select the following options in the next page, keeping in mind the Source table in SQL is EMP we chose EMP as the table for which a form page need to be created with Add/Edit/Delete Option.

Step 7: Select the buttons / options need to be added in the Form page to be created. By default all the three options will be enabled Add/ Edit/ Delete

Step 8: Click Next and Click on the Finish button to create the Calendar Page.

Step 9: Run the Page now try and Drag & Drop Data, oops! does not happen........

Must be wondering where is the Drag & Drop option shown, Don't worry will answer it, Drag & Drop option will be shown while Creating Easy Calendar but not for SQL Calendar, because the SQL Calendar can have "Select sysdate,'apex 4.1' desc from dual which is valid SQL, but impossible to generate a Drag & Drop process for that, so how will I enable Drag & Drop.

The following post Calendar creation process steps need to be followed to make Calendar Draggable.

Step 1: Edit Calendar Page.

Step 2: Click on Create Process of Type PL/SQL and execution point will be On-Demand, let's name it Calendar_Drag_Drop

Step 3: Enter the following code for the process

l_date_value varchar2(32767) := apex_application.g_x01;
l_primary_key_value varchar2(32767) := apex_application.g_x02;
update EMP set HIREDATE = to_date(l_date_value,'YYYYMMDD HH24MISS')
where ROWID = l_primary_key_value;

The above code is actually updating the Date column of the Calendar so that the Data can be moved in the Calendar Grid.

Step 4: Click on Create Process button

Step 5: Click on Edit Page > Edit Calendar > Calendar Attributes

Step 6: Click on "Drag & Drop" on top of the screen of Calendar Attributes page

Step 7: Select the Process Created under Process and if you want to enable Authorization for Drag & Drop select your Authorization and Apply Changes.

Step 8: Run the Page (make sure that EMP Table has data to be displayed in the Calendar)

Step 9: You should be able to Drag & Drop Employee name around the Calendar Grid.

Step 10: Click on the Empty Date Grid a popup will be opened to add a new record on EMP and click on the Employee name to edit the Data.