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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close