Originally posted by: jvanboga
I have a procedure I call in an old model. Due to changes we are writing a new procedure that will eventually replace the existing proc.
The both original and new procedures use the same 10 input variables and returns the same 36 results, in the same order with the same format.
The difference is that the old/current version executes a procedure that is tied to a package while the new procedure is part of the package.
That said I use DBID to call the original procedure as below:
call SID.procedure_current(#P_ID#,#P_S_DOB#,#P_P_DOB#,#P_F_NAME#,#P_D_RANGE#,#P_CLM_NO#,#P_TX_ID#,#P_S_DEP_IND#,#P_PLN_NO#,#P_PLN_ID#,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
The new one is below:
call SID.package_a.procedure_new(#P_ID#,#P_S_DOB#,#P_P_DOB#,#P_F_NAME#,#P_D_RANGE#,#P_CLM_NO#,#P_TX_ID#,#P_S_DEP_IND#,#P_PLN_NO#,#P_PLN_ID#,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
The first call produces results while the second produces the following error message.
<3092-4076>: The columns are of the following types:
<3092-4076>: Column 1 (P_IDENTIFIER) type is CHAR(4000).
<3092-4076>: Column 2 (P_SUBSCRIBER_DOB) type is CHAR(4000).
<3092-4076>: Column 3 (P_PATIENT_DOB) type is CHAR(4000).
<3092-4076>: Column 4 (P_DEP_FIRST_NAME) type is CHAR(4000).
<3092-4076>: Column 5 (P_DATE_RANGE) type is CHAR(4000).
<3092-4076>: Column 6 (P_CLAIM_NUMBER) type is CHAR(4000).
<3092-4076>: Column 7 (P_TAX_ID) type is CHAR(4000).
<3092-4076>: Column 8 (P_SUB_DEP_IND) type is CHAR(4000).
<3092-4076>: Column 9 (P_PLAN_NUMBER) type is CHAR(4000).
<3092-4076>: Column 10 (P_PLAN_ID) type is CHAR(4000).
<3092-4076>: Column 11 (P_RETURN_CODE) type is ADT.
<3092-4076>: Error returned by OCIDescribeAny, errcode=4043
ORA-04043: object package_a does not exist
Here is a summary of the in/out definition of the current proc....
create or replace PROCEDURE procedure_current(
p_plan_id IN CHAR,
p_return_code OUT HDR_RETURN_CODE_TBL,
p_summ_claim_count OUT HDR_CLAIM_COUNT_TBL,
...);
Here is a summary of the in/out definition of the new proc....
create or replace PACKAGE package_a AS
...
PROCEDURE procedure_new (...
,p_return_code OUT HDR_RETURN_CODE_TBL
,p_summ_claim_count OUT HDR_CLAIM_COUNT_TBL
...);
#IBMSterlingTransformationExtender#IBM-Websphere-Transformation-Extender#DataExchange