---- [email protected] wrote: > > ---- 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!! >
Hi, I'm assuming that the parameter setting I mentioned above was what you meant, because I just tried setting that to "0" and ran several test runs, and sure enough, that seemed to get rid of most of the errors. There is still one error occurrence that seems to happen only on the last request, which I don't quite understand. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
