By default Oracle uses indexes created. When I change to NLS_COMP=Linguistic and NLS_Sort=Binary_CI, I get full table scans. I'd read somewhere that creating an index using (nlssort(name, 'NLS_SORT=BINARY_CI')); Would work. As my attempt below shows, not so much. Even if I force it, the performance does not seem to be what I would e...
When writing a row-level trigger in Oracle, I know that you can use the OLD and NEW pseudo-records to reference the old and new state of the row that fired the trigger. I know that in an INSERT trigger OLD doesn't contain any data, but I'm not sure how this affects the evaluation of a WHEN clause for that trigger. For example, if I have ...
So let's say I have a few million records to pull from in order to generate some reports, and instead of running my reports of the live table, I create a temp where I can then create my indexes and use it for further data extraction. I know cached tables tend to be quicker / faster seeing as the data is stored in memory, but I'm curi...
How would I do in a SELECT query to reverse this path : z/y/x for x/y/z where / is the delimiter and where there can be many delimiters in a single line ex: select (... z/y/x/w/v/u ...) reversed_path from ... ...
I have a small table of measurement units in Oracle (10.2.0.4). It's defined as CREATE TABLE Units ( UNIT_ID number, UNIT varchar2(12) ) It's populated with a few records, and one of those records has a unit value of 'μL'. When I try to query for that record using this query... select * from units where unit = 'μL' .. I g...
Hi everybody, cmd.CommandText = "SELECT alarm_id,definition_description,element_id, TO_CHAR (alarm_datetime, 'YYYY-MM-DD HH24:MI:SS'),severity, problem_text,status FROM aircom.alarms WHERE status = 1 and TO_DATE (alarm_datetime,'DD.MM.YYYY HH24:MI:SS') > TO_DATE ('07.09.2008 09:43:00', 'DD.MM.YYYY HH24:MI:SS') order by ALARM_DATETI...
After some struggle with Oracle Advanced Queuing and dbms_aq package I encountered another issue. I copied code from Oracle tutorials but when I compile this code: create or replace procedure jms_test(msg varchar2) is id pls_integer; message sys.aq$_jms_stream_message; enqueue_options dbms_aq.en...
Im trying to get the standard vim :clist, :cope functionality working with vim. Specifically, I'm trying (and failing) to capture the filename from the compiler output. I have the PL/SQL code compiling okay (well, when there is no errors =), and I've got errorformat picking up the error messages, line numbers and column numbers, but I...
If I have materialized view in Oracle which is defined as REFRESH FAST ON COMMIT every 15 minutes. It works when initially created and refreshes happily. What can cause it to stop fast refreshing? I can see that it has stopped refreshing based on this: select mview_name, last_refresh_date from all_mviews; ...
I have a table with data such as below Group Start Date End Date A 01/01/01 01/03/01 A 01/01/01 01/02/01 A 01/03/01 01/04/01 B 01/01/01 01/01/01 ETC I am looking to produce a view that gives a count for each day, like this Group Date Count A 01/01/01 2 A 01/02/01 2 A 01/03/01 ...
Which operator in oracle gives better performance IN or OR ex: select * from table where y in (1,2,3) or select * from table where y = 1 or y = 2 or y = 3 ...
Hi I have a materialized view of a complex join, which I would like to have some of its columns be searcheable using Oracle Text. I've already created the indicies on the underlying table. Do you I have to create new indicies again for this materialized view? thank you, Joyce ...
Will Oracle flatten multiple between clauses if they have overlapping data? In my application, users can create dynamic search terms, so it's possible that there could be overlapping data. Will Oracle optimize the SQL for me or do I have to calculate it before I create the SQL? i.e. Select id from xxtable WHERE id (BETWEEN 10 AND 20)...
Here's a simplified pseudo-code version of what I'd like to be able to do in PL-SQL (Oracle): DECLARE mylist as ARRAY BEGIN mylist (1) := '1' mylist (2) := '3' ... SELECT * FROM aTable WHERE aKey IN mylist; END; The SELECT should return the matching records for mylist(1), mylist(2) etc. It should be similar to ORing all ...
My database (10gR2) is single-byte (NLS_CHARACTERSET = WE8DEC). I have a Unicode XML file that I would like to parse. If I read the file into a CLOB and try to convert it to an XMLType, Oracle chokes when the XML contains special characters (in this case Norwegian characters such as "øæå"). ORA-31011: XML parsing failed ORA-19202: Erro...
I am hoping to find how I can get the kb size of a result set in OracleDB. I am not an sysadmin, but often run queries that return over 100k rows and I would need to find a way to determine what is the total kb size. thank you ...
I need to send a properly formatted date comparison WHERE clause to a program on the command line in bash. Once it gets inside the called program, the WHERE clause should be valid for Oracle, and should look exactly like this: highwater>TO_DATE('11-Sep-2009', 'DD-MON-YYYY') The date value is in a variable. I've tried a variety o...
In Oracle 10g, I'd like to create a regular expression to list the characters that are different between two strings. Here is the reason : I have a table with a field that contains sometimes Unicode characters that are not in the french language. I am able to list the rows containing these non standards characters to make a future c...
Hi, I require a means of checking to see if a string has the following exact pattern within it, i.e.: (P) Examples where this would be true is: 'Test System (P)' Unsure though how to check for cases when the string that doesn't have '(P)', i.e: 'Test System (GUI for Prof)' - in this case, this would be false but I am using REGEXP_...
Hi, insert into XYZ(col1, col2) values (1,2) update XYZ set ... where col1 = 1 COMMIT As in can see in the above code, we havent yet commited our insert statement, and we performed an update operation on the same row, and finally we commit the whole batch. What exactly would happen in this case? Are there any chances of loosing data ...