I have put together the following plsql and can verify that it does
return the expected result..."the closest existing salary to a user
entered salary of 8000"
The problem i face with this having to make this a stored procedure or
function without whole-sale changes. Any ideas? The required use of
a cursor makes this especially challenging for me.




DECLARE
name empbb02.ename%TYPE;
salary empbb02.sal%TYPE;
TYPE cursor_var IS REF CURSOR;
myCursorVar cursor_var;
TargetSalary empbb02.sal%TYPE;
rk number(5);


BEGIN

OPEN myCursorVar FOR SELECT RANK()OVER(ORDER BY ABS(E.sal - 8000))AS
RNK, E.ename
, E.sal
FROM empbb02 E;

LOOP
FETCH myCursorVar INTO rk, name,salary;
EXIT WHEN myCursorVar%NOTFOUND OR rk ='2';
DBMS_OUTPUT.PUT_LINE(rk||' '||name||' '||salary);
END LOOP;
CLOSE myCursorVar;
END;
/

On 10 avr, 08:54, "matt" <reflectio...@gmail.com> wrote:
> I have put together the following plsql and can verify that it does
> return the expected result..."the closest existing salary to a user
> entered salary of 8000"
> The problem i face with this having to make this a stored procedure or
> function without whole-sale changes. Any ideas? The required use of
> a cursor makes this especially challenging for me.
>
> DECLARE
> name empbb02.ename%TYPE;
> salary empbb02.sal%TYPE;
> TYPE cursor_var IS REF CURSOR;
> myCursorVar cursor_var;
> TargetSalary empbb02.sal%TYPE;
> rk number(5);
>
> BEGIN
>
> OPEN myCursorVar FOR SELECT RANK()OVER(ORDER BY ABS(E.sal - 8000))AS
> RNK, E.ename
> , E.sal
> FROM empbb02 E;
>
> LOOP
> FETCH myCursorVar INTO rk, name,salary;
> EXIT WHEN myCursorVar%NOTFOUND OR rk ='2';
> DBMS_OUTPUT.PUT_LINE(rk||' '||name||' '||salary);
> END LOOP;
> CLOSE myCursorVar;
> END;
> /

Dear Matt, please allow me a few comments :

1) This group is more oriented toward oracle administration
discussions. Questions like the above would be more on-topic
in comp.databases.oracle.misc.

2) People posting here are experts (not me :-). They will gladly
help, but they - rightly - require that documentation has been read
and research has been performed before a question is asked.

3) One way to start with Oracle is to read the concept manual
from top to bottom, and from there to move on to some other
manuals - eg the PL/SQL User's Guide and Reference. The
documentation is freely available at http://tahiti.oracle.com/

4) A great resource to search for is http://asktom.oracle.com/
eg http://asktom.oracle.com/tkyte/ResultSets/index.html

Hope it helps. Have a very nice day

--- Raoul

On Apr 10, 1:01 am, hasta...@hotmail.com wrote:
> On 10 avr, 08:54, "matt" <reflectio...@gmail.com> wrote:
>
>
>
> > I have put together the following plsql and can verify that it does
> > return the expected result..."the closest existing salary to a user
> > entered salary of 8000"
> > The problem i face with this having to make this a stored procedure or
> > function without whole-sale changes. Any ideas? The required use of
> > a cursor makes this especially challenging for me.
>
> > DECLARE
> > name empbb02.ename%TYPE;
> > salary empbb02.sal%TYPE;
> > TYPE cursor_var IS REF CURSOR;
> > myCursorVar cursor_var;
> > TargetSalary empbb02.sal%TYPE;
> > rk number(5);
>
> > BEGIN
>
> > OPEN myCursorVar FOR SELECT RANK()OVER(ORDER BY ABS(E.sal - 8000))AS
> > RNK, E.ename
> > , E.sal
> > FROM empbb02 E;
>
> > LOOP
> > FETCH myCursorVar INTO rk, name,salary;
> > EXIT WHEN myCursorVar%NOTFOUND OR rk ='2';
> > DBMS_OUTPUT.PUT_LINE(rk||' '||name||' '||salary);
> > END LOOP;
> > CLOSE myCursorVar;
> > END;
> > /
>
> Dear Matt, please allow me a few comments :
>
> 1) This group is more oriented toward oracle administration
> discussions. Questions like the above would be more on-topic
> in comp.databases.oracle.misc.
>
> 2) People posting here are experts (not me :-). They will gladly
> help, but they - rightly - require that documentation has been read
> and research has been performed before a question is asked.
>
> 3) One way to start with Oracle is to read the concept manual
> from top to bottom, and from there to move on to some other
> manuals - eg the PL/SQL User's Guide and Reference. The
> documentation is freely available athttp://tahiti.oracle.com/
>
> 4) A great resource to search for ishttp://asktom.oracle.com/
> eghttp://asktom.oracle.com/tkyte/ResultSets/index.html
>
> Hope it helps. Have a very nice day
>
> --- Raoul

