Hi Experts....

I have problem to read data from SP & Package

Here My SP Code :-
CREATE OR REPLACE
PROCEDURE PROJECT_SUM_STRATEGY_PROC (P_PRJ_VERSION_ID IN NUMBER,
P_PRJ_ID IN NUMBER,
OUT_CUR OUT PKG_COMMON.CURCOMMON)
IS
BEGIN

OPEN OUT_CUR FOR
SELECT option_id, option_description,insurance_cost_rec_percent,
government_cost_rec_percent,DOCUMENT_REC_INFO, option_short_name,
probability_of_occurance,decile_selected_ind, option_selection_rationale,
currency_id, option_number_selected,
option_budget_aggregate_total,probability_of_occur ance,
NVL((SELECT no_of_years-DECODE(TO_CHAR(p.current_budget_year,'YYYY') + TO_NUMBER(TO_CHAR(MIN(wbs_budget_date), 'YYYY')),NULL, 0,
TO_CHAR(p.current_budget_year,'YYYY') - TO_NUMBER(TO_CHAR(MIN(wbs_budget_date), 'YYYY'))) no_of_years
FROM wbs_budget_amt
WHERE option_id = options.option_id
AND prj_version_id = P_PRJ_VERSION_ID
AND prj_master_id = project_master.prj_master_id),0) no_of_years1,
NVL((SELECT TO_NUMBER(TO_CHAR(MAX(wbs_budget_date),'YYYY')) -TO_NUMBER(TO_CHAR(MIN(wbs_budget_date),'YYYY'))+1
FROM wbs_budget_amt , portfolio
WHERE option_id = options.option_id
AND prj_version_id = P_PRJ_VERSION_ID
AND prj_master_id = project_master.prj_master_id
AND WBS_BUDGET_DATE >= CURRENT_BUDGET_YEAR),0) no_of_years
FROM project_version, project_master, options, portfolio p
where p.portfolio_name = 'TRW'
AND project_version.prj_master_id = project_master.prj_master_id
AND project_version.prj_master_id = options.prj_master_id
AND project_version.prj_version_id = options.prj_version_id
AND options.active_ind='Y'
AND project_master.prj_master_id = P_PRJ_ID
AND project_version.prj_version_id = P_PRJ_VERSION_ID
ORDER BY options.probability_of_occurance DESC,option_short_name;


END PROJECT_SUM_STRATEGY_PROC;


My Package code :-
create or replace PACKAGE "PKG_COMMON" AS
TYPE CURCOMMON IS REF CURSOR;
END;

And my asp classic code

Dim adCmdStoredProc,adNumeric,adVarChar,adParamInput,a dParamOutput, cmdStoredProc, Conn

'set parameters for stored procedure
adCmdStoredProc = 4
adNumeric =131
adVarChar = 200
adParamInput = 1
adParamOutput = 2

Set cmdStoredProc = Server.CreateObject ("ADODB.Command")
Set cmdStoredProc.ActiveConnection = adoConn

cmdStoredProc.CommandText = "PROJECT_SUM_STRATEGY_PROC"
cmdStoredProc.CommandType = adCmdStoredProc

cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("@P_PRJ_VERSION_ID", 3,1 ,4)
cmdStoredProc("@P_PRJ_VERSION_ID") = Version_ID

cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("@P_PRJ_ID",3,1 ,4)
cmdStoredProc("@P_PRJ_ID") = Project_ID
'cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("@OUT_CUR", 3, 2,4)
cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("@OUT_CUR", 200, 2,100)
cmdStoredProc.Execute

When i run the asp file i got this error :-
OraOLEDB error '80040e14'

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'PROJECT_SUM_STRATEGY_PROC' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

I execute the SP in Oracle Developer it work fine, probally the error in executing from asp pages. Anyone know how to execute SP with Package and return the output from asp classic.

Any info and solution very appriaciated.

Many thx

Regards,
Shaffiq

Is this a data type mismatch perchance?

In fact, if it's an output parameter, do you even need to specify it? What happens if you comment out that line of ASP code?