Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2004
    Location
    South Carolina
    Posts
    8

    Talking Unanswered: dynamic columns, dynamic data in a CLOB

    Hi Everyone,

    I've tried searching and didn't see anything that addressed this. But maybe I just don't know what question to ask the search engine.

    I have a table. The table has a CLOB column. The data in the CLOB is dynamic and changes on each row. Inside the clob is a series of key,value pairs and I'd like to be able to dynamically grab each key/value. I'm thinking that internally somewhere, the keys would form temporary columns and the values the data. Between a series of rows, some of the keys will match.

    Using SQL I'd like to have a query that retrieves the embedded data, parses it, then if the correct value for a known key is present, use that value to get a lookup string from another
    table. For return data, I need the full chunk of embedded data, though if it
    was dynamically parsed by the SQL that would be nicer, plus the lookup string
    result.

    The CLOB data is something like:

    "statement - {key1=value1,key2=value2,key3=value3,...} ... more data"

    The problem here is the list of keys aren't always the same.
    And if a certain key is present, I want to use its corresponding value as a
    lookup to another table.

    Is this possible to do in pure SQL as one SQL statement?

    Currently I do it in SQL but two separate requests.

    sql -- request #1: load the lookup table to memory (once)
    sql -- request #2: Retrieve all the rows of data

    in c:
    parse the CLOB embedded data field, and match up the lookup key (if present)
    with the lookup table string from call #1.

    I know basic SQL but thats about it.

    Thanks,

    jenni
    Last edited by skibunni; 09-03-04 at 13:48.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Unfortunately, whoever designed your database has made it as difficult as possible to query the data by "hiding" it from SQL inside a CLOB! A CLOB can contain any conceivable text string, and Oracle has no notion that this particular one contains key/value pairs.

    Perhaps you could write a function that parses the CLOB and returns the required key value, if it is there. Then you could perhaps write your query in a single SQL statement like:

    select bt.a, bt.b, ..., lt.string
    from bbase_table bt, lookup_table lt
    where get_key_value(bt.clobcol) = lt.keyval (+);

  3. #3
    Join Date
    Sep 2004
    Location
    South Carolina
    Posts
    8

    sql inside the clob

    Quote Originally Posted by andrewst
    Unfortunately, whoever designed your database has made it as difficult as possible to query the data by "hiding" it from SQL inside a CLOB! A CLOB can contain any conceivable text string, and Oracle has no notion that this particular one contains key/value pairs.

    Perhaps you could write a function that parses the CLOB and returns the required key value, if it is there. Then you could perhaps write your query in a single SQL statement like:

    select bt.a, bt.b, ..., lt.string
    from bbase_table bt, lookup_table lt
    where get_key_value(bt.clobcol) = lt.keyval (+);

    Thanks for the reply. Yes it is difficult. the table was designed by oracle and they stuff it with data (we are on a project with an oracle team and our team). I asked them why they did it that way and din't have much of a good answer. I think its their way of being tricky.

    enough of that.

    ok so when you say write a function do you mean a stored procedure? Ive never written such a thing but i think i can try figuring it out i guess. Or is it an on the fly defined function in sql?

    Thanks

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Oracle Consulting? I see! I used to work for them myself a few years ago. Of course, I was the ultimate professional but a lot of Oracle consultants are clever rookies, with < 1 years experience but lots of feature training and egos the size of Canada. No doubt they'd do everything the trickiest way possible because it was more "fun"!

    Anyway, yes I mean a stored procedure something like this:
    Code:
    CREATE OR REPLACE FUNCTION get_key_value
    ( p_clob IN CLOB
    , p_keyname IN VARCHAR2
    ) RETURN VARCHAR2
    IS
      v_keyval VARCHAR2(100);
    BEGIN
      -- Parse p_clob, look for p_keyname, and if found then put the
      -- corresponding value into v_keyval.  You are likely to use INSTR and
      -- SUBSTR functions to do that.  Then...
      RETURN v_keyval;
    END;
    Of course, in reality all* stored procedures should be in packages - I just did stand-alone for simplicity.

    (* OK, with very few exceptions)

  5. #5
    Join Date
    Sep 2004
    Location
    South Carolina
    Posts
    8
    Quote Originally Posted by andrewst
    Code:
    
    
    Of course, in reality all* stored procedures should be in packages - I just did stand-alone for simplicity.

    (* OK, with very few exceptions)
    ok I'm going to try this. I haven't written the code yet just typing in your skeleton and using tora.

    oracle is giving me ora-06553: pls-306: wrong number or types of arguments in getkeyvalue

    Its complaining where I do the call the function
    Code:
     create or replace function getKeyValue
     ( p_clob in CLOB
     , p_keyname in VARCHAR2
     ) return VARCHAR2
     IS
        V_KEYVAL VARCHAR2(100);
     BEGIN
         -- PARSE P_CLOB, LOOK FOR P_KEYNAME, AND IF FOUND THEN PUT THE
         -- CORRESPONDING VALUE INTO V_KEYVAL.  YOU ARE LIKELY TO USE INSTR AND
         -- SUBSTR FUNCTIONS TO DO THAT. THEN...
         RETURN V_KEYVAL;
     END;
    select * from cust_bad_records br, cust_message_codes mc
    where get_key_value(br.message_text) = mc.code(+) ;

    oracle blows out with \
    [code] where <error>getKeyValue

    Is there a reference where I can find out how to write a function in sql?

    Thanks

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    In your example (select * from cust_bad_records ...) you have only one parameter while calling the function (and it is created with two parameters). It will cause a "wrong number or types of arguments in call to keyGetValue" error.

    You can read more about functions (and PL/SQL generally) here.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Sorry if I confused you. Originally I suggesetd a function with one argument only (the CLOB value), but when I wrote the example function I thought it would probably be a good idea to add another for the key name. I should have mentioned that in my post!

  8. #8
    Join Date
    Sep 2004
    Location
    South Carolina
    Posts
    8

    Red face no errors now

    Quote Originally Posted by andrewst
    Sorry if I confused you. Originally I suggesetd a function with one argument only (the CLOB value), but when I wrote the example function I thought it would probably be a good idea to add another for the key name. I should have mentioned that in my post!
    Thanks yes that is a good idea !

    I added 'messageCodeKey' as the second parameter and this time it didn't crash.

    Now I have a skeleton. stay tuned, i'll see if i can write the parser

    Thanks and i'll post what i end up using

    Jennifer

  9. #9
    Join Date
    Sep 2004
    Location
    South Carolina
    Posts
    8

    Question compile error? ORA-24344 success with compilation error ????

    I googled and found out how to use instr and substr like you suggested as potential functions to use. I tried typing up a function that I think should work. I looked over the code. I don't see the error. What have I done wrong?

    Oracle has given me a 'success with compilation error' and i do not know what that means. I'd also like to know is there a way to see what the values of my variables like V_LENGTH are as that would help me debug.

    Code:
    create or replace function getKeyValue
    ( p_clob in CLOB
    , p_keyname in VARCHAR2
    ) return VARCHAR2
     IS
       V_KEYVALUE VARCHAR2(100);
       V_KEYPOS NUMBER;
       V_EQUALS NUMBER;
       V_COMMA NUMBER;
       V_LENGTH NUMBER;
       BEGIN
           -- get first occurance of our keyname  
           --   format: 'keyname=keyvalue,'   (no quotes) 
           V_KEYPOS := instr(p_clob, p_keyname,1,1);
           V_EQUALS := instr(p_clob, '=', V_KEYPOS, 1)+1;
           -- v_equals is now pointing at the start of our value 
           V_COMMA := instr(p_clob, ',', V_EQUALS,1);
           V_LENGTH := V_COMMA - V_EQUALS + 1;
           V_KEYVAL := SUBSTR(p_clob, V_EQUALS, V_LENGTH);
           RETURN V_KEYVAL;
       END;


    Tora warning message:
    ORA-24344 success with compilation error

    confused jenni

  10. #10
    Join Date
    Sep 2004
    Location
    South Carolina
    Posts
    8
    Quote Originally Posted by skibunni
    I googled and found out how to use instr and substr like you suggested as potential functions to use. I tried typing up a function that I think should work. I looked over the code. I don't see the error. What have I done wrong?
    I think i mispelled v_keyvalue. I'll give that a try....

  11. #11
    Join Date
    Sep 2004
    Location
    South Carolina
    Posts
    8

    Talking IT works!!!! It Works!!!!



    It works great in TORA

    Code:
    create or replace function getKeyNumFromText
    ( p_clob in CLOB
    , p_keyname in VARCHAR2
    ) return number
      IS
         V_KEYVALUE VARCHAR2(100);
         v_keylength number;
         V_KEYPOS NUMBER;
         V_EQUALS NUMBER;
         V_COMMA NUMBER;
         V_LENGTH NUMBER;
         BEGIN
            v_keylength := length(p_keyname) +1; -- length of keyname + the '=' delimiter
            V_KEYPOS := instr(p_clob, p_keyname,1,1) + v_keylength; -- where our data starts
            V_COMMA := instr(p_clob, ',', V_KEYPOS,1);
            V_LENGTH := V_COMMA - V_KEYPOS;
            RETURN to_number( substr(p_clob, V_KEYPOS, V_LENGTH) );
         END;
    
    select * from cust_bad_records br, cust_message_codes mc
    where getKeyNumFromText(br.message_text, 'messagetype') = mc.code(+) ;
    Thanks to everyone for all their help!!


    jenni fur

Posting Permissions

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