PL/SQL Function error

[dkwong]

This is a test procedure to parse out lines of a memo and output it. I'm not sure why it doesn't work. It complains that it doesn't like the value assigned to the memo_field_tx variable. The error is :

'FIRST LINE
SECOND LINE
THIRD & LAST LINE' is not a valid integer value

PROCEDURE PR_PARSE_MEMO_INFOTRAC
IS

/* Current position in string */
lv_current_pos NUMBER(2);
/* End of line position to determine length of line */
lv_eol_pos NUMBER(2);
/* End of string position to determine max of loop */
lv_eos_pos NUMBER(2);
-- FOR TESTING - this field will be passed in as parameter in production
memo_field_tx VARCHAR2(1000);

BEGIN

  /* Initialize variables */
  lv_current_pos := 0;
  lv_eol_pos := 0;
  lv_eos_pos := 0;

  -- FOR TESTING to determine memo_field_tx
  memo_field_tx := 'FIRST LINE'||chr(10)||'SECOND LINE'||chr(10)||'THIRD & LAST LINE';

  WHILE lv_eos_pos < LENGTH(memo_field_tx)

  LOOP

    lv_eol_pos := INSTR(SUBSTR(memo_field_tx,lv_current_pos),chr(10)); 
  
    IF lv_eol_pos < 63 THEN
      DBMS_output.put(RPAD(SUBSTR(UPPER(memo_field_tx),lv_current_pos,lv_eol_pos - 1),62,' '));
    ELSE
      DBMS_output.put(SUBSTR(UPPER(memo_field_tx),lv_current_pos,62));
    END IF;
  
    /* Reset current position to the end of line position skipping the line break char */
    lv_current_pos := lv_eol_pos + 1;
  
    /* Determine position in string */
    lv_eos_pos := lv_eos_pos + lv_eol_pos;
  
END LOOP;

END PR_PARSE_MEMO_INFOTRAC;

 

[sem]

You may experience some problems if creating this procedure from sql*plus, because of using & (substituting character) in your code. But if using SET DEFINE OFF everything looks like OK. Can you provide full error message?

 

[dkwong]

This procedure is written in PL/SQL, not SQL*Plus. There error only says that the memo_field_tx "is not a valid integer value".

 

[lewisp]

As Sem says, change & to AND and see if that cures your problem.

 

[Turkbear]

Altho' I lost count, recount your parens in the various substr,rpad and instr functions..
It may be that,due to some mismatch, the parser thinks that ,at least in one operation, memo_field_tx is one of the position indicators instead of the text field being operated on by the function.

 

[sem]

I believe that you retell the error text almost exactly, but could you provide ERROR TEXT rather then its description in your own words? And do you get this error upon procedure creation or during a call? I successfully created this procedure an as it doesn't use any parameters, the result is completely predictable (though, probably incorrect as it outputs nothing, try to use put_line instead of put).