locked
Calling a Store Procedure RRS feed

  • Question

  • User-541003552 posted

    Hi All,

    Please  I have store Procedure "get_id" in oracle that is under a package called "sp_acc". I am supposed to input the CusId and output Cust_NO + SPR.

    please any idea on how to do this or sample. I can share what am trying to do and is not working.

    Thanks in advance

    Argument Name    Type         In/Out     Default?
    ------------------------------ ----------------------- ------ - -------
    CusId         VARCHAR2      IN
    VOUT         REF CURSOR     OUT
    Result                        OUT
    Cus_NO       VARCHAR2(6)    OUT
    SPR             VARCHAR2(7)    OUT



    Wednesday, May 13, 2015 9:40 AM

All replies

  • User269602965 posted

    Example of calling stored procedure from .NET

    In this case you are passing filter parameters to select statement in the PL/SQL

    and returning a filtered dataset for use in a data grid on .NET side

    /* VB.NET code in ASP.NET application */
    
        ' Get data from stored procedure '
        Try
          Dim connstr As String = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString
          Using conn As New OracleConnection(connstr)
            Using cmd As New OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcGET_BIRD_DATA", conn)
              cmd.CommandType = CommandType.StoredProcedure
              cmd.Parameters.Clear()
              cmd.Parameters.Add("p_DATE_BAND", OracleDbType.Date, ParameterDirection.Input)
              cmd.Parameters.Add("p_AOU_CODE", OracleDbType.Decimal, ParameterDirection.Input)
              cmd.Parameters.Add("p_BIRD_CLASS", OracleDbType.Varchar2, ParameterDirection.Input)
              cmd.Parameters.Add("ret_REFCUR", OracleDbType.RefCursor, ParameterDirection.Output)
              conn.Open()
              Using oda As New OracleDataAdapter(cmd)
                Dim ds As New DataSet()
                oda.Fill(ds)
                Me.RadGrid1.MasterTableView.DataSource = ds.Tables(0)
              End Using
            End Using
          End Using
        Catch ex As Exception
        End Try
    
    /* Oracle PL/SQL code */
    
    CREATE OR REPLACE PACKAGE {SCHEMANAME}.{PACKAGENAME}
    AS
    PROCEDURE rcGET_BIRD_DATA(p_DATE_BAND   IN  DATE
                             ,p_AOU_CODE    IN  NUMBER
                             ,p_BIRD_CLASS  IN  VARCHAR2
                             ,ret_REFCUR    OUT SYS_REFCURSOR);
    END PKG_BIRDS;
    /
    
    CREATE OR REPLACE PACKAGE BODY {SCHEMANAME}.{PACKAGENAME}
    AS
    PROCEDURE rcGET_BIRD_DATA(p_DATE_BAND   IN  DATE
                             ,p_AOU_CODE    IN  NUMBER
                             ,p_BIRD_CLASS  IN  VARCHAR2
                             ,ret_REFCUR    OUT SYS_REFCURSOR)
    BEGIN
    	OPEN ret_REFCUR FOR
    		SELECT
    			AOU_CODE,
    			BIRD_CLASS,
    			BIRD_NAME,
    			DATE_BAND,
    			WING_LENGTH,
    			TAIL_LENGTH,
    			EYE_COLOR,
    			PLUMAGE_CODE
    		FROM
    			{SCHEMANAME}BANDED_BIRDS
    		WHERE
    			DATE_BAND  > p_DATE_BAND
    			AND
    			AOU_CODE   = p_AOU_CODE
    			AND
    			BIRD_CLASS = p_BIRD_CLASS
    END;
    
    END {SCHEMANAME}.{PACKAGENAME};
    /
    
    

     

    Wednesday, May 13, 2015 11:05 AM