Hi Tom,
I am currently attending th DBA 8i tract. I have completed the PL/SQL
intro., and has accepted a position in an Oracle shop. My first task
is to convert several SQL Server scripts to Oracle. My situation is that
I have not worked on either products before..
Could you advise on how I can convert the following script to an
Oracle stored procedure..I am contemplating using context, corsors,
packages, and procedures.
I would appreciate very much any help..a conversion with comments of the following would get me started.
I thank you in advance.

> CREATE procedure prLIVE_SALE_SOLD_RPT
> @StartDate datetime,
> @EndDate datetime,
> @Site varchar(4),
> @Make varchar(20),
> @Model varchar(20),
> @Sponsor varchar(30)
>
> as
>
> set nocount on
>
> declare
> @Year varchar(4),
> @Make1 varchar(50),
> @Model1 varchar(50),
> @vin varchar(17),
> @Sku varchar(50),
> @SellingPrice decimal(11,2),
> @ReservePrice decimal(11, 2),
> @DealerId int,
> @DealerName varchar(50),
> @City varchar(40),
> @Province varchar(2),
> @NumberOfBids int,
> @NumberOfUniqueBidders int,
> @NumberOfVehiclesOffered int,
> @TotalNumberOfUniqueBidders int,
> @IsArchived char(1),
> @sponsorId int,
> @sellerName varchar(50),
> @eventId int
>
>
>
>
> create table #result(
> event_id int null,
> site_code varchar(4) null,
> [Year] varchar(4) null,
> Make varchar(50) null,
> Model varchar(50) null,
> vin varchar(17) null,
> Sku varchar(50) null,
> StartPrice decimal(11,2) null,
> SellingPrice decimal(11,2) null,
> ReservePrice decimal(11, 2) null,
> StartTime datetime null,
> EndTime datetime null,
> DealerId int null,
> DealerName varchar(50) null,
> City varchar(40) null,
> Province varchar(2) null,
> NumberOfBids int null,
> NumberOfUniqueBidders int null,
> NumberOfVehiclesOffered int null,
> TotalNumberOfUniqueBidders int null,
> sellerName varchar(50) null,
> IsArchived char(1) null
> )
>
> create table #tmp (
> business_id int null
> )
>
> select @EndDate = DATEADD(day, 1, @EndDate)
> select @Site = upper(rtrim(@Site))
>
> select @SponsorId = 0
> select @SellerName = 'ALL'
>
> if upper(@Sponsor) != 'ALL'
> begin
> select @SponsorId = sponsor_id from sponsor where name like @Sponsor
> + '%'
> select @SellerName = name from sponsor where sponsor_id =
@sponsorId
> end
>
>
> insert into #result(
> Sku,
> SellingPrice,
> ReservePrice,
> event_id,
> site_code,
> StartPrice,
> StartTime,
> EndTime,
> DealerId,
> DealerName,
> City,
> Province,
> IsArchived
> )
> select
> x1.sku, x1.current_price, x1.reserved_price, x7.auction_event_id,
> substring(x1.sku,1,4),
> x1.start_price, x7.start_timestamp, x7.end_timestamp,
x3.business_id,
> x3.[name], x5.city, x6.prov_code, 'N'
> from
> auction_item as x1 join
> ice_user as x2 on (x1.high_bid_acct_id = x2.[user_id]) join
> business as x3 on (x2.business_id = x3.business_id) join
> business_address as x4 on (x3.business_id = x4.business_id) join
> address as x5 on (x4.address_id = x5.address_id) join
> province as x6 on (x5.province_id = x6.province_id),
> auction_event as x7
> where
> x1.current_price >= x1.reserved_price and
> x1.start_date >= @StartDate and
> x1.start_date <= @EndDate
> and ((@Site = 'ALL') or (substring(x1.sku, 1, 4)= @Site))
> and x1.auction_event_id = x7.auction_event_id
> and ((@SponsorId = 0) or (x7.sponsor_id = @SponsorId))
>
>
>
> declare result_cursor cursor for
> select sku from #result
>
> open result_cursor
> fetch next from result_cursor
> into @sku
> while @@FETCH_STATUS = 0
> begin
> begin
> SELECT @Year = VALUE
> FROM PRODUCT_SKU_DICTIONARY t1
> WHERE (KEY_NAME = 'year') and SKU = @sku
>
> SELECT @Make1 = t2.VALUE
> FROM PRODUCT_SKU t1 INNER JOIN
> PRODUCT_LOOKUP t3 ON (t1.PRODUCT_ID = t3.PRODUCT_ID) INNER JOIN
> LOOKUP_DETAIL t2 ON (t3.LOOKUP_DETAILS_ID = t2.LOOKUP_DETAIL_ID)
> WHERE (t2.KEY_NAME = 'make') and t1.SKU = @sku
>
> SELECT @Model1 = t2.VALUE
> FROM PRODUCT_SKU t1 INNER JOIN
> PRODUCT_LOOKUP t3 ON (t1.PRODUCT_ID = t3.PRODUCT_ID) INNER JOIN
> LOOKUP_DETAIL t2 ON (t3.LOOKUP_DETAILS_ID = t2.LOOKUP_DETAIL_ID)
> WHERE (t2.KEY_NAME = 'model') and t1.SKU = @sku
>
> SELECT @Vin = VALUE
> FROM PRODUCT_SKU_DICTIONARY t1
> WHERE (KEY_NAME = 'vin') and SKU = @sku
>
> select @NumberOfBids = count(*) from
> bid as x1 join
> auction_item as x2 on (x1.auction_item_id = x2.auction_item_id)
> where
> x2.sku = @sku
>
> select @NumberOfUniqueBidders = count(distinct x3.business_id)
from
> bid as x1 join
> auction_item as x2 on (x1.auction_item_id = x2.auction_item_id)
> join
> ice_user as x3 on (x1.user_id = x3.user_id)
> where
> x2.sku = @sku
>
> end
>
> update #result
> set
> [year] = @Year,
> make = @Make1,
> model = @Model1,
> vin = @Vin,
> NumberOfBids = @NumberOfBids,
> NumberOfUniqueBidders = @NumberOfUniqueBidders,
> sellerName = @SellerName
> where sku = @sku
>
> fetch next from result_cursor
> into @sku, @isarchived
> end
>
> close result_cursor
> deallocate result_cursor
>
>
> declare resultCursor cursor for
> select distinct(event_id)
> from #result
>
> open resultCursor
> fetch next from resultCursor
> into @eventId
>
> while @@FETCH_STATUS = 0
> begin
>
>
> insert into #tmp
> select x3.business_id
> from auction_item as x1 join
> bid as x2 on x1.auction_item_id = x2.auction_item_id join
> ice_user as x3 on x2.user_id = x3.user_id
> where
> x1.start_date >= @StartDate and
> x1.start_date <= @EndDate and
> x1.auction_event_id = @eventId
>
> -- ((@Site = 'All') or (substring(x1.sku, 1, 4) = @Site))
>
> select @TotalNumberOfUniqueBidders = 0
> select @NumberOfVehiclesOffered = 0
>
> select @TotalNumberOfUniqueBidders = count(*) from #tmp
> delete from #tmp
>
> select @NumberOfVehiclesOffered = count(sku) from
> auction_item
> where
> start_date >= @StartDate and
> start_date <= @EndDate and
> auction_event_id = @eventId
>
> -- ((@Site = 'ALL') or (substring(sku, 1, 4)= @Site))
>
> select @NumberOfVehiclesOffered = @NumberOfVehiclesOffered +
count(sku)
> from
> adesa_arc..auction_item
> where
> start_date >= @StartDate and
> start_date <= @EndDate and
> auction_event_id = @eventId
>
> -- ((@Site = 'ALL') or (substring(sku, 1, 4)= @Site))
>
>
> update #result
> set
> TotalNumberOfUniqueBidders = @TotalNumberOfUniqueBidders,
> NumberOfVehiclesOffered = @NumberOfVehiclesOffered
> where event_id = @eventId
>
> fetch next from resultCursor
> into @eventId
> end
>
> close resultCursor
> deallocate resultCursor
>
>
>
>
>
> select * from #result
> where
> ((Upper(RTrim(@Make)) = 'ALL') or (make = @Make)) and
> ((Upper(RTrim(@Model)) = 'ALL') or (model = @Model))
> order by event_id
>
>
>
>
> /*