?
Solved

Urgent:Pipeline functions and Cursors

Posted on 2007-07-26
7
Medium Priority
?
393 Views
Last Modified: 2013-12-18
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

0
Comment
Question by:samir25
  • 5
  • 2
7 Comments
 
LVL 14

Expert Comment

by:sathyagiri
ID: 19580237
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
 
LVL 14

Accepted Solution

by:
sathyagiri earned 2000 total points
ID: 19580244
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
 
LVL 1

Author Comment

by:samir25
ID: 19580383
ok let me try
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Author Comment

by:samir25
ID: 19580408
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
 
LVL 1

Author Comment

by:samir25
ID: 19580425
oh i didnt try the i. let me try again sorry for such haste
0
 
LVL 1

Author Comment

by:samir25
ID: 19580439
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
 
LVL 1

Author Comment

by:samir25
ID: 19580481
ok i got it working
THANKSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS A TONS!!!!!!!!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question