sorry about that. thanks for the tip.

On Apr 10, 10:01 am, hasta...@hotmail.com wrote:
> On 10 avr, 08:54, "matt" <reflectio...@gmail.com> wrote:
>
>
>
>
>
> > I have put together the following plsql and can verify that it does
> > return the expected result..."the closest existing salary to a user
> > entered salary of 8000"
> > The problem i face with this having to make this a stored procedure or
> > function without whole-sale changes. Any ideas? The required use of
> > a cursor makes this especially challenging for me.
>
> > DECLARE
> > name empbb02.ename%TYPE;
> > salary empbb02.sal%TYPE;
> > TYPE cursor_var IS REF CURSOR;
> > myCursorVar cursor_var;
> > TargetSalary empbb02.sal%TYPE;
> > rk number(5);
>
> > BEGIN
>
> > OPEN myCursorVar FOR SELECT RANK()OVER(ORDER BY ABS(E.sal - 8000))AS
> > RNK, E.ename
> > , E.sal
> > FROM empbb02 E;
>
> > LOOP
> > FETCH myCursorVar INTO rk, name,salary;
> > EXIT WHEN myCursorVar%NOTFOUND OR rk ='2';
> > DBMS_OUTPUT.PUT_LINE(rk||' '||name||' '||salary);
> > END LOOP;
> > CLOSE myCursorVar;
> > END;
> > /
>
> Dear Matt, please allow me a few comments :
>
> 1) This group is more oriented toward oracle administration
> discussions. Questions like the above would be more on-topic
> in comp.databases.oracle.misc.
>
> 2) People posting here are experts (not me :-). They will gladly
> help, but they - rightly - require that documentation has been read
> and research has been performed before a question is asked.
>
> 3) One way to start with Oracle is to read the concept manual
> from top to bottom, and from there to move on to some other
> manuals - eg the PL/SQL User's Guide and Reference. The
> documentation is freely available athttp://tahiti.oracle.com/
>
> 4) A great resource to search for ishttp://asktom.oracle.com/
> eghttp://asktom.oracle.com/tkyte/ResultSets/index.html
>
> Hope it helps. Have a very nice day
>
> --- Raoul- Hide quoted text -
>
> - Show quoted text -


Please allow me to explain to you the division between the various
groups in comp.databases.oracle

..server is geared at the RDBMS, and SQL
..tools is geared at Oracle frontends
..misc is geared at all non-Oracle products interfacing to Oracle.
..marketplace is for spam
--
Sybrand Bakker
Senior Oracle DBA

On 10 avr, 11:12, "sybrandb" <sybra...@gmail.com> wrote:
> On Apr 10, 10:01 am, hasta...@hotmail.com wrote:
>
>
>
> > On 10 avr, 08:54, "matt" <reflectio...@gmail.com> wrote:
>
> > > I have put together the following plsql and can verify that it does
> > > return the expected result..."the closest existing salary to a user
> > > entered salary of 8000"
> > > The problem i face with this having to make this a stored procedure or
> > > function without whole-sale changes. Any ideas? The required use of
> > > a cursor makes this especially challenging for me.
>
> > > DECLARE
> > > name empbb02.ename%TYPE;
> > > salary empbb02.sal%TYPE;
> > > TYPE cursor_var IS REF CURSOR;
> > > myCursorVar cursor_var;
> > > TargetSalary empbb02.sal%TYPE;
> > > rk number(5);
>
> > > BEGIN
>
> > > OPEN myCursorVar FOR SELECT RANK()OVER(ORDER BY ABS(E.sal - 8000))AS
> > > RNK, E.ename
> > > , E.sal
> > > FROM empbb02 E;
>
> > > LOOP
> > > FETCH myCursorVar INTO rk, name,salary;
> > > EXIT WHEN myCursorVar%NOTFOUND OR rk ='2';
> > > DBMS_OUTPUT.PUT_LINE(rk||' '||name||' '||salary);
> > > END LOOP;
> > > CLOSE myCursorVar;
> > > END;
> > > /
>
> > Dear Matt, please allow me a few comments :
>
> > 1) This group is more oriented toward oracle administration
> > discussions. Questions like the above would be more on-topic
> > in comp.databases.oracle.misc.
>
> > 2) People posting here are experts (not me :-). They will gladly
> > help, but they - rightly - require that documentation has been read
> > and research has been performed before a question is asked.
>
> > 3) One way to start with Oracle is to read the concept manual
> > from top to bottom, and from there to move on to some other
> > manuals - eg the PL/SQL User's Guide and Reference. The
> > documentation is freely available athttp://tahiti.oracle.com/
>
> > 4) A great resource to search for ishttp://asktom.oracle.com/
> > eghttp://asktom.oracle.com/tkyte/ResultSets/index.html
>
> > Hope it helps. Have a very nice day
>
> > --- Raoul- Hide quoted text -
>
> > - Show quoted text -
>
> Please allow me to explain to you the division between the various
> groups in comp.databases.oracle
>
> .server is geared at the RDBMS, and SQL
> .tools is geared at Oracle frontends
> .misc is geared at all non-Oracle products interfacing to Oracle.
> .marketplace is for spam
> --
> Sybrand Bakker
> Senior Oracle DBA


