Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00922: missing or invalid option (ORACLE 10G, WINDOWS)
ORA-00922: missing or invalid option [message #578304] Tue, 26 February 2013 13:14 Go to next message
shankar680
Messages: 11
Registered: February 2013
Location: HYDERABAD
Junior Member
create or replace p_aggop(dno number, maxi number,mini number,avgi number,tot number,cnt number) is
cursor c1 is select * from emp1 WHERE DEPTNO=dno GROUP BY DEPTNO;
v_emp1 c1%rowtype;
begin
open c1;
loop
fetch c1 into v_emp1;
select min(v_emp1.sal),max(v_emp1.sal),avg(v_emp1.sal),sum(v_emp1.sal),count(*) into mini,maxi,avgi,tot,cnt from v_emp1;
exit when c1%notfound;
Dbms_output.put_line('EXECUTED ');
end loop;
dbms_output.put_line('The minimum salaray in dept is :'||v_emp1.mini);
dbms_output.put_line('The maximum salaray in dept is :'||v_emp1.maxi);
dbms_output.put_line('The average salaray in dept is :'||v_emp1.avgi);
dbms_output.put_line('The total salaray in dept is :'||v_emp1.tot);
dbms_output.put_line('The total members in dept is :'||v_emp1.cnt);
CLOSE C1;
end;
  • Attachment: ERROR.SQL
    (Size: 0.79KB, Downloaded 43 times)
Re: ORA-00922: missing or invalid option [message #578306 is a reply to message #578304] Tue, 26 February 2013 13:20 Go to previous messageGo to next message
BlackSwan
Messages: 21955
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


just asking

exactly which type of database object are you trying to CREATE; VIEW, PROCEDURE, etc.?
Re: ORA-00922: missing or invalid option [message #578311 is a reply to message #578304] Tue, 26 February 2013 15:19 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, if you used SQL*Plus it'd tell you where is the error.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: ORA-00922: missing or invalid option [message #578312 is a reply to message #578304] Tue, 26 February 2013 15:22 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
As Blackswan's reply implies, you have omitted the type of object that you are creating. As his reply clearly states, please follow the posting guidelines esp formatting (and possibly supply a little more detail in your future posts.
Re: ORA-00922: missing or invalid option [message #578380 is a reply to message #578312] Wed, 27 February 2013 06:29 Go to previous messageGo to next message
bharathi89
Messages: 39
Registered: May 2012
Location: chennai
Member
HI, shankar680 .. Smile

How Many Years Working In Oracle Field... Smile ?.

You Missed Object Type 'PROCEDURE' In Your Code...Correct it Smile..
Re: ORA-00922: missing or invalid option [message #578395 is a reply to message #578380] Wed, 27 February 2013 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@cvbharathi89

Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:

Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel
Re: ORA-00922: missing or invalid option [message #578415 is a reply to message #578380] Wed, 27 February 2013 16:16 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
@cvbharathi89 it must be great to be a perfect coder. Never making silly errors that you can't spot. Tell us all what it's like to be perfect.
Re: ORA-00922: missing or invalid option [message #578419 is a reply to message #578304] Wed, 27 February 2013 17:55 Go to previous messageGo to next message
shankar680
Messages: 11
Registered: February 2013
Location: HYDERABAD
Junior Member
I tried and changed into this way

CREATE OR REPLACE PROCEDURE P_AGGOP(DNO NUMBER) IS
MAXI NUMBER;
MINI NUMBER;
AVGI NUMBER;
TOT NUMBER;
CNT NUMBER;
BEGIN
SELECT MIN(SAL),MAX(SAL),AVG(SAL),SUM(SAL),COUNT(*) INTO MINI,MAXI,AVGI,TOT,CNT FROM EMP WHERE DEPTNO=DNO GROUP BY DEPTNO;
DBMS_OUTPUT.PUT_LINE('EXECUTED ');
DBMS_OUTPUT.PUT_LINE('The minimum salaray in dept is :'||MINI);
DBMS_OUTPUT.PUT_LINE('The maximum salaray in dept is :'||MAXI);
DBMS_OUTPUT.PUT_LINE('The average salaray in dept is :'||AVGI);
DBMS_OUTPUT.PUT_LINE('The total salaray in dept is :'||TOT);
DBMS_OUTPUT.PUT_LINE('The total members in dept is :'||CNT);
END;

EXEC P_AGGOP(&P);
Re: ORA-00922: missing or invalid option [message #578420 is a reply to message #578419] Wed, 27 February 2013 19:30 Go to previous message
BlackSwan
Messages: 21955
Registered: January 2009
Senior Member
does your last post mean that this issue has been resolved for you?
Previous Topic: Data Type to return decimal values in function
Next Topic: Join help needed
Goto Forum:
  


Current Time: Sun Apr 20 20:02:21 CDT 2014

Total time taken to generate the page: 0.08099 seconds