Re: Need Help with string output from function or Proc

A copy of this was sent to eclipse98_at_hotmail.com (if that email address didn't require changing) On Thu, 30 Sep 1999 18:32:40 GMT, you wrote:

>Hi All !!!
>
>I would appreciate if anybody point me to the right direction
>with this problem.
>
>I need to generate a string of concatenated values from a table
>and return it either as function or output parameter in Proc.
>In function below I loop through cursor and append field value
>to vTEMP variable (no problem here). However when I try to return
>it it gives me this error:
>
>ORA-06502: PL/SQL: numeric or value error
>ORA-06512: at line 1
>
>When I limit the string length to 1999 chars long it works just
>fine, but I need to return much longer string (50,000+). I tried
>to return long instead of varchar2, but get the same error.
>
>Is there any other datatype I can use or is there some workaround?
>I know there is LOB in Oracle8, but I am using 7.
>
>TIA, Davie (using Oracle7 Server Release 7.3.4.1.0).
>

functions called from SQL can only return SQL datatypes. that is numbers, dates, varchar2's upto 2000 bytes in 7.x (4000 in 8.x). They cannot return LONGs or LONG raws since plsql doesn't really have a LONG or LONG raw type (a long in plsql is really a type'd definition for varchar2(32760)...)

a function called from SQL cannot return >2000 bytes in 7.x and 4000 bytes in 8.x.

>---------------------------------------------------------------
>
>CREATE OR REPLACE FUNCTION F_TEST
>RETURN VARCHAR2
>IS
> CURSOR MYCURSOR IS
> SELECT G.FIRST_NAME, GP.PHONE_NO
> FROM GUEST G, GUEST_PHONE GP
> WHERE G.GUEST_ID = GP.GUEST_ID AND
> G.FIRST_NAME is not null;
> vTEMP varchar2(32000);
>BEGIN
> vTEMP := 'A';
> FOR MYREC IN MYCURSOR LOOP
> IF LENGTH(vTEMP) < 1999 THEN
> vTEMP := vTEMP || MYREC.FIRST_NAME;
> END IF;
> END LOOP;
> RETURN vTEMP;
>END;
>/
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 30 1999 - 14:06:40 CDT