Working w/Oracle 7.3.4 LONG variables as VARCHAR2 variables in PL/SQL

From: Ken Ho <hoke_at_gse.harvard.edu>
Date: Wed, 07 Feb 2001 16:10:32 GMT
Message-ID: <3a816804.2755366@news.harvard.edu>

On p. 96 of the 2nd edition of Steven Feuerstein's Oracle PL/SQL programming book, he explains that one can put data from an Oracle database LONG variable into a PL/SQL VARCHAR2 variable, since the PL/SQL VARCHAR2 type can hold 32767 bytes instead of the 2000 which VARCHAR2 is limited to in Oracle databases. This would ostensibly allow you to use various string functions on data that came from an Oracle LONG variable, which wouldn't otherwise be usable by such functions.

Can anyone tell me where the problem occurs in the following code, where I try to use the above idea? In its original form, the cursor is declared globally within a PL/SQL package with the other code being in its own procedure in the same package. Long_table has a LONG variable column called long_var.

CURSOR long_cur IS
  SELECT * FROM long_table;

local_long_var varchar2(2000);

FOR rec IN long_cur LOOP
  local_long_var := rec.long_var;

	IF length(local_long_var) > 2000 THEN
	  dbms_output.put_line(length(local_long_var));  -- this works
	  dbms_output.put_line(local_long_var);  -- this fails
	END IF;

END LOOP; The error message I get in both SQL Worksheet and the SQL window of TOAD (Tool for Oracle Application Developer) is:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SCOTT.LONG_PKG", line 25
ORA-06512: at line 2

It doesn't seem to be directly related to DBMS_OUTPUT.PUT_LINE since I get a similar error when, instead of using DBMS_OUTPUT.PUT_LINE, I use UTL_FILE.PUT_LINE to output local_long_var to a file:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SCOTT.LONG_PKG", line 89
ORA-06512: at line 2

I'd appreciate suggestions.

-Ken Ho
hoke_at_gse.harvard.edu Received on Wed Feb 07 2001 - 10:10:32 CST