Ad:

Oracle Discussion Thread
Marked Solved
Views: 6026

Jun 14th, 2009

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

Expand Post »

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:

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:

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

Similar Threads

Folder Option missing! in Windows NT / 2000 / XP
Oracle "ORA-00922" error in Database Design
Folder Option missing from Tools menu in Windows NT / 2000 / XP
oracle utlmail problem in Oracle
Helllppp..> <<invalid Syntax Error>> in Viruses, Spyware and other Nasties

culbysl

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Feb 2008

Permalink

Jun 16th, 2009

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

the syntax that you are using is not Oracle syntax.

debasisdas

Featured Poster

Reputation Points: 665

Solved Threads: 427

Posting Genius

Offline

6,406 posts
since Feb 2007

Permalink

Jun 16th, 2009

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

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

culbysl

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Feb 2008

Permalink

Oct 20th, 2009

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

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

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)

arjun0

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Oct 2009

Permalink

Oct 20th, 2009

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

@arjun0 - If you want your questions answered you need to start a new thread.

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

Permalink

Oct 21st, 2009

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

missing CONSTRAINT key word on line 8.

debasisdas

Featured Poster

Reputation Points: 665

Solved Threads: 427

Posting Genius

Offline

6,406 posts
since Feb 2007

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Oracle Forum Timeline: INSERT sql statement for DateTime

Next Thread in Oracle Forum Timeline: outputting too many

DaniWeb Message

Cancel Changes

User Name
Password