java.sql.SQLException: ORA-01403: no data found

In a PL/SQL function, I do:

SELECT ISRETRIABLE INTO isRetriable
from ACME_ERRORCODES
where ERRORCODE=theERRORCODE;

If theERRORCODE is not found, I get a "ORA-01403: no data found".
In reality, in this case I would like to return 0.
This can be achieved by adding the clause:

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      return 0;
  WHEN OTHERS THEN
    RAISE;

SchemaName in oracle.tip.adapter.db.DBStoredProcedureInteractionSpec (JCA DbAdapter)

In JDeveloper, the wizard automatically generates an element "property name="SchemaName" value="DEV1_SOAINFRA"" in the endpoint-interaction/interaction-spec className="oracle.tip.adapter.db.DBStoredProcedureInteractionSpec" element.
This is annoying since the schema is already associated to the JCA instance we are using (connection-factory location="eis/DB/Nesoa" UIConnectionName="DEV1_SOAINFRA" adapterRef="")
You can safely remove the SchemaName element.
It is also safe to rename UIConnectionName to anything you like.

Oracle DB PL/SQL package example

I have always considered packages as a nuisance, as an extra, useless thing. In fact, their syntax is highly redundant (why do I have to declare twice the signature of a procedure???).
But they help keep your PL/SQL organized.
Here is an example:

CREATE TABLE ACME_ALERTS (
ERRORCODE VARCHAR2(100) NOT NULL,
INTERFACEID VARCHAR2(100) NOT NULL,
CREATIONDATE DATE NOT NULL,
ISACTIVE NUMBER
);

CREATE INDEX "ACME_ALERTS_INDEX1" ON "ACME_ALERTS" ("ERRORCODE", "INTERFACEID") ;

create or replace package PKG_ACME_ALERTS
IS
 FUNCTION ACME_findAlertInstance (theERRORCODE IN VARCHAR2, theINTERFACEID IN VARCHAR2 )  return NUMBER;
 PROCEDURE ACME_insertAlertInstance (theERRORCODE IN VARCHAR2, theINTERFACEID IN VARCHAR2 );
 PROCEDURE ACME_resetAlertInstance (theERRORCODE IN VARCHAR2, theINTERFACEID IN VARCHAR2 );
END PKG_ACME_ALERTS;
/


 
CREATE OR REPLACE PACKAGE BODY PKG_ACME_ALERTS is

FUNCTION ACME_findAlertInstance 
  (theERRORCODE IN VARCHAR2, theINTERFACEID IN VARCHAR2 )  return NUMBER
is 
numberOfAlerts number;
begin
 SELECT COUNT(*) INTO numberOfAlerts
 from ACME_ALERTS
 where ERRORCODE=theERRORCODE
 and INTERFACEID=theINTERFACEID
 and ISACTIVE=1
 and CREATIONDATE < (SYSDATE - 4/24);
 
   return  numberOfAlerts;
end ACME_findAlertInstance;



PROCEDURE ACME_insertAlertInstance 
  (theERRORCODE IN VARCHAR2, theINTERFACEID IN VARCHAR2 )
as
begin
INSERT INTO ACME_ALERTS (
ERRORCODE, INTERFACEID, CREATIONDATE, ISACTIVE
) VALUES (
theERRORCODE, theINTERFACEID, SYSDATE, 1
);
end ACME_insertAlertInstance;


PROCEDURE ACME_resetAlertInstance 
  (theERRORCODE IN VARCHAR2, theINTERFACEID IN VARCHAR2 )
as
begin
UPDATE ACME_ALERTS set ISACTIVE = 0 where ERRORCODE=theERRORCODE and INTERFACEID=theINTERFACEID;
end ACME_resetAlertInstance;


end PKG_ACME_ALERTS;
/