PL/SQL question

Posted on 2005-04-14
Last Modified: 2008-02-07
Hi experts,

I have a block of code that is supposed to basically take a line of data from a file and seperate it by each "field or word" and copy them into specified variables. The thing is the "words" are seperated by tabs abd not spaces... the code I have used works on some tabs but not on the others... I don't know why..could someone correct it so it will seperate them whether a tab or space is found...maybe the ascii value I used is wrong..

Also, about the If block that seperates the line into variables according to column number, I tried using CASE and I think I did something do I change it to use CASE???



extracted_line             VARCHAR2(300);
first_word             VARCHAR(20);
column_count             NUMBER(18)      :=0;

v_posdate             DATE;
v_currency             VARCHAR2(3);
v_producttype             VARCHAR2(10);
v_cash                   VARCHAR2(5);
v_folder             VARCHAR2(10);
v_tenor                   NUMBER(4);
v_cashflowdate           DATE;
v_sensitivity            NUMBER(8);
v_X1                     NUMBER(5);
v_X2                     NUMBER(5);
v_currency2              VARCHAR2(3);
v_source                 VARCHAR2(9);
v_curve                  VARCHAR2(8);
v_activity               VARCHAR2(8);


FUNCTION get_column (ext_line in varchar2) return varchar2 IS

v_pos                    NUMBER(18)      :=1;
one_letter             VARCHAR2(20);
one_word             VARCHAR2(20);      


 WHILE (column_count <> 1 ) LOOP  

 one_letter:= SUBSTR(ext_line,v_pos,1);
 dbms_output.put_line('LETTER IS:'|| one_letter);

    IF  (one_letter <> Chr(32)) THEN
       v_pos := v_pos +1;
       one_word:=one_word || one_letter;

       column_count := column_count+ 1;
       dbms_output.put_line('column count :' ||column_count);
       dbms_output.put_line('FOUND TAB!!!');

IF    column_count=1  THEN v_posdate      :=to_date(TRIM(one_word),'dd-mon-yyyy');
ELSIF column_count=2  THEN v_currency      :=TRIM(one_word);
ELSIF column_count=3  THEN v_producttype:=TRIM(one_word);
ELSIF column_count=4  THEN v_cash      :=TRIM(one_word);
ELSIF column_count=5  THEN v_folder      :=TRIM(one_word);
ELSIF column_count=6  THEN v_tenor      :=to_number(TRIM(one_word));
ELSIF column_count=7  THEN v_cashflowdate:=to_date(TRIM(one_word),'yyyymmdd');
ELSIF column_count=8  THEN v_sensitivity:=to_number(TRIM(one_word));
ELSIF column_count=9  THEN v_X1            :=to_number(TRIM(one_word));
ELSIF column_count=10 THEN v_X2            :=to_number(TRIM(one_word));
ELSIF column_count=11 THEN v_currency2      :=TRIM(one_word);
ELSIF column_count=12 THEN v_source      :=TRIM(one_word);
ELSIF column_count=13 THEN v_curve      :=TRIM(one_word);
ELSIF column_count=14 THEN v_activity      :=TRIM(one_word);

END IF;          

    END IF;


       return one_word;

END get_column;


extracted_line :='06-APR-2005      SAR      FWD      c      DEI      64      20050609      7.112696      0      0      SAR      KdBsfAll      SAR-SWAP';

dbms_output.put_line('FIRST WORD IS:'|| first_word);