Dear sybrandb,

I had hoped to help the group and avoid the OP a flame
with the information above... I am sorry if it is incorrect,
and I thank you for the clarifications.

However, note that the information I provided come from
the charters of c.d.o.misc and c.d.o.server :

CHARTER: comp.databases.oracle.server

Comp.databases.oracle.server is a newsgroup to discuss Oracle server
and database administration subjects. Any topic which is especially
of
interest to those performing database or system administration duties
is welcome in this group.

CHARTER: comp.databases.oracle.misc

Comp.databases.oracle.misc is a news group where topics generally
related
to the use of software from Oracle Corporation may be posted. This
group
provides a forum for topics which do not fall within any of the more
specific
comp.databases.oracle subgroups.

Sources :

http://orafaq.com/usenet/charter1.htm
http://orafaq.com/usenet/charter3.htm

May I know where exactly I messed up ?

Thanks

--- Raoul

On Apr 10, 1:23 pm, hasta...@hotmail.com wrote:
> On 10 avr, 11:12, "sybrandb" <sybra...@gmail.com> wrote:
>
>
>
>
>
> > On Apr 10, 10:01 am, hasta...@hotmail.com wrote:
>
> > > On 10 avr, 08:54, "matt" <reflectio...@gmail.com> wrote:
>
> > > > I have put together the following plsql and can verify that it does
> > > > return the expected result..."the closest existing salary to a user
> > > > entered salary of 8000"
> > > > The problem i face with this having to make this a stored procedure or
> > > > function without whole-sale changes. Any ideas? The required use of
> > > > a cursor makes this especially challenging for me.
>
> > > > DECLARE
> > > > name empbb02.ename%TYPE;
> > > > salary empbb02.sal%TYPE;
> > > > TYPE cursor_var IS REF CURSOR;
> > > > myCursorVar cursor_var;
> > > > TargetSalary empbb02.sal%TYPE;
> > > > rk number(5);
>
> > > > BEGIN
>
> > > > OPEN myCursorVar FOR SELECT RANK()OVER(ORDER BY ABS(E.sal - 8000))AS
> > > > RNK, E.ename
> > > > , E.sal
> > > > FROM empbb02 E;
>
> > > > LOOP
> > > > FETCH myCursorVar INTO rk, name,salary;
> > > > EXIT WHEN myCursorVar%NOTFOUND OR rk ='2';
> > > > DBMS_OUTPUT.PUT_LINE(rk||' '||name||' '||salary);
> > > > END LOOP;
> > > > CLOSE myCursorVar;
> > > > END;
> > > > /
>
> > > Dear Matt, please allow me a few comments :
>
> > > 1) This group is more oriented toward oracle administration
> > > discussions. Questions like the above would be more on-topic
> > > in comp.databases.oracle.misc.
>
> > > 2) People posting here are experts (not me :-). They will gladly
> > > help, but they - rightly - require that documentation has been read
> > > and research has been performed before a question is asked.
>
> > > 3) One way to start with Oracle is to read the concept manual
> > > from top to bottom, and from there to move on to some other
> > > manuals - eg the PL/SQL User's Guide and Reference. The
> > > documentation is freely available athttp://tahiti.oracle.com/
>
> > > 4) A great resource to search for ishttp://asktom.oracle.com/
> > > eghttp://asktom.oracle.com/tkyte/ResultSets/index.html
>
> > > Hope it helps. Have a very nice day
>
> > > --- Raoul- Hide quoted text -
>
> > > - Show quoted text -
>
> > Please allow me to explain to you the division between the various
> > groups in comp.databases.oracle
>
> > .server is geared at the RDBMS, and SQL
> > .tools is geared at Oracle frontends
> > .misc is geared at all non-Oracle products interfacing to Oracle.
> > .marketplace is for spam
> > --
> > Sybrand Bakker
> > Senior Oracle DBA
>
> Dear sybrandb,
>
> I had hoped to help the group and avoid the OP a flame
> with the information above... I am sorry if it is incorrect,
> and I thank you for the clarifications.
>
> However, note that the information I provided come from
> the charters of c.d.o.misc and c.d.o.server :
>
> CHARTER: comp.databases.oracle.server
>
> Comp.databases.oracle.server is a newsgroup to discuss Oracle server
> and database administration subjects. Any topic which is especially
> of
> interest to those performing database or system administration duties
> is welcome in this group.
>
> CHARTER: comp.databases.oracle.misc
>
> Comp.databases.oracle.misc is a news group where topics generally
> related
> to the use of software from Oracle Corporation may be posted. This
> group
> provides a forum for topics which do not fall within any of the more
> specific
> comp.databases.oracle subgroups.
>
> Sources :
>
> http://orafaq.com/usenet/charter1.htm
> http://orafaq.com/usenet/charter3.htm
>
> May I know where exactly I messed up ?
>
> Thanks
>
> --- Raoul- Hide quoted text -
>
> - Show quoted text -

