Re: LONG Field / 7.3.4 / PL-SQL

Hi

A variable declared LONG can store strings up to 32760 bytes (seven fewer bytes than varchar2).
The LONG datatype in a Oracle table can store 2^31-1 bytes.

This means that you can not select a LONG database value larger than 32760 bytes into a PL/SQL LONG variable!

I recommend that you always use the varchar2 datatype in PL/SQL programs. LONGs have a place in RDBMS.

The solution in your case is Use LOBs !!! Oracle recommends that you should no longer use LONG or LONG RAW in your applications. Oracle offers a powerful built-in package DBMS_LOB, to help you manipulate the contens of LOBs in ways not possible with LONGs.

define in your program the variable prev_history from type CLOB and use DBMS_LOB.SUBSTR and DBMS.INSTR and DBMS.COPY

Regards Andreas

Bruno Plankensteiner schrieb:

> Hi
> I have a stored procedure which does the following:
>
> add a string to a long field in a table_case
>
> If the long has more than something like 36K bytes I get the error 6502
>
> -----------------------------------------------------------------------------
>
> APPEND_CASE_HISTORY(obj IN NUMBER, note IN VARCHAR, ret OUT VARCHAR)
> AS
> prev_history long;
> BEGIN
> select case_history into prev_history from table_case where objid=obj;
> prev_history:=prev_history || note;
> update table_case set case_history=prev_history where objid=obj;
> commit;
> END APPEND_CASE_HISTORY
> --------------------------------------------------------------------------------------
>
> There is a limit of 36K if you select a long type into a long. Do you
> know a workaround?
>
> Thank you
>
> Bruno
Received on Tue Sep 28 1999 - 07:43:20 CDT