I am getting ORA-02049 occasionally for some long-running and/or intensive transactions. There is seemingly no pattern to this, but it happens on a simple INSERT. I have no clue how to get any sort …
Tag: oracle
How does contains() in PL-SQL work?
Have a lot of unnecessary results using contains() method in my query. Don’t tell me to use like or something else. It is hardcoded and couldn’t be changed. Answer Contains is used on text fields that have a ‘CONTEXT Index’, which indexes a text field for searching. The standard usage …
Oracle: ‘= ANY()’ vs. ‘IN ()’
I just stumbled upon something in ORACLE SQL (not sure if it’s in others), that I am curious about. I am asking here as a wiki, since it’s hard to try to search symbols in google… I just found that when checking a value against a set of values you can do As opposed to the usual So I’m …
Benefits Of Using SQL Ordinal Position Notation?
Background Information Ordinal position notation, AKA ordinals, is column shorthand based on the column order in the list of columns in the SELECT clause, instead of either the column name or column alias. Commonly supported in the ORDER BY clause, some databases (MySQL 3.23+, PostgreSQL 8.0+) support the syn…
SELECT INTO using Oracle
I’m trying to do a SELECT INTO using Oracle. My query is: But I get the following error: Any ideas what’s wrong? The Standard behavior of the above should be as I originally thought: However Oracle implemented it totally differently in their own dialect of SQL Oracle Docs on Insert … Select …
How to select columns from a table which have non null values?
I have a table containing hundreds of columns many of which are null, and I would like have my select statement so that only those columns containing a value are returned. It would help me analyze data better. Something like: Select (non null columns) from tablename; I want to select all columns which have at…
Variables in TOAD scripts
I have a SQL script that is being executed in TOAD. Currently, I have it laid out with just statement after statement, thusly: And so on. Some of the where clauses end up being repetitive because I have complex inner queries to get particular IDs to operate on. I’d like to capture the ID in the beginnin…
Force index use in Oracle
I encountered this question in an interview and had no clue how to answer: There is a table which has a index on a column, and you query: The query takes too long, and you find out that the index is not being used. If you think the performance of the query will be better using the index, how could
Displaying the constraints in a table
Hello I am trying to display the constraints in one of my tables but for some reason I get the message no rows selected. Noted below is the table I have created. This is the code I am using to show my constraints. I am a rookie so I want to make sure I understand what is wrong. I have
Convert timestamp/date time from UTC to EST Oracle SQL
I have a field with a date/time value like this: It’s in UTC. In the query how can I convert this to EST? I’m trying something like this but it throws an error. Answer I had to tweak it slightly to get it to work on my database, but this worked: The key is the “at time zone” syntax.