Forum Stats

  • 3,782,428 Users
  • 2,254,644 Discussions
  • 7,880,077 Comments

Discussions

ORABPEL-11811 on creation of DB adapter returning record

Steve_Macleod
Steve_Macleod Member Posts: 127
edited Sep 23, 2008 6:27AM in BPEL
Hi,
I have been getting some really frustrating results from using the database adapter with some PL/SQL function calls which have input or output %ROWTYPE paramerters.

On creation of the DB adapter, I get notification that a wrapper package is to be created. I confirm this, deploy the process and everything runs fine.
The next day, I start the dev server, only to find the same process generate the following error:


<bindingFault xmlns="http://schemas.oracle.com/bpel/extension"><part name="code"><code>4063</code>
</part><part name="summary"><summary>file:/C:/soa_suite/bpel/domains/default/tmp/.bpel_fetchMedAssess_1.0_5e8d9ed419a39e6c3b8583856366f207.tmp/fetchMedicalAssess.wsdl [ fetchMedicalAssess_ptt::fetchMedicalAssess(InputParameters,OutputParameters) ] - WSIF JCA Execute of operation 'fetchMedicalAssess' failed due to: Error while trying to prepare and execute an API.
An error occurred while preparing and executing the NHSSOA.BPEL_FETCHMEDICALASSESS.TOPLEVEL$NHSSOA_GET_MED_ASSES API. Cause: java.sql.SQLException: ORA-04063: package body "NHSSOA.BPEL_FETCHMEDICALASSESS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "NHSSOA.BPEL_FETCHMEDICALASSESS"
ORA-06512: at line 1
[Caused by: ORA-04063: package body "NHSSOA.BPEL_FETCHMEDICALASSESS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "NHSSOA.BPEL_FETCHMEDICALASSESS"
ORA-06512: at line 1
]
; nested exception is:
ORABPEL-11811
Error while trying to prepare and execute an API.
An error occurred while preparing and executing the NHSSOA.BPEL_FETCHMEDICALASSESS.TOPLEVEL$NHSSOA_GET_MED_ASSES API. Cause: java.sql.SQLException: ORA-04063: package body "NHSSOA.BPEL_FETCHMEDICALASSESS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "NHSSOA.BPEL_FETCHMEDICALASSESS"
ORA-06512: at line 1
[Caused by: ORA-04063: package body "NHSSOA.BPEL_FETCHMEDICALASSESS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "NHSSOA.BPEL_FETCHMEDICALASSESS"
ORA-06512: at line 1
]
Check to ensure that the API is defined in the database and that the parameters match the signature of the API. Contact oracle support if error is not fixable.
</summary>
</part><part name="detail"><detail>
Internal Exception: java.sql.SQLException: ORA-04063: package body "NHSSOA.BPEL_FETCHMEDICALASSESS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "NHSSOA.BPEL_FETCHMEDICALASSESS"
ORA-06512: at line 1
Error Code: 4063</detail>
</part></bindingFault>


It looks like the wrapper package is erring somehow, although I have no idea what could be causing it. Could it be something to do with the %ROWTYPE parameters? No DDL has been changed between yesterday (working) and today (not working).

Im running JDev / SOA Suite 10.1.3.3

Edited by: Steve_Macleod on Sep 13, 2008 3:02 PM

Edited by: Steve_Macleod on Sep 13, 2008 6:32 PM

Edited by: Steve_Macleod on Sep 13, 2008 6:55 PM
Tagged:

