Error ORA-06502 WHEN-BUTTON-PRESSED oracle forms

Question

I am trying to show data from "CSV" File to Oracle forms. I am using Client_Text_IO procedure to read data from "CSV" file with comma separated into Data Block.

I have one column in which some data empty and have some data. If I open file in excel then:

No. 6 column some rows are empty:

No.6 column some rows have data:

When I open file in notepad then:

6,6,6000000116,HH00000471,Abdul akbar,,1610223056753

You can see after "ABDUL AKBAR" there are 2 commas due to empty data

With data in notepad:

6,6,6000000189,HH00000544,Raishma bibi,Gul akbar,1610216789294

When I run procedure then I getting following error on 6th column:

CODE:

PROCEDURE p_output_line(p_line varchar2) IS

 vLINE VARCHAR2(4000);
 vVALUE VARCHAR2(1000); 
 vCOMMA_COUNT NUMBER;

BEGIN

 vLINE := p_line;
 vCOMMA_COUNT := LENGTH(vLINE)- LENGTH(REPLACE(vLINE,',',''));

  FOR I IN 1.. vCOMMA_COUNT+1 LOOP  
  vVALUE := SUBSTR(vLINE,1,INSTR(vLINE,',')-1);
IF vVALUE IS NULL THEN
    vVALUE := vLINE;
END IF;    
  vLINE := SUBSTR(vLINE,INSTR(vLINE,',')+1) ; -- CHANGE   123,ABC,9877 TO BE   ABC,9877

  IF I = 1 THEN
:WE_GROUP_HOF_K.CLIENTID := vVALUE; 
 END IF;

 IF I = 2 THEN 
:WE_GROUP_HOF_K.PROJECTID := vVALUE;
 END IF;

 IF I = 3 THEN
:WE_GROUP_HOF_K.GROUP_HOF_ID := vVALUE;
 END IF;

 IF I = 4 THEN
:WE_GROUP_HOF_K.NRSP_HOFID := vVALUE;
 END IF;

 IF I = 5 THEN
:WE_GROUP_HOF_K.HOF_NAME := vVALUE;
 END IF;

 IF I = 6 THEN
    :WE_GROUP_HOF_K.FATHER_NAME := vVALUE;
 END IF;

 END LOOP; 
 EXCEPTION
  WHEN NO_DATA_FOUND THEN
  MESSAGE('Please Check the data type is appropriate on you excel file');
  MESSAGE('Please Check the data type is appropriate on you excel file');
END;

score 1 · Answer 1 · answered May 14 '19 at 06:06

1

Because it is empty your counts don't work. Use a split function to split your data. Split function

Also in your when no_data_found exception you should put pause; after your message, then there is no need to put it twice.

answered May 14 '19 at 06:06

nightfox79

2,077
3
27
40

As far as I know Oracle Forms lacks built-in split functionality. One option is to [install a third-party PJC which supports regular expressions](http://forms.pjc.bean.over-blog.com/article-4134827.html). The other option is to leverage database functionality. SO has plenty of questions on splitting delimited strings into tokens: [have a pipe at some of them](https://stackoverflow.com/search?q=%5Boracle%5D+string+tokenize). A third option is to use the third-party code mentioned [in this answer here](https://stackoverflow.com/a/44964461/146325). – APC May 14 '19 at 06:58
Just put the code in the link I gave you in the declare part of your code as a procedure, and then you can use it. – nightfox79 May 15 '19 at 05:51

Error ORA-06502 WHEN-BUTTON-PRESSED oracle forms

1 Answers1