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

[culbysl]

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:

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:

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

[debasisdas]

the syntax that you are using is not Oracle syntax.

[culbysl]

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

[arjun0]

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

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

[cgyrob]

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

[debasisdas]

missing CONSTRAINT key word on line 8.