Re: Help : Convert interbase stored proc

Thanks for the reply.

I've managed to crack it but the main thing I was having difficulty with was returning a complete dataset from a procedure / function. In Interbase, suspend will actually return the row of data, so looping through a dataset you can choose what you return. Oracle doesn't seem to support this and you end up having to jump through hoops to do this simple task.
To get round it i've created a table "type" which I populate as I loop through the function. I then return this table at the end of the function. As a result, my function is roughly 4 times longer than the equivalent interbase version.

Will

"mcstock" <mcstockx_at_xenquery.com> wrote in message news:<v5WdnYDGaq3xmzWiRVn-gw_at_comcast.com>...
> study out the PL/SQL Users' Guide for syntax details
>
> the differences i notice are:
>
> the 'cursor for loop' syntax is difference (requires a record name, parens
> around the SQL, LOOP .. END LOOP keywords
>
> the suspend keyword looks like the equivalent of EXIT, RETURN or
> RAISE_APPLICATION_ERROR
>
> procedures don't have return values, in PL/SQL only functions have return
> values
>
> however, functions can only have a single return value (with the keyword
> RETURN, not RETURNS) -- multiple 'output' values can be passed via output
> parameters (parameters are input by default, can be declared as IN, OUT or
> IN OUT)
>
> you must explicitly set return values for output parameter values -- there
> is not implied relationship between parameters and procedure DML
>
> PL/SQL has a declare section in which variables are declared -- declare
> keyword is used once, variable keyword is not used in PL/SQL
>
> 'if' structures are terminated with 'end if'
>
> (i assume your code omitted the logic that sets LastVal)
>
> --
> Mark C. Stock
> email mcstock -> enquery(dot)com
> www.enquery.com
> (888) 512-2048
>
>
> "William Buchanan" <william.buchanan_at_freenet.co.uk> wrote in message
> news:26d39d38.0311030426.768726eb_at_posting.google.com...
> > Hi
> >
> > I have the following stored proc in interbase (which might contain
> > errors - i'm doing it off the top of my head), which I would like to
> > convert into oracle. Can you help? What I want back is a dataset of
> > all the records where Ch_Val is different to the previous value.
> >
> > Thanks for any help.
> >
> > create procedure GetChanges(StartDate timestamp, EndDate timestamp)
> > returns(SettDate timestamp, Ch_ID integer, Ch_Val integer)
> > as
> > declare variable lastVal integer;
> > begin
> > lastVal = -1;
> > for select * from ChData
> > where SettDate between :StartDate and :EndDate
> > order by SetDate
> > do
> > begin
> > if Ch_Val <> lastVal then
> > suspend;
> > end;
> > end
Received on Wed Nov 05 2003 - 10:46:00 CET