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!

inconsistent datatypes

Discussion in 'SQL PL/SQL' started by Marco, Sep 21, 2012.

  1. Marco

    Marco Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Hello everybody,

    I'm new in Oracel and with PL/Sql and I need your help.

    I have VARRAY declared as belowe:
    TYPE ARRAY_OF_VARCHAR IS VARRAY (100) OF VARCHAR2(255);

    In sqlplus console I write something like this:
    declare
    products array_of_varchar := array_of_varchar('1', '2', '3');
    cnt number(3) := 0;
    begin
    select count(*) into cnt from testtab where name in (products);
    end;
    /

    and I got following error:

    ERROR at line 5:
    ORA-06550: line 5, column 54:
    PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got
    IS3.ARRAY_OF_VARCHAR
    ORA-06550: line 5, column 1:
    PL/SQL: SQL Statement ignored


    I have tried to find solution on many oracle forums but I couldn't find answer.

    Marco.
     
  2. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Hello there,


    Please provide DDL of your testtab.

    Also when you use IN as a multiset operation

    Code (Text):

    tab IN (...)
     
    What you put at the left side of the IN is also a Nested table. So I think you didn't write the query correctly. If you're searching a scalar value in a collection by using a SELECT then you have to use something like this:

    Code (Text):

    SELECT ...
    FROM testtab
    WHERE name IN (SELECT COLUMN_VALUE
                   FROM TABLE(<your collection object>)
     

    Regards,
    Dariyoosh
     
  3. Marco

    Marco Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Hej,

    this is DDl for testtab:

    CREATE TABLE "IS3"."TESTTAB" ( "ID" NUMBER NOT NULL , "NAME" VARCHAR2(50), "SURENAME" VARCHAR2(100)) TABLESPACE "IS3_TABLESPACE" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING NOCOMPRESS ENABLE ROW MOVEMENT

    Marco.
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    The issue was bolded. Actually you the name field is varchar2 type and you have provided varray type. Thats the issue its reflecting. You need to pass characters as parameters there.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The issue stems from the anonymous block:

    declare
    products array_of_varchar := array_of_varchar('1', '2', '3');
    cnt number(3) := 0;
    begin
    select count(*) into cnt from testtab where name in (products);
    end;
    /

    You have a varray in your IN list and that's not going to work; you CAN modify the query as shown in the following example:

    Code (SQL):
    SQL> CREATE OR REPLACE TYPE ARRAY_OF_VARCHAR IS VARRAY (100) OF VARCHAR2(255);
      2  /
     
    TYPE created.
     
    SQL>
    SQL> CREATE TABLE TESTTAB( ID NUMBER NOT NULL , NAME VARCHAR2(50), SURNAME VARCHAR2(100)) ;
     
    TABLE created.
     
    SQL>
    SQL> BEGIN
      2     FOR i IN 1..50 loop
      3             INSERT INTO testtab
      4             VALUES(i, to_char(MOD(i, 5)), to_char(i));
      5     END loop;
      6
      7     commit;
      8  END;
      9  /
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> DECLARE
      2  products array_of_varchar := array_of_varchar('1', '2', '3');
      3  cnt NUMBER(3) := 0;
      4  BEGIN
      5  SELECT COUNT(*) INTO cnt FROM testtab WHERE name IN (SELECT * FROM TABLE(products));
      6  dbms_output.put_line(cnt);
      7  END;
      8  /
    30
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
     
     
    Marco likes this.
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It would have been helpful to provide an actual working example.