Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Working w/Oracle 7.3.4 LONG variables as VARCHAR2 variables in PL/SQL

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

From: Ken Ho <hoke_at_gse.harvard.edu>
Date: Thu, 08 Feb 2001 18:54:04 GMT
Message-ID: <3a82eadb.17957289@news.harvard.edu>

I now see in Feuerstein's Oracle Built-In Packages book that DBMS_OUTPUT is limited to 255 characters per line and prior to Oracle 8.0 UTL_FILE is limited to 1023, which seems to be the issue.

On Wed, 07 Feb 2001 16:10:32 GMT, hoke_at_gse.harvard.edu (Ken Ho) wrote:

>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 Thu Feb 08 2001 - 12:54:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US