OraFAQ Forum: SQL & PL/SQL » ORA-00922: missing or invalid option

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00922: missing or invalid option (ORACLE 10G, WINDOWS)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

ORA-00922: missing or invalid option [message #578304]

Tue, 26 February 2013 13:14

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 19 times)

Report message to a moderator

Re: ORA-00922: missing or invalid option [message #578306 is a reply to message #578304]

Tue, 26 February 2013 13:20

BlackSwan
Messages: 20107
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.?

Report message to a moderator

Re: ORA-00922: missing or invalid option [message #578311 is a reply to message #578304]

Tue, 26 February 2013 15:19

Michel Cadot
Messages: 54223
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

Report message to a moderator

Re: ORA-00922: missing or invalid option [message #578312 is a reply to message #578304]

Tue, 26 February 2013 15:22

pablolee
Messages: 2083
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.

Report message to a moderator

Re: ORA-00922: missing or invalid option [message #578380 is a reply to message #578312]

Wed, 27 February 2013 06:29

cvbharathi89
Messages: 35
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

Report message to a moderator

Re: ORA-00922: missing or invalid option [message #578395 is a reply to message #578380]

Wed, 27 February 2013 08:49

Michel Cadot
Messages: 54223
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

Report message to a moderator

Re: ORA-00922: missing or invalid option [message #578415 is a reply to message #578380]

Wed, 27 February 2013 16:16

pablolee
Messages: 2083
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.

Report message to a moderator

Re: ORA-00922: missing or invalid option [message #578419 is a reply to message #578304]

Wed, 27 February 2013 17:55

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);

Report message to a moderator

Re: ORA-00922: missing or invalid option [message #578420 is a reply to message #578419]

Wed, 27 February 2013 19:30

BlackSwan
Messages: 20107
Registered: January 2009

Senior Member

does your last post mean that this issue has been resolved for you?

Report message to a moderator

Previous Topic:	Data Type to return decimal values in function
Next Topic:	Join help needed

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 23 23:19:49 CDT 2013

Total time taken to generate the page: 0.18974 seconds