By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

Help : Convert interbase stored proc

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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" <wi**************@freenet.co.uk> wrote in message
news:26**************************@posting.google.c om...
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

Jul 19 '05 #2

P: n/a
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" <mc******@xenquery.com> wrote in message news:<v5********************@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" <wi**************@freenet.co.uk> wrote in message
news:26**************************@posting.google.c om...
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

Jul 19 '05 #3

P: n/a
i don't know if this would simplify the function for you, but you could try
a structure like this:

create or replace function get_emp(
ip_empno in emp.empno%type
)
return emp%rowtype
is
returnRow emp%rowtype;
begin
for r1 in (
select *
from emp
where empno = ip_empno
)
loop
if 1 < 2 -- some condition that makes theloop necessary
then
returnRow := r1;
exit;
end if;
end loop;
return returnRow;
end;
/

if you don't want to return the entire row, then you do need to create a
type as you indicated, or put the code in a package and declare a cursor in
the package to use with '%ROWTYPE':

create or replace package emp_stuff
is
cursor crsr_empNameSal(cp_empno in emp.empno%type)
is
select ename, sal
from emp
where empno = cp_empno
order by ename;

function get_emp (
ip_empno in emp.empno%type
)
return crsr_empNameSal%rowtype;

end emp_stuff;
/
create or replace package body emp_stuff
is
function get_emp (
ip_empno in emp.empno%type
)
return crsr_empNameSal%rowtype
is
returnRow crsr_empNameSal%rowtype;
begin
for r1 in crsr_empNameSal(ip_empno)
loop
if 1 < 2 -- some condition that makes this loop necessary
then
returnRow := r1;
exit;
end if;
end loop;
return returnRow;
end get_emp;
end emp_stuff;
/

also, did you see if you can avoid the loop altogether by using a SQL
statement with subqueries? (may or may not be worth the effort, but always a
good idea to try to avoid explicit looping)

-- mcs

"mcstock" <mc******@xenquery.com> wrote in message
news:v5********************@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" <wi**************@freenet.co.uk> wrote in message
news:26**************************@posting.google.c om...
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


Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.