You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@jmeter.apache.org by "Daly, James" <da...@ibacorp.us> on 2009/10/21 14:48:22 UTC
JMeter - How do i run a stored procedure
To whom it may concern,
I am trying to call a stored procedure using jmeter (Version - 2.3.2)
and getting some errors.
This SQL procedure which gets userinfo by e-mail for user: siioTSC09.
Below is the actual procedure i used in JMeter and the procedure from
pl/sql viewer under packages.
For "Parameter Values", I entered the username(not sure if this is
correct).
Actual procedure i am using in JMeter JDBC Request control panel:
CALL CRIS_SYSTEM_ADMIN_PKG.CRIS_GET_USERINFO_BY_EMAIL_SP();
Query Type: Callable statement
Parameter values: siioTSC09
Parameter types: VARCHAR
- Get the following message after running JDBX Request.
Response message: java.sql.SQLException: ORA-00911: invalid character.
Procedure from PL/SQL:
CALL CRIS_SYSTEM_ADMIN_PKG.CRIS_GET_USERINFO_BY_EMAIL_SP(P_USER_EMAIL IN
VARCHAR2,
P_REF_CURSOR OUT CRIS_GLOBAL_PARMS_PKG.T_REF_CUR);
Can anyone help me with this?
Thank you.
Thank you,
James Daly
dalyj@ibacorp.us
Re: JMeter - How do i run a stored procedure
Posted by sebb <se...@gmail.com>.
In 22/10/2009, Daly, James <da...@ibacorp.us> wrote:
> Hi Deepak,
> Thank you much for your response. You are correct, I removed the semi
> colon at the end and stopped getting "invalid character" on the View
> Results Tree".
> I have followed all your steps but have a question on how do I <FIND the
> integer value for SqlTypes.Other> as you mention below.
>
>
> Parameters types = VARCHAR, OUT <FIND the integer value for
> SqlTypes.Other>
>
>
> Can I just type in for Parameters types: VARCHAR, OUT
Try removing the space before OUT.
>
> But if I do this I get on "view result tree" - Response message:
> java.sql.SQLException: Invalid data type: OUT
>
> How can I fix this?
> Thank you.
> James
>
>
> -----Original Message-----
> From: Deepak Shetty [mailto:shettyd@gmail.com]
> Sent: Wednesday, October 21, 2009 12:11 PM
> To: JMeter Users List
> Subject: Re: JMeter - How do i run a stored procedure
>
> Some observations
> a. The error you see is probably caused by the semi colon at the end.
> This
> shouldnt be there in java
> b. Second your call doesnt match the declaration.
> CALL CRIS_SYSTEM_ADMIN_PKG.CRIS_
> >
> > GET_USERINFO_BY_EMAIL_SP(?,?)
> >
> Parameters types = VARCHAR, OUT <FIND the integer value for
> SqlTypes.Other>
> Parameter Values = siioTSC09, NULL
>
> Note that i havent tested this out :) but the general principle is that
> as
> many '?' are needed as there are parameters
>
> c. As far as I know you cannot return OUT ref cursors in a form that can
> be
> processed by the JDBC sampler , i.e. you can execute the Stored proc but
> if
> you want to process the return resultset, you cant use the JDBC sampler.
>
> regards
> deepak
>
>
>
> On Wed, Oct 21, 2009 at 5:48 AM, Daly, James <da...@ibacorp.us> wrote:
>
> > To whom it may concern,
> >
> >
> >
> > I am trying to call a stored procedure using jmeter (Version - 2.3.2)
> > and getting some errors.
> >
> > This SQL procedure which gets userinfo by e-mail for user: siioTSC09.
> >
> > Below is the actual procedure i used in JMeter and the procedure from
> > pl/sql viewer under packages.
> >
> > For "Parameter Values", I entered the username(not sure if this is
> > correct).
> >
> >
> >
> > Actual procedure i am using in JMeter JDBC Request control panel:
> >
> > CALL CRIS_SYSTEM_ADMIN_PKG.CRIS_GET_USERINFO_BY_EMAIL_SP();
> >
> >
> >
> > Query Type: Callable statement
> >
> > Parameter values: siioTSC09
> >
> > Parameter types: VARCHAR
> >
> >
> >
> > - Get the following message after running JDBX Request.
> >
> > Response message: java.sql.SQLException: ORA-00911: invalid character.
> >
> >
> >
> >
> >
> > Procedure from PL/SQL:
> >
> > CALL CRIS_SYSTEM_ADMIN_PKG.CRIS_GET_USERINFO_BY_EMAIL_SP(P_USER_EMAIL
> IN
> > VARCHAR2,
> >
> > P_REF_CURSOR OUT CRIS_GLOBAL_PARMS_PKG.T_REF_CUR);
> >
> >
> >
> > Can anyone help me with this?
> >
> > Thank you.
> >
> >
> >
> > Thank you,
> >
> > James Daly
> >
> > dalyj@ibacorp.us
> >
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
RE: JMeter - How do i run a stored procedure
Posted by "Daly, James" <da...@ibacorp.us>.
Hi Mark,
Thank you. This way it works much better!
SELECT * FROM TABLE(name_of_your_pipelined_function)
and get a normal resultset.
BR,
James
-----Original Message-----
From: Mark Rotteveel [mailto:mark@pluton.nl]
Sent: Friday, October 23, 2009 7:25 AM
To: JMeter Users List
Subject: Re: JMeter - How do i run a stored procedure
> Hi
> You would need to write some java program that prints out
> java.sql.Types.OTHER. I just ran it the value would be 1111.
>
> So in your
> Types VARCHAR,OUT 1111
> ParameterValues siioTSC09,]NULL[
>
> Note that i havent run any Oracle SP with jmeter so Im guessing based
on
> how
> I would have to implement this for calling the SP directly from Java.
The
> problem really is that Oracle doesnt directly allow you to return a
> ResultSet from an SP (like say MS SQL server does and I dont think the
> current JDBC sampler handles this idiosyncrasy in oracle too well)
Actually, if the stored procedure is a 'table' function (or 'PIPELINED'
function), you can do SELECT * FROM
TABLE(name_of_your_pipelined_function)
and get a normal resultset.
Mark
--
Mark Rotteveel
Pluton IT
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
Re: JMeter - How do i run a stored procedure
Posted by Mark Rotteveel <ma...@pluton.nl>.
> Hi
> You would need to write some java program that prints out
> java.sql.Types.OTHER. I just ran it the value would be 1111.
>
> So in your
> Types VARCHAR,OUT 1111
> ParameterValues siioTSC09,]NULL[
>
> Note that i havent run any Oracle SP with jmeter so Im guessing based on
> how
> I would have to implement this for calling the SP directly from Java. The
> problem really is that Oracle doesnt directly allow you to return a
> ResultSet from an SP (like say MS SQL server does and I dont think the
> current JDBC sampler handles this idiosyncrasy in oracle too well)
Actually, if the stored procedure is a 'table' function (or 'PIPELINED'
function), you can do SELECT * FROM TABLE(name_of_your_pipelined_function)
and get a normal resultset.
Mark
--
Mark Rotteveel
Pluton IT
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
Re: JMeter - How do i run a stored procedure
Posted by Deepak Shetty <sh...@gmail.com>.
Hi
You would need to write some java program that prints out
java.sql.Types.OTHER. I just ran it the value would be 1111.
So in your
Types VARCHAR,OUT 1111
ParameterValues siioTSC09,]NULL[
Note that i havent run any Oracle SP with jmeter so Im guessing based on how
I would have to implement this for calling the SP directly from Java. The
problem really is that Oracle doesnt directly allow you to return a
ResultSet from an SP (like say MS SQL server does and I dont think the
current JDBC sampler handles this idiosyncrasy in oracle too well)
Your other option is to use a java sampler to run the Stored Proc .
regards
deepak
On Thu, Oct 22, 2009 at 5:20 AM, Daly, James <da...@ibacorp.us> wrote:
> Hi Deepak,
> Thank you much for your response. You are correct, I removed the semi
> colon at the end and stopped getting "invalid character" on the View
> Results Tree".
> I have followed all your steps but have a question on how do I <FIND the
> integer value for SqlTypes.Other> as you mention below.
>
> Parameters types = VARCHAR, OUT <FIND the integer value for
> SqlTypes.Other>
>
> Can I just type in for Parameters types: VARCHAR, OUT
>
> But if I do this I get on "view result tree" - Response message:
> java.sql.SQLException: Invalid data type: OUT
>
> How can I fix this?
> Thank you.
> James
>
> -----Original Message-----
> From: Deepak Shetty [mailto:shettyd@gmail.com]
> Sent: Wednesday, October 21, 2009 12:11 PM
> To: JMeter Users List
> Subject: Re: JMeter - How do i run a stored procedure
>
> Some observations
> a. The error you see is probably caused by the semi colon at the end.
> This
> shouldnt be there in java
> b. Second your call doesnt match the declaration.
> CALL CRIS_SYSTEM_ADMIN_PKG.CRIS_
> >
> > GET_USERINFO_BY_EMAIL_SP(?,?)
> >
> Parameters types = VARCHAR, OUT <FIND the integer value for
> SqlTypes.Other>
> Parameter Values = siioTSC09, NULL
>
> Note that i havent tested this out :) but the general principle is that
> as
> many '?' are needed as there are parameters
>
> c. As far as I know you cannot return OUT ref cursors in a form that can
> be
> processed by the JDBC sampler , i.e. you can execute the Stored proc but
> if
> you want to process the return resultset, you cant use the JDBC sampler.
>
> regards
> deepak
>
>
>
> On Wed, Oct 21, 2009 at 5:48 AM, Daly, James <da...@ibacorp.us> wrote:
>
> > To whom it may concern,
> >
> >
> >
> > I am trying to call a stored procedure using jmeter (Version - 2.3.2)
> > and getting some errors.
> >
> > This SQL procedure which gets userinfo by e-mail for user: siioTSC09.
> >
> > Below is the actual procedure i used in JMeter and the procedure from
> > pl/sql viewer under packages.
> >
> > For "Parameter Values", I entered the username(not sure if this is
> > correct).
> >
> >
> >
> > Actual procedure i am using in JMeter JDBC Request control panel:
> >
> > CALL CRIS_SYSTEM_ADMIN_PKG.CRIS_GET_USERINFO_BY_EMAIL_SP();
> >
> >
> >
> > Query Type: Callable statement
> >
> > Parameter values: siioTSC09
> >
> > Parameter types: VARCHAR
> >
> >
> >
> > - Get the following message after running JDBX Request.
> >
> > Response message: java.sql.SQLException: ORA-00911: invalid character.
> >
> >
> >
> >
> >
> > Procedure from PL/SQL:
> >
> > CALL CRIS_SYSTEM_ADMIN_PKG.CRIS_GET_USERINFO_BY_EMAIL_SP(P_USER_EMAIL
> IN
> > VARCHAR2,
> >
> > P_REF_CURSOR OUT CRIS_GLOBAL_PARMS_PKG.T_REF_CUR);
> >
> >
> >
> > Can anyone help me with this?
> >
> > Thank you.
> >
> >
> >
> > Thank you,
> >
> > James Daly
> >
> > dalyj@ibacorp.us
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
>
>
RE: JMeter - How do i run a stored procedure
Posted by "Daly, James" <da...@ibacorp.us>.
Hi Deepak,
Thank you much for your response. You are correct, I removed the semi
colon at the end and stopped getting "invalid character" on the View
Results Tree".
I have followed all your steps but have a question on how do I <FIND the
integer value for SqlTypes.Other> as you mention below.
Parameters types = VARCHAR, OUT <FIND the integer value for
SqlTypes.Other>
Can I just type in for Parameters types: VARCHAR, OUT
But if I do this I get on "view result tree" - Response message:
java.sql.SQLException: Invalid data type: OUT
How can I fix this?
Thank you.
James
-----Original Message-----
From: Deepak Shetty [mailto:shettyd@gmail.com]
Sent: Wednesday, October 21, 2009 12:11 PM
To: JMeter Users List
Subject: Re: JMeter - How do i run a stored procedure
Some observations
a. The error you see is probably caused by the semi colon at the end.
This
shouldnt be there in java
b. Second your call doesnt match the declaration.
CALL CRIS_SYSTEM_ADMIN_PKG.CRIS_
>
> GET_USERINFO_BY_EMAIL_SP(?,?)
>
Parameters types = VARCHAR, OUT <FIND the integer value for
SqlTypes.Other>
Parameter Values = siioTSC09, NULL
Note that i havent tested this out :) but the general principle is that
as
many '?' are needed as there are parameters
c. As far as I know you cannot return OUT ref cursors in a form that can
be
processed by the JDBC sampler , i.e. you can execute the Stored proc but
if
you want to process the return resultset, you cant use the JDBC sampler.
regards
deepak
On Wed, Oct 21, 2009 at 5:48 AM, Daly, James <da...@ibacorp.us> wrote:
> To whom it may concern,
>
>
>
> I am trying to call a stored procedure using jmeter (Version - 2.3.2)
> and getting some errors.
>
> This SQL procedure which gets userinfo by e-mail for user: siioTSC09.
>
> Below is the actual procedure i used in JMeter and the procedure from
> pl/sql viewer under packages.
>
> For "Parameter Values", I entered the username(not sure if this is
> correct).
>
>
>
> Actual procedure i am using in JMeter JDBC Request control panel:
>
> CALL CRIS_SYSTEM_ADMIN_PKG.CRIS_GET_USERINFO_BY_EMAIL_SP();
>
>
>
> Query Type: Callable statement
>
> Parameter values: siioTSC09
>
> Parameter types: VARCHAR
>
>
>
> - Get the following message after running JDBX Request.
>
> Response message: java.sql.SQLException: ORA-00911: invalid character.
>
>
>
>
>
> Procedure from PL/SQL:
>
> CALL CRIS_SYSTEM_ADMIN_PKG.CRIS_GET_USERINFO_BY_EMAIL_SP(P_USER_EMAIL
IN
> VARCHAR2,
>
> P_REF_CURSOR OUT CRIS_GLOBAL_PARMS_PKG.T_REF_CUR);
>
>
>
> Can anyone help me with this?
>
> Thank you.
>
>
>
> Thank you,
>
> James Daly
>
> dalyj@ibacorp.us
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: jmeter-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jmeter-user-help@jakarta.apache.org
Re: JMeter - How do i run a stored procedure
Posted by Deepak Shetty <sh...@gmail.com>.
Some observations
a. The error you see is probably caused by the semi colon at the end. This
shouldnt be there in java
b. Second your call doesnt match the declaration.
CALL CRIS_SYSTEM_ADMIN_PKG.CRIS_
>
> GET_USERINFO_BY_EMAIL_SP(?,?)
>
Parameters types = VARCHAR, OUT <FIND the integer value for SqlTypes.Other>
Parameter Values = siioTSC09, NULL
Note that i havent tested this out :) but the general principle is that as
many '?' are needed as there are parameters
c. As far as I know you cannot return OUT ref cursors in a form that can be
processed by the JDBC sampler , i.e. you can execute the Stored proc but if
you want to process the return resultset, you cant use the JDBC sampler.
regards
deepak
On Wed, Oct 21, 2009 at 5:48 AM, Daly, James <da...@ibacorp.us> wrote:
> To whom it may concern,
>
>
>
> I am trying to call a stored procedure using jmeter (Version - 2.3.2)
> and getting some errors.
>
> This SQL procedure which gets userinfo by e-mail for user: siioTSC09.
>
> Below is the actual procedure i used in JMeter and the procedure from
> pl/sql viewer under packages.
>
> For "Parameter Values", I entered the username(not sure if this is
> correct).
>
>
>
> Actual procedure i am using in JMeter JDBC Request control panel:
>
> CALL CRIS_SYSTEM_ADMIN_PKG.CRIS_GET_USERINFO_BY_EMAIL_SP();
>
>
>
> Query Type: Callable statement
>
> Parameter values: siioTSC09
>
> Parameter types: VARCHAR
>
>
>
> - Get the following message after running JDBX Request.
>
> Response message: java.sql.SQLException: ORA-00911: invalid character.
>
>
>
>
>
> Procedure from PL/SQL:
>
> CALL CRIS_SYSTEM_ADMIN_PKG.CRIS_GET_USERINFO_BY_EMAIL_SP(P_USER_EMAIL IN
> VARCHAR2,
>
> P_REF_CURSOR OUT CRIS_GLOBAL_PARMS_PKG.T_REF_CUR);
>
>
>
> Can anyone help me with this?
>
> Thank you.
>
>
>
> Thank you,
>
> James Daly
>
> dalyj@ibacorp.us
>
>