On 27 May 2014 03:09, <oh...@cox.net> wrote:
>
> ---- oh...@cox.net wrote:
>>
>> ---- sebb <seb...@gmail.com> wrote:
>> > On 26 May 2014 22:09, <oh...@cox.net> wrote:
>> > >
>> > > ---- oh...@cox.net wrote:
>> > >>
>> > >> ---- sebb <seb...@gmail.com> wrote:
>> > >> > On 25 May 2014 18:19, <oh...@cox.net> wrote:
>> > >> > > Hi,
>> > >> > >
>> > >> > > I am trying to use Jmeter 2.11 to load test against Oracle database.
>> > >> > >
>> > >> > > I can use a SELECT query in the JDBC Request and that works fine,
>> > >> > > but when I try the following:
>> > >> > >
>> > >> > > exec DBMS_SESSION.set_identifier('frank')
>> > >> > >
>> > >> > > I get:
>> > >> > >
>> > >> > > Response message: java.sql.SQLException: ORA-00900: invalid SQL
>> > >> > > statement
>> > >> > >
>> > >> > > However, if I run that EXEC command using sqlplus, it works fine.
>> > >> > >
>> > >> > > Does Jmeter not allow using the EXEC command in a JDBC request?
>> > >> >
>> > >> > An EXEC command is not a Select statement; it is not in fact standard
>> > >> > SQL.
>> > >> >
>> > >> > However, according to
>> > >> >
>> > >> > http://docs.oracle.com/cd/E11882_01/java.112/e16548/getsta.htm#JJDBC28075
>> > >> >
>> > >> > the Oracle JDBC driver supports them using the syntax
>> > >> >
>> > >> > {call DBMS_SESSION.set_identifier('frank')}
>> > >> >
>> > >> > This needs to be invoked in JMeter using the Callable Statement
>> > >> > option.
>> > >> >
>> > >> > > I tried putting this in a BEGIN...END:
>> > >> > >
>> > >> > > BEGIN
>> > >> > > exec DBMS_SESSION.set_identifier('frank');
>> > >> > > select * from employees;
>> > >> > > END;
>> > >> > >
>> > >> > > but then I got:
>> > >> > >
>> > >> > > Response message: java.sql.SQLException: ORA-06550: line 2, column
>> > >> > > 6:
>> > >> > > PLS-00103: Encountered the symbol "DBMS_SESSION" when expecting one
>> > >> > > of the following:
>> > >> > >
>> > >> > > := . ( @ % ;
>> > >> > > The symbol ":=" was substituted for "DBMS_SESSION" to continue.
>> > >> > >
>> > >> > > Is there a way that this can be done with Jmeter?
>> > >> > >
>> > >> > >
>> > >> > > ---------------------------------------------------------------------
>> > >> > > To unsubscribe, e-mail: user-unsubscr...@jmeter.apache.org
>> > >> > > For additional commands, e-mail: user-h...@jmeter.apache.org
>> > >> > >
>> > >> >
>> > >> > ---------------------------------------------------------------------
>> > >> > To unsubscribe, e-mail: user-unsubscr...@jmeter.apache.org
>> > >> > For additional commands, e-mail: user-h...@jmeter.apache.org
>> > >> >
>> > >>
>> > >>
>> > >> As you suggested, I tried as a Callable Statement:
>> > >>
>> > >> {call DBMS_SESSION.set_identifier('jim')}
>> > >>
>> > >> and got:
>> > >>
>> > >> Thread Name: Thread Group 1-1
>> > >> Sample Start: 2014-05-26 00:43:16 EDT
>> > >> Load time: 3
>> > >> Latency: 1
>> > >> Size in bytes: 40
>> > >> Headers size in bytes: 0
>> > >> Body size in bytes: 40
>> > >> Sample Count: 1
>> > >> Error Count: 1
>> > >> Response code: 72000 1006
>> > >> Response message: java.sql.SQLException: ORA-01006: bind variable does
>> > >> not exist
>> > >>
>> > >>
>> > >> Response headers:
>> > >> oracle.jdbc.driver.OracleConnection@6735fad1
>> > >>
>> > >> SampleResult fields:
>> > >> ContentType: text/plain
>> > >> DataEncoding: UTF-8
>> > >>
>> > >> Actually, for the test, I really need to do the
>> > >> DBMS_SESSION.set_identifier, followed by a "select * from employees".
>> > >>
>> > >> Can I do those in the JDBC Request with the Query Type set to Callable
>> > >> Statement, and if so, how?
>> > >>
>> > >> Thanks,
>> > >> Jim
>> > >
>> > >
>> > > Hi,
>> > >
>> > > I was able to get JUST the DBMS_SESSION.set_identifier to work in the
>> > > JDBC request by using a variable instead of literal:
>> > >
>> > > CALL DBMS_SESSION.set_identifier(:xyz)
>> > >
>> > > However, as mentioned earlier, I'd like to do the
>> > > DBMS_SESSION.set_identifier and then a SELECT, e.g.:
>> > >
>> > > CALL DBMS_SESSION.set_identifier(:xyz)
>> > > select * from employee
>> > >
>> > > However, when I try that in Jmeter, I get:
>> > >
>> > > Response message: java.sql.SQLException: ORA-00933: SQL command not
>> > > properly ended
>> > >
>> > > I've tried added semi-colon etc to the end, but that didn't work either.
>> >
>> > The CALL and SELECT statements have to be done in separate samplers.
>>
>>
>> Hi,
>>
>> Thanks! That works now :)!
>>
>>
>
> Hi,
>
> Actually, I'm not sure if it "works" now. Let me explain:
>
> As we've been discussing, I'm now using 2 JDBC request samplers.
>
> The 1st one does the:
>
> CALL DBMS_SESSION.set_identifier('joe')
>
> That is suppose to set the string "joe" into the Oracle USERENV Context
> CLIENT_IDENTIFIER
>
> Then, the 2nd sample does the:
>
> SELECT * from EMPLOYEES
>
> Now, I have a PL/SQL function "attached" to the EMPLOYEES table via what
> Oracle calls their "Virtual private database" or "VPD" functionality. my
> function gets triggered whenever a SELECT is done on the EMPLOYEES table.
>
> My function is SUPPOSE to get the user name ("joe") from the USERENV Context
> CLIENT_IDENTIFIER and then do some checking based on the username, but, for
> some reason, when i do load testing with the above 2 samplers, about 2% of
> the time I am seeing errors in Jmeter.
>
> I don't know what's causing the error (the only thing I see in Jmeter is that
> there was an error), but one possibility is that the string ("joe") that is
> being set by the 1st sampler is not making it through to the 2nd sampler,
> i.e., when the 2nd sampler executes the SELECT, the USERENV CLIENT_IDENTIFIER
> is, for example, empty, instead of having "joe" in it.
>
> Is that possible that when there're 2 samplers in the test plan, that the 2nd
> sampler might not be getting what's set by the 1st sampler?
>
> The reason that I suspect (and it's only one possible suspicion at this
> point) is that if I hard-code "joe" into the PL/SQL function instead of it
> pulling the name from the CLIENT_IDENTIFIER, then I always get 0% errors in
> Jmeter.
>
> Is there some mechanism to guarantee that the 2 samplers in the test plan run
> sequentially?
Samplers always run sequentially within a thread.
However if you configure a connection pool, there is no guarantee that
the samplers will use the same connection, so ensure that the JDBC
config uses Max Number of Connections = 0
> Thanks,
> Jim
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscr...@jmeter.apache.org
For additional commands, e-mail: user-h...@jmeter.apache.org
