I have a a cursor loop that's building a string by concatenating the contents of a table together, using code along these lines: OPEN cur_t; LOOP FETCH cur_t INTO v_texttoadd; v_string := v_string || v_texttoadd; EXIT WHEN cur_t%notfound; END LOOP; The problem is, of course, that the last item gets added twice because the sys...
Hi all, I have a situation where I need to transfer Oracle 9i archive logs to an Oracle 10g database, from where they are to be mined by a log-miner and then used by an Oracle streams capture/apply processes. (Oracle 9 archive logs can be read by the Oracle 10 logminer - I can manually copy the archive logs across, manually register th...
Is there a way to extract timezone information directly from an oracle.sql.TIMESTAMPTZ object (selected from a TIMESTAMP WITH TIME ZONE column)? Ideally, I'd like to be able to pull the time zone information directly out of the object without jumping through potentially expensive or fragile hoops (like converting things into strings and...
I am using Perl to collect data from several logfiles and store it into an Oracle database on the same Windows 2003 host I am running my script on. Is it better to use the Perl DBI module or to set up a system DSN and use ODBC? Thank you ahead of time! Michael ...
I wish to create an external table in an Oracle database, retrieving its data from a flat file on the server. The format of this file is non-trivial. Each line in this file can be one of several different layouts, depending on the line's prefix (the prefix itself is always a fixed length). For example, a line beginning with 'TYPE1' would...
Is there a way to detect missing command line parameters from the oracle script for example my oracle script test.sql expects 3 parameters to run. my script is executed from the install program like this sqlplus /NOLOG @test.sql userid pw tablespace When one of the parameters is missing, the sqlplus prompting for the userinput. This can...
Hi, I have a oracle table with one of the column as long raw type. I know that we cannot use sql to get the data from that column. Can any one tell me how to get all the data from that column? Thanks in advance ...
In Oracle, what is the the default ordering of rows for a select query if no "order by" clause is specified. Is it the order in which the rows were inserted there is no default ordering at all none of the above. ...
Has anyone had any luck in getting an Oracle client to install and work with Windows 7 RC? I went and upgraded from Vista to Windows 7 but having terrible problems getting my Oracle databases to work... ...
Hi, At work I have an app sitting on Oracle 7.3 (old stuff, yes). Recently I tried to create an account that would be able to SELECT on all tables, but nothing else i.e. no updates or deletes. But whenever I create a new account it already has access to all application tables. I even stripped it down to CREATE SESSION only. This didn't ...
How can I work around the Oracle's limitation of not allowing subqueries in triggers. Here's an example trigger I'm trying to create, but am unable to because I can't use a subquery. CREATE OR REPLACE TRIGGER trigger_w_subquery AFTER UPDATE OR INSERT ON project_archiving FOR EACH ROW WHEN (old.archiving_status <> new.archiving_status ...
Hi, The query below is perfectly functional, and queries a single table to find the last 50 usernames added via a sequential userid number column. Logic so far is to: find out the highest userid; subtract 50 from that; pull usernames back where greater. However, it doesn't look elegant, and uses two subqueries to achieve it's goal: ...
I have a simple web service that uses an oracle database. When I test the service internally it works fine, however, calling the web service through my client (on the same machine but in a different WAR) throws an invocationtargetexception. I've finally discovered it's an issue with instantiating the OracleDriver. It doesn't throw any...
Hi folks, I'm trying to get better performance out of this Oracle query (which is terribly slow). I'm an Oracle beginner so maybe someone can point out a better way to approach this query. I have a table with information on different families. I want to extract the different relationships based on a relationship type. The solution I cam...
Hi, I want to learn more on how to build CEP based applications. So I looked around and found several products (overview found here: http://rulecore.com/CEPblog/?page_id=47). But as there are quite a few at the moment, I don't know which is the best to start with. And overall I just would consider the one available for free. The rest i...
Hi all, I'm using (have to) a badly designed Oracle(10) DB, for which I don't have admin rights (although I can create tables, triggers, etc in my scheme). Now I had run into a problem: this DB connected with several users/programs. I must find out who updates a certain row, when, and if possible: with what kind of statement. Is it pos...
This call - SELECT ((TO_DATE ('06/06/2009 16:00:00', 'DD/MM/YYYY HH24:MI:SS') - TO_DATE ('06/06/2009 14:25:00', 'DD/MM/YYYY HH24:MI:SS')) * 1440.000) from dual Produces a result of 95 - which is what you would expect from the calculation. This call - SELECT trunc((TO_DATE ('06/06/2009 16:00:00', 'DD/MM/YYYY HH24:MI:SS') - TO_DATE ...
If I have multiple Oracle clients installed on a given workstation (i.e. Oracle 9 and Oracle 10), how do I specify which one is used by ADO? Is there a way to swap between the two clients? I am currently using the Oracle Provide - oraoledb, not the Microsoft provider. ...
I have a java-application using JDBC for database interaction. I want to do a search based on a series of regular-expressions, however the application should be generic, we do not know if the database-engine will by mysql,oracle,sql server etc, but we are pretty sure it's gonna be either mysql or oracle. Will regular-expressions limit m...
I am working on an API to query a database server (Oracle in my case) to retrieve massive amount of data. (This is actually a layer on top of JDBC.) The API I created tries to limit as much as possible the loading of every queried information into memory. I mean that I prefer to iterate over the result set and process the returned row o...