We want to use a ctxsys.context index type for full text search. But I was quite surprised, that an index of this type is not automatically updated. We have 3 million documents with about 10k updates/inserts/deletes per day. What are your recommendations for syncing and optimizing an Oracle Text index? ...
with account as ( select 'client1' as client, to_date('09.2009' ,'MM.YYYY') as months, '09_1' as bill_num, 100 as BF_sum, 400 as Payed_SUM from dual union select 'client1' as client, to_date('09.2009' ,'MM.YYYY') as months, '09_2' as bill_num, 150 as BF_sum, 50 as Payed_SUM from dual union select 'client1' as ...
I have apache tomcat 5.5.28 on my windows box and I am trying to deploy a web application (WAR) which works fine on other servers. However I am having trouble creating a datasource. I am not sure of the format. The db is oracle. Here is what I have in server.xml. <GlobalNamingResources> <Environment name="simpleValue" ...
Hi, using CFMX7 and Oracle 10g ent on a query-intensive and active web site, I'm having a problem that some of the Oracle connections in my web server connection pool are accumulating open cursors. (In JDBC parlance this might be called a ResultSet object leak.) This is a confusing situation in Oracle; read here for an explanation. htt...
We have an Oracle database server specifically for our unit tests to run against. Is there a way to tune Oracle specifically for this kind of purpose? As the data is constantly being thrown away (since it's just test data). I wonder if there is a way to have an Oracle database in-memory and connect without the TCP/IP stack perhaps to ...
I've got a query joining several tables and returning quite a few columns. An indexed column of another table references the PK of one of these joined tables. Now I would like to add another column to the query that states if at least one row with that ID exists in the new table. So if I have one of the old tables ID 1 2 3 and th...
Does anyone have any frameworks/apps/methodologies for creating Unit tests with Oracle?. I'm using Oracle 11g. I also have Team Foundation Server installed, where we do all our other development work with Visual Studio on a web app. Edit: I'm looking to run unit tests on Packages, Procedures and Functions within Oracle (PL/SQL) ...
I want to write a PLSQL stored procedure that accepts a table name as argument. This table is source table. Now inside my procedure i want to manipulate the fields of that table. EX: I want to insert the records of this source table into another target table whose name is XYZ_<source table name>. The column names for source and targ...
I'm importing a database dump from one Oracle 10g installation into another. The source has a layout with several tablespaces. The target has one default tablespace for the user I'm importing the dump into. Everything works fine, for ordinary tables. The tables are relocated from their original tablespace to the user's default. The prob...
Hello, I need to find a string in the source code (DDL) for all stored procedures in Oracle schema. I use this query to perform the task, but I think can be improved SELECT T0.OBJECT_NAME FROM USER_PROCEDURES T0 WHERE T0.OBJECT_TYPE='PROCEDURE' AND INSTR( (SELECT DBMS_METADATA.GET_DDL('PROCEDURE',T0.OBJECT_NAME,'MySCHEMA') ...
Hi there any way to capture the objects (such as table and column) where an exception was thrown in ORACLE? I need to determine the object name to customize the error message showing the user the name of the table and field where the exception occurred. I know there is a variable SQLCODE and SQLERRM, but I wonder if there is any additi...
It so happens that I have to get a date from a varchar2 column in oracle, but it is inconsistent in formatting. Some fields might have '2009.12.31', others '2009/12/32 00:00:00'. Is there any standard construct I could use, so that I don't have to go through begin to_date(date, mask1) exception begin to_date(date,mask2) except...
I am able to get a list of Campaigns and filter on those to get only the campaigns I want. How do I get a list of Contacts for 1 specific campaign? I have the web service classes in my project but can't see any way of relating to the Child objects of the Campaign object. Documentation is awful, none of the examples had working code a...
After many Google and SO searches, I cannot find a definitive answer to this simple question: How can I define a procedure inside of another procedure to use? I know that there are nested blocks and nested procedures, but I haven't seen the exact syntax for what I want. i.e. create or replace PROCEDURE TOP_PROCEDURE (...) IS -- nested...
Hi all, I have a simple table based queue system. In its simplest form, it consist of an id, a queue name, and a status. When reading the next message from a given queue, we need to ensure FIFO (first in first out), i.e. the lowest id from the given queue with the given status. This all works fine with some thousand rows, but when we re...
I'm trying to spot-test an Oracle backed database with hsqldb and dbunit, but I've run into a snag. The problem is with the following EJB-QL (simplified a bit): SELECT o FROM Offer o WHERE :nowTime BETWEEN o.startDate AND o.startDate + 7 This seems to only work in Oracle's version of SQL. What's the easiest way for me to conver...
Hi I have a requirement like this: I have to select a certain number of records from an Oracle table using a stored procedure. I will capture the entire resultset in ref cursors, but at the same time I have to update a flag in the selected records ( the ones stored in ref cursors). So I want to know whether this is possible in stored ...
Having used an Oracle DB for a while, I got used to using the Oracle DB Application Context to implement access restrictions. To simply put it, upon login a procedure is called that puts the user details into the Oracle Application context like so: DBMS_SESSION.SET_CONTEXT('context_name', 'user_id', user_id); Then access can be enforc...
The title pretty much says it all. I'm trying to run an Oracle Lite application using a Windows Mobile 6.1 device. I keep getting "invalid database or password" errors. (It's not the password...) The device I'm using requires 6.1, so I haven't been able to try with 5.0 or 6.0. Has anyone gotten this combination to work? If so HOW? ...
Hello my friends, I want to use the fact that on two tables t1 and t2 I can make an inner join with on t1.colum1>t2.colum2 to calculate the maximum drawdown of a return vector. The problem is that an inner join is only possible with two stored databases or tables and I wanted to do it selecting just a part of the tables. Is there any ot...