Re: help on dual schemas and returning nested table types

I believe Oracle Enterprise Manager has features to easily replicate schemas on another server.

Christopher

Edmund Landgraf <elandgraf_at_nospam.1rent.com> wrote in message news:rr5vtqk9krl38_at_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 Thu Aug 12 1999 - 12:51:51 CDT