Re: LONG Field / 7.3.4 / PL-SQL

Hi
Thank you,
As far as I know the package DBMS_LOB is only available on Oracle 8.

Since we are using a package (Clarify) and this field is a long (out of the box) I cannot change the type.

I added an exception handler to the stored procedure. Since the corresponding Client doesn´t show more than 32k I just catch the exception in the stored procurdure.

Thank you
Bruno

Andreas Hundt wrote:

> 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.
>
> Declare in your program the variable prev_history from type CLOB
> and use DBMS_LOB.SUBSTR and DBMS_LOB.INSTR and DBMS_LOB.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 - 09:45:49 CDT