3 Replies Latest reply: Jul 4, 2012 6:47 AM by B Dave RSS

    Converting/Extracting from CLOB

    B Dave
      Hi there.

      We are able to insert CLOB into database, Using oracle Text I'm able to search inside clob.
      [two questions solved ]
      The question arise when we need to send this data to application either as file or as text(varchar2).

      I'm able to generate file from CLOB using function,unfortunately it resides inside db and developer is not able to access it.
      1)There is option to mount application partition inside db and export file over there but it is not viable option according to management.
      2)I've googled the solution to create JAVA API. Which will perform OS command like scp to send file from db to app(or any remote host).
      For security reason this option is also dropped.
      3)I tried dbms_lob.substr but actual text inside clob is too long. File generated from CLOB sized around 5 MB.
      So I guess it requires lot of effort if I wanted to convert into varchar2 as out parameter inside function.

      So, to give CLOB data to developer as file or as varchar2. I'm not able to get any solution.
      Is there any other option using database to convert to string/varchar2 from CLOB ?
      Or do I need to drill down more into third option.

      I'm having oracle 10.2.0.5.

      Regards,
      Dave.
        • 1. Re: Converting/Extracting from CLOB
          AlbertoFaenza
          Hi Dave,

          your question is not really clear:
          I'm able to generate file from CLOB using function,unfortunately it resides inside db and developer is not able to access it.
          I guess you are talking about using UTL_FILE package. In this case the file DOES NOT reside inside db but on a file system which is accessible by Oracle.

          I still don't understand what is your goal?
          Create a file and send to some other applications?

          What are you doing with non CLOB data?

          Please give us the whole picture. You could consider writing an application which is reading the CLOB data and writing it in a local directory but I did not understand if this is the solution dropped for security,
          Check this link: [url:http://www.dba-oracle.com/t_export_unload_blob_clob.htm]http://www.dba-oracle.com/t_export_unload_blob_clob.htm

          Regards.
          Al
          • 2. Re: Converting/Extracting from CLOB
            B Dave
            sorry Alberto. I was in hurry to put everything down at once.

            Concept is:
            Insert CLOB into database.
            Search keyword from CLOB.
            Extract content of CLOB field and display to user.

            First two however are went successful. But for getting clob into varchar2 is not working. varchar2 is not able to handle such large data.

            Link that you have provided is the option dropped for security reason.

            Question, in short get data from clob. Having this 2 options (if any possible :)):
            1) get data in varchar2 from clob
            2)get data from clob and put it into file(using utl_file) and available that file to other remote server.
            And 1 more thing
            If you have other option to get clob data as string/any variable then please let me know.

            Regards
            Dave
            • 3. Re: Converting/Extracting from CLOB
              B Dave
              asked developer to handle with the clob issue.