Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Pl/SQL procedure runs successfully but no output generated

Posted on 2009-06-30
12
Medium Priority
?
1,146 Views
Last Modified: 2013-12-19
Hi All,
I really need help. My problem definition:
1. I have a database table "testcase" in oracle 10g which has a column "testdata" of type clob. "testdata" column has xml data in it. I want to fetch the value of taga <Match> under <Expected_After> tag, concatinate the values if more than one <Match> tag exists and later
insert this value into a column "EXPECTAFTER"  in the same DB table i.e "testcase".

2. I wrote a procedure to do the xml parsing and insertion of data into table which I is under procedure1 in the code snippet below.

3. I wrote a procedure to call the above procedure with "testdata" column value of each row.

4. The first procedure is called xml_proc and the second procedure is called tcupdate.

whne I run "Exec tcupdate", the procudure runs fine and say " PL/SQL Procedure ran successfully", but I don't get any result.

Any help is appreciated.
Procedure1:
 
CREATE OR REPLACE PROCEDURE HITOOLS2.xml_proc (p_xml IN CLOB,tid IN NUMBER) AS
      l_parser    dbms_xmlparser.Parser;
      l_doc       dbms_xmldom.DOMDocument;
      l_nl        dbms_xmldom.DOMNodeList;
      l_n         dbms_xmldom.DOMNode;
      
     
      
      
      l_expa VARCHAR2(100) :='';
      f_expa VARCHAR2(500) :='';
      
      
    BEGIN
 
      -- Create a parser.
      l_parser := dbms_xmlparser.newParser;
 
      -- Parse the document and create a new DOM document.
      dbms_xmlparser.parseClob(l_parser, p_xml);
      l_doc := dbms_xmlparser.getDocument(l_parser);
 
      -- Get a list of all the TABLE nodes in the document using the XPATH syntax.
      l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),'/Test/Expected_After/Match');
 
      -- Loop through the list and display the contents
      FOR cur_tab IN 0 .. dbms_xmldom.getLength(l_nl) - 1 LOOP
        l_n := dbms_xmldom.item(l_nl, cur_tab);
        
        --prc status and prc mode
 
        dbms_xslprocessor.valueOf(l_n,'text()',l_expa);
        
        f_expa := f_expa || ',' || l_expa;
        
      END LOOP;
      
      UPDATE TESTCASE SET EXPECTAFTER = f_expa where testcase_id = tid;
 
      -- Free resources.
      dbms_xmldom.freeDocument(l_doc);
      dbms_xmlparser.freeParser(l_parser);
    EXCEPTION
      WHEN OTHERS THEN
        dbms_xmldom.freeDocument(l_doc);
        dbms_xmlparser.freeParser(l_parser);
        RAISE;
    END;
/
 
-----------------------------------------------------------------------------------------------------------------
Procedure 2:
 
CREATE OR REPLACE PROCEDURE HITOOLS2.tcupdate AS
 
   cursor c is
    select testdata,testcase_id FROM TESTCASE;
 
BEGIN
   FOR r IN c loop
      xml_proc(r.testdata,r.testcase_id);
   END LOOP;
exception when others then
   dbms_output.put_line('error: '||sqlerrm);
END;
/

Open in new window

0
Comment
Question by:jgudavalli
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 14

Expert Comment

by:shru_0409
ID: 24755579
0
 
LVL 20

Expert Comment

by:flow01
ID: 24756436
If running sqlplus to execute the procedure you wil not get any output from dbms_output if you dont
enable dbms_output

You can add extra dbms_output to debug your procedure

CREATE OR REPLACE PROCEDURE HITOOLS2.tcupdate AS
 
   cursor c is
    select testdata,testcase_id FROM TESTCASE;
 
BEGIN
   dbms_output.put_line('1');
  FOR r IN c loop
   dbms_output.put_line('2:' || r_testcase_id);
     xml_proc(r.testdata,r.testcase_id);
   END LOOP;
exception when others then
   dbms_output.put_line('error: '||sqlerrm);
END;
/


set serveroutput on
exec tcupdate

0
 

Author Comment

by:jgudavalli
ID: 24757254
Hi flow01,
I did the exact same thing as you said. It didn't gice any output. It just ran for five seconds or less and said "PL/SQL procedure successfully completed".

What could be wrong. Is the procedure running at all?

Thanks,
Jyothi
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 20

Expert Comment

by:flow01
ID: 24757442
You set serveroutput on and the  '1'  of the first  dbms_output.put_line('1'); didn't show ?

are you running it as user  HITOOLS2 ?
0
 

Author Comment

