[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1087
  • Last Modified:

Urgent:How to run the PL/SQL procedure

hi
my pl/sql def ination is
 Procedure ParseXML(P_ItemNo In Varchar2, P_Operation In Varchar2, P_Status In Varchar2, Data_Cursor OUT sys_refcursor) IS  

i want to run it thru sql 1st to see its output .how can i run this ?
0
samir25
Asked:
samir25
  • 9
  • 6
  • 3
2 Solutions
 
sujith80Commented:
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;
/
0
 
samir25Author Commented:
i didnt understand..is this what i need to write ont he sql prompt?
so many thign to run it?
0
 
samir25Author Commented:
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
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.

 
sujith80Commented:
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;
/

0
 
prasanthi_kCommented:
At SQL prompt first declare a variable for refcursor
SQL> var xcursor refcursor

Then execue the proc as shown below. Pass the values for input parameters and the variable declared above for refcursor. Don't miss the ':' before the variable.

SQL>EXEC ParseXML('value of item_number', 'value of P_Operation', 'value of P_Status', :xcursor);  

Now to see the ref cursor result give the below command.
SQL> PRINT xcursor
0
 
samir25Author Commented:
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
0
 
samir25Author Commented:
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...???
0
 
prasanthi_kCommented:
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.
0
 
sujith80Commented:
If you want to see the values of the fetched data you should use dbms_output.put_line. This piece of code is NOT A PROCEDURE. It is just an anonymous block which shows how to manipulate your procedure and the arguments in a calling pl/sql program.

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;
  dbms_output.put_line(itemno_r);
  dbms_output.put_line(operationr);
  dbms_output.put_line(status);
  dbms_output.put_line('----');
  exit when l_cur%NOTFOUND;
end loop;
close l_cur;
end;
/
0
 
samir25Author Commented:
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
0
 
prasanthi_kCommented:
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.
0
 
samir25Author Commented:
no error returned. if you notice i am using record type
could that be an issue
0
 
prasanthi_kCommented:
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.


0
 
samir25Author Commented:
ok now its giving one error(after removing close)
SQL>  print xcursor
SP2-0625: Error printing variable "xcursor"
SQL> show errors
No errors.
0
 
samir25Author Commented:
i am doing this
FETCH Data_Cursor INTO rc;

so wouldnt my cursor be empty?
0
 
prasanthi_kCommented:
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;
0
 
samir25Author Commented:
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
0
 
prasanthi_kCommented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now