Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2013
    Posts
    6

    Question Unanswered: Help Required: Parsing String Collection or Concatenated String

    Hi All,

    Whenever the length of string l_long_string is above 4000 characters, the following code is throwing an error:
    ORA-01460: unimplemented or unreasonable conversion requested

    Instead of the nested regexp_substr query, when I try to use SELECT column_value FROM TABLE(l_string_coll), it throws:
    ORA-22905: cannot access rows from a non-nested table item

    How can I modify the dynamic query?

    Notes:
    1. l_string_coll is of type DBMS_SQL.VARCHAR2S, and comes as input to my procedure (here, i have just shown as an anonymous block)
    2. I'll have to manage without creating a User-defined Type in DB schema, so I am using the in-built DBMS_SQL.VARCHAR2S.
    3. This is not the actual business procedure, but is close to this. (Can't post the original)
    4. Dynamic query has to be there since I am using it for building the actual query which uses session, current application schema name etc.

    Code:
    /*
    CREATE TABLE some_other_table
    (word_id NUMBER(10), word_code VARCHAR2(30), word VARCHAR2(255));
    INSERT INTO some_other_table VALUES (1, 'A', 'AB');
    INSERT INTO some_other_table VALUES (2, 'B', 'BC');
    INSERT INTO some_other_table VALUES (3, 'C', 'CD');
    INSERT INTO some_other_table VALUES (4, 'D', 'DE');
    COMMIT;
    */
    
    DECLARE
        l_word_count  NUMBER(10)      := 0;
        l_counter     NUMBER(10)      := 0;
        l_long_string VARCHAR2(30000) := NULL;
        l_dyn_query   VARCHAR2(30000) := NULL;
        l_string_coll DBMS_SQL.VARCHAR2S;
        
    BEGIN
        -- l_string_coll of type DBMS_SQL.VARCHAR2S comes as Input to the procedure
        FOR i IN 1 .. 4100
        LOOP
            l_counter := l_counter + 1;
            l_string_coll(l_counter) := 'AB';
        END LOOP;
        -- Above input collection is concatenated into CSV string 
        FOR i IN l_string_coll.FIRST .. l_string_coll.LAST
        LOOP
            l_long_string := l_long_string || l_string_coll(i) || ', ';
        END LOOP;
        l_long_string := TRIM(',' FROM TRIM(l_long_string));
        dbms_output.put_line('Length of l_long_string = ' || LENGTH(l_long_string));
        /*
        Some other tasks in PLSQL done successfully using the concatenated string l_long_string
        */
        l_dyn_query := ' SELECT COUNT(*)
                         FROM   some_other_table
                         WHERE  word IN ( SELECT TRIM(REGEXP_SUBSTR(str, ''[^,]+'', 1, LEVEL)) word
                                          FROM   ( SELECT :string str FROM SYS.DUAL )
                                          CONNECT BY TRIM(REGEXP_SUBSTR(str, ''[^,]+'', 1, LEVEL)) IS NOT NULL )';
                         --WHERE  word IN ( SELECT column_value FROM TABLE(l_string_coll) )';
        EXECUTE IMMEDIATE l_dyn_query INTO l_word_count USING l_long_string;
        dbms_output.put_line('Word Count = ' || l_word_count);
        
    EXCEPTION
        WHEN OTHERS 
        THEN
          dbms_output.put_line('SQLERRM = ' || SQLERRM);
          dbms_output.put_line('FORMAT_ERROR_BAKCTRACE = ' || dbms_utility.format_error_backtrace);
        
    END;
    /

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How can I modify the dynamic query?

    dynamically

    We don't have your tables.
    We don't have your data.
    We don't have your requirements.
    We don't know what are expected/desired results.

    Dynamic SQL does not scale & should be avoided.
    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
    Jul 2014
    Posts
    4

  4. #4
    Join Date
    Sep 2013
    Posts
    6

    Query answered

    Hi Anacedent,

    I have provided my table and data in the commented section above the anonymous block.
    The requirement was to count the number of words that matches the table data from a long string.
    I am sorry for any confusion though. Next time if I am posting I'll categorize it into these sections.
    Thanks for your response..

    The query has already been answered in OraFaq forum at the following link:
    OraFAQ Forum: SQL & PL/SQL Help Required: Parsing String Collection or Concatenated String

    Thanks,
    Nick

Posting Permissions

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