1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

PL/SQL: ORA-00932: inconsistent datatypes error reg

Discussion in 'SQL PL/SQL' started by laxman, Jun 2, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Hi all,
    while compiling one package i am facing one error

    PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got EXP.NUM_ARRAY

    Here in my package i am calling one function

    Code (SQL):
    CREATE OR REPLACE package pkg_a(iN_num NUMBER)
    AS
    var_arr num_array; -- variable of array type;
    BEGIN
        SELECT pkg_b.func_call(IN_id,',') INTO var_arr FROM dual; -- calling func_a

    END;
    --- Inside function the return variable is also array type

    numbers num_array := num_array(); --declared inside function which will return some values

    but i am getting some error

    PL/SQL: ORA-00932: inconsistent datatypes: expected EXP.NUM_ARRAY got ORADB
    A.NUM_ARRAY

    Kindly provide me some information to solve it

    Regards
    Laxman
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If you won't post the code for the function no one can assist you.
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear sir,
    here is the code
    Code (SQL):
    FUNCTION func_b(IN_Str VARCHAR2, IN_Delim VARCHAR2) RETURN num_array  
    IS
        l_count NUMBER;
        l_value   varchar2(2000);
        string_to_parse varchar2(2000);
        numbers  num_array := num_array();
        num_ind NUMBER := 1;
       
    BEGIN
        string_to_parse := IN_Str||IN_Delim;
        l_count := LENGTH(string_to_parse) - LENGTH(REPLACE(string_to_parse,IN_Delim,''));
           
        IF l_count IS NOT NULL THEN
            FOR i IN 1 .. l_count loop
                SELECT regexp_substr(string_to_parse,'[^'||IN_Delim||']+',1,i)
                        INTO l_value
                        FROM dual;
           
                IF l_value IS NOT NULL THEN
                        numbers.EXTEND;
                        numbers(num_ind) := l_value;
                        num_ind := num_ind + 1;
                END IF;
            END loop;
        END IF;
        RETURN numbers;
    END func_b;
    Regards
    Laxman
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You're going about this incorrectly as you cannot use SELECT ... INTO ... with a collection. You have two choices: assign var_array the returned value from func_b or use BULK COLLECT and the TABLE() function. Examples of both shown below:

    Code (SQL):
     
    SQL> DECLARE
      2          var_array num_array;
      3  BEGIN
      4
      5          SELECT * bulk collect INTO var_array FROM TABLE(func_b('1,2,3,4,5,6,7,8,9', ','));
      6          FOR i IN var_array.FIRST..var_array.LAST loop
      7                  dbms_output.put_line(var_array(i));
      8          END loop;
      9
     10  END;
     11  /
    1
    2
    3
    4
    5
    6
    7
    8
    9
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> DECLARE
      2          var_array num_array;
      3  BEGIN
      4
      5          var_array:=func_b('1,2,3,4,5,6,7,8,9', ',');
      6          FOR i IN var_array.FIRST..var_array.LAST loop
      7                  dbms_output.put_line(var_array(i));
      8          END loop;
      9
     10  END;
     11  /
    1
    2
    3
    4
    5
    6
    7
    8
    9
    PL/SQL PROCEDURE successfully completed.
    SQL>

     
     
    laxman likes this.
  5. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Thanks a lot Sir, i will modify accordingly and let you know the status.

    Thanks n regards
    Laxman