Answers

  • Kalidass
    Kalidass Member Posts: 108
    From what I have seen with DB Adapters there are 2 possible reasons that I always found when I get this error.

    Number 1:
    The package is really invalid or does not exist. Which I assume is not possible but it is best to check.

    Number 2:
    The Package or any on the dependent package or function etc has been recompiled with or without modifications in its body.

    The ways I have fixed this is to refresh the connection pool and that always did the trick.

    It always looked like AS some how seems to remember in some kind of cache the last time the package was complied and if there are changes there after, the connection pool needs to refresh.

    Regards
    Kalidass Mookkaiah
    http://oraclebpelindepth.blogspot.com/
    Kalidass
  • Hi and thanks for the response,
    1) No package/ dependent or otherwise has been recompiled.
    2) I have refreshed the connection pool, and the error still occurs.

    I am noticing many issues relating to ORABPEL-11811 on metalink. I believe this to be a bug relating to passing of records as input / output parameters. I would be very grateful if anyone could assist in resolving this issue.

    Many thanks
  • Steve_Macleod
    Steve_Macleod Member Posts: 127
    edited Sep 13, 2008 2:00PM
    Ok, Im replying to myself now... clearly loosing the plot!

    I have noticed that the BPEL_GETPERSHISTDAT2.sql file (which is added when the DB adapter is created) looks like this:

    -----

    -- Declare the SQL type for the PL/SQL type ROWTYPE_PL0
    CREATE OR REPLACE TYPE ROWTYPE_SQL2 AS OBJECT (
    ASSESS_FORM_ID NUMBER,
    ASSESS_ID NUMBER,
    PERS_HIST_DETS VARCHAR2(4000),
    FORM_OWNER NUMBER,
    ASSESS_UPDATED DATE,
    ASSESS_ADDED DATE
    );
    /
    show errors
    -- Declare package containing conversion functions between SQL and PL/SQL types
    CREATE OR REPLACE PACKAGE BPEL_GETPERSHISTDAT2 AS
    -- Redefine a PL/SQL RECORD type originally defined via CURSOR%ROWTYPE
    TYPE ROWTYPE_PL0 IS RECORD (
    ASSESS_FORM_ID NUMBER,
    ASSESS_ID NUMBER,
    PERS_HIST_DETS VARCHAR2(4000),
    FORM_OWNER NUMBER,
    ASSESS_UPDATED DATE,
    ASSESS_ADDED DATE);
    -- Declare the conversion functions the PL/SQL type ROWTYPE_PL0
    FUNCTION PL_TO_SQL5(aPlsqlItem ROWTYPE_PL0)
    RETURN ROWTYPE_SQL2;
    FUNCTION SQL_TO_PL5(aSqlItem ROWTYPE_SQL2)
    RETURN ROWTYPE_PL0;
    FUNCTION TOPLEVEL$NHSSOA_GET_PERS_HIST (P_ASSESS_FORM_ID NUMBER) RETURN ROWTYPE_SQL2;
    END BPEL_GETPERSHISTDAT2;
    /
    show errors
    CREATE OR REPLACE PACKAGE BODY BPEL_GETPERSHISTDAT2 IS
    FUNCTION PL_TO_SQL5(aPlsqlItem ROWTYPE_PL0)
    RETURN ROWTYPE_SQL2 IS
    aSqlItem ROWTYPE_SQL2;
    BEGIN
    -- initialize the object
    aSqlItem := ROWTYPE_SQL2(NULL, NULL, NULL, NULL, NULL, NULL);
    aSqlItem.ASSESS_FORM_ID := aPlsqlItem.ASSESS_FORM_ID;
    aSqlItem.ASSESS_ID := aPlsqlItem.ASSESS_ID;
    aSqlItem.PERS_HIST_DETS := aPlsqlItem.PERS_HIST_DETS;
    aSqlItem.FORM_OWNER := aPlsqlItem.FORM_OWNER;
    aSqlItem.ASSESS_UPDATED := aPlsqlItem.ASSESS_UPDATED;
    aSqlItem.ASSESS_ADDED := aPlsqlItem.ASSESS_ADDED;
    RETURN aSqlItem;
    END PL_TO_SQL5;
    FUNCTION SQL_TO_PL5(aSqlItem ROWTYPE_SQL2)
    RETURN ROWTYPE_PL0 IS
    aPlsqlItem ROWTYPE_PL0;
    BEGIN
    aPlsqlItem.ASSESS_FORM_ID := aSqlItem.ASSESS_FORM_ID;
    aPlsqlItem.ASSESS_ID := aSqlItem.ASSESS_ID;
    aPlsqlItem.PERS_HIST_DETS := aSqlItem.PERS_HIST_DETS;
    aPlsqlItem.FORM_OWNER := aSqlItem.FORM_OWNER;
    aPlsqlItem.ASSESS_UPDATED := aSqlItem.ASSESS_UPDATED;
    aPlsqlItem.ASSESS_ADDED := aSqlItem.ASSESS_ADDED;
    RETURN aPlsqlItem;
    END SQL_TO_PL5;

    FUNCTION TOPLEVEL$NHSSOA_GET_PERS_HIST (P_ASSESS_FORM_ID NUMBER) RETURN ROWTYPE_SQL2 IS
    RETURN_ ROWTYPE_SQL2;
    BEGIN
    RETURN_ := BPEL_GETPERSHISTDAT2.PL_TO_SQL5(NHSSOA_GET_PERS_HIST(P_ASSESS_FORM_ID));
    return RETURN_;
    END TOPLEVEL$NHSSOA_GET_PERS_HIST;

    END BPEL_GETPERSHISTDAT2;
    /
    show errors
    exit


    -----

    The procedure that I am running from the DB adapter looks like this (I initially had this in a package, but simplified it into a stand-alone function):


    create or replace FUNCTION NHSSOA_GET_PERS_HIST (p_assess_form_id NUMBER) RETURN nhssoa_a_types.pers_hist AS
    CURSOR c_get_assess_dets IS
    SELECT *
    FROM nhscont_ass_pers_hist
    WHERE assess_form_id = p_assess_form_id;
    r_c_get_assess_dets c_get_assess_dets%ROWTYPE;
    BEGIN
    OPEN c_get_assess_dets;
    FETCH c_get_assess_dets INTO r_c_get_assess_dets;
    CLOSE c_get_assess_dets;
    RETURN r_c_get_assess_dets;
    END NHSSOA_GET_PERS_HIST;

    -----

    The nhssoa_a_types.pers_hist type looks like this

    TYPE pers_hist IS RECORD (
    ASSESS_FORM_ID NUMBER,
    ASSESS_ID NUMBER,
    PERS_HIST_DETS VARCHAR2(4000 BYTE),
    FORM_OWNER NUMBER,
    ASSESS_UPDATED DATE,
    ASSESS_ADDED DATE
    );

    I should add that the NHSSOA_GET_PERS_HIST function has been tested outside BPEL and works OK.
  • 658231
    658231 Member Posts: 15
    Hello Steve,

    Try bouncing you databse server. I had the same probelm and bouncing the database worked.

    Thanks
    Rahul
  • Kalidass
    Kalidass Member Posts: 108
    If you still are unable to resolve, send me your code from JDev.
    Let me see if it works in my DEV environment.

    Send it to Email: [[email protected]]

    Every little helps
    Kalidass Mookkaiah
    http://oraclebpelindepth.blogspot.com/
  • 428263
    428263 Member Posts: 364
    Bouncing the database server is not the solution. You need to bounce the BPEL server. Perhaps that is what you meant. Bouncing the BPEL server clears and resets the TopLink connection cache, which might resolve your problem.
  • Kalidass
    Kalidass Member Posts: 108
    You can also check if any other schemas also has the same package names.
    I have seen issues, If there are.

    But it still does not explain the fact that it was working before and is not working now.

    Every Little Helps
    Kalidass Mookkaiah
    http://oraclebpelindepth.blogspot.com/
This discussion has been closed.