Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2016
    Posts
    2

    Unanswered: PLS-00364: loop index variable 'SPos' use is invalid

    Hi all,

    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?

    Code:
    CREATE OR REPLACE FUNCTION myinstr(
    	SourceStr_IN           VARCHAR2,
    	SearchStr_IN           VARCHAR2,
    	SPos_IN                NUMBER DEFAULT 1) RETURN NUMBER AS
    
    SourceStr           VARCHAR2(255) := SourceStr_IN;
    SearchStr           VARCHAR2(255) := SearchStr_IN;
    SPos                NUMBER(5) := SPos_IN;
    FPos                NUMBER(5);
    SourceLen           NUMBER(5);
    SearchLen           NUMBER(5);
    ItoO_selcnt         NUMBER;
    ItoO_rowcnt         NUMBER;
    
    BEGIN
    	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; 
    					EXIT;
    				END IF;
    			END LOOP;
    		END IF;
    	END IF; 
    	RETURN FPos;
    END myinstr;

    Best regards

    Max

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,504
    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 08:04.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jun 2016
    Posts
    2
    Yes it is. I want to understand why this error occurs.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,504
    You may not have a declared variable match the index on a loop. You are using Spos for both.

    I would also use the parameters directly instead of assigning them to internal variables. The only time you want to do that is if you have to alter the incoming information in the parameters.
    Last edited by beilstwh; 06-10-16 at 08:07.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •