//java method
CallableStatement cStmt = requisitionamimpl.getOADBTransaction().createCallableStatement("begin tke_us_cus_fd_39_pk_exp_stat.GET_ORG_NAME(:1,:2,:3); end;", 1);
try
{
cStmt.setString(1,V_Proj_No);
((OracleCallableStatement)cStmt).registerOutParameter(2, OracleTypes.VARCHAR, 0, 240);
((OracleCallableStatement)cStmt).registerOutParameter(3, OracleTypes.VARCHAR, 0, 240);
cStmt.execute();
V_Org_Name = cStmt.getString(2);
V_Org_Id = cStmt.getString(3);
pageContext.writeDiagnostics(this, "V_Org_Name No From Fun-->"+V_Org_Name, OAFwkConstants.PROCEDURE);
cStmt.close();
}
catch (Exception e)
{
e.printStackTrace();
pageContext.writeDiagnostics(this, "Exception in calling GET_ORG_NAME_PROCEDURE " + e.getMessage(), OAFwkConstants.PROCEDURE);
}
proc pkg
===========================
CREATE OR REPLACE PACKAGE test as
PROCEDURE GET_ORG_NAME(V_Proj_No IN VARCHAR2, x_exp_org_name OUT VARCHAR2, x_exp_org_id OUT NUMBER);
END ;
/
CREATE OR REPLACE PACKAGE BODY test IS
BEGIN
PROCEDURE GET_ORG_NAME(V_Proj_No IN VARCHAR2, x_exp_org_name OUT VARCHAR2, x_exp_org_id OUT NUMBER)
--RETURN VARCHAR2
AS
V_ORG_NAME VARCHAR2(240);
--v_exp_org_id NUMBER;
BEGIN
SELECT ORG.NAME
,PROJ.CARRYING_OUT_ORGANIZATION_ID
INTO x_exp_org_name
,x_exp_org_id
FROM HR_ORGANIZATION_UNITS ORG, PA_PROJECTS_ALL PROJ
WHERE PROJ.CARRYING_OUT_ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND PROJ.SEGMENT1 = V_Proj_No;
--RETURN V_ORG_NAME;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log, 'Exception in PKG.GET_ORG_NAME - ' || SQLERRM);
END;
END tke_us_cus_fd_39_pk_exp_stat;
/