[x]
Posted via EE Mobile

Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.

11/02/2009 at 10:41AM PST, ID: 24864712 | Points: 500
[x]
Attachment Details

How do I pass in and out parameters to an Oracle Stored Procedure from an ASP.NET web application using the VB.net language.

Asked by mmp1030 in Oracle CRM, Programming for ASP.NET, .NET Framework 3.x versions

Tags: ASP.NET and Oracle Stored procedures

I have create a Oracle stored procedure that check to see if a record exist and then returns an integer 0 or 1.  I need to call and pass parameters to ths store procedure from a public Function ASP.net.  Here is the Error message that I am getting returned in the ASP.net debugger:  Message = "ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'RECORDEXISTS' ORA-06550: line 1, column 7: PL/SQL: Statement ignored ".  Here is my Oracle stored procedure code:

PROCEDURE recordExists(i_domain          VARCHAR2 ,
                          i_status          VARCHAR2,
                          i_sdtm_ig_version VARCHAR2,
                          recordExists  OUT NUMBER)
   AS
   BEGIN
      SELECT count(*) INTO recordExists
         FROM vw_mapping_domain_ref_all
            WHERE upper(domain) = upper(i_domain) AND
                  upper(status) = upper(i_status) AND
                  sdtm_ig_version = i_sdtm_ig_version;

   END recordExists;

Here is my ASP.net Code:

Public Sub draftrecordExists(ByVal dom As domainRefDetails)
        Dim ora As New OracleDataAccess(connectionString)
        Dim myParams As OracleParameter()
        myParams = ora.InitParameters(3)

        myParams(0) = OracleDataAccess.CreateInputParameter("i_domain", dom.domain, OracleType.NVarChar)
        myParams(1) = OracleDataAccess.CreateInputParameter("i_status", dom.status, OracleType.NVarChar)
        myParams(2) = OracleDataAccess.CreateInputParameter("i_sdtm_ig_version", dom.sdtm_ig_version, OracleType.NVarChar)
        myParams(3) = OracleDataAccess.CreateOutputParameter("o_recordExistts", OracleType.Int16)

        Try
            ora.ExecuteStoredProc("pkg_vw_mapping_domain_ref.RecordExists", myParams)

        Catch ex As OracleException

        End Try
    End Sub

Here is the code that calls the procedure:

mynum = checkforexistrecord(DATAKEY1, DATAKEY2, DATAKEY3)
[+][-]11/02/09 10:56 AM, ID: 25722405

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]11/02/09 11:05 AM, ID: 25722494

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]11/02/09 11:07 AM, ID: 25722518

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]11/02/09 11:22 AM, ID: 25722680

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]11/02/09 11:28 PM, ID: 25726664

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]11/03/09 04:38 AM, ID: 25728293

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]11/03/09 10:00 PM, ID: 25736767

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]11/04/09 12:47 PM, ID: 25743726

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20091111-EE-VQP-91 - Hierarchy / EE_QW_3_20080625