Results 1 to 2 of 2

Thread: Utl_file.fopen

  1. #1
    Join Date
    Oct 2002
    Location
    London
    Posts
    5

    Angry Unanswered: Utl_file.fopen

    Right, why oh why do Oracle make it so damn difficult to load CSV format files using UTL_FILE..... well I think it is.. never having done it before!

    Any way IM about half way through and then I hit the column placement bit...

    could someone please explain to me what this means?????

    and I quote...'

    --Use the SUBSTR to extract the fields

    V_ENO:=SUBSTR(V_NewLine,1,V_FirstComma -1);
    V_ENAME:=SUBSTR(V_NewLine,V_FirstComma +1, V_SecondComma-V_FirstComma -1);
    V_SAL:=SUBSTR(V_NewLine,V_SecondComma+1);

    --

    OK what does all this -1 +1 -some Variable mean?

    Thanks in Advance

    Adam

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Utl_file.fopen

    Originally posted by AJCG1976
    Right, why oh why do Oracle make it so damn difficult to load CSV format files using UTL_FILE..... well I think it is.. never having done it before!

    Any way IM about half way through and then I hit the column placement bit...

    could someone please explain to me what this means?????

    and I quote...'

    --Use the SUBSTR to extract the fields

    V_ENO:=SUBSTR(V_NewLine,1,V_FirstComma -1);
    V_ENAME:=SUBSTR(V_NewLine,V_FirstComma +1, V_SecondComma-V_FirstComma -1);
    V_SAL:=SUBSTR(V_NewLine,V_SecondComma+1);

    --

    OK what does all this -1 +1 -some Variable mean?

    Thanks in Advance

    Adam
    SUBSTR takes a string, a starting position within the string, and a length to get a substring. Suppose your comma-delimited record looks like this:

    123456,Jones,25000

    To get the ENO value (123456) you need to get the substring from position 1 up to (but not including) the first comma (which is at position 7), i.e. SUBSTR(v_newline,1,6)
    = SUBSTR(v_newline,1,7-1)
    = SUBSTR(v_newline,1,v_firstcomma-1)
    where v_firstcomma has been determined (probably using INSTR(v_newline,',',1,1) to be 7

    Then to get the ENAME value, you need the substring that starts just after the first comma (i.e. at v_firstcomma+1), and has length = number of chars between 1st comma and 2nd comma, i.e. v_secondcomma-v_firstcomma-1

    ... and so on.

    Of course, the code could be written more elegantly. For example, it could break the record up into pieces in an index-by table, and then assign the values to the EMP variables:

    ...
    TYPE cols_tab_type IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
    cols_tab cols_tab_type;
    PROCEDURE parse_line
    ( p_line IN VARCHAR2
    , p_cols_tab OUT cols_tab_type
    )
    IS
    v_numcols PLS_INTEGER := 0;
    v_commapos PLS_INTEGER;
    v_line VARCHAR2(32767) := p_line;
    BEGIN
    LOOP
    v_numcols := v_numcols+1;
    v_commapos := INSTR( v_line, ',' );
    IF v_commapos = 0 THEN
    -- No more commas
    p_cols_tab(v_numcols) := v_line;
    EXIT;
    ELSE
    p_cols_tab(v_numcols) := SUBSTR(v_line,1,v_commapos-1);
    v_line := SUBSTR(v_line,v_commapos+1);
    END IF;
    END LOOP;
    END parse_line;
    BEGIN

    ...
    parse_line( v_newline, cols_tab );
    v_eno := cols_tab(1);
    v_ename := cols_tab(2);
    v_sal := cols_tab(3);
    ...

    (The parse_line procedure is generic, and could be put into a package so it can be used in other dataload programs also).

Posting Permissions

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