Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2010
    Posts
    15

    Question Unanswered: How to extract string from column with CLOB data_type & data_length of 4000?

    I am running in Oracle 11i. A table exists with a column 'bo_data_area' which is defined as data_type CLOB & data_length of 4000.

    My need is to extract several strings of data strung together within this column. The string values are wrapped with the column value name (such as: '<startDate>', which indicates the beginning of the start date & </startDate>', which indicates the end of the start date string).

    Sample of two strings contained within data column bo_data_area:
    <startDate>2008-01-01</startDate><endDate>2008-01-31</endDate>. The values bolded are the strings I am attempting to extract.

    I have attempted several different ways and get errors. I am listing the different functions I have used and the results:

    Code:
    select form_id, form_type instr('</startDate>',bo_data_area) as st_dt_pos from form_table;
    My results: Oracle error ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 5556, maximum:4000)

    Code:
    select form_id, form_type substr('</startDate>',bo_data_area) as st_dt_pos from form_table;
    My results: ORA-00932: inconsistent datatypes: expected NUMBER got CLOB.

    Code:
    select form_id, substr(bo_data_area,1,instr(bo_data_area, '</startDate>')-1) as st_dt_pos from form_table;
    My results: Rows were returned but values are <CLOB>,



    Any suggestions would be greatly appreciated!
    Last edited by ncsthbell; 03-06-13 at 11:48.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Why do you post in the ANSI forum, if you are using Oracle? CLOB handling is highly DBMS dependent.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Aug 2010
    Posts
    15
    I posted it in ANSI Sql because it is Sql. I thought the Oracle forum was for database questions, not sql.

    If I need to move it, can you tell me where and how to move it?

    THanks

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by ncsthbell View Post
    I posted it in ANSI Sql because it is Sql. I thought the Oracle forum was for database questions, not sql.
    It's a question about Oracle SQL

    If I need to move it, can you tell me where and how to move it?
    A moderator needs to do that.

    Did you have a look at the DBMS_LOB package:
    http://docs.oracle.com/cd/E11882_01/...b.htm#BABDDFDH
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Why shazam! The thread has moved!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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