Laura Bellini (laura.bellini@compaq.com]) wrote:
: Is there any conversion function or method to convert a Long variable type
: into a varchar2? (varchar can be v. large...2500+).
: 
: thx.
: Laura Bellini
: laura_bellini@compaq.com

Laura,

You cannot do string functions on LONG columns with regular SQL. You will
have to use PL/SQL. First, if you expect values to be more than 2000
characters, then you have two choices:
1) Switch to Oracle8, where the varchar2 fields can have 4000
characters. Oracle7 has a 2000 limit.
2) Stay in Oracle7 and break the LONG field into two varchar2(2000)
fields.

The only way to convert LONG columns is in PL/SQL. Look at the following
example, which determines the length of the LONG field:

SET SERVEROUTPUT ON SIZE 10000;
DECLARE
long_var LONG;
BEGIN
SELECT text_column INTO long_var
FROM table_with_long
WHERE rownum < 2;
DBMS_OUTPUT.PUT_LINE('The length is '||LENGTH(long_var));
END;
/


Basically, you define a variable as the LONG type, then SELECT the column
INTO the variable. Finally, it is output to the user. SET SERVEROUTPUT ON
SIZE 10000 allows spooling from the PUT_LINE to go to the screen.

You can use a similar method to select the LONG into a varchar field. The
following example puts the first 2000 characters into TABLE_B, which for
our purposes has one column, TEXT_FIELD:

DECLARE
long_var LONG;
var_var  VARCHAR2(2000);
BEGIN
   SELECT text_column INTO long_var
   FROM table_with_long
   WHERE rownum < 2;
   var_var := substr(long_var,1,2000);
   INSERT INTO table_b
   VALUES (var_var);
END;
/

Best of luck,

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 150+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page 18">Back to Ari Kaplan's Home Page