Urgent:Pipeline functions and Cursors

hi
I have a this pl/sql function, which works wonderfully
CREATE OR REPLACE
FUNCTION GETVALUES(P_ItemNo In Varchar2, P_Operation In Varchar2, P_Status In Varchar2)
RETURN DS_TABLE
PIPELINED IS
out_rec DATASTRUCTURE_TYPE := DATASTRUCTURE_TYPE (NULL,NULL,NULL,NULL,NULL,NULL);
tmp_clob_data clob;
xmldata varchar2(5000);
start_position number;
end_position number;
BEGIN
  IF P_ItemNo IS NOT NULL and P_Operation IS NOT Null and P_Status IS NOT Null THEN
      SELECT test_id, serial, operation, station, start_time, xml_data
      INTO out_rec.test_id, out_rec.serial, out_rec.operation, out_rec.station, out_rec.start_time, tmp_clob_data
      FROM test.test_data_v6
      WHERE status=P_Status and Operation=P_Operation and item=P_ItemNo and test_id='34343'
      ORDER BY start_time;
     
      start_position := dbms_lob.instr(tmp_clob_data,'<Exception>',1,1) + 11;
      xmldata := dbms_lob.substr(tmp_clob_data, 2000,start_position);
      end_position := dbms_lob.instr(tmp_clob_data,'</Exception>',start_position,1);
      out_rec.xml_data:=dbms_lob.substr(tmp_clob_data, end_position - start_position , start_position);
  end if;
  PIPE ROW(out_rec);
  RETURN;
END;

right now i have written the query such that it will only return one record. I multiple rows are retunred then this function will fail. The alternative is too use the cursors. If i can use cursors and return this statement PIPE ROW (out_Rec); as many times as there are rows in the table then i can get what i want.

Can someone help  me implement cursors here?
anyone has any opinion

LVL 1
samir25Asked:
Who is Participating?
 
sathyagiriConnect With a Mentor Commented:
Should be
CREATE OR REPLACE
FUNCTION GETVALUES(P_ItemNo In Varchar2, P_Operation In Varchar2, P_Status In Varchar2)
RETURN DS_TABLE
PIPELINED IS
out_rec DATASTRUCTURE_TYPE := DATASTRUCTURE_TYPE (NULL,NULL,NULL,NULL,NULL,NULL);
tmp_clob_data clob;
xmldata varchar2(5000);
start_position number;
end_position number;
BEGIN
  IF P_ItemNo IS NOT NULL and P_Operation IS NOT Null and P_Status IS NOT Null THEN
    FOR I IN (SELECT test_id, serial, operation, station, start_time, xml_data
          FROM test.test_data_v6
      WHERE status=P_Status and Operation=P_Operation and item=P_ItemNo and test_id='34343'
      ORDER BY start_time)
   LooP  
      start_position := dbms_lob.instr(i.tmp_clob_data,'<Exception>',1,1) + 11;
      xmldata := dbms_lob.substr(i.tmp_clob_data, 2000,start_position);
      end_position := dbms_lob.instr(i.tmp_clob_data,'</Exception>',start_position,1);
      out_rec.xml_data:=dbms_lob.substr(i.tmp_clob_data, end_position - start_position , start_position);
  PIPE ROW(out_rec);
  END LOOP
  end if;
  RETURN;
END;
0
 
sathyagiriCommented:
Try thisCREATE OR REPLACE
FUNCTION GETVALUES(P_ItemNo In Varchar2, P_Operation In Varchar2, P_Status In Varchar2)
RETURN DS_TABLE
PIPELINED IS
out_rec DATASTRUCTURE_TYPE := DATASTRUCTURE_TYPE (NULL,NULL,NULL,NULL,NULL,NULL);
tmp_clob_data clob;
xmldata varchar2(5000);
start_position number;
end_position number;
BEGIN
  IF P_ItemNo IS NOT NULL and P_Operation IS NOT Null and P_Status IS NOT Null THEN
    FOR I IN (SELECT test_id, serial, operation, station, start_time, xml_data
      INTO out_rec.test_id, out_rec.serial, out_rec.operation, out_rec.station, out_rec.start_time, tmp_clob_data
      FROM test.test_data_v6
      WHERE status=P_Status and Operation=P_Operation and item=P_ItemNo and test_id='34343'
      ORDER BY start_time)
   LooP  
      start_position := dbms_lob.instr(tmp_clob_data,'<Exception>',1,1) + 11;
      xmldata := dbms_lob.substr(tmp_clob_data, 2000,start_position);
      end_position := dbms_lob.instr(tmp_clob_data,'</Exception>',start_position,1);
      out_rec.xml_data:=dbms_lob.substr(tmp_clob_data, end_position - start_position , start_position);
  PIPE ROW(out_rec);
  END LOOP
  end if;

  RETURN;
END;
0
 
samir25Author Commented:
ok let me try
0
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.

 
samir25Author Commented:
r u there?

wehn i run the above funtion then it runs for as many rows are tehre..but no data is getting retrieved.
why is this so?
any help
0
 
samir25Author Commented:
oh i didnt try the i. let me try again sorry for such haste
0
 
samir25Author Commented:
oh here is what i did and i get this error
ORA-03106: fatal two-task communication protocol error
CREATE OR REPLACE
FUNCTION GETVALUES(P_ItemNo In Varchar2, P_Operation In Varchar2, P_Status In Varchar2)
RETURN DS_TABLE
PIPELINED IS
out_rec DATASTRUCTURE_TYPE := DATASTRUCTURE_TYPE (NULL,NULL,NULL,NULL,NULL,NULL);
tmp_clob_data clob;
xmldata varchar2(5000);
start_position number;
end_position number;
j number;
BEGIN
j :=0;
  IF P_ItemNo IS NOT NULL and P_Operation IS NOT Null and P_Status IS NOT Null THEN
    FOR I IN (SELECT test_id, serial, operation, station, start_time, xml_data
            --  INTO out_rec.test_id, out_rec.serial, out_rec.operation, out_rec.station, out_rec.start_time, tmp_clob_data
              FROM test.test_data_v6
              WHERE status=P_Status and Operation=P_Operation and item=P_ItemNo
              ORDER BY start_time)              
    LOOP    
        start_position := dbms_lob.instr(I.xml_data,'<Exception>',1,1) + 11;
        xmldata := dbms_lob.substr(I.xml_data, 2000,start_position);
        end_position := dbms_lob.instr(I.xml_data,'</Exception>',start_position,1);
        out_rec.xml_data:=dbms_lob.substr(I.xml_data, end_position - start_position , start_position);
        PIPE ROW(out_rec);
    END LOOP;
  END IF;
  RETURN;
END;
0
 
samir25Author Commented:
ok i got it working
THANKSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS A TONS!!!!!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.