Q

Viewing data stored in CLOB, part 2

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?
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 last published in February 2004

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close