This discussion is archived
6 Replies Latest reply: Apr 1, 2013 3:57 AM by jeneesh RSS

PLS-00382: expression is of wrong type

996818 Newbie
Currently Being Moderated
Hi

PL/SQL Code
CREATE OR REPLACE TYPE prof_ctab_value_rec_t AS OBJECT (
  prof_id         INTEGER
 ,ctabv_id        INTEGER
 ,ctab_id         INTEGER
)


CREATE OR REPLACE TYPE prof_ctab_value_table_t IS TABLE OF prof_ctab_value_rec_t



FUNCTION get_prof_ctab_value

RETURN prof_ctab_value_table_t PIPELINED
IS

  v_sql          VARCHAR2(4000);
  v_cursor       SYS_REFCURSOR;
  v_result_set   prof_ctab_value_table_t;


BEGIN

   v_sql := 'select .....';

  EXECUTE IMMEDIATE v_sql USING
  OUT v_cursor;
      
    LOOP
    FETCH v_cursor INTO v_result_set;
    EXIT WHEN v_cursor%NOTFOUND;
    PIPE ROW(v_result_set);

    END LOOP;


END;
Compilation errors


Error: PLS-00382: expression is of wrong type
Text: PIPE ROW(v_result_set);

Error: PL/SQL: Statement ignored
Text: PIPE ROW(v_result_set);

please tell me wht causing the compilation error . thanks in advance.
  • 1. Re: PLS-00382: expression is of wrong type
    Ramin Hashimzadeh Expert
    Currently Being Moderated
    CREATE OR REPLACE TYPE prof_ctab_value_rec_t AS OBJECT (
    prof_id         INTEGER
    ,ctabv_id        INTEGER
    ,ctab_id         INTEGER
    )
    
    
    CREATE OR REPLACE TYPE prof_ctab_value_table_t IS TABLE OF prof_ctab_value_rec_t
    
    
    
    FUNCTION get_prof_ctab_value
    
    RETURN prof_ctab_value_table_t PIPELINED
    IS
    
    v_sql          VARCHAR2(4000);
    v_cursor       SYS_REFCURSOR;
    v_result_set   prof_ctab_value_table_t;
    
    
    BEGIN
    
    open v_cursor for select .....;
    
    --EXECUTE IMMEDIATE v_sql USING
    --OUT v_cursor;
    
    LOOP
    FETCH v_cursor INTO v_result_set;
    EXIT WHEN v_cursor%NOTFOUND;
    PIPE ROW(v_result_set);
    
    END LOOP;
    
    
    END;
  • 2. Re: PLS-00382: expression is of wrong type
    996818 Newbie
    Currently Being Moderated
    sorry , this is also gives me compilation error.
  • 3. Re: PLS-00382: expression is of wrong type
    jeneesh Guru
    Currently Being Moderated
    I dont know what you are trying to do ..Probably, this will help..
    SQL> create or replace FUNCTION get_prof_ctab_value
      2  RETURN prof_ctab_value_table_t PIPELINED
      3  IS
      4    v_sql          VARCHAR2(4000);
      5    v_cursor       SYS_REFCURSOR;
      6    v_result_set   prof_ctab_value_rec_t :=
      7                prof_ctab_value_rec_t(null,null,null);
      8  BEGIN
      9     v_sql := 'select 1,2,3 from dual union all select 4,5,6 from dual';
     10    open  v_cursor for v_sql;
     11      LOOP
     12      FETCH v_cursor INTO
     13     v_result_set.prof_id,
     14     v_result_set.ctabv_id,
     15     v_result_set.ctab_id;
     16      EXIT WHEN v_cursor%NOTFOUND;
     17      PIPE ROW(v_result_set);
     18      END LOOP;
     19   return;
     20  END;
     21  /
    
    Function created.
    
    SQL> select *
      2  from table(get_prof_ctab_value);
    
       PROF_ID   CTABV_ID    CTAB_ID
    ---------- ---------- ----------
             1          2          3
             4          5          6
  • 4. Re: PLS-00382: expression is of wrong type
    996818 Newbie
    Currently Being Moderated
    hi ,


    the variable v_sql is an dynamic select statement not fixed

    like

    v_sql := 'select ' ||v_column|| ' from dddd';

    so i use execute immediate to run v_sql;
  • 5. Re: PLS-00382: expression is of wrong type
    Ramin Hashimzadeh Expert
    Currently Being Moderated
    Raj,Infant wrote:
    hi ,


    the variable v_sql is an dynamic select statement not fixed

    like

    v_sql := 'select ' ||v_column|| ' from dddd';

    so i use execute immediate to run v_sql;
    i gave you same solution but you dont understand me )))
  • 6. Re: PLS-00382: expression is of wrong type
    jeneesh Guru
    Currently Being Moderated
    Ramin Hashimzadeh wrote:
    Raj,Infant wrote:
    hi ,


    the variable v_sql is an dynamic select statement not fixed

    like

    v_sql := 'select ' ||v_column|| ' from dddd';

    so i use execute immediate to run v_sql;
    i gave you same solution but you dont understand me )))
    Same ...?
    SQL> ed
    Wrote file afiedt.buf
    
      1  declare
      2    v_sql          VARCHAR2(4000);
      3    v_cursor       SYS_REFCURSOR;
      4    v_result_set   prof_ctab_value_table_t;
      5  BEGIN
      6     open v_cursor for select 1,2,3 from dual;
      7    --EXECUTE IMMEDIATE v_sql USING
      8    --OUT v_cursor;
      9      LOOP
     10      FETCH v_cursor INTO v_result_set;
     11      EXIT WHEN v_cursor%NOTFOUND;
     12      --PIPE ROW(v_result_set);
     13      END LOOP;
     14* END;
     15  /
    declare
    *
    ERROR at line 1:
    ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
    ORA-06512: at line 10
    
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  declare
      2    v_sql          VARCHAR2(4000);
      3    v_cursor       SYS_REFCURSOR;
      4    v_result_set   prof_ctab_value_table_t;
      5  BEGIN
      6     open v_cursor for 'select 1,2,3 from dual';
      7    --EXECUTE IMMEDIATE v_sql USING
      8    --OUT v_cursor;
      9      LOOP
     10      FETCH v_cursor INTO v_result_set;
     11      EXIT WHEN v_cursor%NOTFOUND;
     12      --PIPE ROW(v_result_set);
     13      END LOOP;
     14* END;
    SQL> /
    declare
    *
    ERROR at line 1:
    ORA-00932: inconsistent datatypes: expected - got -
    ORA-06512: at line 10

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points