• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1756
  • Last Modified:

"Expression is of wrong type" error for PIPE ROW

I'm encountering a compile error when trying to pipe a row from my function.

Oracle says that I am using an expression of the wrong type, but I am following my types closely, and can't find any difference in the way I am using this functionality from the way it is used in examples I've seen.

I am using Oracle 10G.

Below is an example.


-- Schema Types
 
create or replace TYPE forms_aggregate_row_type AS OBJECT
(
  form_id VARCHAR2(100),
  form_name VARCHAR2(500),
  link_id VARCHAR2(100),
  link_link VARCHAR2(500),
  link_text VARCHAR2(500),
  favorite NUMERIC
);
 
create or replace TYPE forms_aggregate_table_type AS TABLE OF forms_aggregate_row_type;
 
-- Package types
 
TYPE aggregate_cursor_type IS REF CURSOR;
 
TYPE form_details_record_type
      IS RECORD (
        form_id form_form_details.form_id%TYPE,
        form_name form_form_details.form_name%TYPE,
        row_number__ NUMERIC
      );
 
-- Function
 
FUNCTION FORM_AGGREGATE_CATEGORY_INNER
    (
      in_page_size IN NUMERIC,
      in_page IN NUMERIC,
      in_category_id IN ccv_scopenotes.id%TYPE,
      in_user_id IN VARCHAR2  
    ) RETURN forms_aggregate_table_type PIPELINED
    IS
      form_details_record form_details_record_type;
      inner_form_details_record forms_aggregate_row_type;
      
      CURSOR fetch_form_details
      IS
        SELECT *
        FROM
        (
          SELECT
            FD.FORM_ID,
            FD.FORM_NAME,
            ROW_NUMBER() OVER (ORDER BY form_name) AS row_number__
          FROM form_form_details FD
          WHERE
            form_id IN
            (
              SELECT distinct form_id
                FROM
                  form_form_categories_relation
                WHERE
                  category_id in
                  (
                      select "ID" from ccv_parents
                      start with "ID" = in_category_id
                      connect by prior "ID" = "PARENT"
                  )
            )
          ORDER BY FD.form_name
        )
        WHERE
          row_number__ >  in_page_size * (in_page - 1)
          and
          row_number__ <= in_page_size * in_page;
    
      CURSOR fetch_form_details_record(form_details_record form_details_record_type)
      IS
        SELECT
          form_details_record.form_id,
          form_details_record.form_name,
          NULL AS link_id,
          NULL AS link_link,
          NULL AS link_text,
          NULL AS favorite
        FROM DUAL;
    
    
    BEGIN
      
      FOR form_details_record IN fetch_form_details
        LOOP
        
          FOR inner_form_details_record IN fetch_form_details_record(form_details_record)
            LOOP PIPE ROW(inner_form_details_record);
            END LOOP;
            
        END LOOP;
      CLOSE fetch_form_details;
      
    END FORM_AGGREGATE_CATEGORY_INNER;

Open in new window

0
BSDev
Asked:
BSDev
  • 4
  • 2
1 Solution
 
sdstuberCommented:
FOR loop indexes are NOT variables.

so, where you have

  "FOR inner_form_details_record  IN ...."

this  inner_form_details_record   is NOT the same entity as your variable

"inner_form_details_record forms_aggregate_row_type;"

it is it's own type a cursor return type.
0
 
BSDevAuthor Commented:
Fantastic. This seems to have solved the compile errors.

So I should be doing something like this right?

FOR crv IN fetch_form_details_record(form_details_record)
            LOOP PIPE ROW(forms_aggregate_row_type(
              crv.form_id,
              crv.form_name,
              null, null, null, null ));
            END LOOP;
            CLOSE fetch_form_details_record;
0
 
sdstuberCommented:
yes, exactly
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
sdstuberCommented:
and remember to end your execution block with RETURN;
0
 
BSDevAuthor Commented:
Exactly what I was looking for.
0
 
sdstuberCommented:
glad I could help
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now