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
Discuss This Question: