Call a stored procedure with output parameters

0 pts.
Tags:
SQL
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
ASKED: March 14, 2007  12:37 PM
UPDATED: March 15, 2007  1:08 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Iyan23,

I copied this out of a help file. It gives you an idea of the syntax:

rem Saving as SQL*Plus script

rem PL/SQL Developer Test Script

set feedback off
set autoprint off

rem Declare variables
variable result varchar2(2000)
variable p_empno number

rem Set variables
begin
:result := null;
:p_empno := 7369;
end;
/

rem Execute PL/SQL Block
begin
:result := employee.deptname(:p_empno);
end;
/

rem Print variables
print result
print p_empno

Hope that helps.
Charles

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following