Unanswered: PLS-00364: loop index variable 'SPos' use is invalid
I have a little problem with this function. I got the error "PLS-00364: loop index variable 'SPos' use is invalid" on the red marked line. Could you please help me to find out why this error appears?
CREATE OR REPLACE FUNCTION myinstr(
SPos_IN NUMBER DEFAULT 1) RETURN NUMBER AS
SourceStr VARCHAR2(255) := SourceStr_IN;
SearchStr VARCHAR2(255) := SearchStr_IN;
SPos NUMBER(5) := SPos_IN;
FPos := 0;
IF SourceStr IS NOT NULL
AND SearchStr IS NOT NULL THEN
SourceLen := LENGTH(SourceStr);
SearchLen := LENGTH(SearchStr);
IF ( SPos BETWEEN 1 AND SourceLen ) AND ( SearchLen > 0 ) AND ( SPos - 1 + SearchLen <= SourceLen ) THEN
FOR SPos IN SPos..(SPos + SourceLen - SearchLen) LOOP
IF SearchStr = SUBSTR(SourceStr, SPos, SearchLen) THEN
FPos := SPos;
Is this home work? If it isn't then oracle has a procedure that does the exact same thing baked into the database. See the INSTR function. The other problem is that your loop variable is Spos and your declared variable is Spos. You may NOT use a declared variable in a loop.
Last edited by beilstwh; 06-10-16 at 09:04.
You do not need a parachute to skydive. You only need a parachute to skydive twice.