Re: PL/SQL: parsing csv string and return it as rowset

From: joes <joes_at_bluewin.ch>
Date: 20 Feb 2004 14:01:43 -0800
Message-ID: <26760a3e.0402201401.4d1d79cf@posting.google.com>

Thank you for the hint about "DBMS_UTILITY.COMMA_TO_TABLE" but this has also other limitations as only the 30 bytes. So tokens inside the strings must also follow the standards of oracle for object names
(tables, etc.) which means no special character and no numbers at the
beginning of tokens.

But based on your hint I found some good examples how a parsing could be reached. Unfortunately these produce only one column based tables. But I need the other way around, the possibillity to produce mutliple columns. So I have to adapt the scripts so that these acts for at least a given fixed number of columns.

While I am a newbie to pl sql, I need again some hints how I could achieve this.

How could I update a column with pl sql by the index, without knowing the name of the column?

Please note it must work for oracle 8.1.7. So if you see that one scipt uses not supported features, so give me again a hint.

example (l_data is type table of a customized type)

l_data( l_data.count )(index) := substr( l_string, 1, l_n-1 );

Many thanks for any help on this
cheers
Mark

Here the founded scripts, one uses a table type... the other inserts the parsed
values into a temporary table. I am not sure if the first would work in oracle 8
(i.e. select * from table(in_list('testA,testB,...')); because of
table() function ?

create or replace function in_list( p_string in varchar2 ) return testTableType

       l_data             testTableType := testTableType();
       l_string           long default p_string || ',';
       l_n                number;

begin

        loop
         exit when l_string is null;
                 l_data.extend;
                 l_n := instr( l_string, ',' );
                 -- how to access here the columns, but based on the 
                 -- index, position of the actual parsed string ?
                 l_data( l_data.count ) := substr( l_string, 1, l_n-1
);
                 l_string := substr( l_string, l_n+1 );
         end loop;
         return l_data;

end;

CREATE GLOBAL TEMPORARY TABLE TEMP
(

  DATA1 VARCHAR2(4000 BYTE),
  DATA2 VARCHAR2(4000 BYTE),
  DATA3 ...
  ...
)
ON COMMIT PRESERVE ROWS; CREATE OR REPLACE PROCEDURE billingoperation.splitti(Param IN VARCHAR2)
  IS
    Param_Str VARCHAR2(4000);
    Comma_Position NUMBER;
  BEGIN
    DELETE FROM temp;
    Param_Str:=Param||',';
    LOOP

      Comma_Position := INSTR(Param_Str, ',');
      EXIT WHEN (NVL(Comma_Position, 0)) = 0;
      INSERT INTO temp VALUES (TRIM(SUBSTR(Param_Str, 1,
Comma_Position-1)));
      Param_Str := SUBSTR(Param_Str, Comma_Position+1);

     -- Here, how to update columns of the new row without knowing
names ?
     -- just working with position from the string parsing above ?

END LOOP;
END splitti;

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1077233768.650362_at_yasure>...

> joes wrote:
> 
> > Hello there
> > 
> > I have currently in a table csv concateneted strings. Now I got the
> > beautiful task to build reports on it, but for this I have first to
> > splitt those in their respetive tokens and build tables on it.
> > 
> > So my first idea is to do this at the fly. Means building a view on
> > this which uses a PL/SQL function for the parsing of the strings.
> > 
> > Unfortunately I have to use Oracle 8.1.7. I have seen that Oracle 9
> > supports "pipelined" returns of tables, but version 8 does this not.
> > 
> > So I need a little bit more help to get this done. Could you bring me
> > up with some good examples and suggestions how I could solve my
> > challenge ?
> > 
> > - How to return dynamical arrays from a pl sql funcion ?
> > - How to splitt strings into its token with pl /sql ?
> > - How to return a "virtual" rowset from pl /sql so that I can execute
> > a "select ..." statement on it ?
> > - Other suggestions how to achieve this ?
> > 
> > thank you best regards
> > Mark
> 
> If the individual pieces are 30 bytes or smaller look at the
> DBMS_UTILITY.COMMA_TO_TABLE built-in package procedure.
> 
> Otherwise ... you are about to become very familiar with SUBSTR
> and INSTR.

Received on Fri Feb 20 2004 - 16:01:43 CST