Path: news.easynews.com!easynews!feedwest.aleron.net!aleron.net!news-out.newsfeeds.com!propagator2-maxim!propagator-maxim!news-in.spamkiller.net!news.he.net!chekhov.conxion.net!news.oracle.com!not-for-mail
Message-ID: <3CFFFD0E.738480D@oracle.com>
From: Vladimir Begun <Vladimir.Begun@oracle.com>
Reply-To: Vladimir.Begun@oracle.com
Organization: Oracle Corporation
X-Mailer: Mozilla 4.78 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.server
Subject: Re: convert delimited string to rows ?
References: <juIK8.1$TV6.40@news.oracle.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Lines: 60
Date: Thu, 06 Jun 2002 17:23:42 -0700
NNTP-Posting-Host: 130.35.118.50
X-Trace: news.oracle.com 1023409539 130.35.118.50 (Thu, 06 Jun 2002 17:25:39 PDT)
NNTP-Posting-Date: Thu, 06 Jun 2002 17:25:39 PDT
Xref: easynews comp.databases.oracle.server:149811
X-Received-Date: Thu, 06 Jun 2002 17:21:12 MST (news.easynews.com)

Sunil wrote:
> 
> I have a string
>     'rob:bob:tod:rod:mod'
> can I use any function to convert this into rows so that I can do something
> like
> SQL> select *  from  some_funct('rob:bob:tod:rod:mod')
> and get :-

-- Oracle 8i
CREATE OR REPLACE TYPE item AS OBJECT (p VARCHAR2(10))
/
CREATE OR REPLACE TYPE arr AS TABLE OF item;
/
CREATE OR REPLACE FUNCTION sc2tab (
  as_str                        VARCHAR2
)
RETURN arr
IS
  la_items                      arr := arr();
BEGIN
  -- your parser code here, it's just a stub
  la_items.EXTEND;
  la_items(1) := item(SUBSTR(as_str, 1, 3));
  la_items.EXTEND;
  la_items(2) := item(SUBSTR(as_str, 5, 3));
  la_items.EXTEND;
  la_items(3) := item(SUBSTR(as_str, 9, 3));
  RETURN la_items;
END sc2tab;
/

SELECT a.p p
  FROM THE (
         SELECT CAST(sc2tab('abc:def:ghi') AS arr)
           FROM dual
       ) a
/

-- Oracle 9i
CREATE OR REPLACE FUNCTION sc2tab (
  as_str                        VARCHAR2
)
RETURN arr PIPELINED
IS
BEGIN
  -- your parser code has to be here, it's just a stub
  PIPE ROW(item(SUBSTR(as_str, 1, 3)));
  PIPE ROW(item(SUBSTR(as_str, 5, 3)));
  PIPE ROW(item(SUBSTR(as_str, 9, 3)));
  RETURN;
END sc2tab;
/

SELECT * FROM TABLE(sc2tab('abc:def:ghi'))
/
-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.