Question by:Samooramad
    LVL 22

    Accepted Solution

    -- tab is CHR(9) by my estimation,  You are using CHR(32) which is a space is it not !

    create or replace procedure strtok ( tok in out varchar, s in out varchar,  ct in varchar ) as
      i pls_integer;
      p pls_integer;
      len pls_integer;
      token_start pls_integer;
      intoken boolean := false;
      if ( s is not NULL ) then
        len := length( s );
        i := 1;
        while ( i <= len ) loop
          p := instr( ct, substr(s,i,1) );
          if ( ( i = len ) or ( p > 0 ) ) then
            if ( intoken ) then
              if ( p > 0 ) then
                tok := substr( s, token_start, i - token_start );
                s   := substr( s, i+1 );
                tok := substr( s, token_start, i - token_start + 1 );
                s   := '';
              end if;
              exit when true;
            end if;
          elsif ( not intoken ) then
              intoken := true;
              token_start := i;
          end if;
          i := i + 1;
        end loop;
      end if;
    -- an example as to how you could use it is given below
    create or replace function token1_token3( s in varchar ) return varchar as
    token1 varchar2(2000);
    token2 varchar2(2000);
    token3 varchar2(2000);
    token4 varchar2(2000);
    scopy  varchar2(2000);
      scopy := s;
      strtok( token1, scopy, chr(9) );
      strtok( token2, scopy, ' ,.' );
      strtok( token3, scopy, ' ,.' );
      strtok( token4, scopy, ' ,.' );
      return token1 || token3
    LVL 7

    Assisted Solution

    by:Mehul Shah
    If you want to replace the tab character with a space character to make your work simple you can check the below query. It replaces each of the tab character with space.

    SQL> select replace ('This      is      a      test      line      .......',chr(9),chr(32))  from dual

    This is a test line .......

    1 row selected.

    Author Comment

    earthman, I'm trying your suggestion and will get back to you..thanks

    MehulS78, those lines are being extracted from a text file... you mena I could replace them?
    LVL 7

    Expert Comment

    by:Mehul Shah
    yes anyway you are storing that line in a variable so you can use the above query in the procedure itself. You just need to define a new variable and store the trimmed output in that variable.

    select replace (ext_line ,chr(9),chr(32)) into ext_line_trimmed from dual;

    Author Comment

    ok thanks guys...both suggestions were very helpfull...
    LVL 7

    Expert Comment

    by:Mehul Shah
    I have a solution for you. Although its a bit confusing but it works fine. You can either run it within your plsql code or you can first copy the data from the text file into a table and than run the query directly on to the table. I have prepared the query on the later way.

    For your convienence CL_ACCOUNT_NAME is the variable or the column name that has the text string and DIM_CLIENT is the table name. If you dont want to load data into a table first than you can replace CL_ACCOUNT_NAME to EXT_LINE_trimmed and DIM_CLIENT with DUAL and it will work fine inside a procedure.

    substr(cl_account_name,1,instr(cl_account_name,' ',1,1)) test,
    substr(cl_account_name,instr(cl_account_name||' ',' ',1,1)+1,instr(cl_account_name||' ',' ',2,2)-instr(cl_account_name||' ',' ',1,1)) test,
    substr(cl_account_name,instr(cl_account_name||' ',' ',2,2)+1,instr(cl_account_name||' ',' ',3,3)-instr(cl_account_name||' ',' ',2,2)) test,
    substr(cl_account_name,instr(cl_account_name||' ',' ',3,3)+1,instr(cl_account_name||' ',' ',4,4)-instr(cl_account_name||' ',' ',3,3)) test,
    substr(cl_account_name,instr(cl_account_name||' ',' ',4,4)+1,instr(cl_account_name||' ',' ',5,5)-instr(cl_account_name||' ',' ',4,4)) test,
    substr(cl_account_name,instr(cl_account_name||' ',' ',5,5)+1,instr(cl_account_name||' ',' ',6,6)-instr(cl_account_name||' ',' ',5,5)) test,
    substr(cl_account_name,instr(cl_account_name||' ',' ',6,6)+1,instr(cl_account_name||' ',' ',7,7)-instr(cl_account_name||' ',' ',6,6)) test,
    substr(cl_account_name,instr(cl_account_name||' ',' ',7,7)+1,instr(cl_account_name||' ',' ',8,8)-instr(cl_account_name||' ',' ',7,7)) test,
    substr(cl_account_name,instr(cl_account_name||' ',' ',8,8)+1,instr(cl_account_name||' ',' ',9,9)-instr(cl_account_name||' ',' ',8,8)) test,
    substr(cl_account_name,instr(cl_account_name||' ',' ',9,9)+1,instr(cl_account_name||' ',' ',10,10)-instr(cl_account_name||' ',' ',9,9)) test,
    substr(cl_account_name,instr(cl_account_name||' ',' ',10,10)+1,instr(cl_account_name||' ',' ',11,11)-instr(cl_account_name||' ',' ',10,10)) test from dim_client

    Sample data

    Screensaved Prodsupport Ltd
    Alto Europe Limited
    Summeraddtom Sire Ltd

    TEST      TEST_1      TEST_2      TEST_3      TEST_4      TEST_5      TEST_6      TEST_7      TEST_8      TEST_9      TEST_10
    Screensaved       Prodsupport       Ltd                                    
    Alto       Europe       Limited                                                      
    Summeraddtom       Sire       Ltd                                                      

    Hope this helps
    LVL 7

    Expert Comment

    by:Mehul Shah
    Hey did I took too much time to respond back with the solution. :) anyway good it worked for you.
    LVL 7

    Expert Comment

    by:Mehul Shah
    In your case the below query to insert data into the table should work fine too

    insert into <<TABLE_NAME>>
    to_date(col1,'dd-mon-yyyy'),col2,col3,col4,col5,to_number(col6),to_date(col7,'yyyymmdd'),to_number(col8),to_number(col9),to_number(col10),col11,col12,col13,col14 from
    substr(ext_line_trimmed,1,instr(ext_line_trimmed,' ',1,1)) col1,
    substr(ext_line_trimmed,instr(ext_line_trimmed||' ',' ',1,1)+1,instr(ext_line_trimmed||' ',' ',2,2)-instr(ext_line_trimmed||' ',' ',1,1)) col2,
    substr(ext_line_trimmed,instr(ext_line_trimmed||' ',' ',2,2)+1,instr(ext_line_trimmed||' ',' ',3,3)-instr(ext_line_trimmed||' ',' ',2,2)) col3,
    substr(ext_line_trimmed,instr(ext_line_trimmed||' ',' ',3,3)+1,instr(ext_line_trimmed||' ',' ',4,4)-instr(ext_line_trimmed||' ',' ',3,3)) col4,
    substr(ext_line_trimmed,instr(ext_line_trimmed||' ',' ',4,4)+1,instr(ext_line_trimmed||' ',' ',5,5)-instr(ext_line_trimmed||' ',' ',4,4)) col5,
    substr(ext_line_trimmed,instr(ext_line_trimmed||' ',' ',5,5)+1,instr(ext_line_trimmed||' ',' ',6,6)-instr(ext_line_trimmed||' ',' ',5,5)) col6,
    substr(ext_line_trimmed,instr(ext_line_trimmed||' ',' ',6,6)+1,instr(ext_line_trimmed||' ',' ',7,7)-instr(ext_line_trimmed||' ',' ',6,6)) col7,
    substr(ext_line_trimmed,instr(ext_line_trimmed||' ',' ',7,7)+1,instr(ext_line_trimmed||' ',' ',8,8)-instr(ext_line_trimmed||' ',' ',7,7)) col8,
    substr(ext_line_trimmed,instr(ext_line_trimmed||' ',' ',8,8)+1,instr(ext_line_trimmed||' ',' ',9,9)-instr(ext_line_trimmed||' ',' ',8,8)) col9,
    substr(ext_line_trimmed,instr(ext_line_trimmed||' ',' ',9,9)+1,instr(ext_line_trimmed||' ',' ',10,10)-instr(ext_line_trimmed||' ',' ',9,9)) col10,
    substr(ext_line_trimmed,instr(ext_line_trimmed||' ',' ',10,10)+1,instr(ext_line_trimmed||' ',' ',11,11)-instr(ext_line_trimmed||' ',' ',10,10)) col11,
    substr(ext_line_trimmed,instr(ext_line_trimmed||' ',' ',11,11)+1,instr(ext_line_trimmed||' ',' ',12,12)-instr(ext_line_trimmed||' ',' ',11,11)) col12,
    substr(ext_line_trimmed,instr(ext_line_trimmed||' ',' ',12,12)+1,instr(ext_line_trimmed||' ',' ',13,13)-instr(ext_line_trimmed||' ',' ',12,12)) col13,
    substr(ext_line_trimmed,instr(ext_line_trimmed||' ',' ',13,13)+1,instr(ext_line_trimmed||' ',' ',14,14)-instr(ext_line_trimmed||' ',' ',13,13)) col14
    from dual) A;

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now