asked on

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

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

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?

SOLUTION