Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    4

    Question Unanswered: dbms_utility.comma_to_table giving problem

    hi,

    I am facing some problem using the dbms_utility.comma_to_table procedure.

    actually comma_to_table is working fine if I call it with comma seperated list of words(each word must start with alphabet only), but it gives me error when I call it passing comma seperated list of numbers(or words starting with a number)

    please see the below example for the same

    CREATE OR REPLACE PROCEDURE TEST_SHOW_FROM_LIST (
    empno_list IN VARCHAR2)
    IS
    empnos DBMS_UTILITY.UNCL_ARRAY;
    numemps INTEGER;
    BEGIN
    DBMS_UTILITY.COMMA_TO_TABLE (empno_list, numemps, empnos);
    DBMS_OUTPUT.PUT_LINE('');
    FOR rownum IN 1 .. numemps
    LOOP
    DBMS_OUTPUT.PUT_LINE('EMP - '||empnos(rownum));
    END LOOP;
    END;
    /


    now when i use it with
    BEGIN
    TEST_SHOW_FROM_LIST('AA,BB,CC,DD,EE');
    END;
    /


    it works fine but, when i try

    BEGIN
    TEST_SHOW_FROM_LIST('11,22,33,44,55');
    END;
    /
    IT GIVES ME THESE ERRORS

    ERROR at line 1:
    ORA-00931: missing identifier
    ORA-06512: at "SYS.DBMS_UTILITY", line 125
    ORA-06512: at "SYS.DBMS_UTILITY", line 160
    ORA-06512: at "SYS.DBMS_UTILITY", line 202
    ORA-06512: at "USER.TEST_SHOW_FROM_LIST", line 7
    ORA-06512: at line 2


    PLEASE suggest me why it is not accepting the list of numbers..
    it would be a great help..

    thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: dbms_utility.comma_to_table giving problem

    Originally posted by vineetgoel
    hi,

    I am facing some problem using the dbms_utility.comma_to_table procedure.

    actually comma_to_table is working fine if I call it with comma seperated list of words(each word must start with alphabet only), but it gives me error when I call it passing comma seperated list of numbers(or words starting with a number)

    please see the below example for the same

    CREATE OR REPLACE PROCEDURE TEST_SHOW_FROM_LIST (
    empno_list IN VARCHAR2)
    IS
    empnos DBMS_UTILITY.UNCL_ARRAY;
    numemps INTEGER;
    BEGIN
    DBMS_UTILITY.COMMA_TO_TABLE (empno_list, numemps, empnos);
    DBMS_OUTPUT.PUT_LINE('');
    FOR rownum IN 1 .. numemps
    LOOP
    DBMS_OUTPUT.PUT_LINE('EMP - '||empnos(rownum));
    END LOOP;
    END;
    /


    now when i use it with
    BEGIN
    TEST_SHOW_FROM_LIST('AA,BB,CC,DD,EE');
    END;
    /


    it works fine but, when i try

    BEGIN
    TEST_SHOW_FROM_LIST('11,22,33,44,55');
    END;
    /
    IT GIVES ME THESE ERRORS

    ERROR at line 1:
    ORA-00931: missing identifier
    ORA-06512: at "SYS.DBMS_UTILITY", line 125
    ORA-06512: at "SYS.DBMS_UTILITY", line 160
    ORA-06512: at "SYS.DBMS_UTILITY", line 202
    ORA-06512: at "USER.TEST_SHOW_FROM_LIST", line 7
    ORA-06512: at line 2


    PLEASE suggest me why it is not accepting the list of numbers..
    it would be a great help..

    thanks.
    Unfortunately dbms_utility.comma_to_table is NOT a general purpose utility as its name would suggest. It only works with comma-separated names that are valid database object names, i.e. up to 30 chars beginning with a letter, etc. You can write your own, or I can post some code I already have if you want.

  3. #3
    Join Date
    Aug 2003
    Posts
    4
    thanks for your responce..
    actually im in hurry to implement few things.. so i would be thankful to you if you would send me the coding.

    thanks again...

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by vineetgoel
    thanks for your responce..
    actually im in hurry to implement few things.. so i would be thankful to you if you would send me the coding.

    thanks again...
    This has a few related procedures: delimstring_to_table is the one you need.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by andrewst
    This has a few related procedures: delimstring_to_table is the one you need.
    Having problems attaching a file. Here's the code:
    Code:
    CREATE OR REPLACE PACKAGE parse AS
    
    /*
    || Package of utility procedures for parsing delimited or fixed position strings into tables
    || of individual values, and vice versa.
    */
    
      TYPE varchar2_table IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
      TYPE integer_table  IS TABLE OF PLS_INTEGER     INDEX BY BINARY_INTEGER;
    
      PROCEDURE delimstring_to_table
        ( p_delimstring IN  VARCHAR2
        , p_table       OUT varchar2_table
        , p_nfields     OUT INTEGER
        , p_delim       IN  VARCHAR2 DEFAULT ','
        );
    
      PROCEDURE table_to_delimstring
        ( p_table       IN  varchar2_table
        , p_delimstring OUT VARCHAR2
        , p_delim       IN  VARCHAR2 DEFAULT ','
        );
    
      PROCEDURE fixedstring_to_table
        ( p_fixedstring IN  VARCHAR2
        , p_table       OUT varchar2_table
        , p_postab      IN  integer_table
        , p_lentab      IN  integer_table
        );
    
      PROCEDURE table_to_fixedstring
        ( p_table       IN  varchar2_table
        , p_fixedstring OUT VARCHAR2
        , p_postab      IN  integer_table
        , p_lentab      IN  integer_table
        );
    
    END parse;
    /
    CREATE OR REPLACE PACKAGE BODY parse AS
    
      PROCEDURE delimstring_to_table
        ( p_delimstring IN  VARCHAR2
        , p_table       OUT varchar2_table
        , p_nfields     OUT INTEGER
        , p_delim       IN  VARCHAR2 DEFAULT ','
        )
      IS
        v_string   VARCHAR2(32767) := p_delimstring;
        v_nfields  PLS_INTEGER := 1;
        v_table    varchar2_table;
        v_delimpos PLS_INTEGER := INSTR(p_delimstring, p_delim);
        v_delimlen PLS_INTEGER := LENGTH(p_delim);
      BEGIN
      NULL;
        WHILE v_delimpos > 0
        LOOP
          v_table(v_nfields) := SUBSTR(v_string,1,v_delimpos-1);
          v_string := SUBSTR(v_string,v_delimpos+v_delimlen);
          v_nfields := v_nfields+1;
          v_delimpos := INSTR(v_string, p_delim);
        END LOOP;
        v_table(v_nfields) := v_string;
        p_table := v_table;
        p_nfields := v_nfields;
      END delimstring_to_table;
    
      PROCEDURE table_to_delimstring
        ( p_table       IN  varchar2_table
        , p_delimstring OUT VARCHAR2
        , p_delim       IN  VARCHAR2 DEFAULT ','
        )
        AUTHID CURRENT_USER
      IS
        v_nfields  PLS_INTEGER := p_table.COUNT;
        v_string   VARCHAR2(32767);
      BEGIN
        FOR i IN 1..v_nfields
        LOOP
          v_string := v_string || p_table(i);
          IF i < v_nfields THEN
            v_string := v_string || p_delim;
          END IF;
        END LOOP;
        p_delimstring := v_string;
      END table_to_delimstring;
    
      PROCEDURE fixedstring_to_table
        ( p_fixedstring IN  VARCHAR2
        , p_table       OUT varchar2_table
        , p_postab      IN  integer_table
        , p_lentab      IN  integer_table
        )
      IS
        v_nfields  PLS_INTEGER := p_postab.COUNT;
      BEGIN
        FOR i IN 1..v_nfields
        LOOP
          p_table(i) := SUBSTR( p_fixedstring, p_postab(i), p_lentab(i) );
        END LOOP;
      END fixedstring_to_table;
    
      PROCEDURE table_to_fixedstring
        ( p_table       IN  varchar2_table
        , p_fixedstring OUT VARCHAR2
        , p_postab      IN  integer_table
        , p_lentab      IN  integer_table
        )
      IS
        v_nfields  PLS_INTEGER := p_postab.COUNT;
        v_string VARCHAR2(32767);
        v_length PLS_INTEGER := 0;
      BEGIN
        /* Determine total length of record */
        FOR i IN 1..v_nfields
        LOOP
          v_length := GREATEST( v_length, p_postab(i)+p_lentab(i)-1 );
        END LOOP;
        /* Create blank record of appropriate length */
        v_string := RPAD( ' ', v_length, ' ' );
        /* Fill blank record with the fields */
        FOR i IN 1..v_nfields
        LOOP
          v_string := SUBSTR( v_string, 1, p_postab(i)-1 )
                      || RPAD( p_table(i), p_lentab(i) )
                      || SUBSTR( v_string, p_postab(i)+p_lentab(i) );
        END LOOP;
        p_fixedstring := v_string;
      END table_to_fixedstring;
    
    END parse;
    /

Posting Permissions

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