Query Application Block - where is cursor type?

Feb 18, 2010 at 2:06 AM

I'd like to use Odp.net provider in QAB.

I made a procedure with cursor type.

It returns simple query.

I set the Parameters in QAB on web.config.

One of the parameters is cursor type and output.

But there is no cursor type in dbtype.

How can I use parameter of cursor type?


Feb 18, 2010 at 2:34 AM
Edited Feb 18, 2010 at 6:26 AM

ummm, It doesn't need parameter of cursor type if cursor type is just one.

But, if there are multiple ref cursors, I don't know...

-- web.config

      <add type="EntLibContrib.Query.ParameterSet, EntLibContrib.Query, Version=, Culture=neutral, PublicKeyToken=null"
          <add dbType="String" direction="Input" type="EntLibContrib.Query.Parameters.DataParameter, EntLibContrib.Query.Database, Version=, Culture=neutral, PublicKeyToken=null"
            name="v_ename" />
      <add databaseInstanceName="Service_Dflt" command="GET_EMPLOY"
        commandType="StoredProcedure" customCommandName="" parameterSetName="get_emp"
        type="EntLibContrib.Query.Queries.DataQuery, EntLibContrib.Query.Database, Version=, Culture=neutral, PublicKeyToken=null"
        name="test2" />

-- cs source

            IDictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("v_ename", "SMITH");
            DataSet ds = QueryFactory.CreateQuery("test2").ExecuteForRead(parameters);
            GridView1.DataSource = ds.Tables[0];

-- procedure

    v_ename IN varchar2,
    -- ,cv_2 OUT SYS_REFCURSOR  -- How I can use this?????

 OPEN cv_1 FOR
     SELECT * FROM emp
     WHERE ename = v_ename;
--    OPEN cv_2 FOR
--     SELECT * FROM dept;    

       -- Consider logging the error and then re-raise
END get_employ;


Feb 22, 2010 at 4:34 PM

Hi elvagood,

Ref cursors are how ODP.NET returns record sets and are dealt with internally within the data provider to produce a DataSet.  You should find that your dataset will contain records from two tables, "emp" and "dept" respectively with the two ref cursors.  If the QAB complains that the number of parameters does not match then you will need to add two dummy OUT parameters but I am certain that ref cursors are dealt with internally and should not be a concern.  If I get time I will do some experiments.

Cheers...   Steve

Feb 22, 2010 at 4:45 PM

Hi elvagood,

I just had another thought.  During the testing of the ODP.NET data provider I had to test Oracle packages which seemed to be the new way to handle record sets returned from Oracle stored procedures.  So if you get no joy out of ref cursors have a look at Oracle Packages.  I can certainly help you out with syntax there as I had to work out the whole lot before I could understand what to do with the DAAB provider.

Cheers...   Steve

Feb 25, 2010 at 4:45 AM
Edited Feb 25, 2010 at 5:06 AM

Sorry but I still can't understand what you said.

How can I add two dummy OUT paramerter?

As I said in the previous post, there is not RefCursor type so I can't add dummy parameters.

If I input any DbType, it display 'error' - mismatch dbtype.

Anyway, is it possible to get multi ref cursors in QAB with ODP.NET Provider?