by:jgudavalli
ID: 24757678
Hi Flow01,
It did give me some output. but after readin 8 rows as below, it says user buffer too small.
Please note on test machine I have 57000 rows and on prodcution we have 100,000 rows. how can we overcome this buffer overflow?

1
2:345
2:346
2:201
2:202
2:204
2:1203212886305
2:1222169331443
error: ORA-24331: user buffer too small
PL/SQL procedure successfully completed.
0
 
LVL 20

Expert Comment

by:flow01
ID: 24757903
I did answer another problem where only 400 rows existed

A)
the dbms_output size is limited but can get raised to 100000

set serveroutput on size 100000

B)
there is a maximum string for dbms_output = 255
dbms_output.put_line(substr('2:' || r_testcase_id,1,255);

C) time to get the error only

CREATE OR REPLACE PROCEDURE HITOOLS2.tcupdate AS
 
   cursor c is
    select testdata,testcase_id FROM TESTCASE;
 
   v_id testcase.testcase_id%TYPE;
BEGIN
   dbms_output.put_line('1');
  FOR r IN c loop
     v_id := r.testcase_id;
     xml_proc(r.testdata,r.testcase_id);
   END LOOP;
exception when others then
   dbms_output.put_line('error id: '||v_id);
   dbms_output.put_line('error: '||sqlerrm);
END;
/
0
 
LVL 20

Expert Comment

by:flow01
ID: 24757952
sorry , probably the error-message is to large

CREATE OR REPLACE PROCEDURE HITOOLS2.tcupdate AS
 
   cursor c is
    select testdata,testcase_id FROM TESTCASE;
 
   v_id testcase.testcase_id%TYPE;
BEGIN
   dbms_output.put_line('1');
  FOR r IN c loop
     v_id := r.testcase_id;
     xml_proc(r.testdata,r.testcase_id);
   END LOOP;
exception when others then
   dbms_output.put_line('error id: '||v_id);
   dbms_output.put_line(substr('error: '||sqlerrm,1,255);
   dbms_output.put_line('error part 2');
  dbms_output.put_line(substr('error: '||sqlerrm,256,255);
 END;
/
0
 

Author Comment

by:jgudavalli
ID: 24758761
Hi Flow01,
It actually passed several step now. I see some rows getting updated.
However, there was another error now:

1
error id: 33302
error: ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00241: entity reference is not well formed
Error at line 6
PL/SQL procedure successfully completed.

further looking into the issue , I figured out that the tag <test> was not the only tag, it has <TestSet> as the parent tag.

I know I can use a if else loop for this. how can I tell the procudure to continue even if there is error at one row and collect all the testcaseid's (i.e the info of the rows ) for which it failed.

thanks much for your help and guidance.

Regards,
Jyothi
0
 
LVL 20

Accepted Solution

by:
flow01 earned 2000 total points
ID: 24762468
define the exception handling at the inner loop

CREATE OR REPLACE PROCEDURE HITOOLS2.tcupdate AS
 
   cursor c is
    select testdata,testcase_id FROM TESTCASE;
 
BEGIN
   dbms_output.put_line('1');
  FOR r IN c loop
     BEGIN
     xml_proc(r.testdata,r.testcase_id);
    exception when others then
        dbms_output.put_line('error *********************');
        dbms_output.put_line('error id: '||r.testcase_id);
        dbms_output.put_line(substr('error: '||sqlerrm,1,255);
        dbms_output.put_line('error part 2');
         dbms_output.put_line(substr('error: '||sqlerrm,256,255);
       -- continue if the was an error
       -- raise;  -- no raise  continue if there was an error  
     END;
   END LOOP;
exception when others then
   dbms_output.put_line('outer error');
   dbms_output.put_line(substr('error: '||sqlerrm,1,255);
   dbms_output.put_line('error part 2');
  dbms_output.put_line(substr('error: '||sqlerrm,256,255);
 END;
/
0
 

Author Comment

by:jgudavalli
ID: 24777000
Hi Flow01,
Thanks for the reply. However, I still cannot get the error message. But the PL?SQL ran successfully on all the rows except on 2000 rows out of which 934 rows need to updated. These 934 rows have <TestSet> as their root tag instead of <Test>. But the procedure didn't show these testcase id's on the console or throw any error.

I think I should accept your solution as I am very close to achieve the results. If you know how to get the
error testcase ids, let me know. Otherwise, I will accept your solution.

Regards,
Jyothi
0
 
LVL 20

Expert Comment

by:flow01
ID: 24779530
Strange,
you did get a display of '1' indicating "set serveroutput on"  is activated ?
0
 

Author Closing Comment

by:jgudavalli
ID: 31598571
Excellent guidance. Thank you very much.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

721 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