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

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

-- 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];
            GridView1.DataBind();

-- procedure

CREATE OR REPLACE PROCEDURE SCOTT.get_employ
(
    v_ename IN varchar2,
    cv_1 OUT SYS_REFCURSOR
    -- ,cv_2 OUT SYS_REFCURSOR  -- How I can use this?????

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

 
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END get_employ;

 

Coordinator
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

Coordinator
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?