Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Comma delimited values into rows
On Tue, 09 May 2006 19:37:37 -0700, johnv wrote:
> I have this string in a field called 'ID':
>
> 1000,1001,1002,...n
>
> In sql only, how can i write a view that treats the comma as a new row
> so that my result set reads as:
You can't. SQL is not a procedural language. You can, however, do it like this:
my $ins="insert into tbl(col) values(:CHK)";
my $sth->prepare($ins);
while(<>) {
chomp; my @arr=split /,/; foreach my $chunk (@arr) { $sth->bind_param(":CHK",$chunk); $sth->execute(); } $db->commit();
If you decide to do it in Perl, be aware that DBD::Oracle is junk which doesn't support anything to improve performance with the large files. No array interface, no direct load, nada. It's good for hacking but it isn't a serious tool. Currently, there isn't much hope that DBD::Oracle will ever become anything more then a mere toy.
-- http://www.mgogala.comReceived on Tue May 09 2006 - 22:44:13 CDT