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

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 Fri Jan 19 2001 - 07:48:02 CST