• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 804
  • Last Modified:

REGEXP_SUBSTR 11G conversion to 9i oracle

V_UNIT_ID := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 1);

V_HGB := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 2);

V_PLT := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 3);

Need help in having 9i version substr function.

Error using REGEXP in 9i.


UNIT_ID VARCHAR2(25);

HGB NUMBER(3,1);

PLT NUMBER;

CREATE OR REPLACE PROCEDURE LOAD_PURPLE_TOP_DATA IS
      F UTL_FILE.FILE_TYPE;
      V_LINE VARCHAR2 (1000);
      V_UNIT_ID VARCHAR2(25);
      V_HGB NUMBER(3,1);
      V_PLT NUMBER;
BEGIN
      F := UTL_FILE.FOPEN ('PURPLE_TOP_DIR', 'purple_top.csv', 'R');
    IF UTL_FILE.IS_OPEN(F) THEN
      LOOP
        BEGIN
          UTL_FILE.GET_LINE(F, V_LINE, 1000);
          IF V_LINE IS NULL THEN
            EXIT;
          END IF;
          V_UNIT_ID := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 1);
          V_HGB := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 2);
          V_PLT := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 3);
          INSERT INTO TEMP_PURPLE_TOP VALUES(V_UNIT_ID, V_HGB, V_PLT);
          COMMIT;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
        END;
      END LOOP;
    END IF;
    UTL_FILE.FCLOSE(F);
END;

Open in new window

0
anumoses
Asked:
anumoses
  • 2
  • 2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
Just use SUBSTR and INSTR.

Here is an example:
with mydata as (
select 'abc,def,ghi' mycol from dual
)
select
	substr(mycol,1,instr(mycol,',',1,1)-1) first_val
	,substr(mycol, instr(mycol,',',1,1)+1, instr(mycol,',',1,2) - instr(mycol,',',1,1)-1 ) second_val
	,substr(mycol, instr(mycol,',',1,2)+1) third_val
from mydata
/

Open in new window

0
 
anumosesAuthor Commented:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at "HBC_DATA.LOAD_PURPLE_TOP_DATA", line 20
ORA-06512: at line 2


V_UNIT_ID := substr(V_LINE,1,instr(V_LINE,',',1,1)-1);
V_HGB := substr(V_LINE, instr(V_LINE,',',1,1)+1, instr(V_LINE,',',1,2) - instr(V_LINE,',',1,1)-1 );
V_PLT := substr(V_LINE, instr(V_LINE,',',1,2)+1);

HGB and PLT are numbers. How do I get them in the to represent in v_line?
0
 
slightwv (䄆 Netminder) Commented:
You can use TO_NUMBER to convert a string to a number.

However, Oracle will to an explicit data conversion for you so I don't think that will fix the ORA-06502.

The ORA-06502 is sort of self-explanatory.

It is likely this:
V_HGB NUMBER(3,1);


You are trying to put a larger number into the field than it can hold.

Check the data.
0
 
anumosesAuthor Commented:
HGB had to be number(3,1) Thanks.
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now