Re: "Connect by" can't be put in Function?

From: Diviner <diviner_at_hknet.com>
Date: Mon, 22 Jan 2001 12:04:06 +0800
Message-ID: <3A6BB136.97D12771@hknet.com>

You are right. The error is caused by my un-solid concept in Stored Procedure. The problem is solved now.

Matthew Fuller wrote:

> In article <3A683AA1.4541E038_at_hknet.com>,
> Diviner <diviner_at_hknet.com> wrote:
> > I created a table and used this for querying very well:
> > select filename from docindex
> > start with refno = 42
> > connect by prior parentnode = refno;
> >
> > But I failed to create this Function object:
> > create or replace function GetFullPath(pDocIndexRefNo
> > DocIndex.RefNo%type)
> > return varchar
> > as
> > begin
> > select filename from docindex
> > start with refno = 42
> > connect by prior parentnode = refno;
> >
> > return 'Hello';
> > end;
> >
> > Just a simple function, where are my wrong?
> >
> > --
> > Best regards,
> > Diviner.
> >
> >

>

> Diviner,

>

> What error are you getting? I'm assuming it looks something like this:

>

> PLS-00428: an INTO clause is expected in this SELECT statement

>

> An in-line SELECT statement in a PL/SQL block must have an INTO clause,
> and it is normally expected to return 0 or 1 rows. You can of course
> trap NO_DATA_FOUND and TOO_MANY_ROWS in an EXCEPTION block.

>

> In your case, you really need to define your SELECT statement as a
> cursor since you are probably expecting multiple rows to be returned.
> Try that and I think your problems will go away.

>

> HTH.

>

> Matt.

>

> Sent via Deja.com
> http://www.deja.com/
Received on Sun Jan 21 2001 - 22:04:06 CST