One additional idea, mentioned here is to use a regular expression to check: SELECT foo FROM bar WHERE REGEXP_LIKE (foo,’^[[:digit:]]+$’); The nice part is you do not need a separate PL/SQL function. The potentially problematic part is that a regular expression may not be the most efficient method for a large number of rows.
Total cursors open, by session: select a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name=”opened cursors current”; Source: http://www.orafaq.com/node/758 As far as I know queries on v$ views are based on pseudo-tables (“x$” tables) that point directly to the relevant portions of the SGA, so … Read more
SELECT CASE WHEN age <= 10 THEN ‘1-10′ WHEN age <= 20 THEN ’11-20′ ELSE ’21+’ END AS age, COUNT(*) AS n FROM age GROUP BY CASE WHEN age <= 10 THEN ‘1-10′ WHEN age <= 20 THEN ’11-20′ ELSE ’21+’ END
ORA-00904-invalid identifier errors are frequently caused by case-sensitivity issues. Normally, Oracle tables and columns are not case sensitive and cannot contain punctuation marks and spaces. But if you use double quotes to create a quoted identifier, that identifier must always be referenced with double quotes and with the correct case. For example: create table bad_design(“goodLuckSelectingThisColumn … Read more
Flushing the shared pool should do it, but Tom Kyte lists a couple reasons below why you may not get the result you are expecting in some cases: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6349391411093
Remove the TO_DATE in the WHERE clause TO_DATE (alarm_datetime,’DD.MM.YYYY HH24:MI:SS’) and change the code to alarm_datetime The error comes from to_date conversion of a date column. Added Explanation: Oracle converts your alarm_datetime into a string using its nls depended date format. After this it calls to_date with your provided date mask. This throws the exception.
The reason for this error is that SQL SELECT statements are logically * processed in the following order: FROM: selection of one table or many JOINed ones and all rows combinations that match the ON conditions. WHERE: conditions are evaluated and rows that do not match are removed. GROUP BY: rows are grouped (and every … Read more
This is a simple way to do it without any triggers or sequences: insert into WORKQUEUE (ID, facilitycode, workaction, description) values ((select max(ID)+1 from WORKQUEUE), ‘J’, ‘II’, ‘TESTVALUES’) It worked for me but would not work with an empty table, I guess.
Pressing ctrl+shift+N will open a new unshared worksheet that can run queries in parallel. In that case you have to paste a query in each tab and run them manually though, but it is handy when just testing a few queries.
It means the connection was successfully established at some point, but when you tried to commit right there, the connection was no longer open. The parameters you mentioned sound like connection pool settings. If so, they’re unrelated to this problem. The most likely cause is a firewall between you and the database that is killing … Read more