Oracle

PL/SQL: ORA-00922: missing or invalid option

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Feb 2008

Posts: 2

Reputation: culbysl is an unknown quantity at this point

Solved Threads: 0

culbysl

Offline

Newbie Poster

PL/SQL: ORA-00922: missing or invalid option

Jun 14th, 2009

I am new at oracle and working on a small project. I am trying to find "[" in a field and produce from that point to the end of the field.

My code is as follows:

 Help with Code Tags 
 Oracle Syntax (Toggle Plain Text) 
DECLARE
startPos PLS_INTEGER;
endPos   PLS_INTEGER;
 
BEGIN
 
SET startPos := (SELECT top 1(INSTR(description, '[', 1, 1)-1) FROM INSTRTEST); 
 
dbms_output.put_line(startPos);
 
END;
declare
startPos PLS_Integer;
endPos   PLS_Integer;

begin

set startPos := (Select top 1(instr(description, '[', 1, 1)-1) FROM INSTRTEST); 

dbms_output.put_line(startPos);

end;

The error I am receiving:

 Help with Code Tags 
 Oracle Syntax (Toggle Plain Text) 
ORA-06550: line 5, COLUMN 5:
PL/SQL: ORA-00922: missing OR invalid OPTION
ORA-06550: line 5, COLUMN 1:
PL/SQL: SQL Statement ignored
 
3. endPos   PLS_INTEGER;
4. BEGIN
5. SET startPos := (SELECT top 1(INSTR(description, '[', 1, 1)-1) FROM INSTRTEST); 
6. --endPos := (select len(description)FROM INSTRTEST)  
7. dbms_output.put_line(startPos);
ORA-06550: line 5, column 5:
PL/SQL: ORA-00922: missing or invalid option
ORA-06550: line 5, column 1:
PL/SQL: SQL Statement ignored

3. endPos   PLS_Integer;
4. begin
5. set startPos := (Select top 1(instr(description, '[', 1, 1)-1) FROM INSTRTEST); 
6. --endPos := (select len(description)FROM INSTRTEST)  
7. dbms_output.put_line(startPos);

Thanks in advance for any help available.
culbysl

•

Join Date: Feb 2007

Posts: 2,086

Reputation: debasisdas will become famous soon enough

Solved Threads: 124

debasisdas debasisdas is online now

Online

Postaholic

Re: PL/SQL: ORA-00922: missing or invalid option

Jun 16th, 2009

the syntax that you are using is not Oracle syntax.

Share your Knowledge.

•

Join Date: Feb 2008

Posts: 2

Reputation: culbysl is an unknown quantity at this point

Solved Threads: 0

culbysl

Offline

Newbie Poster

Re: PL/SQL: ORA-00922: missing or invalid option

Jun 16th, 2009

Thanks for your reply debasisdas. I see the mistake is selecting the top N. SQL Server habits are hard to overcome. Again thanks for your reply.

culbysl

•

Join Date: Oct 2009

Posts: 1

Reputation: arjun0 is an unknown quantity at this point

Solved Threads: 0

arjun0

Offline

Newbie Poster

Oct 20th, 2009

hi i have error ora-00922(missing or invalid option) while creating this table

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
CREATE TABLE emp5
(
empno varchar2(6) CONSTRAINT che_emp CHECK(empno IN('A%','M%','P%','S%')),
ename varchar2(10) CONSTRAINT en_nn NOT NULL,
dob DATE CONSTRAINT dob_che 
CHECK(to_char(dob,'dd-mon-yyyy')<'01-jan-1983'),
sex CHAR CONSTRAINT se_che CHECK(sex IN('m','f','M','F')),
salary number(10,2) sal_che 
CHECK( salary BETWEEN 15000 AND 100000),
CONSTRAINT pk_empno PRIMARY KEY(empno)
);
create table emp5
(
empno varchar2(6) constraint che_emp check(empno in('A%','M%','P%','S%')),
ename varchar2(10) constraint en_nn not null,
dob date constraint dob_che 
check(to_char(dob,'dd-mon-yyyy')<'01-jan-1983'),
sex char constraint se_che check(sex in('m','f','M','F')),
salary number(10,2) sal_che 
check( salary between 15000 and 100000),
constraint pk_empno primary key(empno)
);

Last edited by peter_budo; Oct 20th, 2009 at 7:05 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)