I have a comma delimited string containing column names.
Ex: strTemp = 'col1,col2,col3,col4'
i want to break up strTemp and assign each column name to a variable as follows.
str1:=col1
str2:=col2
str3:=col3
str4:=col4
If i was using Java, i would use the StringTokenizer.
What would be the easiest way to do this in PL/SQL.
Thank you.
Ramesh
</code>
http://asktom.oracle.com/~tkyte/SkipCols/index.html <code>
has a "sophisticated" delimited package (it obeys an enclosure, like SQLLDR would if you need).
But very simply:
ops$tkyte@ORA815.US.ORACLE.COM> create or replace type myTableType as table of
number
2 /
Type created.
ops$tkyte@ORA815.US.ORACLE.COM>
ops$tkyte@ORA815.US.ORACLE.COM>
ops$tkyte@ORA815.US.ORACLE.COM> create or replace function str2tbl( p_str in
varchar2 ) return myTableType
2 as
3 l_str long default p_str || ',';
4 l_n number;
5 l_data myTableType := myTabletype();
6 begin
7 loop
8 l_n := instr( l_str, ',' );
9 exit when (nvl(l_n,0) = 0);
10 l_data.extend;
11 l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
12 l_str := substr( l_str, l_n+1 );
13 end loop;
14 return l_data;
15 end;
16 /
takes a string delimited by commas and returns an array so str(1), str(2)
All it takes in instr and substr -- parsing is pretty easy in plsql...