Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00103
PLS-00103 [message #284124] Wed, 28 November 2007 23:53 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Hi All .. Where am I doing wrong?

  1  create or replace procedure uza
  2  as
  3  cursor c1 is select regno,rpad(grn.name,40) as name,grn.faculty,round(cgpa,2) as cgpa,
  4  (dense_rank () over(partition by substr(regno,1,4) order by round(cgpa,2) desc)) as cgpa_rank,
  5  (count(regno) over(partition by substr(regno,1,4))) as batch_total
  6  from it,grn
  7  where it.regno=grn.rn
  8  and sc=1 and sy=2004
  9  and regno not like '9%';
 10  begin
 11  for x in c1 loop
 12  null;
 13  end loop;
 14* end;
SQL> /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE UZA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/16     PLS-00103: Encountered the symbol "OVER" when expecting one of
         the following:
         . ( ) , * % & | = - + < / > at in mod not range rem => ..
         <an exponent (**)> <> or != or ~= >= <= <> and or like
         between is null is not || is dangling

SQL> 




Thanks
rz
Re: PLS-00103 [message #284126 is a reply to message #284124] Thu, 29 November 2007 00:01 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Did you try it as a stand alone sql statement?
Re: PLS-00103 [message #284133 is a reply to message #284124] Thu, 29 November 2007 00:08 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Yes , of course. below is the output

SQL> select regno,rpad(grn.name,40) as name,grn.faculty,round(cgpa,2) as cgpa,
  2  dense_rank () over(partition by substr(regno,1,4) order by round(cgpa,2) desc) as cgpa_rank,
  3  count(regno) over(partition by substr(regno,1,4)) as batch_total
  4  from it,grn
  5  where it.regno=grn.rn
  6  and sc=1 and sy=2004
  7  and regno not like '9%'
  8  
SQL> /

MOD-Edit: Frank removed results on poster's request, since it showed personal data




rz

[Updated on: Mon, 28 June 2010 01:25] by Moderator

Report message to a moderator

Re: PLS-00103 [message #284140 is a reply to message #284124] Thu, 29 November 2007 00:27 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Analytic functions (and CASE expressions) are not supported in 8i PL/SQL where as they are supported in SQL.
Create a view with the same query and use the view in the procedure.

Regards
Michel

[Updated on: Thu, 29 November 2007 00:27]

Report message to a moderator

Previous Topic: dbms_stats
Next Topic: abt index
Goto Forum:
  


Current Time: Fri Dec 02 20:23:00 CST 2016

Total time taken to generate the page: 0.18939 seconds