troubleshooting Question

Urgent:Pipeline functions and Cursors

Avatar of samir25
samir25 asked on
Oracle Database
7 Comments1 Solution428 ViewsLast Modified:
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

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros