---- sebb <[email protected]> wrote: > On 27 May 2014 03:09, <[email protected]> wrote: > > > > ---- [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? > > 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 > \
Hi, FYI, it took quite a lot of doing with some tricky debugging and writing to the Oracle trace files, but I've confirmed that the problem is that intermittently, the CLIENT_IDENTIFIER coming into my function is empty, so I'm like 100% sure that the value is not being set by the CALL DBMS_SESSION.set_identifier in sequence, at least some of the time. Did you mean under the JDBC Connection Configuration ==>Connection Pool Configuration ==> Max Number of Connections, and to set that to 0? That is currently "10" now, by default I guess? Thanks for your help!! Jim --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
