Good Morning, I have created an oracle stored procedure which has 2 input parameters of type date and 1 output parameter of type PL/SQL table. I recieve errors when I try to execute the stored procedure in SQL*Plus. The stored procedure exists in a package and the Oracle database version is 10g. I want to execute the procedure for testing purposes. I'm looking to find out the correct syntax for calling the procedure. Below is a copy of my code: HEADER CREATE OR REPLACE PACKAGE DWOWS_general_functions_pkg IS TYPE TWorkCountRecord IS RECORD (CaseType varchar2(30), CmpCnt NUMBER, CmpSum NUMBER(9,2), CmpAvg NUMBER(9,2) , OutCnt NUMBER , OutSum NUMBER(9,2) , OutAvg NUMBER(9,2) , CmpsteCnt NUMBER , CmpsteSum NUMBER(9,2), CmpsteAvg NUMBER(9,2)); TYPE TWorkCountTable IS TABLE OF TWorkCountRecord INDEX BY BINARY_INTEGER; PROCEDURE p_testPerformance (in_Start IN claims.rcvd_dt%type, in_End IN claims.closed_dt%type, out_WorkCountTable OUT TWorkCountTable ); --Imari L Bremer Added for performance tuning) ; BODY PROCEDURE p_testPerformance (in_start IN claims.rcvd_dt%type, in_end IN claims.closed_dt%type, out_WorkCountTable OUT TWorkCountTable) IS -- Declaration section temp VARCHAR2(1) := 'N'; v_completecount number := 0; v_outstandingcount number := 0; v_totalcount number := 0; v_completedsum number(9,2) := 0; v_outstandingSum number(9,2) := 0; v_totalSum number(9,2) := 0; v_caseTypeOutstandingSum number(9,2) := 0; v_caseTypeCompletedSum number(9,2) := 0; v_completeAverage number(9,2) := 0; v_outstandingAverage number (9,2) :=0; v_totalAverage number(9,2):= 0; v_Count number := 0; --Cursor declaration to get case types for Completed and Outstanding claims CURSOR c_CaseType IS SELECT case_type, count(id) as grand_total FROM claims WHERE CLAIMS.SYSTEM IN ('ACT','SEP','DEP','RSV','A_R','SPC','XXX') AND ( ((CLAIMS.RCVD_DT BETWEEN IN_START AND IN_END OR (CLAIMS.RCVD_DT <= IN_START)) AND (CLAIMS.CLOSED_DT IS NULL OR CLAIMS.CLOSED_DT > IN_END)) OR (CLAIMS.CLOSED_DT BETWEEN IN_START AND IN_END) ) GROUP BY CASE_TYPE ORDER BY CASE_TYPE ; --CLAIMS.CASE_TYPE = 'CCL-TRIPLE TD EMAILS' --case_type = 'W-2 REQ' and --Cursor declaration to get work counts completed CURSOR c_ClaimsCompletedOnDate (p_Type type_cases.case_type%type) IS SELECT case_type, rcvd_dt, closed_dt, susp_start, susp_stop FROM claims WHERE case_type = p_type AND CLAIMS.SYSTEM IN ('ACT','SEP','DEP','RSV','A_R','SPC','XXX') AND CLAIMS.CLOSED_DT BETWEEN IN_START AND IN_END; --Cursor declaration to get work counts outstanding CURSOR c_ClaimsOutstandingOnDate (p_Type type_cases.case_type%type) IS SELECT rcvd_dt, closed_dt, susp_start, susp_stop,id FROM claims WHERE case_type = p_type AND CLAIMS.SYSTEM IN ('ACT','SEP','DEP','RSV','A_R','SPC','XXX') AND ((CLAIMS.RCVD_DT BETWEEN IN_START AND IN_END OR (CLAIMS.RCVD_DT <= IN_START)) AND (CLAIMS.CLOSED_DT IS NULL OR CLAIMS.CLOSED_DT > IN_END)); BEGIN --Implicit Open of the loop --Implicit Fetch of each case type into the v_caseType record variable FOR v_caseType IN c_CaseType LOOP v_count := v_count + 1; --How many claims were completed within the date range in_start thru in_end SELECT count(*) INTO v_completecount FROM claims WHERE case_type = v_caseType.case_type AND (CLAIMS.SYSTEM IN ('ACT','SEP','DEP','RSV','A_R','SPC','XXX')) AND CLAIMS.CLOSED_DT BETWEEN IN_START AND IN_END; --Determine the sum of the completed age claims IF NOT c_ClaimsCompletedOnDate%ISOPEN THEN FOR v_claimsCompletedOnDate IN c_ClaimsCompletedOnDate(v_caseType.Case_Type) LOOP -- Get each completed individual sum v_caseTypeCompletedSum := f_get_age_for_reporting(v_claimsCompletedOnDate.Rcvd_Dt, v_claimsCompletedOnDate.Closed_Dt, v_claimsCompletedOnDate.Susp_Start, v_claimsCompletedOnDate.Susp_Stop, null, null); --Collect the running completed grand total sum v_completedSum := v_completedSum + nvl(v_caseTypeCompletedSum,0); --Reset the individual completed total to zero for the next claim ID v_caseTypeCompletedSum := 0; END LOOP; END IF; --Determine the sum of the outstanding age claims IF NOT c_claimsOutstandingOnDate%ISOPEN THEN FOR v_claimsOutstandingOnDate IN c_claimsOutstandingOnDate(v_caseType.Case_Type) LOOP -- Get each individual outstanding sum v_caseTypeOutstandingSum := f_get_age_for_reporting(v_claimsOutstandingOnDate.Rcvd_Dt, v_claimsOutstandingOnDate.Closed_Dt, v_claimsOutstandingOnDate.Susp_Start, v_claimsOutstandingOnDate.Susp_Stop, null, null); --Collect the outstanding running grand total sum v_outstandingSum := v_outstandingSum + nvl(v_caseTypeOutstandingSum,0); --Reset the outstanding individual total to zero for the next claim ID v_caseTypeOutstandingSum := 0; END LOOP; END IF; --Calculate the completed average for the case type IF v_completecount > 0 THEN v_completeAverage := v_completedSum / v_completecount; END IF; --Calculate the outstanding count of claims v_outstandingcount := v_caseType.Grand_Total - v_completecount; --Calculate the outstanding average for the case type IF v_outstandingcount > 0 THEN v_outstandingAverage := v_outstandingSum / v_outstandingcount; END IF; --Calculate Grand total count of records, sum and average v_totalCount := v_completecount + v_outstandingcount; v_totalsum := v_completedSum + v_outstandingSum; IF v_totalcount > 0 THEN v_totalAverage := v_totalsum / v_totalCount; END IF; --Populate the output table variables with the correct values out_WorkCountTable(v_count).CaseType := v_caseType.case_type; out_WorkCountTable(v_count).CmpCnt := v_completecount; out_WorkCountTable(v_count).CmpSum := v_completedSum; out_WorkCountTable(v_count).CmpAvg := v_completeAverage; out_WorkCountTable(v_count).OutCnt := v_outstandingcount; out_WorkCountTable(v_count).OutSum := v_outstandingcount; out_WorkCountTable(v_count).OutAvg := v_outstandingcount; out_WorkCountTable(v_count).CmpsteCnt := v_totalCount; out_WorkCountTable(v_count).CmpsteSum := v_totalsum; out_WorkCountTable(v_count).CmpsteAvg := v_totalAverage; /* --Display the completed, outstanding and grand totals DBMS_OUTPUT.PUT_LINE('Case Type '||v_caseType.case_type); DBMS_OUTPUT.PUT_LINE(v_caseType.case_type||' Completed Count '||to_char(v_completecount)); DBMS_OUTPUT.PUT_LINE(v_caseType.case_type||' Completed Sum '||to_char(v_completedSum)); DBMS_OUTPUT.PUT_LINE(v_caseType.case_type||' Completed Average '||to_char(v_completeAverage)); DBMS_OUTPUT.PUT_LINE(v_caseType.case_type||' Outstanding Count '||to_char(v_outstandingcount)); DBMS_OUTPUT.PUT_LINE(v_caseType.case_type||' Outstanding Sum '||to_char(v_outstandingSum)); DBMS_OUTPUT.PUT_LINE(v_caseType.case_type||' Outstanding Average '||to_char(v_outstandingAverage)); DBMS_OUTPUT.PUT_LINE(v_caseType.case_type||' Total Count '||to_char(v_totalCount)); DBMS_OUTPUT.PUT_LINE(v_caseType.case_type||' Total Sum '||to_char(v_totalsum)); DBMS_OUTPUT.PUT_LINE(v_caseType.case_type||' Total Average '||to_char(v_totalAverage)); --DBMS_OUTPUT.PUT_LINE(v_caseType.case_type||' OUTSTANDING ID # '||v_testString);*/ --Reset the variables for the next case type v_caseTypeCompletedSum := 0; v_caseTypeOutstandingSum := 0; v_completecount := 0; v_completedSum := 0; v_completeAverage := 0; v_outstandingcount := 0; v_outstandingSum := 0; v_outstandingAverage := 0; v_totalCount := 0; v_totalsum := 0; v_totalAverage := 0; -- Implicit check of %NOTFOUND END LOOP; --Implicit CLOSE of CURSOR EXCEPTION WHEN OTHERS THEN sql_code_num:=SQLCODE; err_msg:=substr(SQLERRM,1,100); -- Use error logging only if the application has -- the proper table set up to do record the errors. -->LOG ERROR --p_error_logging('f_wasClaimOpenOnDate',sql_code_num,err_msg); temp := 'X'; --RETURN temp; END; --END: p_testPerformance

Software/Hardware used: