Repost -- could someone please give me some opinions.. thx

From: Edmund Landgraf <elandgraf_at_nospam.1rent.com>
Date: Fri, 6 Aug 1999 12:19:35 -0700
Message-ID: <rqmd20f1kur6@corp.supernews.com>

Hi folks,

Problem 1:

I've got two schemas (in 8.1.5), one that holds my tables (DEV) and one that holds my views and packages (CODE)

I'm looking for an easy way to copy both these schemas to a another machine.

I've used IMP to get the data across, and then DDL scripts from there... but there must be a cleaner way???

Problem 2:

Since I can't do a straight select out of my packages, I use custom user types (nested tables -- see below) to put things into single column arrays and rearrange it on the Java side (calling program).

I need to be able to test these packages. SQL Navigator does a good job of normally writing the anonymous code block, doing the declares, but doesn't write the loop to do the output to dbms_output.

How do others test their packages that need to do a "select" our of the DB?

Example:

type RENTAGENT.date_arr as table of date;

Package body RENTAGENT.SHOWINGSCHEDULE
  IS

procedure getUnitSchedule

    (p_UnitID       in      integer,
     v_ShowingDate  out     date_arr,
     v_StartTime    out     date_arr,
     v_EndTime      out     date_arr) is

     cursor cur_listing (p_UnitID in integer) is
     select
        ul.UnitID,
        ul.ListingID,
        ls.ShowingDate,
        ls.StartTime,
        ls.EndTime
     from
        dev.ListingShowing ls,
        dev.UnitListing ul
     where
        ls.ListingID    = ul.ListingID    and
        ul.UnitID       = p_UnitID;

     rec_listing cur_listing%RowType;

     v_mult pls_integer;

     BEGIN

     v_ShowingDate  :=   date_arr();
     v_StartTime    :=   date_arr();
     v_EndTime      :=   date_arr();

     v_mult :=1;

     for rec_listing in cur_listing(p_UnitID) loop
        v_ShowingDate.EXTEND;
        v_StartTime.EXTEND;
        v_EndTime.EXTEND;

        v_ShowingDate(v_mult)   :=  rec_listing.ShowingDate;
        v_StartTime(v_mult)     :=  rec_listing.StartTime;
        v_EndTime(v_mult)       :=  rec_listing.EndTime;

        v_mult := v_mult + 1;

      end loop;

   END; Thoughts?

Thx,
-Edmund Received on Fri Aug 06 1999 - 14:19:35 CDT