[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

How to capture error within Oracle procedure - <faultstring>Internal Error</faultstring>

Hello,
I am getting an "Internal Error" when calling a web service within a procedure.  The majority of the time, when getting this error, it is due to no rows being returned from the service.
So, what would be the best way to capture this error?
1.  Should I parse out the xml soap message and look for the element <faultstring>?
2.  Or is there an exception message that I can use to capture these errors.

What I want to do, is that when there are no rows being returned, is to raise an exception like
"no_data_found" if that is possible.

Here is the soap response message that comes back, when no rows are being retrieved via the
web service call.  
Thanks.

<?xml version = '1.0' encoding = 'UTF-8'?>
<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"">
   <soapenv:Body>
      <soapenv:Fault xmlns:axis2ns1=""http://schemas.xmlsoap.org/soap/envelope/"">
         <faultcode>axis2ns1:Server</faultcode>
         <faultstring>Internal Error</faultstring>
         <detail></detail>
      </soapenv:Fault>
   </soapenv:Body>
</soapenv:Envelope>
0
Machinegunner
Asked:
Machinegunner
  • 5
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
Parse the results and manually raise the exception whenever you want to.

...
raise no_data_found;
...

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm#LNPLS99863
0
 
MachinegunnerAuthor Commented:
Thanks for the reply,
I forgot to mention, the result(s) are returned within a XMLType datatype, so either way, there would be some kind of data within the xmltype column that is used to store this data, within a table.
So it looks like no_data_found wouldn't work on this scenario.
Maybe I can query the result set and look for the <faultstring> element?
Thanks
0
 
slightwv (䄆 Netminder) Commented:
>>Maybe I can query the result set and look for the <faultstring> element?

That is what I thought you were after.  Yes, you would need to parse the XML looking for 'errors' then raise whatever pl/sql error you wanted based on the text in the XML.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Wasim Akram ShaikCommented:
Just to add, if you are after how to parse an xml document type, there is one of similar questions asked on EE refer to it

http://www.experts-exchange.com/Database/Oracle/Q_24627617.html
0
 
slightwv (䄆 Netminder) Commented:
EXTRACT and EXTRACTVALUE have been deprecated.  You should use the new XQUERY and XMLTABLE syntax.

Since the asker was nice enough to provide you the XML, why not post working code?


Here's an example with XMLTABLE.

Note: I had to remove the double double quotes around the namespaces.

with mydata as (
	select '
<?xml version = ''1.0'' encoding = ''UTF-8''?>
 <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
    <soapenv:Body>
       <soapenv:Fault xmlns:axis2ns1="http://schemas.xmlsoap.org/soap/envelope/">
          <faultcode>axis2ns1:Server</faultcode>
          <faultstring>Internal Error</faultstring>
          <detail></detail>
       </soapenv:Fault>
    </soapenv:Body>
 </soapenv:Envelope> 
' xml
from dual
)
select faultcode, faultstring
 from mydata, 
       xmltable(
              xmlnamespaces(
 					'http://schemas.xmlsoap.org/soap/envelope/' as "soapenv"
             ),
		'/soapenv:Envelope'
       passing xmltype(regexp_replace(mydata.xml,'<\?xml .*\?>'))
       columns
             faultcode varchar2(20) path '/soapenv:Envelope/soapenv:Body/soapenv:Fault/faultcode',
             faultstring varchar2(20) path '/soapenv:Envelope/soapenv:Body/soapenv:Fault/faultstring'
       ) xmltab
/ 

Open in new window

0
 
MachinegunnerAuthor Commented:
Thanks for the replies, I took 'slightwv' code example and tried it out and works great for what I am trying to do and tweeked it a little bit, as I have a xml table that is used for the data.

Here is the code that compiled and seems to work, but if there is any better logic or approach that what you see here, let me know.  (posting code for others to use, as an example as well)
Thanks again for the help.

CREATE OR REPLACE PROCEDURE SelectAsBuiltXML
      (partnbr       in varchar2,
       pgmcode       IN VARCHAR2,
       serialnbr     IN VARCHAR2,
       ReuseNbr      IN VARCHAR2,
       RefurbNbr     IN VARCHAR2,
       DAR_CURSOR    OUT SYS_REFCURSOR)

AS

    record_count        integer;
    faultcode           varchar2(50);
    faultstring         varchar2(50);
    no_data_from_ws     exception;
    
BEGIN

-- Execute the procedure that calls the web service and populates the carxml table with the result set...    
   CALL_WSCAR(partnbr, pgmcode, serialnbr, ReuseNbr, RefurbNbr);

  select distinct faultcode, faultstring
  into faultcode, faultstring
  from carxml, 
       xmltable(
              xmlnamespaces(
 					'http://schemas.xmlsoap.org/soap/envelope/' as "soapenv"
             ),
		'/soapenv:Envelope'
      passing xmlcolumn
       columns
             faultcode varchar2(20) path '/soapenv:Envelope/soapenv:Body/soapenv:Fault/faultcode',
             faultstring varchar2(20) path '/soapenv:Envelope/soapenv:Body/soapenv:Fault/faultstring'
       ) xmltab; 

begin
  case faultstring  --No data retrieved...
   when 'Internal Error' then
	   raise no_data_from_ws;
   when NULL then  --There is data to be retrieved...
	   OPEN DAR_CURSOR FOR    
	   SELECT leveler "Level", 
			  description "Description",
			  drnbr "DrNbr",
			  partnbr "PartNbr", 
			  programcd "ProgramCd",
			  messages "Messages",
			  refurbnbr "RefurbNbr",
			  reusenbr "ReuseNbr", 
			  rev "Rev", 
			  reviewstatus "ReviewStatus", 
			  sequences "Sequence", 
			  serialnbr "SerialNbr", 
			  stocknbr "StockNbr", 
			  ChangeDate(usedt) "UseDate"
	   FROM carxml,
		xmltable(
		   xmlnamespaces(
			'http://schemas.xmlsoap.org/soap/envelope/' as "soapenv",
				'http://svc.mynamespace.com/' as "ns2"
		  ),
		'/soapenv:Envelope/soapenv:Body/ns2:listAsBuiltResponse/return'
		passing (xmlcolumn)
		columns
		  leveler       varchar2(2)   path 'level',  
		  description   varchar2(250) path 'description',
		  drnbr         varchar2(50)  path 'drNbr',    
		  partnbr       varchar2(50)  path 'partNbr',
		  programcd     varchar2(50)  path 'programCd',
		  messages      varchar2(4000)  path 'string-join(messages,"<\br> ")',      
		  refurbnbr     varchar2(50)  path 'refurbNbr',
		  reusenbr      varchar2(50)  path 'reuseNbr',
		  rev           varchar2(50)  path 'rev',    
		  reviewstatus  varchar2(50)  PATH 'reviewStatus',    
		  sequences     varchar2(10)  path 'sequence',
		  serialnbr     varchar2(50)  path 'serialNbr',
		  stocknbr      varchar2(50)  path 'stockNbr',
		  usedt         varchar2(50)  path 'useDt'    
		) xmltab
		WHERE programcd = pgmcode
		ORDER BY to_number(sequences);
    commit;
  end case;
exception
  when no_data_from_ws then
     open dar_cursor for  
     select 'no records found' from dual;
  when others then
     raise;
end;  
--  Get the count of records within the XML table..
    SELECT COUNT(*) INTO record_count FROM carxml;
    
--  If there are records within the XML table, truncate table...
    IF record_count > 0 THEN
        execute immediate 'TRUNCATE TABLE carxml';
    END IF;

END;
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
I'm not sure about the truncate and the open cursor.

If there are rows, the truncate will kill the cursor.

See this test case:
drop table tab1 purge;
create table tab1(col1 char(1));

insert into tab1 values('a');
commit;

create or replace procedure myproc(outcur out sys_refcursor)
is
begin
open outcur for select * from tab1;
execute immediate 'truncate table tab1';
end;
/

--test with sqlplus
var mycur refcursor
exec myproc(:mycur);

print mycur

Open in new window

0
 
MachinegunnerAuthor Commented:
Yes, thanks for the reply.  
What I am trying to do is, if there are any rows within the table, I want to remove them
before being populated again, when the procedure is ran.
0
 
slightwv (䄆 Netminder) Commented:
Can more than one person run the procedure at the same time?

Can you change CALL_WSCAR?

Look into global temporary tables.  You create them once and use them over and over again.

Only the session that inserts rows into them can see the data and the data is magically removed once the session ends.

http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11633

If only one person can run the procedure at a time:
delete/truncate the rows at the TOP of the procedure as the first step.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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