Re: LONG to VARCHAR2

From: Steve Bell <swayne.bell_at_sympatico.ca>
Date: Fri, 23 Mar 2001 14:12:30 GMT
Message-ID: <3ABB5744.DB81BBD@sympatico.ca>

Hi,

You can try a function like this:

• This function will select from a LONG column and store the size
• indicated in the parameter as a varchar2 value.

CREATE OR REPLACE FUNCTION LONGVAR (V_ID NUMBER, V_SIZE INTEGER)
RETURN VARCHAR2
IS

• V_SIZE IS NUMBER OF CHARS TO RETRIEVE

V_CUR  INTEGER;
V_RC   INTEGER;
V_BUFFER VARCHAR2(4000);
V_BUF_LEN INTEGER;

BEGIN V_CUR := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(V_CUR,'SELECT PO_ITM_DESC FROM LONGTABLE WHERE PK_COLUMN = ' ||
TO_CHAR(V_ID),DBMS_SQL.NATIVE); DBMS_SQL.DEFINE_COLUMN_LONG(V_CUR,1);
V_RC := DBMS_SQL.EXECUTE_AND_FETCH(V_CUR); DBMS_SQL.COLUMN_VALUE_LONG(V_CUR,1,V_SIZE,0,V_BUFFER,V_BUF_LEN); DBMS_SQL.CLOSE_CURSOR(V_CUR);
RETURN SUBSTR(V_BUFFER,1,V_BUF_LEN);
END;
/

• USAGE IS SELECT LONGVAR(23,4) FROM DUAL TO SELECT A STRING WITH LENGTH
• OF 4 FROM THE RECORD WITH PK OF 23 FOR EXAMPLE.

Hth,
Steve

Michael Krolewski wrote:

> "André Klos" <andre.klos_at_pikon.com> wrote in article
> <99d1op$62n$1_at_piesbach.saarnet.de> :
> >Hi!
> >
> >How can I convert LONG to VARCHAR2?
> >
> >Thanks
> >
> >André
> >
> >
> >
> You question is a bit vague. What are you trying to do?
> Convert a column in a table or just a local variable.
>
> Assuming that the LONG are long -- beyond 32K, you cannot
> use PL/SQL as its internal LONG is limited to 32K ( check out
> the manual). I ran into the same problem -- we had a LONG
> that needed to be transferred from one table to another.
>
> I found that within C you can write the conversion -- using the
> C environment to buffer the transfer between the columns.
>
> Michael Krolewski
> m_krolewski_at_netzero.net
> _______________________________________________
> Submitted via WebNewsReader of http://www.interbulletin.com
Received on Fri Mar 23 2001 - 08:12:30 CST