ASP.NET AND ORACLE PROCEDURE

[taree]

I have a oracle procedure that i would like to use in my asp.net page. This procedure has one parameter and the parameter accepts more than one value. my problem is how can I embede this procdure in my asp.net page. I do not have a problme if this procedure takes one value. but in this case it takes more than one value. if someone has an idea how this can be done i really appreciate that.

procedure:

CREATE OR REPLACE PROCEDURE planholderslist (
   p_letting_date   IN       string_table,
   p_results        OUT      sys_refcursor
)
AS
BEGIN
   OPEN p_results FOR
      SELECT DISTINCT DECODE (TRIM (MIN (j.route)),
                              NULL, 'N/A',
                              TRIM (MIN (j.route))
                             ) routenumber,
                      l.lcontid contractid, (q.cdescr) jobdescription,
                      INITCAP (q.clocat1 || q.clocat2) LOCATION,
                      SUBSTR (q.cprojnum, 1, 10) projectnumber,
                         SUBSTR (l.letting, 3, 2)
                      || '-'
                      || SUBSTR (l.letting, 5, 2)
                      || '-'
                      || SUBSTR (l.letting, 1, 2) lettingdate
                 FROM vendor v,
                      vendaddr r,
                      letprop l,
                      planhold p,
                      proposal q,
                      project j,
                      propproj k,
                      bidlet bd
                WHERE v.vendor = r.vendor
                  AND k.contid = q.contid
                  AND k.pcn = j.pcn
                  AND l.lcontid = k.contid
                  AND p.vendor = v.vendor
                  AND l.letting = p.letting
                  AND (TO_CHAR (bd.datelet, 'MM/DD/YYYY') IN (
                                                   SELECT COLUMN_VALUE
                                                     FROM TABLE
                                                               (p_letting_date))
                      )
                  AND l.CALL = p.CALL
                  AND r.addrnum = p.billto
                  AND bd.letting = l.letting
             GROUP BY q.cdescr,
                      q.clocat1,
                      q.clocat2,
                      bd.letting,
                      l.letting,
                      l.lcontid,
                      q.cprojnum;
END planholderslist;

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        BindLetData()
    End Sub


    Sub BindLetData()
        Dim connectionString As String = ConnectionStrings("costEstimating1").ConnectionString
        Dim oOracleConn As OracleConnection = New OracleConnection(connectionString)
        oOracleConn.Open()

        Dim drLettingData As OracleDataReader
        Dim cmdLetting As OracleCommand = New OracleCommand()
        With cmdLetting
            .Connection = oOracleConn
            .CommandText = "planholderslist"
            .CommandType = CommandType.StoredProcedure
            .Parameters.Clear()
            .Parameters.Add(New OracleParameter("p_letting_date", OracleType.VarChar)).Value = ("'07/28/2000','05/26/2000'")----this is were I am confuesed how to pass multiple values
            .Parameters.Add(New OracleParameter("p_results", OracleType.Cursor)).Direction = ParameterDirection.Output
        End With
        drLettingData = cmdLetting.ExecuteReader()
        gvPhllist.DataSource = drLettingData
        gvPhllist.DataBind()
        drLettingData.Close()
    End Sub

 

[jmeckley]

have you tried google? If you're not finding anything it may be that the feature doesn't exist in Ado.Net I have never seen an array/table used as a parameter for Ado.Net.

that said, you could try

var parameter = new New OracleParameter("p_letting_date", OracleType.VarChar)
{
   Value = new []{"07/28/2000","05/26/2000"}
};

command.Parameters.Add(parameter);

where the value is an array of strings instead of a string. Again, I haven't seen this before so I don't know if it's possible.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[taree]

Thank you Jason for your help on this. After a lot of googling and help form oracle forum, I got it to work. Just incase someone needs it as a reference here it is.
Hope this will help someone.

step 1:

--create your type
create or replace type varcharTableType as table   of varchar2 (255);

step 2
You can pass in the value as an VARCHAR2 (make the array a comma list) and use a function like this to turn it into your table for your IN statement
[asktom variable in list |

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425

this would be simplest to the code you currently have

--create your function
function in_varchar( p_string in varchar2 ) return varcharTableType  
    as
        l_string        long default p_string || ',';
        l_data          varcharTableType := varcharTableType();
        n               number;
    begin
      loop
          exit when l_string is null;
          n := instr( l_string, ',' );
         l_data.extend;
         l_data(l_data.count) := 
                 ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
         l_string := substr( l_string, n+1 );
    end loop;
 
    RETURN L_DATA;
  END in_varchar;

step 3
---your procedure

CREATE OR REPLACE PROCEDURE planholderslist (
   p_letting_date   IN       VARCHAR2,
   p_results        OUT      sys_refcursor
)
AS

BEGIN

   OPEN p_results FOR
      SELECT DISTINCT DECODE (TRIM (MIN (j.route)),
                              NULL, 'N/A',
                              TRIM (MIN (j.route))
                             ) routenumber,
                      l.lcontid contractid, (q.cdescr) jobdescription,
                      INITCAP (q.clocat1 || q.clocat2) LOCATION,
                      SUBSTR (q.cprojnum, 1, 10) projectnumber,
                         SUBSTR (l.letting, 3, 2)
                      || '-'
                      || SUBSTR (l.letting, 5, 2)
                      || '-'
                      || SUBSTR (l.letting, 1, 2) lettingdate
                 FROM vendor v,
                      vendaddr r,
                      letprop l,
                      planhold p,
                      proposal q,
                      project j,
                      propproj k,
                      bidlet bd
                WHERE v.vendor = r.vendor
                  AND k.contid = q.contid
                  AND k.pcn = j.pcn
                  AND l.lcontid = k.contid
                  AND p.vendor = v.vendor
                  AND l.letting = p.letting
                  AND (TO_CHAR (bd.datelet, 'MM/DD/YY') IN (
                                        SELECT COLUMN_VALUE
                                        FROM TABLE (in_varchar (p_letting_date)
                                                   ))
                      )
                  AND l.CALL = p.CALL
                  AND r.addrnum = p.billto
                  AND bd.letting = l.letting
             GROUP BY q.cdescr,
                      q.clocat1,
                      q.clocat2,
                      bd.letting,
                      l.letting,
                      l.lcontid,
                      q.cprojnum;
END planholderslist;

 

[taree]

sorry I forgot the code behind This shows how to pass multiplevalues to one parameter

 Sub BindLetData()
        Dim connectionString As String = ConnectionStrings("costEstimating1").ConnectionString
        Dim oOracleConn As OracleConnection = New OracleConnection(connectionString)
        oOracleConn.Open()

        Dim cmdLetting As OracleCommand = New OracleCommand()
        With cmdLetting
            .Connection = oOracleConn
            .CommandText = "planholderslist"
            .CommandType = CommandType.StoredProcedure
            .Parameters.Clear()
            .Parameters.Add(New OracleParameter("p_letting_date", OracleType.VarChar)).Value = ("02/25/00,03/28/08")
            .Parameters.Add(New OracleParameter("p_results", OracleType.Cursor)).Direction = ParameterDirection.Output
        End With

        Dim adpFederal As New OracleDataAdapter(cmdLetting)

        Dim myDataSet As New DataSet
        adpFederal.Fill(myDataSet, "BidRcvd")
        gvPhlList.DataSource = myDataSet
        gvPhlList.DataBind()
        gvPhlList.Visible = True

 

[jbenson001]

Great, thanks for posting your find.