Re: Cursor Parameter Problems/Question

From: Rupert Kemp <rupert.kemp_at_gmail.com>
Date: Sun, 9 Apr 2006 19:58:47 +0100
Message-ID: <123imd7d40ubr57@news.supernews.com>

It's not the NVL statement as such - any function would cause this. It is not a problem, it's a feature. The problem is that you do not have any column in your cursor called "hits". Once you apply a function (like NVL), the column is no longer referenced by the underlying column name. You need to alias the column(s) like this in your cursor:

CURSOR c_batting ( p_player_id_char IN in_player.player_id_char%TYPE ) IS
  SELECT year,

         stint,
         team,
         nvl(at_bats,0) AS at_bats,
         nvl(runs,0) AS runs,
         nvl(hits,0) AS hits,
         nvl(doubles,0) AS doubles,
         nvl(triples,0) AS triples,
         nvl(home_runs,0) AS home_runs,
         nvl(rbi,0) AS rbi

   FROM in_batting
   WHERE player_id_char = p_player_id_char    AND year < 1995;

The keyword "AS" is optional.

Peter Crump

"Dereck L. Dietz" <dietzdl_at_ameritech.net> wrote in message news:h9b_f.642$Lm5.337_at_newssvr12.news.prodigy.com...
> I'm trying to write a package and in the package header I have this cursor
> declared:
>
> CURSOR c_batting ( p_player_id_char IN in_player.player_id_char%TYPE )
> IS
> SELECT year,
> stint,
> team,
> nvl(at_bats,0),
> nvl(runs,0),
> nvl(hits,0),
> nvl(doubles,0),
> nvl(triples,0),
> nvl(home_runs,0),
> nvl(rbi,0)
> FROM in_batting
> WHERE player_id_char = p_player_id_char
> AND year < 1995;
>
> In my package body I'm trying to get this section of code to work:
>
> PROCEDURE Load_BattingInfo ( p_player_id_char IN
> in_player.player_id_char%TYPE )
> IS
>
> v_singles batting.singles%TYPE;
>
> BEGIN
>
> FOR c1 IN c_batting( p_player_id_char )
> LOOP
>
> v_singles := ( c1.hits - ( c1.doubles + c1.triples +
c1.home_runs ) );
>
> END LOOP;
>
> END Load_BattingInfo;
>
> The problem I'm getting is that I get 'PLS-00302: component 'HITS' must be
> declared'. I thought I was passing the parameter correctly but I'm
> obviously not doing something correctly and I can't find the answer in any
> of my books.
>
> Can/will anybody please tell me what I'm doing wrong?
>
> Thanks very much.
>
>
Received on Sun Apr 09 2006 - 13:58:47 CDT