Urgent:How to run the PL/SQL procedure

declare
l_cur sys_refcursor;
-- declare variables to fetch the cursor
-- <var1>
-- <var2>
-- <var3>
.
.
begin
 ParseXML('<item number value>', '<operation value>', '<status>', l_cur);
loop
 fetch l_cur into <var1>, <var2>, ....
 exit when l_cur%NOTFOUND;
end loop;
close l_cur;
end;
/

i didnt understand..is this what i need to write ont he sql prompt?
so many thign to run it?

here is what i did
declare
itemno_r varchar2;
operationr varchar2;
status varchar2;
l_cur sys_refcursor;
begin
ParseXML('3242','Test','Occur');
loop
      fetch L_cur into itemno_r, operationr,status
      exit when l_cur%NOTFOUND
end loop;
close l_cur;
end
;
/

which give me below error
ERROR at line 10:
ORA-06550: line 10, column 2:
PLS-00103: Encountered the symbol "EXIT" when expecting one of the following:
. ( , % ; limit

to get the pl/sql fundas read this guide.
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm

In your code
-- you are missing the width for varchar2 variables.
-- in the call to the procedure l_cur is not included
-- semicolons are missing at the end of some statements

See the corrected code. Its assumed that the cursor is returning 3 fields.

declare
itemno_r varchar2(20);
operationr varchar2(20);
status varchar2(20);
l_cur sys_refcursor;
begin
ParseXML('3242','Test','Occur', l_cur);
loop
  fetch L_cur into itemno_r, operationr,status;
  exit when l_cur%NOTFOUND;
end loop;
close l_cur;
end;
/

ok it completely succesfully. i did somemore modifications.
if i want to print the values the cursor returns.how can i do that?
bms_output.put_line(cursor); ????

i will really go through the link you gave me, trust me i need to finish this soon. after that i will do so

hi
prasanthi_k: just saw ur comment...

i did what you told. but i coudlnt understand how is exec different from what Sujith wrote in the pl/sql...???

It need not be different. But this is how I execute procedures or packages in SQL *Plus.
You need not write another procedure, instead use the commands, VAR, EXEC, PRINT etc. You can also see the output records of REF CURSOR here.

hi can anyone of u help me in this.
i have chaged my pl a little bit==>
CREATE OR REPLACE PACKAGE BODY "DOTNET" AS
 Procedure ParseXML(P_ItemNo In Varchar2, P_Operation In Varchar2, P_Status In Varchar2, Data_Cursor OUT sys_refcursor) IS    
 type rec_return is record
  (
   test_id number,
   serial varchar2(20),
   operation varchar2(50),
   station varchar2(35),
   start_time date,
   xml_data clob
  );
  rc rec_return;

 tmp_clob_data varchar2(500);
 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
    OPEN Data_Cursor FOR
      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
      ORDER BY start_time;
    LOOP
       FETCH Data_Cursor INTO rc;
        start_position := dbms_lob.instr(rc.xml_data,'Exception',1,1);
        tmp_clob_data := dbms_lob.substr(rc.xml_data, 400,start_position);
        end_position := dbms_lob.instr(tmp_clob_data,'</Exception>',start_position,1);
        rc.xml_data:=dbms_lob.substr(tmp_clob_data, end_position - start_position , start_position);
       EXIT WHEN Data_Cursor%notfound;
    END LOOP;
    CLOSE Data_Cursor;  
  END IF;
END;
END;

after doing exec and printing..here is the error i get
SQL> print xcursor
ERROR:
ORA-24338: statement handle not executed
SP2-0625: Error printing variable "xcursor"
CAN you suggest something

Did the EXEC command give any errors?
It should have said 'PL/SQL procedure successfully completed'
say 'SHOW errors' after executing the procedure and see if there are any errors there.

no error returned. if you notice i am using record type
could that be an issue

I read that the cause and action for this error are

ORA-24338: statement handle not executed
Cause: A fetch or describe was attempted before executing a statement handle.
Action: Execute a statement and then fetch or describe the data.

I am not very sure what would be the problem now, but try removing "CLOSE Data_Cursor; "  statement from your package code and try compiling and executing again.

ok now its giving one error(after removing close)
SQL>  print xcursor
SP2-0625: Error printing variable "xcursor"
SQL> show errors
No errors.

i am doing this
FETCH Data_Cursor INTO rc;

so wouldnt my cursor be empty?

Please try compiling and running the below package. I made a small change and post the results....

CREATE OR REPLACE PACKAGE BODY "DOTNET" AS
 Procedure ParseXML(P_ItemNo In Varchar2, P_Operation In Varchar2, P_Status In Varchar2, Data_Cursor OUT sys_refcursor) IS    
 type rec_return is record
  (
   test_id number,
   serial varchar2(20),
   operation varchar2(50),
   station varchar2(35),
   start_time date,
   xml_data clob
  );
  rc rec_return;
 return_value sys_refcursor;

 tmp_clob_data varchar2(500);
 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
    OPEN return_value FOR
      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
      ORDER BY start_time;
    Data_Cursor := return_value;
    LOOP
       FETCH return_value INTO rc;
        start_position := dbms_lob.instr(rc.xml_data,'Exception',1,1);
        tmp_clob_data := dbms_lob.substr(rc.xml_data, 400,start_position);
        end_position := dbms_lob.instr(tmp_clob_data,'</Exception>',start_position,1);
        rc.xml_data:=dbms_lob.substr(tmp_clob_data, end_position - start_position , start_position);
       EXIT WHEN return_value%notfound;
    END LOOP;
    CLOSE return_value;  
  END IF;
END;
END;

ok. here is the error i get
SQL> print xcursor
ERROR:
ORA-24338: statement handle not executed
SP2-0625: Error printing variable "xcursor"

on commenting close cursor
i get this
SQL> print xcursor
ERROR:
ORA-01002: fetch out of sequence
no rows selected

I keep looking for the solution for this error, whenever I find I'll post here. Meanwhile I suggest you to post another question for this problem so that other experts will contribute.