0

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

4
Contributors
5
Replies
7
Views
8 Years
Discussion Span
Last Post by debasisdas
0

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

0

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

Edited by peter_budo: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.