EnterpriseDB: Public Synonyms Oracle Tips by Burleson Consulting

Before migrating the views, I want to create public synonyms so that when I reference the Traker tables, I don't need to include the schema. 

If you remember from chapter 3, we create public synonyms like this:

CREATE PUBLIC SYNONYM TT_SCHEDULES FOR traker.TT_SCHEDULES;
CREATE PUBLIC SYNONYM TT_XML_INTERFACE FOR traker.TT_XML_INTERFACE;            
CREATE PUBLIC SYNONYM TT_TASKS FOR traker.TT_TASKS;                            
CREATE PUBLIC SYNONYM TT_PROJECTS FOR traker.TT_PROJECTS;                       
CREATE PUBLIC SYNONYM TT_RESOURCES FOR traker.TT_RESOURCES;                    
CREATE PUBLIC SYNONYM TT_PROJECTS_TASKS FOR traker.TT_PROJECTS_TASKS;          
CREATE PUBLIC SYNONYM TT_TASKS_RESOURCES FOR traker.TT_TASKS_RESOURCES;         
CREATE PUBLIC SYNONYM TT_TASK_ITEM_UPDATE FOR traker.TT_TASK_ITEM_UPDATE;      
CREATE PUBLIC SYNONYM TT_TASK_DEPENDS FOR traker.TT_TASK_DEPENDS;              
CREATE PUBLIC SYNONYM TT_CALENDAR FOR traker.TT_CALENDAR;    
CREATE PUBLIC SYNONYM tt_general_Sequence FOR traker.tt_general_Sequence;

I will start with the easiest views that should compile with only minimal changes:  HOURS_WORKED_BY_RESOURCES, PROJECT_TASKS, PROJECT_RESOURCES and PROJ_HOURS_REMAINING.  EnterpriseDB requires aliases to be prefaced with the AS keyword (which the online migration tool will actually do for you automatically), so:

CREATE OR REPLACE VIEW PROJ_HOURS_REMAINING
AS
SELECT tt_projects_tasks.project_task_name,
       nvl(tt_projects_tasks.estimated_hours,0)  ESTIMATED_HOURS,
       nvl(tt_projects_tasks.estimated_hours -
       sum(tt_task_item_update.hours_worked),0)
            hours_remaining_in_Project
  FROM tt_tasks JOIN tt_projects_tasks
       ON (tt_tasks.task_id = tt_projects_tasks.task_id)
       JOIN tt_tasks_resources
       ON (tt_tasks.task_id = tt_tasks_resources.task_id)
       JOIN tt_resources
       ON (tt_resources.resource_id = tt_tasks_resources.resource_id)
       LEFT OUTER JOIN tt_task_item_update
       ON ((tt_tasks_resources.task_id = tt_task_item_update.task_id)
       AND (tt_tasks_resources.resource_id = tt_task_item_update.resource_id))
  GROUP BY tt_projects_tasks.project_task_name,
       tt_projects_tasks.estimated_hours

Becomes:

CREATE OR REPLACE VIEW PROJ_HOURS_REMAINING
AS
SELECT tt_projects_tasks.project_task_name,
       nvl(tt_projects_tasks.estimated_hours,0) AS ESTIMATED_HOURS,
       nvl(tt_projects_tasks.estimated_hours -
       sum(tt_task_item_update.hours_worked),0) AS
            hours_remaining_in_Project
  FROM tt_tasks JOIN tt_projects_tasks
       ON (tt_tasks.task_id = tt_projects_tasks.task_id)
       JOIN tt_tasks_resources
       ON (tt_tasks.task_id = tt_tasks_resources.task_id)

       JOIN tt_resources
       ON (tt_resources.resource_id = tt_tasks_resources.resource_id)
       LEFT OUTER JOIN tt_task_item_update
       ON ((tt_tasks_resources.task_id = tt_task_item_update.task_id)
       AND (tt_tasks_resources.resource_id = tt_task_item_update.resource_id))
  GROUP BY tt_projects_tasks.project_task_name,
       tt_projects_tasks.estimated_hours

Small changes but important.

The last view is a bit harder to migrate.  The use of SQL Analytics, which is not currently supported in EnterpriseDB, makes this a larger task.  The original view cannot be easily written in EnterpriseDB SQL:

