My god sometime I bang my head on the wall to do something simple on oracle. So oracle don’t support limit so to do something simple has :
SELECT f_name, l_name, age from employee_data ORDER BY age DESC LIMIT 20
become :
SELECT f_name, l_name, age FROM ( SELECT f_name, l_name, age, ROWNUM r FROM ( SELECT f_name, l_name, age FROM employee_data ORDER BY age DESC ) WHERE ROWNUM <= 40 ) WHERE r >= 21;
I was trying to update a table that using sql join but I kept getting ORA-00933
I tried the following:
Using A SQL JOIN In A SQL UPDATE Statement Using A SQL JOIN In A SQL UPDATE Statement (www.bennadel.com)
update table using inner join (venushuynh.wordpress.com)
but I kept getting the ORA-00933
then tried this Oracle Update with Join (geekswithblogs.net) but still didn’t work.
After some deep search I’ve found my error in a oracle forum: SQL Error: ORA-01779: cannot modify a column which maps to a non key-preser
Even though it’s not the same ORA error it fixed the problem nonetheless
Here my sql statement that worked for me:
UPDATE mytable m
set m.status=7
where m.id in (select mytable.id from mytable inner join otherTable on mytable.matchid=othertable.matchid where othertable.userid=1234)
Another problem I’ve it recently with saveOrUpdate that can be fix by using the same saveOrUpdate but give the class name.
Using the session.saveOrUpdate(myObject) might give you a unknown entity while using the same method with the class name in parameter won’t.
session.saveOrUpdate(myObject); //give a unknown entity
session.saveOrUpdate("MyObjectClassName", MyObject); //Work
The important difference between the org.hibernate.Session class methods, save & saveOrUpdate is, save generates a new identifier and results in an INSERT query, whereas saveOrUpdate does an INSERT or an UPDATE.
For a more complete summary of what each of these method do check the hibernate site or this stackoverflow question/answer
If you happen to rename your folder that contain your jsp and js, don’t forget to update the struts config (and the tiles). This will save your precious time even hours …
For some reason Oracle have no Boolean
Since there is no BOOLEAN datatype in Oracle, as far as tables are concerned.
CREATE TABLE BooleanTable (MyBool BOOLEAN);
return :
ORA-00902: invalid datatype
But there is a BOOLEAN datatype in PL/SQL.
What should we do instead?
According to Tom at ThinkOracle you could create the table using char like this :
CREATE TABLE BoolTable (MyBool CHAR(1) CHECK (MyBool IN ( 'Y', 'N' )));
In our case (where I used to worked) we used Number instead. Same thing we did with the char but instead of “y/n” we use ‘0’ or ‘1’ where ‘0’ = false and ‘1’ = true
Related:
One of our engineer made some change to the Jmeter script and I couldn’t find what was the error. The test was giving me “Error in TestPlan – see log file”
After some digging I found out there was an ext lib missing.
ERROR - jmeter.save.SaveService: Conversion error com.thoughtworks.xstream.converters.ConversionException: kg.apc.jmeter.threads.SteppingThreadGroup : kg.apc.jmeter.threads.SteppingThreadGroup : kg.apc.jmeter.threads.SteppingThreadGroup : kg.apc.jmeter.threads.SteppingThreadGroup
—- Debugging information —-
message : kg.apc.jmeter.threads.SteppingThreadGroup : kg.apc.jmeter.threads.SteppingThreadGroup cause-exception : com.thoughtworks.xstream.mapper.CannotResolveClassException cause-message : kg.apc.jmeter.threads.SteppingThreadGroup : kg.apc.jmeter.threads.SteppingThreadGroup class : org.apache.jorphan.collections.ListedHashTree required-type : org.apache.jorphan.collections.ListedHashTree path: /jmeterTestPlan/hashTree/hashTree/kg.apc.jmeter.threads.SteppingThreadGroup
Finally this error was due to 2 things
1- We needed to have the same version of jmeter than the other programmer was using (2.5.1 + at the time)
2- We needed a library that I didn’t know about http://jmeter-plugins.googlecode.com/files/JMeterPlugins-0.5.1.zip
When you are handling string you sometime have single quote in your string. I was looking on how to handle that in Oracle and it’s not pretty but you can cover that case with the chr(39) function, 39 being the code for the single quote.
So it you are looking the “it’s” you have to look for “it‘||CHR(39)||’s”
I’ve read I’ve could have use 2 quote or the $ in latter version. In that case:
SELECT 'it''s' name FROM DUAL;
Or use the new (10g+) quoting method
SELECT q'$it's$' NAME FROM DUAL;Note I’ve used the chr(39) and worked fine for me I didn’t try the other one, so good luck with that.
I had in the past had to work with oracle and had to convert it to mysql. Unfortunately (or fortunately) that project was cancel but here are the bit of information I’ve found that I didn’t know about and made me lost a lot of time.
From mySQL Forum:
NUMBER Any NUMERIC
DEC Any NUMERIC
DECIMAL Any NUMERIC
NUMERIC Any NUMERIC
DOUBLE PRECISION Any NUMERIC
FLOAT Any NUMERIC
REAL Any NUMERIC
SMALLINT Any SMALLINT
VARCHAR <256 VARCHAR
VARCHAR2 <256 VARCHAR
CHAR <256 CHAR
VARCHAR2 >255 TEXT
VARCHAR >255 TEXT
CHAR >255 TEXT
LONG <256 VARCHAR
LONGRAW <256 VARCHAR
RAW <256 VARCHAR
LONG >255 TEXT
LONGRAW >255 TEXT
RAW >255 TEXT
DATE – DATETIME (Since DATE in oracle can include time information!)
From what we needed we had to
decimal(50,0) = NUMBER
decimal(22,0) = INTEGER
DATE = datetime
Now that I look at it I think It would have been better to turn them both to number.
The best course of action is to find a DBA and use tool for the job like omega sync like it’s recommended in the following stackoverflow thread.
The to_date function we talked earlier make a come back.
The wrong way to do it is :
SELECT field 1, field2, date_and_time, FROM table1 WHERE date_and_time = '12-04-27'
you end up comparing string in that case to work you need to do the following:
SELECT field 1, field2, date_and_time,
FROM table1
WHERE date_and_time = to_date('12-04-27','yy-mm-dd')
I saw some example where user needed to use the TRUNC function on the date_and_time, so if it doesn’t work with the previous answer try using the TRUNC function.
I was having problem making one of my SQL query work in ORACLE until I realize that I got the check on the date wrong. It seem that Oracle didn’t interpreted my query has I expected when handling a date with a greater or lesser than operator (< or >).
So this won’t work :
date_and_time <= '01-DEC-02'
After some digging I’ve found out that there is
But this will :
to_date('01-DEC-2002','dd-mmm-yyyy');
The
TO_TIMESTAMP can be used to, check the reference for more information: TO_TIMESTAMP reference and TO_DATE Reference
Recent Comments