PL/SQL question

Posted on 2005-04-14
Medium Priority
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 wrong..how 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
  • 5
  • 2
LVL 22

Accepted Solution

earth man2 earned 1400 total points
ID: 13786385
-- 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

Assisted Solution

by:Mehul Shah
Mehul Shah earned 600 total points
ID: 13789440
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

ID: 13789569
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?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Expert Comment

by:Mehul Shah
ID: 13789581
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

ID: 13789636
ok thanks guys...both suggestions were very helpfull...

Expert Comment

by:Mehul Shah
ID: 13789648
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

Expert Comment

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

Expert Comment

by:Mehul Shah
ID: 13789752
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses
Course of the Month14 days, 5 hours left to enroll

807 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