Home » SQL & PL/SQL » SQL & PL/SQL » Setting SELECT Count(*) Value Into Output Variable (Oracle Developer 11g)
Setting SELECT Count(*) Value Into Output Variable [message #503060] Tue, 12 April 2011 11:05 Go to next message
BennyBoy76
Messages: 4
Registered: March 2011
Location: UK
Junior Member
I'm trying to return the number of records in my link table that contains the excursion_id I pass in by counting them. It doesn't seem to like the select count(*) into my output variable. Has anyone got any ideas?

Thanks.............Ben


create or replace
PROCEDURE BOOK_PASSENGER(
    EXCURSION_ID IN excursion_booking.excursion_id%TYPE,
    PASSENGER_ID IN excursion_booking.passenger_id%TYPE,
    NUMBER_BOOKED OUT NUMBER)
    
AS

BEGIN 

INSERT INTO EXCURSION_BOOKING VALUES(EXCURSION_ID, PASSENGER_ID, NULL);

SELECT COUNT(*) INTO NUMBER_BOOKED FROM EXCURSION_BOOKING WHERE EXCURSION_ID = EXCURSION_ID;

END BOOK_PASSENGER;

[Updated on: Tue, 12 April 2011 11:10]

Report message to a moderator

Re: Setting SELECT Count(*) Value Into Output Variable [message #503061 is a reply to message #503060] Tue, 12 April 2011 11:11 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Quote:
It doesn't seem to like the select count(*) into my outpur variable.
What is that mean?
Can't you give any different parameter names than the column names?

By
Vamsi

Report message to a moderator

Re: Setting SELECT Count(*) Value Into Output Variable [message #503063 is a reply to message #503060] Tue, 12 April 2011 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
It doesn't seem to like the select count(*) into my output variable. Has anyone got any ideas?

Did you read/display the variable?

Regards
Michel

Report message to a moderator

Re: Setting SELECT Count(*) Value Into Output Variable [message #503074 is a reply to message #503060] Tue, 12 April 2011 11:52 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
BennyBoy76 wrote on Tue, 12 April 2011 12:05

SELECT COUNT(*) INTO NUMBER_BOOKED FROM EXCURSION_BOOKING WHERE EXCURSION_ID = EXCURSION_ID;


This is just poor code here. Either this will return every row in the table as it is the same as saying where 1=1, or how do you think Oracle is supposed to know the difference between a variable and a table column?

Report message to a moderator

Re: Setting SELECT Count(*) Value Into Output Variable [message #503088 is a reply to message #503074] Tue, 12 April 2011 15:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA
Senior Member
It is a common practice to preface input and output parameter names of table.column%type with p_. That avoids conflicts with column names, so that Oracle knows whether you mean the column or the parameter. It also makes the code easier to read if any time that you see p_ you know it is an input or ouput parameter. Some people even use p_in_... and p_out_... or p_..._in and p_..._out.

create or replace PROCEDURE BOOK_PASSENGER
  (p_EXCURSION_ID  IN  excursion_booking.excursion_id%TYPE,
   p_PASSENGER_ID  IN  excursion_booking.passenger_id%TYPE,
   p_NUMBER_BOOKED OUT NUMBER)
AS
BEGIN 
  INSERT INTO EXCURSION_BOOKING VALUES
   (p_EXCURSION_ID, p_PASSENGER_ID, NULL);
  SELECT COUNT(*) 
  INTO   p_NUMBER_BOOKED 
  FROM   EXCURSION_BOOKING 
  WHERE  EXCURSION_ID = p_EXCURSION_ID;
END BOOK_PASSENGER;
/

Report message to a moderator

Re: Setting SELECT Count(*) Value Into Output Variable [message #503104 is a reply to message #503088] Wed, 13 April 2011 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
To add to Barbara, the best is to always use aliases and prefixes:
create or replace PROCEDURE BOOK_PASSENGER
  (p_EXCURSION_ID  IN  excursion_booking.excursion_id%TYPE,
   p_PASSENGER_ID  IN  excursion_booking.passenger_id%TYPE,
   p_NUMBER_BOOKED OUT NUMBER)
AS
BEGIN 
  INSERT INTO EXCURSION_BOOKING VALUES
   (p_EXCURSION_ID, p_PASSENGER_ID, NULL);
  SELECT COUNT(*) 
  INTO   p_NUMBER_BOOKED 
  FROM   EXCURSION_BOOKING e
  WHERE  e.EXCURSION_ID = BOOK_PASSENGER.p_EXCURSION_ID;
END BOOK_PASSENGER;
/

Regards
Michel

Report message to a moderator

Re: Setting SELECT Count(*) Value Into Output Variable [message #503209 is a reply to message #503104] Wed, 13 April 2011 09:51 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
yes, this is an excellent example of how scoping rules work. Notice that the sql is not invalid. The code is presented with to objects of different types with the same name both of which are valid in the context being used (aka. a parameter name vs. a column name). How is oracle to decide which one to use. Thus scoping rules.

This is an all too common practice: to not name parameters with some kind of prefix or suffix or otherwise make them so that they do not match column names. The other side of the fence is to remember that if you are generally going to use a prefix or suffix to name parameters in your code, don't name columns using the same kind of strategy (hehe).

Good luck, Kevin

Report message to a moderator

Previous Topic: help translating an update query in access to oracle! please!
Next Topic: SELECT records in the order they were inserted
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri May 16 11:19:25 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.