You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user-cs@ibatis.apache.org by Andrew P Chan <an...@yahoo.com.hk> on 2009/07/22 12:10:12 UTC

Some Oracle questions.....

Hi guys, 

Thank you for your last help, I have another issue on retrieving Out value
from the stored procedure.
I have search around from google, but there are not much example in it.

It seems I have to make ParameterMaps for storing the out value, but I dunno
how? Can someone give me an example of it??

Another question is, if program retrieve 50, 000 to 100, 000 records, using
iBatis (QueryForList) is very slow, any solution on getting all records
faster???/

Regards
Andrew
-- 
View this message in context: http://www.nabble.com/Some-Oracle-questions.....-tp24603478p24603478.html
Sent from the iBATIS - User - Cs mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-cs-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-cs-help@ibatis.apache.org

Re: Some Oracle questions.....

Posted by Juan Pablo Araya <ju...@gmail.com>.

It depends on the procedure. If the out value points to a refcursor use the
example i wrote a few days ago. But, if the out is just a simple parameter
in the procedure, with the Direction property in the parameter you can get
the value:

<parameterMap id="parameterInsert" class="Colilla">
      <parameter column="P_COLL_ID" property ="Id" direction="Output"/>
      ... other parameters
</parameterMap>

<procedure id="InsertColilla" parameterMap="parameterInsert">
      PAMPKG_COLILLAS.usp_Insert
</procedure>

In this case the procedure insert a record in the table and set the
P_COLL_ID variable as the sequence ID in the insert:

PROCEDURE usp_Insert(
        P_COLL_ID OUT NUMBER,
    ..... the other parameters
) IS
BEGIN
  INSERT INTO PAMT_COLILLAS (
COLL_ID, .... the other columns,...
) values (the ID from sequence, ...the other values from
parameters...) *returning
COLL_ID into P_COLL_ID;*

With this, iBatis then receives P_COLL_ID and set it on the Id property of
the Colilla class.

Don't know how to solve the issue of performance. Perhaps it's slow because
of the cast to a new class for each record, but I'm not sure how you can
optimize it.

Greetings and sorry for my poor English!

On Wed, Jul 22, 2009 at 6:10 AM, Andrew P Chan <an...@yahoo.com.hk>wrote:

>
> Hi guys,
>
> Thank you for your last help, I have another issue on retrieving Out value
> from the stored procedure.
> I have search around from google, but there are not much example in it.
>
> It seems I have to make ParameterMaps for storing the out value, but I
> dunno
> how? Can someone give me an example of it??
>
> Another question is, if program retrieve 50, 000 to 100, 000 records, using
> iBatis (QueryForList) is very slow, any solution on getting all records
> faster???/
>
> Regards
> Andrew
> --
> View this message in context:
> http://www.nabble.com/Some-Oracle-questions.....-tp24603478p24603478.html
> Sent from the iBATIS - User - Cs mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-cs-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-cs-help@ibatis.apache.org
>
>

Re: Some Oracle questions.....

Posted by Michael Schall <mi...@gmail.com>.

The following link describes ParameterMaps
http://ibatis.apache.org/docs/dotnet/datamapper/ch03s04.html

Here is an example of a SQL Map using stored procedures and parameter maps.

<?xml version="1.0" encoding="utf-8" ?>
<sqlMap namespace="ShiftVsp" xmlns="http://ibatis.apache.org/mapping"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<alias>
		<typeAlias alias="Shift" type="... />
	</alias>
	<resultMaps>
		...
	</resultMaps>
	<statements>
		...
		<procedure id="Insert" parameterMap="InsertShiftParameterMap"
resultClass="map" >
			i_dmShifts
		</procedure>
		...
	</statements>
	<parameterMaps>
		...
		<parameterMap id="ShiftParameterMap" class="Shift">
			<parameter column="Shift" property="Name" type="System.String"
dbType="varchar" />
			<parameter column="Comments" property="Comments"
type="System.String" dbType="varchar" />
			<parameter column="Status" property="Status" type="Int16"
dbType="smallint" />
			<parameter column="EnteredBy" property="AuditInformation.EnteredBy"
type="System.String" dbType="varchar" />
		</parameterMap>
		<parameterMap id="InsertShiftParameterMap" class="Shift"
extends="ShiftParameterMap">
			<parameter column="Key" property="ReferenceKey.Id"
type="System.Int32" dbType="int" mode="OUTPUT" />
		</parameterMap>
		...
	</parameterMaps>
</sqlMap>

As for speed, what is slow?  A result set that large will take time to
materialize.  How many columns are you returning? How long does the
database take to return the rows?  Do you actually need all these rows
at the same time?  If you are only processing one row at a time and
don't need to get the full list and then process it, you can look at
QueryWithRowDelegate.  This really has more to due with memory than
speed.

http://ibatis.apache.org/docs/dotnet/datamapper/ch04s04.html#id401029

Hope this helps.

Mike

On Wed, Jul 22, 2009 at 5:10 AM, Andrew P Chan
<an...@yahoo.com.hk> wrote:
>
> Hi guys,
>
> Thank you for your last help, I have another issue on retrieving Out value
> from the stored procedure.
> I have search around from google, but there are not much example in it.
>
> It seems I have to make ParameterMaps for storing the out value, but I dunno
> how? Can someone give me an example of it??
>
> Another question is, if program retrieve 50, 000 to 100, 000 records, using
> iBatis (QueryForList) is very slow, any solution on getting all records
> faster???/
>
> Regards
> Andrew
> --
> View this message in context: http://www.nabble.com/Some-Oracle-questions.....-tp24603478p24603478.html
> Sent from the iBATIS - User - Cs mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-cs-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-cs-help@ibatis.apache.org
>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-cs-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-cs-help@ibatis.apache.org