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:

startPos PLS_Integer;
endPos   PLS_Integer;


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



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.

the syntax that you are using is not Oracle syntax.

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.


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

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

missing CONSTRAINT key word on line 8.