Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Location
    Kuala Lumpur
    Posts
    38

    Unanswered: Read from sp & package

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    OUT_CUR OUT PKG_COMMON.CURCOMMON

    ...

    cmdStoredProc.Parameters.Append cmdStoredProc.CreateParameter("@OUT_CUR", 200, 2,100)
    Is this a data type mismatch perchance?
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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?
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •