Hi all,
I have SQL developer 3.2.20.10
When I run the following SQL in a SQL worksheet it works fine.
with a as
(select 1 n from dual union all
select 2 n from dual union all
select 3 n from dual union all
select 4 n from dual union all
select 5 n from dual
)
select
*
from
a MATCH_RECOGNIZE (
ORDER BY n
MEASURES
first(digit.n) as first_n
,last(digit.n) as last_n
one ROW PER MATCH
AFTER MATCH SKIP past last row
PATTERN (digit+)
DEFINE
digit as digit.n between 0 and 9
) MR
;
FIRST_N LAST_N
---------- ----------
1 5
However whn I run the following SQL I get an error:
Missing IN or OUT parameter at index:: 1
with a as
(select 1 n from dual union all
select 2 n from dual union all
select 3 n from dual union all
select 4 n from dual union all
select 5 n from dual
)
select
*
from
a MATCH_RECOGNIZE (
ORDER BY n
MEASURES
first(digit.n) as first_n
,last(digit.n) as last_n
one ROW PER MATCH
AFTER MATCH SKIP past last row
PATTERN (digit+?)
DEFINE
digit as digit.n between 0 and 9
) MR
;
The difference is the +? in stead of the + in the pattern clause.
If I run the same SQL in SQLPlus I get the correct result as:
FIRST_N LAST_N
---------- ----------
1 1
2 2
3 3
4 4
5 5
Also if I put "Create or replace view mr_2 as" in front of the SQL a view is created and the result of: "select * from mr_2" is the same as the outcome from SQLPlus.
I am sure the syntax is correct and the error is not from oracle but from SQL developer.
Does anybody know what to do about this?
Do other people have the same problem?
Regards,
Peter