Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186

    Unanswered: dbms_lob.substr returns varchar2?????

    I have a 400kb text string in a clob variable. I need to strip out certain characters to remove html tags. To do this I need to do lots of substr

    This is taking a very long time and using massive amounts of temp tablespace, nearly 2GB!!

    I'm trying to use dbms_lob.substr instread as I assume the issue is there are lots of clob to varchar2 conversions going on or something, still seems excessive that a 400kb file can consume nearly 2GB of temp tablespace!!

    When I use dbms_lob.substr on the clob you cant return more than 32767 characters as it returns a varchar2, I need to to return a clob ... am I missing something as it seems ridiculous that dbms_lob.substr does not return a clob

    My only work around is to have a varchar2(32767) array, break my clob down into chunks and, work with that and then stitch it all back together into a clob again ...... I'm hoping I am missing something and there is a better way

    Thanks
    Robert
    There are 10 types of people in the world, those that know Binary and those that don't.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I'm hoping I am missing something and there is a better way
    use the right tool for the job.
    While a hammer is a fine tool, it is suboptimal to use to make 2 pieces of lumber from 1 board. You could succeed by doing so, but likely expend more energy than if you used a saw

    Perhaps using a language other than PL/SQL, such as Java or PERL, that does not limit strings to 32767 would provide faster processing.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    Quote Originally Posted by anacedent View Post
    >I'm hoping I am missing something and there is a better way
    use the right tool for the job.
    While a hammer is a fine tool, it is suboptimal to use to make 2 pieces of lumber from 1 board. You could succeed by doing so, but likely expend more energy than if you used a saw

    Perhaps using a language other than PL/SQL, such as Java or PERL, that does not limit strings to 32767 would provide faster processing.
    PL/SQL is my only option as all of our application is written PL/SQL so it's not really practical to have a single java / perl etc routine just to do this one thing
    There are 10 types of people in the world, those that know Binary and those that don't.

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    I agree w/ anacedent, wrong tool for the job. In the time you spent trying to figure this out and arguing why it must be done in PL/SQL, you could have implemented a much more efficient solution with a tool designed for text processing.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    removed...
    Last edited by chuck_forbes; 01-19-10 at 14:27. Reason: bad solution

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •