print Printer View e-mail E-mail this page Bookmark Bookmark
Purpose
To inform Migration Engineers of the need to change the stored procedure code if multiple results sets are returned.
Scope & Application
The audience for this document is Oracle partners and customers that are using the Migration Workbench to migrate stored procedures to Oracle.
Verify Number Of Returned Reference Cursors
In Sybase and SQLServer, result sets are returned to the application implicitly. Oracle emulates this by passing back weak reference cursors to the application through an IN OUT parameter.

The number of reference cursors must match the number of results sets. For example, if 2 select statements are present in the stored procedure code, then 2 reference cursors are passed back.

The Oracle Migration Workbench creates the correct number of reference cursors but assigns each select statement results to the first cursor created. Therefore only the last set of results are returned to the client.
Before (SQLServer) After (Migration Workbench) After (Manual Change)
CREATE PROCEDURE
get_sch_associated_appointment_info
(@piAcc_itn int)
 AS
 
 SELECT s.acc_itn,
   r.internal_key,
    r.mnemonic,
    r.descp,
    sh.start_dtime,
    sh.end_dtime
 FROM
    schdtl s,
    schdtlhdr sh,
    resource r
 WHERE
    s.acc_itn = @piAcc_itn and
    sh.acc_itn = @piAcc_itn and
    sh.resource_itn = r.internal_key
 
 SELECT sdcr.acc_itn,
    sdcr.rsch_dtime,
    sdcr.rsch_by_init,
    sdcr.rsch_code,
    sdcr.rsch_reason,
    sdcr.cncl_dtime,
    sdcr.cncl_by_init,
    sdcr.cncl_code,
    sdcr.cncl_reason,
    sdcr.prev_start_dtime,
    sdcr.prev_by_init
 
 FROM schdtl_canrsch sdcr
 WHERE
    sdcr.acc_itn = @piAcc_itn
 
 SELECT
    sdi.acc_itn,
    i.sched_notes,
    i.post_sched_notes,
    d.pre_sch_notes,
    d.post_sch_notes,
    i.detail_key,
    i.output_notes
 FROM
    schdtl_info sdi,
    extitem i,
    dept d
 WHERE
    sdi.acc_itn = @piAcc_itn and
    sdi.actual_dept = i.dept and
    sdi.actual_proc_no = i.proc_no and
    sdi.actual_dept = d.dept 		CREATE OR REPLACE PROCEDURE
get_sch_associated_appointment_info
  (piAcc_itn int,
  RC1 IN OUT Omwb_emulation.globalPkg.RCT1)
 AS
 
 OPEN RC1
 SELECT s.acc_itn,
   r.internal_key,
    r.mnemonic,
    r.descp,
    sh.start_dtime,
    sh.end_dtime
 FROM
    schdtl s,
    schdtlhdr sh,
    resource r
 WHERE
    s.acc_itn = piAcc_itn and
    sh.acc_itn = piAcc_itn and
    sh.resource_itn = r.internal_key;
 
 OPEN RC1
 SELECT sdcr.acc_itn,
    sdcr.rsch_dtime,
    sdcr.rsch_by_init,
    sdcr.rsch_code,
    sdcr.rsch_reason,
    sdcr.cncl_dtime,
    sdcr.cncl_by_init,
    sdcr.cncl_code,
    sdcr.cncl_reason,
    sdcr.prev_start_dtime,
    sdcr.prev_by_init
 
 FROM schdtl_canrsch sdcr
 WHERE
    sdcr.acc_itn = piAcc_itn;
 
 OPEN RC1
 SELECT
    sdi.acc_itn,
    i.sched_notes,
    i.post_sched_notes,
    d.pre_sch_notes,
    d.post_sch_notes,
    i.detail_key,
    i.output_notes
 FROM
    schdtl_info sdi,
    extitem i,
    dept d
 WHERE
    sdi.acc_itn = piAcc_itn and
    sdi.actual_dept = i.dept and
    sdi.actual_proc_no = i.proc_no and
    sdi.actual_dept = d.dept; 		CREATE OR REPLACE PROCEDURE
get_sch_associated_appointment_info
   (piAcc_itn int,
   RC1 IN OUT Omwb_emulation.globalPkg.RCT1,
   RC2 IN OUT Omwb_emulation.globalPkg.RCT1,
   RC3 IN OUT Omwb_emulation.globalPkg.RCT1)
 AS
 
 OPEN RC1
 SELECT s.acc_itn,
   r.internal_key,
    r.mnemonic,
    r.descp,
    sh.start_dtime,
    sh.end_dtime
 FROM
    schdtl s,
    schdtlhdr sh,
    resource r
 WHERE
    s.acc_itn = piAcc_itn and
    sh.acc_itn = piAcc_itn and
    sh.resource_itn = r.internal_key;
 
 OPEN RC2
 SELECT sdcr.acc_itn,
    sdcr.rsch_dtime,
    sdcr.rsch_by_init,
    sdcr.rsch_code,
    sdcr.rsch_reason,
    sdcr.cncl_dtime,
    sdcr.cncl_by_init,
    sdcr.cncl_code,
    sdcr.cncl_reason,
    sdcr.prev_start_dtime,
    sdcr.prev_by_init
 
 FROM schdtl_canrsch sdcr
 WHERE
    sdcr.acc_itn = piAcc_itn;
 
 OPEN RC3
 SELECT
    sdi.acc_itn,
    i.sched_notes,
    i.post_sched_notes,
    d.pre_sch_notes,
    d.post_sch_notes,
    i.detail_key,
    i.output_notes
 FROM
    schdtl_info sdi,
    extitem i,
    dept d
 WHERE
    sdi.acc_itn = piAcc_itn and
    sdi.actual_dept = i.dept and
    sdi.actual_proc_no = i.proc_no and
    sdi.actual_dept = d.dept;
E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy