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

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

Select allOpen in new window

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

Select allOpen in new window