6 Replies Latest reply: Apr 1, 2013 5:57 AM by jeneesh RSS

    PLS-00382: expression is of wrong type

    996818
      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
          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
            sorry , this is also gives me compilation error.
            • 3. Re: PLS-00382: expression is of wrong type
              jeneesh
              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
                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
                  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
                    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