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

A copy of this was sent to "Edmund Landgraf" <elandgraf_at_nospam.1rent.com> (if that email address didn't require changing) On Fri, 6 Aug 1999 12:19:35 -0700, you wrote:

>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???
>

exp both users.
imp both users.

don't need to use DDL scripts, just exp the data, then exp the code. then imp the data, imp the code...

>Problem 2:
>
>Since I can't do a straight select out of my packages, I use custom user

yes you can.

>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?
>

why not use cursor variables and ResultSets in java. see my .sig for an example "getting result sets...".

shows how it works in sqlplus for testing. shows how to use java to get the result set.

much more natural i believe then flinging these arrays around...

>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
>
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Aug 06 1999 - 15:51:41 CDT