We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

"Expression is of wrong type" error for PIPE ROW

BSDev
BSDev asked
on
Medium Priority
2,298 Views
Last Modified: 2012-05-06
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

Comment
Watch Question

Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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;
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
yes, exactly
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
and remember to end your execution block with RETURN;

Author

Commented:
Exactly what I was looking for.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
glad I could help
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.