Home » SQL & PL/SQL » SQL & PL/SQL » Help, procedure not compiling
Help, procedure not compiling [message #18366] Tue, 29 January 2002 10:53 Go to next message
Hitesh
Messages: 12
Registered: October 2000
Junior Member
I am trying to perform a simple select query where the value of variable "x" is used as a denominator in a mathematical expression. See the second select query for reference. I received an error shown at the end of the procedure. I would appreciate any feedback. thank you.

Declare
x number;
Begin
select count(*) into x
from GREATBRITAIN
where class!='NSS'
and class!='CSC'
and class!='BLDG';
select creator, count(creator)/x
from GREATBRITAIN
where class!='NSS'
and class!='CSC'
and class!='BLDG'
group by creator
order by count(creator) desc
END;
/

ERROR at line 16:
ORA-06550: line 16, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
, ; for
The symbol ";" was substituted for "END" to continue.
Re: Help, procedure not compiling [message #18367 is a reply to message #18366] Tue, 29 January 2002 11:45 Go to previous messageGo to next message
JonPease
Messages: 2
Registered: June 2001
Junior Member
Put in a semi-colon after the desc in the second query; also, I think you'll need an "into" on that query.
Re: Help, procedure not compiling [message #18370 is a reply to message #18366] Tue, 29 January 2002 12:37 Go to previous messageGo to next message
Hitesh
Messages: 12
Registered: October 2000
Junior Member
Hi Jim,
Thanks to your help, I'm a little closer to getting this procedure to work. However, being somewhat of an ORACLE novice, I'm not sure i understand your second suggestion about an INTO CLAUSE. Will I need to create a dummy table and then reference that table in my procedure's sql statement? If you have an example, that might be of some help. Thanks again for the help!
Re: Help, procedure not compiling [message #18379 is a reply to message #18366] Tue, 29 January 2002 15:49 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
set serveroutput on
Declare
x number;
l_creator GREATBRITAIN.creator%type;
l_count number;

Begin
select count(*) into x
from GREATBRITAIN
where class!='NSS'
and class!='CSC'
and class!='BLDG';
select creator, count(creator)/x INTO l_creator,l_count
from GREATBRITAIN
where class!='NSS'
and class!='CSC'
and class!='BLDG'
group by creator
order by count(creator) desc;
EXCEPTION
WHEN too_many_rows then
dbms_output.put_line('query returned more than one row.. use cursor');

END;
Previous Topic: How to query records with same value in specific columns from a table
Next Topic: How to use strong typed ref cursor in dynamic SQL?
Goto Forum:
  


Current Time: Thu Mar 28 08:54:20 CDT 2024