I have migrated a database from MS Access to Oracle9i, which has three CLOB fields. Now when I try to view data, which has been stored in CLOB, I am only able to retrieve 4,000 KB of data in Form 6i. What should I do to retrieve data greater than 4,000 KB in Form 6i?

    Requires Free Membership to View

Click to return to part 1.
DECLARE
    Temp_File_Name      VARCHAR2(300);
    Buffer_Hold1        VARCHAR2(32767);
    Start Point         NUMBER;
    Tmp1_Modified_Date  Date;
    Tmp2_Modified_Date  Date;
    LOCATION_VAR        CLOB;
    Read_File           Text_IO.FILE_TYPE;
    Temp_File           Text_IO.FILE_TYPE; 
    Line_Buffer         VARCHAR2(32767);
    No_Of_Hits          NUMBER := 0;
BEGIN

  TOOL_ENV.GETVAR('TEMP', Temp_File_Name);
  Temp_File_Name := Temp_File_Name||'Script'||File_No||'.txt';


--Delete temp files if they exist

WIN_API_UTILITY.DELETE_FILE(Temp_File_Name, FALSE); 
WIN_API_UTILITY.DELETE_FILE(Temp_File_Name(1), FALSE);

--open for writing

Temp_File := Text_Io.Fopen(Temp_File, 'W'); 
Set_Application_Property(CURSOR_STYLE, 'BUSY');  


/* Now Select the CLOB field into the Temp_File 
START_POINT := 0;
 

    
LOOP  
  Buffer_Hold1 := DBMS_LOB.SUBSTR(CLOB_FIELD, START_POINT + 1,
                                   START_POINT + 32767);

--writes data to tmp file


 If Length(NVL(Buffer_Hold1, ' ')) BETWEEN 2 AND 32767 Then 
             Text_Io.Put(Temp_File, Buffer_Hold1);
 Else
             Exit;
 End If;

 START_POINT := START_POINT + LENGTH(BUFFER_HOLD1) +1;

END LOOP'

Text_Io.Put(Temp_File, Buffer_Hold1);

WIN_API_UTILITY.COPY_FILE(Temp_File_Name, Temp_File_Name(1), TRUE,    
                        FALSE); --copy to second tmp
Host('NOTEPAD '|| Temp_File_Name, Screen);  
-- This opens the file to display the entire CLOB.

/* Now if the File has changed 
WIN_API_FILE.MODIFIED_DATE(Temp_File_Name, Tmp1_Modified_Date
, Return_MS, FALSE);
WIN_API_FILE.MODIFIED_DATE(Temp_File_Name(1), Tmp2_Modified_Date, 
Return_MS, FALSE);

-- make sure file is >0 bytes
If WIN_API_FILE.FILE_SIZE(Temp_File_Name, FALSE) > 0 Then

--tmp file changed so table needs to be updated

        If Tmp1_Modified_Date > Tmp2_Modified_Date Then 
              Read_File := Text_Io.Fopen(Temp_File_Name, 'R'); 

        /*-opening and closing the file once will make sure the file
          is opened correctly for the next instance of open
          */

              Text_Io.Fclose(Read_File);        
              Synchronize;
              Read_File := Text_Io.Fopen(Temp_File_Name, 'R');

              Text_Io.Get_Line(Read_File, Line_Buffer);

              Offset_Var := 1;
              -- write first set of text

              /*  NOTE LOCATION_VAR is a handle to a database CLOB
              PLEASE MAKE SURE THAT it is initialized to the database 
              CLOB field.
              */

              DBMS_LOB.WRITE(Location_Var, Buffer_Var, Offset_Var, 
              LINE_BUFFER);

              OFFSET_VAR := LENGTH(LINE_BUFFER);
          LOOP
              IF OFFSET_VAR + LENGTH('  ') > 32767 THEN
                    Next_record;
              ELSE
                EXIT;
             END IF;
             Text_Io.Get_Line(Read_File, Line_Buffer);

             OFFSET_Var := LENGTH(Line_Buffer);

          -- writeappend the rest of the text
             DBMS_LOB.WRITEAPPEND(Location_Var OFFSET_VAR, 
           LINE_BUFFER);
      
   END LOOP;

   /* Write the first set or the last*/

   DBMS_LOB.WRITEAPPEND(Location_Var OFFSET_VAR, LINE_BUFFER);
   TEXT_IO.FCLOSE(Read_File);
 END IF;
END IF;
..

..
END;

This was first published in February 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: