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

    Red face Unanswered: wrong number or types of arguments in call sp

    I counter this problem in my asp program :-

    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



    Here my SP : -

    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;



    Here My ASP Code :-

    <%

    'On error Resume next
    dim iStart, iEnd, strConnection, adoConn, strQuery, rsResults,output
    Response.Buffer=true

    Response.Write "<br><b>Starting... </b>"

    strConnection = Application("strConnection")
    iStart = instr(1,strConnection,"Source")
    iEnd = instr(instr(1,strConnection,"Source"),strConnectio n,";")
    Response.Write "<br><b>Connection String:</b> " & mid(strConnection,iStart,iEnd-iStart+1)
    strConnection = strConnection & "PLSQLRSet=1;"

    Set adoConn = Server.CreateObject("ADODB.connection")
    adoConn.Open strConnection

    strQuery = "SELECT TO_CHAR(CURRENT_BUDGET_YEAR, 'mm-dd-yyyy') as CURRENT_BUDGET_YEAR FROM portfolio WHERE portfolio_name = 'TRW'"

    Set rsResults = adoConn.Execute(strQuery)

    Response.Write "<br><b>Current Budget Year:</b> " & Year(rsResults("CURRENT_BUDGET_YEAR"))

    Set rsResults = nothing


    Response.Write "<br><b>End </b>"

    Dim rsstrategy, Version_ID, Project_ID, strSQL,objRecordSet

    Version_ID = 5099
    Project_ID = 50


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

    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

    Response.Write "<br><b>Ver:</b> " & Version_ID
    Response.Write "<br><b>Project_ID:</b> " & Project_ID


    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

    Set objRecordSet = Server.CreateObject("ADODB.recordset")
    Set objRecordSet = cmdStoredProc.Execute
    if not objRecordSet.EOF then
    output = objRecordSet("option_description")
    else
    output = now()
    end if


    Response.Write "<br><b>option_description:</b> " & output

    adoConn.Close
    Set adoConn = nothing


    Response.Flush
    %>


    The prob was, this error only occured at production server. Both Test and staging server work fine. Any advise for to check on. all are 10G, I suspect something to do with oracle setting. But I have no idea what to check. Please anyone?


    Thank in advance

    Shaffiq

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking Bind variable

    You still have to create a cursor and provide a bind variable for this parameter:

    OUT_CUR OUT PKG_COMMON.CURCOMMON)
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Oct 2004
    Location
    Kuala Lumpur
    Posts
    38
    Thank LKBrwn DBA,

    Can you show me how to do it.

    Thank in advance

    Shaffiq

Posting Permissions

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