I'm not sure. I don't recall this is the original distinction, and I
don't agree with the Orafaq definition. I'm positive
comp.databases.oracle wasn't renamed to comp.databases.oracle.misc, in
fact it existed until last year.
So I am afraid the moderator of orafaq made something up, and one
would need to go back to the original ballot, to confirm the
distinction which was made at that time.
On average however, I have a very good memory, so I'm positive I am
correct.

--
Sybrand Bakker
Senior Oracle DBA

On 10 avr, 14:07, "sybrandb" <sybra...@gmail.com> wrote:
> On Apr 10, 1:23 pm, hasta...@hotmail.com wrote:
>
> > > > 1) This group is more oriented toward oracle administration
> > > > discussions. Questions like the above would be more on-topic
> > > > in comp.databases.oracle.misc.
>
>
> > > Please allow me to explain to you the division between the various
> > > groups in comp.databases.oracle
>
> > > .server is geared at the RDBMS, and SQL
> > > .tools is geared at Oracle frontends
> > > .misc is geared at all non-Oracle products interfacing to Oracle.
> > > .marketplace is for spam
> > > --
>
> > However, note that the information I provided come from
> > the charters of c.d.o.misc and c.d.o.server :
>
> > CHARTER: comp.databases.oracle.server
>
> > Comp.databases.oracle.server is a newsgroup to discuss Oracle server
> > and database administration subjects. Any topic which is especially
> > of
> > interest to those performing database or system administration duties
> > is welcome in this group.
>
> > CHARTER: comp.databases.oracle.misc
>
> > Comp.databases.oracle.misc is a news group where topics generally
> >related
> > to the use of software from Oracle Corporation may be posted. This
> > group
> > provides a forum for topics which do not fall within any of the more
> > specific
> > comp.databases.oracle subgroups.
>
> > Sources :
>
> > http://orafaq.com/usenet/charter1.htm
> > http://orafaq.com/usenet/charter3.htm
>
> > May I know where exactly I messed up ?
>
> > Thanks
>
>
> I'm not sure. I don't recall this is the original distinction, and I
> don't agree with the Orafaq definition. I'm positive
> comp.databases.oracle wasn't renamed to comp.databases.oracle.misc, in
> fact it existed until last year.
> So I am afraid the moderator of orafaq made something up, and one
> would need to go back to the original ballot, to confirm the
> distinction which was made at that time.
> On average however, I have a very good memory, so I'm positive I am
> correct.

Interesting....

I did a bit of archeology.... The charters as available on orafaq
have been posted in c.od.server and c.o.d.misc in 1996

http://tinyurl.com/2xv23e
http://tinyurl.com/23tko6

To get a feeling of actual usage, I classified the best I could
the threads still available on my newsfeed.. The results are :


c.d.o.server c.d.o.misc
Oracle administration 48 0
Oracle non-administration 19 14
Interaction Third-party/Oracle 5 3
Other 3 3
Overlap (execution plans) 2 0