Originally posted by: jvanboga

Map calls a stored procedure owned by scheme 1 as scheme 1 and is successful.  Map calls same procedure as scheme 2 and the map fails with the identified error.  I asked our DBAs to review scheme 2s rights.  They found rights appear to be granted correctly.

To confirm this we called the same proc as scheme 2 using putty and toad.  The procedure executed successfully.  Is anyone aware of an issue similar to this?  If so were you able to find a solution?

WTX8.4

Windows 7

Scheme 2 has 'execute' rights for the proc and type tables.

Map 1 extracts procedure input variables and executes map 2 which is compiled with an input card pointing to the procedure via an mdq file.

Sample call

<868-4576>: Host string: SID
<868-4576>: UserID     : sch02
<868-4576>: Password   : *****
<868-4576>: Context: Input Card, Transaction scope: Map
<868-4576>: Query      : call sch01.DDS_276_GET_CLAIMS_PROC('ABCDEFGHI','19730424','19730424','','20130724-20130724','','123456789','S','212','','','P','106',?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

Error from dbl file...
 

<868-4576>: The columns are of the following types:
<868-4576>:    Column 1 (P_IDENTIFIER) type is VARCHAR(4000).
<868-4576>:    Column 2 (P_SUBSCRIBER_DOB) type is VARCHAR(4000).
<868-4576>:    Column 3 (P_PATIENT_DOB) type is VARCHAR(4000).
<868-4576>:    Column 4 (P_DEP_FIRST_NAME) type is VARCHAR(4000).
<868-4576>:    Column 5 (P_DATE_RANGE) type is VARCHAR(4000).
<868-4576>:    Column 6 (P_CLAIM_NUMBER) type is VARCHAR(4000).
<868-4576>:    Column 7 (P_TAX_ID) type is VARCHAR(4000).
<868-4576>:    Column 8 (P_SUB_DEP_IND) type is VARCHAR(4000).
<868-4576>:    Column 9 (P_PLAN_NUMBER) type is VARCHAR(4000).
<868-4576>:    Column 10 (P_PORTAL_PLAN_ID) type is VARCHAR(4000).
<868-4576>:    Column 11 (P_NPI) type is VARCHAR(4000).
<868-4576>:    Column 12 (P_SUB_PRV_IND) type is VARCHAR(4000).
<868-4576>:    Column 13 (P_SUBMITTED) type is VARCHAR(4000).
<868-4576>:    Column 14 (P_RETURN_CODE) type is ADT.
<868-4576>: Error returned by OCIDescribeAny, errcode=4043

ORA-04043: object HDR_RETURN_CODE_TBL does not exist

<868-4576>: Number of buffers in fetch array = 1
<868-4576>: The procedure statement to be executed is:
<868-4576>: begin sch01.DDS_276_GET_CLAIMS_PROC(:a00,:a01,:a02,:a03,:a04,:a05,:a06,:a07,:a08,:a09,:a10,:a11,:a12,:a13); end;
<868-4576>: *ERR: in adpProcessStream (-2)
<868-4576>: Returned status: (-1097) Failed_to_create_an_object
<868-4576>: Cleaning up and closing the transaction...
<868-4576>: Transaction rollback was successful.

extract from proc:

create or replace PROCEDURE       DDS_276_GET_CLAIMS_PROC (p_identifier                   IN VARCHAR2
                     ,p_subscriber_dob               IN VARCHAR2
                     ,p_patient_dob                  IN VARCHAR2
                     ,p_dep_first_name               IN VARCHAR2
                     ,p_date_range                   IN VARCHAR2
                     ,p_claim_number                 IN VARCHAR2
                     ,p_tax_id                       IN VARCHAR2
                     ,p_sub_dep_ind                  IN VARCHAR2
                     ,p_plan_number                  IN VARCHAR2
                     ,p_portal_plan_id               IN VARCHAR2
                     ,p_npi                          IN VARCHAR2          
                     ,p_sub_prv_ind                  IN VARCHAR2  
                     ,p_submitted                    IN VARCHAR2      
                     ,p_return_code                  OUT HDR_RETURN_CODE_TBL
                     ,p_summ_claim_count             OUT HDR_CLAIM_COUNT_TBL
                     ,p_detail_proc_count            OUT HDR_PROC_COUNT_TBL
                     ,p_provider_count               OUT HDR_PROVIDER_COUNT_TBL
                     ,p_bus_seg                      OUT HDR_BUS_SEG_TBL
 

Originally posted by: paul.brett

Your error message is: "ORA-04043: object HDR_RETURN_CODE_TBL does not exist ".

Does the procedure (sch01.DDS_276_GET_CLAIMS_PROC) refer to object 'HDR_RETURN_CODE_TBL' when perhaps it should use 'sch01.HDR_RETURN_CODE_TBL'?

Thank-you.

Paul.

Follow me on Twitter

Originally posted by: jvanboga

I asked the developer to try that with the first type table item.  Figured if it worked the error would change to the second.  If that worked we'd update all table references.  Received same error.

                     ,p_return_code                  OUT sch01.HDR_RETURN_CODE_TBL
                     ,p_summ_claim_count             OUT HDR_CLAIM_COUNT_TBL
                     ,p_detail_proc_count            OUT HDR_PROC_COUNT_TBL
                     ,p_provider_count               OUT HDR_PROVIDER_COUNT_TBL
                     ,p_bus_seg                      OUT HDR_BUS_SEG_TBL
 

I also tried this:

call sot01.DDS_276_GET_CLAIMS_PROC('ABCDEFGHI','19730424','19730424','','20130724-20130724','','123456789','S','212','','','P','106',sot01.?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

Originally posted by: paul.brett

I recommend you open a PMR, supplying the dtxver report and enclosing a testcase to replicate the issue.

Thank-you.

Paul.

Follow me on Twitter