---- [email protected] wrote:
>
> ---- sebb <[email protected]> wrote:
> > On 26 May 2014 22:09, <[email protected]> wrote:
> > >
> > > ---- [email protected] wrote:
> > >>
> > >> ---- sebb <[email protected]> wrote:
> > >> > On 25 May 2014 18:19, <[email protected]> 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: [email protected]
> > >> > > For additional commands, e-mail: [email protected]
> > >> > >
> > >> >
> > >> > ---------------------------------------------------------------------
> > >> > To unsubscribe, e-mail: [email protected]
> > >> > For additional commands, e-mail: [email protected]
> > >> >
> > >>
> > >>
> > >> 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?
Thanks,
Jim
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