CREATE OR REPLACE VIEW PROJ_HOURS_REMAIN_BY_RESOURCE
AS
SELECT PROJECT_TASK_NAME,
       RESOURCE_NAME,
       ESTIMATED_HOURS,
       HOURS_WORKED_BY_RESOURCE,
       HOURS_REMAINING_IN_PROJECT,
       RN FROM (
SELECT tt_projects_tasks.project_task_name,
       tt_resources.resource_name,
       tt_projects_tasks.estimated_hours,
       nvl(sum(tt_task_item_update.hours_worked) over
           (partition by tt_taSKS.task_id, tt_resources.resource_name),0)
             hours_worked_by_resource,
       tt_projects_tasks.estimated_hours -
       nvl(sum(tt_task_item_update.hours_worked) over
          (partition by tt_projects_tasks.project_id), 0)
            hours_remaining_in_Project,
       row_number() over(partition by tt_taSKS.task_id, tt_resources.resource_name
                         order by tt_taSKS.task_id, tt_resources.resource_name) rn
  FROM tt_tasks JOIN tt_projects_tasks
       ON (tt_tasks.task_id = tt_projects_tasks.task_id)
       JOIN tt_tasks_resources
       ON (tt_tasks.task_id = tt_tasks_resources.task_id)
       JOIN tt_resources
       ON (tt_resources.resource_id = tt_tasks_resources.resource_id)
       LEFT OUTER JOIN tt_task_item_update
       ON ((tt_tasks_resources.task_id = tt_task_item_update.task_id)
     AND (tt_tasks_resources.resource_id = tt_task_item_update.resource_id)) )
WHERE rn = 1

Using the sample data provided in the Code Depot files, Figure 9.8 shows what the output looks like in Oracle:

Figure 9.8: Oracle Output Project Hours Remaining By Resource

We need to mimic that output in EnterpriseDB.  The above query becomes:

CREATE OR REPLACE VIEW PROJ_HOURS_REMAIN_BY_RESOURCE
AS
SELECT project_task_name,
       resource_name,
       estimated_hours,
       hours_worked_by_resource,
       estimated_hours - hours_worked_by_resource hours_remaining_in_project,
       1 as rn FROM (
SELECT tt_projects_tasks.project_task_name,
       tt_resources.resource_name,
       tt_projects_tasks.estimated_hours,
       nvl(sum(tt_task_item_update.hours_worked),0)
             hours_worked_by_resource
  FROM tt_tasks LEFT OUTER JOIN tt_projects_tasks
       ON (tt_tasks.task_id = tt_projects_tasks.task_id)
       LEFT OUTER JOIN tt_tasks_resources
       ON (tt_tasks.task_id = tt_tasks_resources.task_id)
       LEFT OUTER JOIN tt_resources
       ON (tt_resources.resource_id = tt_tasks_resources.resource_id)
       LEFT OUTER JOIN tt_task_item_update
       ON ((tt_tasks_resources.task_id = tt_task_item_update.task_id)
     AND (tt_tasks_resources.resource_id = tt_task_item_update.resource_id))
GROUP BY  tt_projects_tasks.project_task_name,
       tt_resources.resource_name,
       tt_projects_tasks.estimated_hours ) AS foo

The output looks like:

Figure 9.9: EnterpriseDB Output Project Hours Remaining By Resource

A quick run through of the code finds a couple of minor errors.  One of those errors is that EnterpriseDB SPL does not support transaction statements within a stored procedure.  Everywhere I have a commit or rollback will have to be eliminated.  I make those changes.

The next big issue is the reports.  Looking through the code, I see that I have cursor declared in cursor for loops.  For example, in Oracle, this code snippet is perfectly valid:

    FOR ci IN (
      SELECT SUBSTR(project_task_name,1, 30) project_task_name,
             to_char(estimated_hours) estimated_hours, 
             SUBSTR(resource_name,1,30) resource_name,
             to_char(hours_worked) hours_worked,
             to_char(item_date, 'MM/DD/YYYY') item_date
         FROM hours_worked_by_resources)
    LOOP

In EnterpriseDB, that code is not valid.  The select statement should be moved to the declaration section and the for loop will reference it by name.

DECLARE
  CURSOR c1 IS
    SELECT SUBSTR(project_task_name,1, 30) project_task_name,
             to_char(estimated_hours) estimated_hours, 
             SUBSTR(resource_name,1,30) resource_name,
             to_char(hours_worked) hours_worked,
             to_char(item_date, 'MM/DD/YYYY') item_date
         FROM hours_worked_by_resources;
BEGIN
  .
  .
  .
    FOR ci IN c1 LOOP
  .
  .
  .



This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.