Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Unanswered: PLS-00382: expression is of wrong type

    I want to use this type field to create a collection to return from my function. The problem is that no space is allocated for storage. I am trying to use " xml_data (xml_data.last) := xml_table();" to do that but I get the 382 error. Without some type of space allocation I get "ORA-06530: Reference to uninitialized composite". Here is my code parts

    Any ideas or different approaches are welcome.

    Thanks.

    Code:
    CREATE OR REPLACE
    TYPE question_detail_xml_type IS OBJECT (
       interaction_id              VARCHAR2 (20),
       interaction_type            VARCHAR2 (20),
       interaction_user_selection  VARCHAR2 (20),
       interaction_result          VARCHAR2 (100)
    );
    
    /
    Code:
    CREATE OR REPLACE FUNCTION report_xml_teb (p_xml_string VARCHAR2)
       RETURN question_detail_xml_table PIPELINED
    
    AS
    
       TYPE xml_table IS TABLE OF question_detail_xml_type;
    
       xml_data       xml_table := xml_table();
    
       i              INTEGER;
       j              NUMBER;
       string_id VARchar2(20);
       string_type VARchar2(20);
    
    BEGIN
    
       FOR i IN 1 .. 3
    
       LOOP
    
            xml_data.EXTEND;
      
            -- xml_data := xml_table(string_id, string_type, NULL, NULL);
            xml_data (i).interaction_id := 'TYPE' || TO_CHAR (i);
    
            PIPE ROW (xml_data (i));
    
       END LOOP;
    
       RETURN;
    
    END report_xml_teb;
    
    /
    Code:
      select interaction_id,
             interaction_type
    		 from TABLE (report_xml_teb('test'))
    NOTE: Please disregard the label "Senior Member".

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    You extend the collection but you don't give it a new "question_detail_xml_type" object, so when you assign

    xml_data(i).interaction_id := 'TYPE' || TO_CHAR (i);

    xml_data(i) does not contain an object.

    Try this:

    Code:
    CREATE OR REPLACE FUNCTION report_xml_teb (p_xml_string VARCHAR2)
       RETURN question_detail_xml_table PIPELINED
    
    AS
       -- TYPE xml_table IS TABLE OF question_detail_xml_type;
       -- xml_data       xml_table := xml_table();
       xml_data     question_detail_xml_table := NEW question_detail_xml_table();
    
       i            PLS_INTEGER;
       j            NUMBER;
       string_id    VARCHAR2(20);
       string_type  VARCHAR2(20);
    
    BEGIN
    
       FOR i IN 1 .. 3
       LOOP
            xml_data.EXTEND;
    
            -- xml_data := xml_table(string_id, string_type, NULL, NULL);
            xml_data(i) := NEW question_detail_xml_type(NULL,NULL,NULL,NULL);
            xml_data(i).interaction_id := 'TYPE' || TO_CHAR (i);
    
            PIPE ROW (xml_data (i));
       END LOOP;
    
       RETURN;
    
    END report_xml_teb;
    /

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •